MySQL 8.0版本 binlog2sql 解析binlog

1.确认MySQL相关配置以及用户权限

使用MySQL server必须设置以下参数:
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
user需要的最小权限集合：
select, super/replication client, replication slave

建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

2.下载安装binlog2sql，并做调整requirements文件

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
**binlog2sql路径下的requirements.txt文件需要调整**
cat requirements.txt
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13

需要将PyMySQL由0.7.11调整为0.9.3
shell> pip install -r requirements.txt

3.测试

1. 测试数据准备

mysql> select * from test;
+----+--------+-------------+
| id | name   | create_time |
+----+--------+-------------+
|  1 | crmsb  | 2020-09-08  |
|  2 | xiaom  | 2020-09-08  |
|  3 | 小陈   | 2020-09-08  |
|  4 | 小高   | 2020-09-08  |
+----+--------+-------------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> flush logs;

Query OK, 0 rows affected (0.48 sec)

mysql> 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000011 |      196 |              |                  | c16876d6-edd0-11ea-b53e-00155d8a8a00:1-45 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+--------+-------------+
| id | name   | create_time |
+----+--------+-------------+
|  1 | crmsb  | 2020-09-08  |
|  2 | xiaom  | 2020-09-08  |
|  3 | 小陈   | 2020-09-08  |
|  4 | 小高   | 2020-09-08  |
+----+--------+-------------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> delete from test;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

2. 解析binlog日志中执行的SQL

sheel>python binlog2sql.py -h127.0.0.1 -P3308 -utest01 -p'XXXXXXX' -dcrmsb --start-file='mysql-bin.000011'
DELETE FROM `crmsb`.`test` WHERE `create_time`='2020-09-08' AND `id`=1 AND `name`='crmsb' LIMIT 1; #start 275 end 542 time 2020-09-08 08:08:35
DELETE FROM `crmsb`.`test` WHERE `create_time`='2020-09-08' AND `id`=2 AND `name`='xiaom' LIMIT 1; #start 275 end 542 time 2020-09-08 08:08:35
DELETE FROM `crmsb`.`test` WHERE `create_time`='2020-09-08' AND `id`=3 AND `name`='小陈' LIMIT 1; #start 275 end 542 time 2020-09-08 08:08:35
DELETE FROM `crmsb`.`test` WHERE `create_time`='2020-09-08' AND `id`=4 AND `name`='小高' LIMIT 1; #start 275 end 542 time 2020-09-08 08:08:35

3. 解析生产回滚的SQL(flashback)，需要加上参数-B

shell>python binlog2sql.py -h127.0.0.1 -P3308 -utest01 -p'XXXXXXX' -dcrmsb --start-file='mysql-bin.000011' -B
INSERT INTO `crmsb`.`test`(`create_time`, `id`, `name`) VALUES ('2020-09-08', 4, '小高'); #start 275 end 542 time 2020-09-08 08:08:35
INSERT INTO `crmsb`.`test`(`create_time`, `id`, `name`) VALUES ('2020-09-08', 3, '小陈'); #start 275 end 542 time 2020-09-08 08:08:35
INSERT INTO `crmsb`.`test`(`create_time`, `id`, `name`) VALUES ('2020-09-08', 2, 'xiaom'); #start 275 end 542 time 2020-09-08 08:08:35
INSERT INTO `crmsb`.`test`(`create_time`, `id`, `name`) VALUES ('2020-09-08', 1, 'crmsb'); #start 275 end 542 time 2020-09-08 08:08:35

4.补充

binlog2sql还有基于时间点和位点的SQL解析，各位看官可自行测试。
基于时间段的参数：–start-datetime=‘2020-09-08 20:25:00’ --stop-datetime=‘2020-09-08 20:30:00’
基于位点的参数：–start-position=3346 --stop-position=3556