当前blog所使用的mysql版本: 5.7, 使用bt面板安装的环境.

0x00 简介

MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句,比如select,show等)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗。

二进制有两个最重要的使用场景:

• MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
• 数据恢复，通过使用mysqlbinlog工具来恢复数据(也可以使用binlong2sql)

二进制日志包括两类文件：

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

0x01 查看是否开启binlog

使用 SHOW variables LIKE 'log_%' 查看 log_bin的配置是否是ON

如果没有开启, 则修改配置文件my.cnf, 使用find / -name my.cnf查找定位:

然后在[mysqld]节点下修改配置:

binlog_format默认是mixed, 上图中是因为之前使用binlog2sql而修改为了row

0x02 查看binlog

2.1 使用mysql命令查看binlog

• 获取binlog文件列表: show binary logs

定位mysql文件的位置: show variables LIKE 'datadir':

mysql> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /www/server/data/ |
+---------------+-------------------+
1 row in set (0.00 sec)

这样就能得到日志文件的路径, 比如: /www/server/data/mysql-bin.000007

• 查看master状态: show master status 会显示当前正在写入的log文件

• 查看第一个log文件: show binlog events

• 查看指定的log文件: show binlog events in 'mysql-bin.xxxxxx', 比如: show binlog events in 'mysql-bin.000007'

2.2 使用mysqlbinlog工具

mysqlbinlog的参数参考: https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html

前面已经查过了数据库文件目录datadir是: /www/server/data/.

定位工具的位置: find / -name mysqlbinlog, 下面是bt面板安装的mysql的默认位置

[root@localhost ~]# find / -name mysqlbinlog
/www/server/mysql/bin/mysqlbinlog

常用过滤参数:

• 时间过滤 --start-datetime="Y-m-d H:i:s"和--stop-datetime="Y-m-d H:i:s"
• 位置过滤 --start-position=the_position_integer和--stop-position=the_position_integer
• 数据库过滤 --database=the_database_name, 或者 -d the_database_name
• 偏移量 --offset=N, -o N

输出控制:

• --base64-output=decode-rows: base64-output，可以控制输出语句输出base64编码的BINLOG语句; decode-rows：选项将把基于行的事件解码成一个SQL语句
• --version , -V: 显示版本号并退出
• --verbose, -v: 重新组织行事件,并以sql注释的方式显示(Reconstruct row events and display them as commented SQL statements)

基于开始和结束时间的查询:

使用参数--start-datetime和--stop-datetime:

/www/server/mysql/bin/mysqlbinlog --start-datetime="2021-4-20 00:00:00" --stop-datetime="2021-04-20 23:59:59" /www/server/data/mysql-bin.000007

得到的是一堆难以阅读的文本. 我们可以加个参数-r写入文件中去(也可以使用linux的>操作符):

/www/server/mysql/bin/mysqlbinlog --start-datetime="2021-4-20 00:00:00" --stop-datetime="2021-04-20 23:59:59" /www/server/data/mysql-bin.000007 -r /home/20210420_1.sql

增加 --base64-output=decode-rows 选项解析:

base64-output，可以控制输出语句输出base64编码的BINLOG语句;decode-rows：选项将把基于行的事件解码成一个SQL语句

/www/server/mysql/bin/mysqlbinlog -v --base64-output=decode-rows --start-datetime="2021-4-20 00:00:00" --stop-datetime="2021-04-20 23:59:59" /www/server/data/mysql-bin.000007

增加--database=database_name选项过滤数据库:

/www/server/mysql/bin/mysqlbinlog -v --base64-output=decode-rows --database=test --start-datetime="2021-4-20 00:00:00" --stop-datetime="2021-04-20 23:59:59" /www/server/data/mysql-bin.000007 > /home/2.sql

得到的部分代码:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210420 20:07:50 server id 1  end_log_pos 123 CRC32 0x166fba4a 	Start: binlog v 4, server v 5.7.33-log created 210420 20:07:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#210420 20:07:51 server id 1  end_log_pos 154 CRC32 0x8c99260e 	Previous-GTIDs
# [empty]
# at 154
#210420 20:22:39 server id 1  end_log_pos 219 CRC32 0xa2a632ff 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210420 20:22:39 server id 1  end_log_pos 291 CRC32 0xcee66f54 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1618921359/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#210420 20:22:39 server id 1  end_log_pos 348 CRC32 0x57a0d867 	Table_map: `test`.`students` mapped to number 108
# at 348
#210420 20:22:39 server id 1  end_log_pos 402 CRC32 0xd7a1a036 	Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`students`
### SET
###   @1=1
###   @2='aben'
###   @3=18
###   @4=1618840800
###   @5=0
# at 402
#210420 20:22:39 server id 1  end_log_pos 433 CRC32 0x76dccf9d 	Xid = 6
COMMIT/*!*/;

# 中间省略...........................

#210420 20:24:21 server id 1  end_log_pos 1180 CRC32 0xf1e18242 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1618921461/*!*/;
BEGIN
/*!*/;
# at 1180
#210420 20:24:21 server id 1  end_log_pos 1237 CRC32 0x6d2e5f81 	Table_map: `test`.`students` mapped to number 108
# at 1237
#210420 20:24:21 server id 1  end_log_pos 1313 CRC32 0x7ef28a01 	Update_rows: table id 108 flags: STMT_END_F
### UPDATE `test`.`students`
### WHERE
###   @1=4
###   @2='sunny'
###   @3=17
###   @4=1618841040
###   @5=0
### SET
###   @1=4
###   @2='sunny'
###   @3=18
###   @4=1618841040
###   @5=1618921461
# at 1313
#210420 20:24:21 server id 1  end_log_pos 1344 CRC32 0x8c12b2f0 	Xid = 12
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

从上面我们可以看到 Start: binlog v 4, 表示位置从4开始, 结束于1344

基于位置的查询

使用参数--start-position和--stop-position

/www/server/mysql/bin/mysqlbinlog -v --base64-output=decode-rows --database=test --start-position=1180 --stop-position=1313 /www/server/data/mysql-bin.000007 > /home/4.sql

得到的sql文件是:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1180
#210420 20:24:21 server id 1  end_log_pos 1237 CRC32 0x6d2e5f81 	Table_map: `test`.`students` mapped to number 108
# at 1237
#210420 20:24:21 server id 1  end_log_pos 1313 CRC32 0x7ef28a01 	Update_rows: table id 108 flags: STMT_END_F
### UPDATE `test`.`students`
### WHERE
###   @1=4
###   @2='sunny'
###   @3=17
###   @4=1618841040
###   @5=0
### SET
###   @1=4
###   @2='sunny'
###   @3=18
###   @4=1618841040
###   @5=1618921461
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

0x03 日志文件格式

事件之前有头部注释(提供其他信息), 比如:

# at 141
#100309  9:28:36 server id 123  end_log_pos 245
  Query thread_id=3350  exec_time=11  error_code=0

第一行, at后面的数字表示在日志文件中的偏移量, 或者当前日志文件的开始位置.

第二行以日期和时间开头，指示该语句何时在事件发生的服务器上启动。复制时此时间戳会传播到分发服务器( replica servers)。

• server id是事件发生的服务器的server_id。
• end_log_pos 是下一个事件的开始位置（即:当前事件的结束位置+ 1）。
• thread_id表示哪个线程执行了该事件。
• exec_time是在源服务器上执行事件所花费的时间。在副本上，它是副本上结束执行时间减去源上开始执行时间的差,差异可作为复制滞后于源的指标。
• error_code指示执行事件的结果, 零表示没有错误发生。

0x04 其他

mysqlbinlog不支持utf8mb4, 如果配置文件中的[client]节点配置了default-character-set=utf8mb4这样, 在调用时需要加参数 --no-defaults, 否则会报错: unknown variable 'default-character-set=utf8mb4'

查看帮助: mysqlbinlog --no-defaults --help

查看版本: mysqlbinlog --no-defaults --version