MySQL 8.0使用binlog2sql解析binlog

本文主要是介绍MySQL 8.0使用binlog2sql解析binlog，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1.确认MySQL相关配置以及用户权限
使用MySQL必须设置以下参数:
[mysqld]
server_id = 1
log_bin=mysql-bin.log
log_bin_index=mysql-bin.log.index
binlog_format=row
max_binlog_size=1G
binlog_row_image = full

user需要的最小权限集合：
select,super/replication client,replication slave

建议授权
grant select,replication client,replication slave on *.* to repl@'%'identified by 'mysql';

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.测试
测试数据准备
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> flush logs;
Query OK, 0 rows affected (0.48 sec)

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> delete from test;
Query OK, 4 rows affected (0.00 sec)

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

解析binlog日志中执行的SQL
sheel> python binlog2sql.py -h127.0.0.1 -P3306 -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

解析生产回滚的SQL(flashback)，需要加上参数-B
shell> python binlog2sql.py -h127.0.0.1 -P3306 -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

这篇关于MySQL 8.0使用binlog2sql解析binlog的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！