目 录CONTENT

文章目录

MySQL日志管理

简中仙
2020-10-14 / 0 评论 / 0 点赞 / 72 阅读 / 0 字 / 正在检测是否收录...
温馨提示:
本文最后更新于2023-11-06,若内容或图片失效,请留言反馈。 本文如有错误或者侵权的地方,欢迎您批评指正!

一、错误日志

1、作用

排查MySQL运行过程的故障

2、默认路径和名字

datadir/主机名.err
# pwd mysql-1.err        //路径是初始化时指定的数据存储位置
/data/mysql/data

3、自定义配置

# vim /etc/my.cnf
#服务端配置
[mysqld]
#用户
user=mysql
#软件安装目录
basedir=/usr/local/mysql
#数据路径
datadir=/data/mysql/data
#socket文件位置
socket=/tmp/mysql.sock
#服务器ID号
server_id=6
#端口号
port=3306
#日志位置(有相关权限)
log_error=/tmp/mysql3306.log
[mysql]
#socket文件位置
socket=/tmp/mysql.sock
# systemctl restart mysqld

二、二进制日志(binlog)

1、简介

MySQL Binlog(MySQL Binary Log,MySQL的二进制日志文件),它记录了所有的 DDLDML 语句(除了数据查询语句select、show等),以事件形式记录,还包含语句所执行的消耗的时间。MySQL的二进制日志是事务安全型的,并以二进制的形式保存在磁盘中;

作用

  • 查看数据库的变更历史
  • 数据库增量备份和恢复
  • MySQL的复制(主从数据库的复制)

2、Binglog日志格式

1、STATEMENT格式:基于SQL语句的复制

  • 每一条会修改数据的sql都会记录在binlog中。

  • MySQL 5.7.7 之前,默认的格式是 STATEMENT

  • 优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。

  • 缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题。

  • 注意:相比row能节约多少性能与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的IO性能问题。

2、ROW格式:基于行的复制

  • 5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息,仅保存哪条记录被修改。
  • MySQL 5.7.7 及更高版本中,默认值是 ROW
  • 优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题.
  • 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容。
  • 注意:新版本的MySQL中对row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。

3、MIXED格式:混合模式复制

  • 从5.1.8版本开始,MySQL提供了Mixed格式,实际上就是Statement与Row的结合。

  • 在Mixed模式下,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。

3、Binlog日志文件

  • 二进制日志索引文件(文件名后缀为.index)用于记录所有有效的的二进制文件
  • 二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML语句事件

binlog是一个二进制文件集合,每个binlog文件以一个4字节的魔数开头,接着是一组Events:

  • 魔数:0xfe62696e对应的是0xfebin;
  • Event:每个Event包含header和data两个部分;header提供了Event的创建时间,哪个服务器等信息,data部分提供的是针对该Event的具体信息,如具体数据的修改;
  • 第一个Event用于描述binlog文件的格式版本,这个格式就是event写入binlog文件的格式;
  • 其余的Event按照第一个Event的格式版本写入;
  • 最后一个Event用于说明下一个binlog文件;
  • binlog的索引文件是一个文本文件,其中内容为当前的binlog文件列表

4、Binlog事件类型

binlog 事件的结构主要有3个版本:

Binlog 版本MySQL版本
1MySQL 3.23 - < 4.0.0支持 "statement based replication events"
2MySQL 4.0.0 - 4.0.1
3MySQL 4.0.2 - < 5.0.0added the relay logs and changed the meaning of the log position
4MySQL 5.0.0+added the FORMAT_DESCRIPTION_EVENT and made the protocol extensible

v4版本的binlog事件类型:

Hex事件类型说明
0x00UNKNOWN_EVENT此事件从不会被触发,也不会被写入binlog中;发生在当读取binlog时,不能被识别其他任何事件,那被视为UNKNOWN_EVENT
0x01START_EVENT_V3每个binlog文件开始的时候写入的事件,此事件被用在MySQL3.23 – 4.1,MYSQL5.0以后已经被 FORMAT_DESCRIPTION_EVENT 取代
0x02QUERY_EVENT执行更新语句时会生成此事件,包括:create,insert,update,delete;
0x03STOP_EVENT当mysqld停止时生成此事件
0x04ROTATE_EVENT当mysqld切换到新的binlog文件生成此事件,切换到新的binlog文件可以通过执行flush logs命令或者binlog文件大于 max_binlog_size 参数配置的大小;
0x05INTVAR_EVENT当sql语句中使用了AUTO_INCREMENT的字段或者LAST_INSERT_ID()函数;此事件没有被用在binlog_format为ROW模式的情况下
0x06LOAD_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL 3.23版本中使用
0x07SLAVE_EVENT未使用
0x08CREATE_FILE_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0和4.1版本中使用
0x09APPEND_BLOCK_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0版本中使用
0x0aEXEC_LOAD_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0和4.1版本中使用
0x0bDELETE_FILE_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0版本中使用
0x0cNEW_LOAD_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL4.0和4.1版本中使用
0x0dRAND_EVENT执行包含RAND()函数的语句产生此事件,此事件没有被用在binlog_format为ROW模式的情况下
0x0eUSER_VAR_EVENT执行包含了用户变量的语句产生此事件,此事件没有被用在binlog_format为ROW模式的情况下
0x0fFORMAT_DESCRIPTION_EVENT描述事件,被写在每个binlog文件的开始位置,用在MySQL5.0以后的版本中,代替了START_EVENT_V3
0x10XID_EVENT支持XA的存储引擎才有,本地测试的数据库存储引擎是innodb,所有上面出现了XID_EVENT;innodb事务提交产生了QUERY_EVENT的BEGIN声明,QUERY_EVENT以及COMMIT声明,如果是myIsam存储引擎也会有BEGIN和COMMIT声明,只是COMMIT类型不是XID_EVENT
0x11BEGIN_LOAD_QUERY_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL5.0版本中使用
0x12EXECUTE_LOAD_QUERY_EVENT执行LOAD DATA INFILE 语句时产生此事件,在MySQL5.0版本中使用
0x13TABLE_MAP_EVENT用在binlog_format为ROW模式下,将表的定义映射到一个数字,在行操作事件之前记录(包括:WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT)
PRE_GA_WRITE_ROWS_EVENT已过期,被 WRITE_ROWS_EVENT 代替
PRE_GA_UPDATE_ROWS_EVENT已过期,被 UPDATE_ROWS_EVENT 代替
PRE_GA_DELETE_ROWS_EVENT已过期,被 DELETE_ROWS_EVENT 代替
0x14WRITE_ROWS_EVENT用在binlog_format为ROW模式下,对应 insert 操作
0x15UPDATE_ROWS_EVENT用在binlog_format为ROW模式下,对应 update 操作
0x16DELETE_ROWS_EVENT用在binlog_format为ROW模式下,对应 delete 操作
0x1aINCIDENT_EVENT主服务器发生了不正常的事件,通知从服务器并告知可能会导致数据处于不一致的状态
0x1bHEARTBEAT_LOG_EVENT主服务器告诉从服务器,主服务器还活着,不写入到日志文件中

5、Binlog事件结构

一个事件对象分为事件头和事件体,事件的结构如下:

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

如果事件头的长度是 x 字节,那么事件体的长度为 (event_length - x) 字节;设事件体中 fixed part 的长度为 y 字节,那么 variable part 的长度为 (event_length - (x + y)) 字节

6、开启配置

# vim /etc/my.cnf
#服务端配置
[mysqld]
log-bin=/data/mysql/logs/binlogs/mysql-bin.log
expire-logs-days=14
max-binlog-size=500M
server-id=1
binlog_format=ROW
binlog_row_image=FULL
relay_log_info_repository=TABLE

验证

show variables like '%binlog%'
# 检查MySQL是否已经开启binlog
show variables like 'log_bin'

# 查看binlog文件列表及大小
show binary logs

# 查看binlog内容
show binlog events

# 查看当前最新一个binlog日志文件的状态信息,显示正在写入的二进制文件,及当前position
show master status;

#查看所有binlog日志列表
show master logs;

7、二进制日志记录内容

语句类型记录内容区别
DDL以语句的方式,原模原样的记录
DCL以语句的方式,原模原样的记录
DML已提交的事务,记录格式(statement,row,mixed),通过binlog_format=row参数控制
statementSBR,语句模式记录日志,做什么命令,记录什么命令可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况
rowRBR,行模式,数据行的变化可读性较弱,对于范围操作日志大,不会出现记录错误,高可用环境中的新特性要依赖于RBR
mixedMBR,混合模式

8、二进制日志管理

1、查看二进制日志位置

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.06 sec)

2、查看所有已存在的二进制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

3、查看正在使用的二进制日志

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4、查看二进制日志事件

mysql> create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use binlog
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      758 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |
| mysql-bin.000002 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 219 | Query          |         6 |         335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid |         6 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 400 | Query          |         6 |         501 | use `binlog`; create table t1(id int)  |
| mysql-bin.000002 | 501 | Anonymous_Gtid |         6 |         566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 566 | Query          |         6 |         640 | BEGIN                                  |
| mysql-bin.000002 | 640 | Table_map      |         6 |         687 | table_id: 108 (binlog.t1)              |
| mysql-bin.000002 | 687 | Write_rows     |         6 |         727 | table_id: 108 flags: STMT_END_F        |
| mysql-bin.000002 | 727 | Xid            |         6 |         758 | COMMIT /* xid=13 */                    |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
11 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000002' limit 6;
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |
| mysql-bin.000002 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 219 | Query          |         6 |         335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid |         6 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 400 | Query          |         6 |         501 | use `binlog`; create table t1(id int)  |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
6 rows in set (0.00 sec)

5、查看二进制日志内容

# cd /data/binlog/
# ll
总用量 12
-rw-r----- 1 mysql mysql 201 10月 12 21:54 mysql-bin.000001
-rw-r----- 1 mysql mysql 758 10月 12 21:54 mysql-bin.000002
-rw-r----- 1 mysql mysql  60 10月 12 21:54 mysql-bin.index
# mysqlbinlog mysql-bin.000002
#  mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002

6、截取二进制日志

mysql> show binlog events in 'mysql-bin.000002' limit 6;
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |
| mysql-bin.000002 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 219 | Query          |         6 |         335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid |         6 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 400 | Query          |         6 |         501 | use `binlog`; create table t1(id int)  |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
6 rows in set (0.00 sec)

# mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000002 >/tmp/a.sql

7、二进制日志清理

1、自动清理
mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 0     |日志永不过期
+--------------------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 15    |至少1轮全备周期长度的过期时间
+--------------------------------+-------+
2 rows in set (0.00 sec)

需要立即清除需要binlog大小超过max_binlog_size,然后执行命令生成一个新的binlog文件和新的relaylog文件

mysql> show variables like '%max_binlog%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| max_binlog_cache_size      | 18446744073709547520 |
| max_binlog_size            | 1073741824           |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
3 rows in set (0.00 sec)

mysql> flush logs;
2、手动清理

将mysql-bin.000032之前的binlog清理掉

mysql> PURGE BINARY LOGS TO 'mysql-bin.000032';

将指定时间之前的binlog清理掉

mysql> PURGE BINARY LOGS BEFORE '2020-10-14 22:46:26';

清理所有日志

mysql> reset master ;

9、binlog的gtid记录模式的管理

1、GTID介绍

server-uuid:TID

# cd /data/mysql/data/
# cat auto.cnf 
[auto]
server_uuid=7d01bcc5-e854-11ea-9c8f-000c29bc0487

查看GTID参数

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |是否启用强制GTID一致性
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |是否开启gtid复制模式
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

2、GTID的幂等性

如果拿有GTID的日志去恢复时,会检查当前系统中是否有相同GTID号,有相同的就自动跳过,会影响到binlog恢复和主从复制。

3、GTID的开启和配置

# vim /etc/my.cnf
#服务端配置
[mysqld]
#用户
user=mysql
#软件安装目录
basedir=/usr/local/mysql
#数据路径
datadir=/data/mysql/data
#socket文件位置
socket=/tmp/mysql.sock
#服务器ID号
server_id=6
#mysql-bin二进制日志文件名的前缀
log_bin=/data/binlog/mysql-bin
binlog_format=row
#端口号
port=3306
#日志位置(有相关权限)
log_error=/tmp/mysql3306.log
#开启GTID
gtid-mode=on
#强制GTID一致性
enforce-gtid-consistency=true
[mysql]
#socket文件位置
socket=/tmp/mysql.sock
# systemctl restart mysqld

4、查看GTID信息

TID是一个自增长的数据,从1开始

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> create database gtid charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000003 |      329 |              |                  | 7d01bcc5-e854-11ea-9c8f-000c29bc0487:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> use gtid;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      491 |              |                  | 7d01bcc5-e854-11ea-9c8f-000c29bc0487:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      744 |              |                  | 7d01bcc5-e854-11ea-9c8f-000c29bc0487:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                             |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                                                   |
| mysql-bin.000003 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '7d01bcc5-e854-11ea-9c8f-000c29bc0487:1' |
| mysql-bin.000003 | 219 | Query          |         6 |         329 | create database gtid charset utf8mb4                              |
| mysql-bin.000003 | 329 | Gtid           |         6 |         394 | SET @@SESSION.GTID_NEXT= '7d01bcc5-e854-11ea-9c8f-000c29bc0487:2' |
| mysql-bin.000003 | 394 | Query          |         6 |         491 | use `gtid`; create table t1(id int)                               |
| mysql-bin.000003 | 491 | Gtid           |         6 |         556 | SET @@SESSION.GTID_NEXT= '7d01bcc5-e854-11ea-9c8f-000c29bc0487:3' |
| mysql-bin.000003 | 556 | Query          |         6 |         628 | BEGIN                                                             |
| mysql-bin.000003 | 628 | Table_map      |         6 |         673 | table_id: 108 (gtid.t1)                                           |
| mysql-bin.000003 | 673 | Write_rows     |         6 |         713 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000003 | 713 | Xid            |         6 |         744 | COMMIT /* xid=12 */                                               |
| mysql-bin.000003 | 744 | Gtid           |         6 |         809 | SET @@SESSION.GTID_NEXT= '7d01bcc5-e854-11ea-9c8f-000c29bc0487:4' |
| mysql-bin.000003 | 809 | Query          |         6 |         901 | drop database gtid                                                |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
13 rows in set (0.00 sec)

5、基于GTID,binlog恢复

# cd /data/binlog/
# mysqlbinlog --skip-gtids --include-gtids='7d01bcc5-e854-11ea-9c8f-000c29bc0487:1-3' mysql-bin.000003 >/tmp/gtid.sql       //截取日志
# mysql -uroot -p1
mysql> set sql_log_bin=0;       //临时关闭二进制日志
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/gtid.sql       //导入日志

mysql> set sql_log_bin=1;       //打开二进制日志
Query OK, 0 rows affected (0.00 sec)

mysql> use gtid;
Database changed
mysql> show tables;
+----------------+
| Tables_in_gtid |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)
参数说明
--skip-gtids在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息
--include-gtids截取日志时,只截取该段日志
--exclude-gtids截取日志时,跳过该段日志

三、慢日志(slow-log)

1、作用

MySQL可以将执行超过指定时间的DQL、DML、DDL等语句记录下来。默认慢查询日志记录是关闭的

  • log_slow_queries :表示是否开启慢查询日志,5.6以前的版本使用此参数指定是否开启慢查询日志,5.6以后的版本使用slow_query_log取代此参数,如果你使用的mysql版本刚好是5.5,那么你可以看到这两个参数同时存在,此时我们不用同时设置它们,设置这两个参数中的任何一个,另一个也会自动保持一致。

  • log_output : 表示当慢查询日志开启以后,以哪种方式存放,log_output可以设置为4种值,"FILE"、"TABLE"、"FILE,TABLE"、"NONE"。此值为"FILE"表示慢查询日志存放于指定的文件中,此值为"TABLE"表示慢查询日志存放于mysql库的slow_log表中,此值为"FILE,TABLE"表示将慢查询日志同时存放于指定的文件与slow_log表中,一般不会进行这样的设置,因为这样会徒增很多IO压力,如果开启,建议设置为"table",此值为"NONE"时表示不记录查询日志,即使slow_query_log设置为ON,如果log_output设置为NONE,也不会记录慢查询日志,其实,log_output不止用于控制慢查询日志的输出,查询日志的输出也是由此参数进行控制,也就是说,log_output设置为file,就表示查询日志和慢查询日志都存放到对应的文件中,设置为table,查询日志和慢查询日志就都存放在对应的数据库表中。

  • slow_query_log :表示是否开启慢查询日志,此参数与log_slow_queries的作用没有区别,5.6以后的版本使用此参数替代log_slow_queries

  • slow_query_log_file :当使用文件存储慢查询日志时(log_output设置为"FILE"或者"FILE,TABLE"时),指定慢查询日志存储于哪个日志文件中,默认的慢查询日志文件名为"主机名-slow.log",慢查询日志的位置为datadir参数所对应的目录位置,一般情况下为 /var/lib/mysql

  • long_query_time :表示"多长时间的查询"被认定为"慢查询",此值得默认值为10秒,表示超过10秒的查询被认定为慢查询。

  • log_queries_not_using_indexes :表示如果运行的sql语句没有使用到索引,是否也被当做慢查询语句记录到慢查询日志中,OFF表示不记录,ON表示记录。

  • log_throttle_queries_not_using_indexes :5.6.5版本新引入的参数,当log_queries_not_using_inde设置为ON时,没有使用索引的查询语句也会被当做慢查询语句记录到慢查询日志中,使用log_throttle_queries_not_using_indexes可以限制这种语句每分钟记录到慢查询日志中的次数,因为在生产环境中,有可能有很多没有使用索引的语句,此类语句频繁的被记录到慢查询日志中,可能会导致慢查询日志快速不断的增长,管理员可以通过此参数进行控制。

  • min_examined_row_limit :扫描记录少于改值的SQL不记录到慢查询日志,结合去记录没有使用索引的SQL语句的例子,有可能存在某一个表,数据量维持在几行左右,且没有建立索引。这种表即使不建立索引,查询也很快,扫描记录很小,如果确定有这种表,则可以通过此参数设置,将这个SQL不记录到慢查询日志。

  • log_slow_admin_statements:记录超时的管理操作SQL到慢查询日志,比如ALTER/ANALYZE TABLE

  • log_slow_slave_statements:在从服务器上开启慢查询日志

  • log_timestamps(5.7+): 写入时区信息。可根据需求记录UTC时间或者服务器本地系统时间

2、配置

# vim /etc/my.cnf
#服务端配置
[mysqld]
#开启慢日志
slow_query_log=1 
#文件位置及名字(提前创建路径及赋权 )
slow_query_log_file=/data/mysql/slow.log
#设定慢查询时间
long_query_time=0.1
#没走索引的语句也记录
log_queries_not_using_indexes

查询慢日志是否开起等其他参数

# 查询慢日志是否开启
show variables like 'slow_query%';
# 查询多少秒的查询视为慢查询
show variables like 'long_query_time%';
# 查询慢查询日志输出到哪儿。
show variables like 'log_output%';

3、分析慢日志

通过mysqldumpslow命令我们可以更加方便的从不同的维度对慢日志进行排序、查找、统计。但是mysqldumpslow只能作用于慢查询日志文件

# 得到返回记录最多的10个sql
# mysqldumpslow -s c -t 10 /data/mysql/slow.log
# 得到平均访问次数最多的20条sql
mysqldumpslow -s ar -t 20 sqlslow.log
# 得到平均访问次数最多,并且里面含有ttt字符的20条sql
mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log

# 如果出现 -bash: mysqldumpslow: command not found 错误,请执行"ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin"
# 如果出现如下错误,Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659.说明你要分析的sql日志太大了,请拆分后再分析
参数说明
-s以什么方式排序
c相同查询以查询条数和从大到小排序
-t以查询总时间的方式从大到小排序
10只会显示前十条
-g可以跟上正则匹配模式,大小写不敏感
l锁定时间
r返回记录
t执行时间
al平均锁定时间
ar平均返回记录数
at平均执行时间

4、使用pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个第三方工具,它可以分析binlog、General log、slowlog,也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。

1、安装pt-query-digest

pt-query-digest本质是perl脚本,所以首先安装perl模块

# wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm
# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
# rpm -ivh percona-toolkit-3.2.1-1.el7.x86_64.rpm 
# pt-query-digest      # 检查是否完成

2、主要命令使用

1、分析慢查询日志统计

# pt-query-digest /data/mysql/slow.log 

输出结果

# 该工具执行日志分析的用户时间,系统时间,物理内存占用大小,虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量,唯一的语句数量,QPS,并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency ________________
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性               总计      最小    最大    平均    95%  标准    中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time             3s   640ms      2s      1s      2s   999ms      1s
# 锁占用时间
# Lock time            1ms       0     1ms   723us     1ms     1ms   723us
# 发送到客户端的行数
# Rows sent              5       1       4    2.50       4    2.12    2.50
# select语句扫描行数
# Rows examine     186.17k       0 186.17k  93.09k 186.17k 131.64k  93.09k
# 查询的字符数
# Query size           455      15     440  227.50     440  300.52  227.50

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
# 由慢到快排序
#    1 0xF9A57DD5A41825CA  2.0529 76.2%     1 2.0529  0.00 SELECT
#    2 0x4194D8F83F4F9365  0.6401 23.8%     1 0.6401  0.00 SELECT wx_member_base

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-22 06:11:40
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     76      2s      2s      2s      2s      2s       0      2s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent     20       1       1       1       1       1       0       1
# 扫描的行数越多,io越大
# Rows examine   0       0       0       0       0       0       0       0
# Query size     3      15      15      15      15      15       0      15
# String:
# Databases    test
# Hosts        192.168.8.1
# Users        mysql
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G

Rows sent和Rows examine相差较大时,说明该SQL的索引命中率不高,或者没有走索引。

2、查看服务器信息

# pt-summary

3、查看服务器磁盘开销

# pt-diskstats

  #ts device    rd_s rd_avkb rd_mb_s rd_mrg rd_cnc   rd_rt    wr_s wr_avkb wr_mb_s wr_mrg wr_cnc   wr_rt busy in_prg    io_s  qtime stime
  0.8 sda        0.0     0.0     0.0     0%    0.0     0.0     1.3     3.5     0.0     0%    0.0     0.0   0%      0     1.3    0.0   0.0
  0.8 sda2       0.0     0.0     0.0     0%    0.0     0.0     1.3     3.5     0.0     0%    0.0     0.0   0%      0     1.3    0.0   0.0
  0.8 dm-0       0.0     0.0     0.0     0%    0.0     0.0     1.3     3.5     0.0     0%    0.0     0.0   0%      0     1.3    0.0   0.0

4、查看MySQL数据库信息

# pt-mysql-summary --host=localhost --user=root --password=123456

5、查找MySQL的从库和同步状态

# pt-slave-find --host=localhost --user=root --password=123456

6、查看MySQL的死锁信息

注意:需要提前运行

# pt-deadlock-logger --run-time=10 --interval=3 --create-dest-table --dest D=test,t=deadlocks u=root,p=123456
参数说明
--run-time运行时间
--interval每隔多少秒检测一次,默认30
--create-dest-table创建一张表
--dest将死锁信息输出至指定数据库的表中(表需要手动建)
D=test死锁生成的库名
t=deadlocks死锁生成的表名
--tab输出带上制表符,格式化显示,更加清晰
--log将死锁信息输出至指定日志文件
--daemonize放到后台运行,守护进程模式
--iterations检测的次数
--ask-pass不显示密码

一般会有下面几种锁类型

  • 记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
  • 间隙锁(LOCK_GAP): lock_mode X locks gap before rec
  • Next-key 锁(LOCK_ORNIDARY): lock_mode X
  • 插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention

7、从慢查询日志中分析索引使用情况

# pt-index-usage --user=root --password=123456 --host=localhost /data/mysql/slow.log

8、从慢查找数据库表中重复的索引

# pt-duplicate-key-checker --host=localhost --user=root --password=123456

9、查看mysql表和文件的当前活动IO开销(不要在高峰时用)

# pt-ioprofile

10、查看不同mysql配置文件的差异(集群常用,双方都生效的变量)

# pt-config-diff /etc/my.cnf /root/my_master.cnf

11、pt-find查找mysql表和执行命令

# 查找数据库里大于1M的表
# pt-find --user=root --password=123456 --tablesize +1M
# 查看表和索引大小并排序
# pt-find --user=root --password=123456 --printf "%T\t%D.%N\n" | sort-rn

12、pt-kill 杀掉符合标准的mysql进程

# 显示查询时间大于3秒的查询
# pt-kill --user=root --password=123456 --bsy-time 3 -print
# kill掉大于3秒的查询
# pt-kill --user=root --password=123456 --busy-time 3 --kill

13、查看mysq|授权(集群常用,授权复制)

# 显示数据库中所有用户的授权信息
# pt-show-grants --user=root --password=123456
# 显示数据库中所有用户的授权信息,并给出撤销的语句
# pt-show-grants --user=root --password=123456 --separate --revoke

14、验证数据库复制的完整性(集群常用,主从复制后检验)

pt-table-checksum --user=root --password=123456
0

评论区