资料来源:
1、安装git和pip
yum -y install git python-pip
2、安装binlog2sql
[root@server-254-163 tmp]# git clone
Cloning into 'binlog2sql'...
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 151.51 KiB | 15.00 KiB/s, done.
Resolving deltas: 100% (170/170), done.
[root@server-254-163 binlog2sql]# ll
total 52
drwxr-xr-x. 2 root root 72 Aug 18 21:34 binlog2sql
drwxr-xr-x. 2 root root 54 Aug 18 21:34 example
-rw-r--r--. 1 root root 35141 Aug 18 21:34 LICENSE
-rw-r--r--. 1 root root 9514 Aug 18 21:34 README.md
-rw-r--r--. 1 root root 54 Aug 18 21:34 requirements.txt
drwxr-xr-x. 2 root root 37 Aug 18 21:34 tests
[root@server-254-163 binlog2sql]# pip install -r requirements.txt
Collecting PyMySQL==0.7.11 (from -r requirements.txt (line 1))
Downloading (78kB)
100% |████████████████████████████████| 81kB 4.2kB/s
Collecting wheel==0.29.0 (from -r requirements.txt (line 2))
Downloading (66kB)
100% |████████████████████████████████| 71kB 2.8kB/s
Collecting mysql-replication==0.13 (from -r requirements.txt (line 3))
Downloading
Installing collected packages: PyMySQL, wheel, mysql-replication
Running setup.py install for mysql-replication ... done
Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0
You are using pip version 8.1.2, however version 20.2.2 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
3、mysql参数调整
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
4、用户授权
select, super/replication client, replication slave
建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
权限说明
-
select:需要读取server端information_schema.COLUMNS表,获取表结构的元信息,拼接成可视化的sql语句。
-
super/replication client:两个权限都可以,需要执行'SHOW MASTER STATUS', 获取server端的binlog列表。
-
replication slave:通过BINLOG_DUMP协议获取binlog内容的权限。
5、测试
进行DML
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 6 |
| 6 |
+------+
10 rows in set (0.00 sec)
mysql> delete from t1 where id=6;
Query OK, 2 rows affected (0.00 sec)
mysql> insert into t1 values(7);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(8);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 7 |
| 8 |
+------+
10 rows in set (0.00 sec)
mysql> update t1 set id=5 where id=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t1 set id=6 where id=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 5 |
| 6 |
+------+
10 rows in set (0.00 sec)
查看binlog
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1844 |
| mysql-bin.000002 | 1181 |
| mysql-bin.000003 | 177 |
| mysql-bin.000004 | 336533397 |
| mysql-bin.000005 | 333768638 |
| mysql-bin.000006 | 327587479 |
| mysql-bin.000007 | 673 |
| mysql-bin.000008 | 1426 |
+------------------+-----------+
8 rows in set (0.00 sec)
解析出标准SQL
python binlog2sql.py -hlocalhost -P3307 -ubackup -pmysql -dhlj -t t1 --start-file='mysql-bin.000008'DELETE FROM `hlj`.`t1` WHERE `id`=6 LIMIT 1; #start 4 end 379 time 2020-08-18 21:21:16
DELETE FROM `hlj`.`t1` WHERE `id`=6 LIMIT 1; #start 4 end 379 time 2020-08-18 21:21:16
INSERT INTO `hlj`.`t1`(`id`) VALUES (7); #start 410 end 630 time 2020-08-18 21:21:46
INSERT INTO `hlj`.`t1`(`id`) VALUES (8); #start 661 end 881 time 2020-08-18 21:21:50
UPDATE `hlj`.`t1` SET `id`=5 WHERE `id`=7 LIMIT 1; #start 912 end 1138 time 2020-08-18 21:22:22
UPDATE `hlj`.`t1` SET `id`=6 WHERE `id`=8 LIMIT 1; #start 1169 end 1395 time 2020-08-18 21:22:34
解析出回滚SQL
python binlog2sql.py --flashback -hlocalhost -P3307 -ubackup -pmysql -dhlj -t t1 --start-file='mysql-bin.000008'
UPDATE `hlj`.`t1` SET `id`=8 WHERE `id`=6 LIMIT 1; #start 1169 end 1395 time 2020-08-18 21:22:34
UPDATE `hlj`.`t1` SET `id`=7 WHERE `id`=5 LIMIT 1; #start 912 end 1138 time 2020-08-18 21:22:22
DELETE FROM `hlj`.`t1` WHERE `id`=8 LIMIT 1; #start 661 end 881 time 2020-08-18 21:21:50
DELETE FROM `hlj`.`t1` WHERE `id`=7 LIMIT 1; #start 410 end 630 time 2020-08-18 21:21:46
INSERT INTO `hlj`.`t1`(`id`) VALUES (6); #start 4 end 379 time 2020-08-18 21:21:16
INSERT INTO `hlj`.`t1`(`id`) VALUES (6); #start 4 end 379 time 2020-08-18 21:21:16
选项
mysql连接配置
-h host; -P port; -u user; -p password
解析模式
--stop-never 持续解析binlog。可选。默认False,同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。默认False
-B, --flashback 生成回滚SQL,可解析大文件,不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。
--back-interval -B模式下,每打印一千行回滚SQL,加一句SLEEP多少秒,如不想加SLEEP,请设为0。可选。默认1.0。
解析范围控制
--start-file 起始解析文件,只需文件名,无需全路径 。必须。
--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never,此选项失效。
--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置;若解析模式为stop-never,此选项失效。
--start-datetime 起始解析时间,格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
--stop-datetime 终止解析时间,格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
对象过滤
-d, --databases 只解析目标db的sql,多个库用空格隔开,如-d db1 db2。可选。默认为空。
-t, --tables 只解析目标table的sql,多张表用空格隔开,如-t tbl1 tbl2。可选。默认为空。
--only-dml 只解析dml,忽略ddl。可选。默认False。
--sql-type 只解析指定类型,支持INSERT, UPDATE, DELETE。多个类型用空格隔开,如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型,则三者都不解析。
限制(对比mysqlbinlog)
- mysql server必须开启,离线模式下不能解析
- 参数 binlog_row_image 必须为FULL,暂不支持MINIMAL
- 解析速度不如mysqlbinlog
优点(对比mysqlbinlog)
- 纯Python开发,安装与使用都很简单
- 自带flashback、no-primary-key解析模式,无需再装补丁
- flashback模式下,更适合
- 解析为标准SQL,方便理解、筛选
- 代码容易改造,可以支持更多个性化解析