长话短说:binlog2sql工具实际就是一个python脚本;利用这个脚本,可以将mysql的binlog日志翻译成对应的sql语句,并且可以生成对应的回滚sql;

当然,mysql自带的mysqlbinlog也可以翻译binlog日志,但只是翻译成了易于可读的日志,并不能用于直接执行,或者回滚;

一、用途

  • 数据快速回滚(闪回)
  • 主从切换后新master丢数据的修复
  • 从binlog生成标准SQL,带来的衍生功能

github.com:https://github.com/danfengcao/binlog2sql

二、安装

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt

直接从github上拉取,或者下载压缩包;

三、使用前置条件

1、需要安装python环境和pip命令;

本环境安装的是python3.8;

2、mysql服务必须设置以下参数:

[mysqld]
server_id = 100
log_bin = /usr/local/mysql/binlog/master-bin	#开启binlog日志
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

参数解读:
max_binlog_size 参数:

binlog日志的大小上限,文件达到1G的时候会自动切割;

binlog_format 参数:

binlog的日志格式(row记录每一行的变化);

binlog_row_image 参数:

binlog_row_image :前提是binlog_format 必须为row;
binlog_row_image参数可以设置三个合法值: FULL、MINIMAL、NOBLOB
FULL:binlog日志记录所有前镜像和后镜像
MINIMAL:binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列),后镜像只记录修改列。
NOBLOB:binlog记录所有的列,就像full格式一样。但对于BLOB或TEXT格式的列,如果他不是唯一识别列(唯一索引列、主键列),或者没有修改,那就不记录。

四、binlog2sql 工具使用

1、提前创建好db_python库;
2、在db_python库中创建tb_test、tb_pycharm表

测试步骤

1、在表中插入数据

首先在 db_python库 的 tb_test表 中插入了4条数据;

2、在另一个表中插入数据

在 db_python库的 tb_pycharm表 中也插入了1条数据;
后又更新了一条数据;

3、在mysql命令行中执行

mysql> show master logs;	#查看最新的binlog日志

4、使用工具查看binlog日志

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' --start-file='master-bin.000003'
INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (24, '大黄蜂', '中国科幻大学', '机械设计'); #start 4 end 445 time 2020-12-01 11:34:43
INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (25, '林玟书', '中南大学', '军事战略学'); #start 476 end 764 time 2020-12-01 11:35:01
INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (26, '林国瑞', '中国农业大学', '人机与环境工程'); #start 795 end 1095 time 2020-12-01 11:35:13
INSERT INTO `db_python`.`tb_pycharm`(`id`, `name`, `school`, `profession`) VALUES (7009, '徐小天', '复旦大学', '计算机应用'); #start 1126 end 1417 time 2020-12-01 11:37:27
UPDATE `db_python`.`tb_pycharm` SET `id`=7008, `name`='苏大大', `school`='南京理工大学', `profession`='农业资源利用' WHERE `id`=7008 AND `name`='王明恭' AND `school`='南京理工大学' AND `profession`='农业资源利用' LIMIT 1; #start 1448 end 1805 time 2020-12-01 11:38:08

可以看到,使用binlog2sql翻译后,变成了可直接执行的sql语句;对,这些就对应了1和2中的操作;
并且sql语句后附带了事务的起始值和终止值,还有起始时间和终止时间;(可以用于过滤)

5、刷新binlog日志

在mysql命令行中 flush logs;

mysql> flush logs;

该命令相当于手动切割binlog日志,会生成一个新的binlog日志文件。当然,旧的binlog也会保留;此处是为了方便测试、查看,提升效果,所以手动切割了日志;

6、清空一个表

mysql> delete from db_python.tb_test;

清空db_python库tb_test表中的数据;

7、继续使用工具查看

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python -t tb_test --start-file='master-bin.000004' 
DELETE FROM `db_python`.`tb_test` WHERE `id`=23 AND `name`='刘华强' AND `school`='清华大学' AND `profession`='机械设计' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52
DELETE FROM `db_python`.`tb_test` WHERE `id`=24 AND `name`='大黄蜂' AND `school`='中国科幻大学' AND `profession`='机械设计' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52
DELETE FROM `db_python`.`tb_test` WHERE `id`=25 AND `name`='林玟书' AND `school`='中南大学' AND `profession`='军事战略学' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52
DELETE FROM `db_python`.`tb_test` WHERE `id`=26 AND `name`='林国瑞' AND `school`='中国农业大学' AND `profession`='人机与环境工程' LIMIT 1; #start 482 end 923 time 2020-12-01 11:50:52

翻译后的binlog,可以清楚的看到执行的delete语句;

因为是清空表,属于批量删除,所以他们都属于同一个事务: #start 482 end 923

8、生成回滚sql

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python -t tb_test --start-file='master-bin.000004 --start-position=482 --stop-position=923 -B > tb_test.sql
[root@mysql2 binlog2sql]# cat tb_test.sql 
INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (26, '林国瑞', '中国农业大学', '人机与环境工程'); #start 482 end 923 time 2020-12-01 11:50:52
INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (25, '林玟书', '中南大学', '军事战略学'); #start 482 end 923 time 2020-12-01 11:50:52
INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (24, '大黄蜂', '中国科幻大学', '机械设计'); #start 482 end 923 time 2020-12-01 11:50:52
INSERT INTO `db_python`.`tb_test`(`id`, `name`, `school`, `profession`) VALUES (23, '刘华强', '清华大学', '机械设计'); #start 482 end 923 time 2020-12-01 11:50:52

在执行命令目录下会生成 tb_test.sql 的一个sql文件;

9、执行回滚sql恢复数据

[root@mysql2 binlog2sql]# mysql -uroot -p < tb_test.sql

mysql> SELECT * FROM db_python.tb_test;
+----+-----------+--------------------+-----------------------+
| id | name      | school             | profession            |
+----+-----------+--------------------+-----------------------+
| 23 | 刘华强    | 清华大学           | 机械设计              |
| 24 | 大黄蜂    | 中国科幻大学       | 机械设计              |
| 25 | 林玟书    | 中南大学           | 军事战略学            |
| 26 | 林国瑞    | 中国农业大学       | 人机与环境工程        |
+----+-----------+--------------------+-----------------------+
4 rows in set (0.00 sec)

其他脚本选项、参数

1、选项

mysql连接配置
-h host; -P port; -u user; -p password

2、解析模式

--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。

3、解析范围控制

--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'。可选。默认不过滤。

4、对象过滤

-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。可选。默认为增删改都解析。用了此参数但没填任何类型,则三者都不解析。

常用命令实例

1、解析某库某表的binlog日志

python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python -t tb_test --start-file='master-bin.000002'

2、指定事务的位置

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python --start-file='master-bin.000004' --start-position=3043 --stop-position=3861
INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (1, '张佳莹'); #start 3043 end 3294 time 2020-12-01 16:01:21
INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (2, '大盖伦'); #start 3325 end 3576 time 2020-12-01 16:01:31
INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (3, '虎式坦克'); #start 3607 end 3861 time 2020-12-01 16:01:52

3、指定时间范围

[root@mysql2 binlog2sql]# python3 binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'admin@admin' -ddb_python --start-file='master-bin.000004' --start-datetime='2020-12-01 16:01:00' --stop-datetime='2020-12-01 16:02:00'
USE b'db_python';
CREATE TABLE `tb_name` (
`id`  int NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) NOT NULL ,
PRIMARY KEY (`id`)
);
INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (1, '张佳莹'); #start 3043 end 3294 time 2020-12-01 16:01:21
INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (2, '大盖伦'); #start 3325 end 3576 time 2020-12-01 16:01:31
INSERT INTO `db_python`.`tb_name`(`id`, `name`) VALUES (3, '虎式坦克'); #start 3607 end 3861 time 2020-12-01 16:01:52
USE b'db_python';
TRUNCATE TABLE tb_name;

binlog2sql工具只能用于 insert、update、delete 类型的操作,其他操作可以记录、翻译,但是无法生成回滚sql;

下期前言

生产环境中还是会出现误删库、表的操作。比如,使用navicat这种客户端连接后,以为操作的是测试数据库,删除库、表后才发现操作的是生产库。
那么该如何避免?

  • 架构只能解决数据库单点故障和容灾切换的问题;
  • 定时备份确实可以保全数据,但无法肯定不是最新数据;(除非实时备份)
  • 通过binlog日志虽然可以恢复删库删表的操作,但是人为操作,难度较大,且容易出错;
打赏
支付宝 微信
上一篇 下一篇