如何利用binlog还原sql或者恢复数据

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

gitee:https://gitee.com/ShiZan/binlog2sql

该方法应用到的工具binlog2sql，需要安装python运行环境

1.安装pip命令

①、如果没有epel源下载阿里的epel源

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

②、安装pip

yum -y install python-pip

pip install socks
pip install pysocks

③.因为提示，版本过于老旧，所以运行以下命令更新

 pip install --upgrade pip

2.安装git环境然后在/mydata目录下执行

git clone https://gitee.com/ShiZan/binlog2sql.git

或者直接上传文件binlog2sql.zip到/mydata目录下

解压

unzip binlog2sql.zip 

3.在/mydata/binlog2sql目录下执行

pip install -r requirements.txt

4.使用方法：

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

在/mydata/binlog2sql/binlog2sql目录下执行

①解析出标准SQL

[root@mac-shuidi-7-121 binlog2sql]# python binlog2sql.py -h192.168.54.120 -P3306 -uroot -p'ShiZan_666' -dcanal_db -t canal_table --start-file='mysql-bin.000005' --start-position=951233 --stop-position=951509
INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('测试binlog还原sql', 0, 42, 1); #start 951233 end 951509 time 2020-12-01 17:58:38

canal_d:数据库名称

canal_table:表名

start-file:binlog日志文件

start-position:开始位置

stop-position:结束位置

②误删恢复演练

假设大概在2020-12-02 10:00:00 ~2020-12-02 10:10:00分之间进行了删除操作

<1>登录mysql，查看目前的binlog文件

mysql> show master status;
+------------------+----------+------------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+------------------+-------------------------------------------------+-------------------+
| mysql-bin.000005 |   953091 | user_db,canal_db | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+------------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

<2> 最新的binlog文件是mysql-bin.000005，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。

[root@mac-shuidi-7-121 binlog2sql]# python binlog2sql.py -h192.168.54.120 -P3306 -uroot -p'ShiZan_666' -dcanal_db -tcanal_table --start-file='mysql-bin.000005' --start-datetime='2020-12-02 10:00:00' --stop-datetime='2020-12-02 10:10:00'

DELETE FROM `canal_db`.`canal_table` WHERE `remark`='新增ql语句2' AND `version`=0 AND `id`=38 AND `from_type`=1 LIMIT 1; #start 951540 end 951810 time 2020-12-02 10:05:01
DELETE FROM `canal_db`.`canal_table` WHERE `remark`='新增sql语句3' AND `version`=2 AND `id`=39 AND `from_type`=1 LIMIT 1; #start 951841 end 952112 time 2020-12-02 10:05:01
DELETE FROM `canal_db`.`canal_table` WHERE `remark`='测试以表名为topic,发送消息后启动应用' AND `version`=1 AND `id`=40 AND `from_type`=1 LIMIT 1; #start 952143 end 952449 time 2020-12-02 10:05:01
DELETE FROM `canal_db`.`canal_table` WHERE `remark`='新增一条数据' AND `version`=0 AND `id`=41 AND `from_type`=1 LIMIT 1; #start 952480 end 952753 time 2020-12-02 10:05:01
DELETE FROM `canal_db`.`canal_table` WHERE `remark`='测试binlog还原sql' AND `version`=0 AND `id`=42 AND `from_type`=1 LIMIT 1; #start 952784 end 953060 time 2020-12-02 10:05:01
INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('误删操作后新增的数据1', 0, 43, 1); #start 953091 end 953377 time 2020-12-02 10:07:35
INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('误删操作后新增的数据2', 0, 44, 1); #start 953408 end 953694 time 2020-12-02 10:07:49
UPDATE `canal_db`.`canal_table` SET `remark`='误删操作后新增的数据1', `version`=1, `id`=43, `from_type`=1 WHERE `remark`='误删操作后新增的数据1' AND `version`=0 AND `id`=43 AND `from_type`=1 LIMIT 1; #start 953725 end 954059 time 2020-12-02 10:07:52

<3>我们得到了误操作sql的准确位置在951540-953060之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)

python binlog2sql.py -h192.168.54.120 -P3306 -uroot -p'ShiZan_666' -dcanal_db -tcanal_table --start-file='mysql-bin.000005' --start-position=951540 --stop-position=953060 -B > rollback.sql | cat

INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('测试binlog还原sql', 0, 42, 1); #start 952784 end 953060 time 2020-12-02 10:05:01
INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('新增一条数据', 0, 41, 1); #start 952480 end 952753 time 2020-12-02 10:05:01
INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('测试以表名为topic,发送消息后启动应用', 1, 40, 1); #start 952143 end 952449 time 2020-12-02 10:05:01
INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('新增sql语句3', 2, 39, 1); #start 951841 end 952112 time 2020-12-02 10:05:01
INSERT INTO `canal_db`.`canal_table`(`remark`, `version`, `id`, `from_type`) VALUES ('新增ql语句2', 0, 38, 1); #start 951540 end 951810 time 2020-12-02 10:05:01

在当前目录下生成 rollback.sql 文件，在业务方确认后执行sql

mysql -h192.168.54.120 -P3306 -uroot -p'ShiZan_666' < rollback.sql

数据恢复

③新增恢复演练

前两步定位同上，得到准确位置在 #start 953408 end 953694

获取恢复sql

[root@mac-shuidi-7-121 binlog2sql]# python binlog2sql.py -h192.168.54.120 -P3306 -uroot -p'ShiZan_666' -dcanal_db -tcanal_table --start-file='mysql-bin.000005' --start-position=953408 --stop-position=953694  -B


DELETE FROM `canal_db`.`canal_table` WHERE `remark`='误删操作后新增的数据2' AND `version`=0 AND `id`=44 AND `from_type`=1 LIMIT 1; #start 953408 end 953694 time 2020-12-02 10:07:49

执行sql恢复数据

④修改恢复演练

前两步定位同上，得到准确位置在 #start 953725 end 954059

获取恢复sql

[root@mac-shuidi-7-121 binlog2sql]# python binlog2sql.py -h192.168.54.120 -P3306 -uroot -p'ShiZan_666' -dcanal_db -tcanal_table --start-file='mysql-bin.000005' --start-position=953725 --stop-position=954059  -B

UPDATE `canal_db`.`canal_table` SET `remark`='误删操作后新增的数据1', `version`=0, `id`=43, `from_type`=1 WHERE `remark`='误删操作后新增的数据1' AND `version`=1 AND `id`=43 AND `from_type`=1 LIMIT 1; #start 953725 end 954059 time 2020-12-02 10:07:52

执行sql恢复数据