MySQL误删数据？试试数据闪回工具binlog2sql

一、前置条件

• log_bin 为 ON ， binlog_format 为 ROW(Mixed也可以)，且 binlog_row_image 为 full 或 noblog，默认为 full ；

  mysql> show variables where variable_name in (&＃39;log_bin&＃39;,&＃39;binlog_format&＃39;,&＃39;binlog_row_image&＃39;);+------------------+-------+| Variable_name | Value |+------------------+-------+| log_bin | ON || binlog_format | ROW || binlog_row_image | FULL |+------------------+-------+


• 必须开启 MySQL Server ，离线模式无法解析；

• 用来闪回数据的 user 需要的最小权限集合。
  select, super/replication client, replication slave
  select：读取 server 端 information_schema.COLUMNS 表，获取表结构的元信息，拼接成可视化的sql语句。
  super/replication client：两个权限都可以，需要执行 SHOW MASTER STATUS ， 获取 server 端的 binlog 列表。
  replication slave：通过 BINLOG_DUMP 协议获取 binlog 内容的权限。

  授权命令如下：

  mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO [user]@&＃39;%&＃39; identified by &＃39;[password]&＃39;;


二、软件部署

1. 下载软件包
   binlog2sql.tar.gz

2. 安装

   tar -zxvf binlog2sql.tar.gz
cd binlog2sql/binlog2sql_dependencies/
tar -zxvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11
python setup.py install
cd ..
tar -zxvf pip-9.0.1.tar.gz
cd pip-9.0.1
python setup.py install
cd ..
pip install *.whl mysql-replication-0.9.tar.gz


三、参数项

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 起始解析时间，格式 &＃39;%Y-%m-%d %H:%M:%S&＃39; 。可选，默认不过滤。--stop-datetime 终止解析时间，格式 &＃39;%Y-%m-%d %H:%M:%S&＃39; 。可选，默认不过滤。


4. 对象过滤

   -d, --databases 只解析目标 db 的 sql ，多个库用空格隔开，如 -d db1 db2 。可选，默认为空。-t, --tables 只解析目标 table 的 sql ，多张表用空格隔开，如 -t tbl1 tbl2 。可选，默认为空。--only-dml 只解析 dml，忽略ddl 。可选，默认 TRUE。--sql-type 只解析指定类型，支持 INSERT, UPDATE, DELETE 。多个类型用空格隔开，可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。


四、命令示例

1. 查看某个时间段内对某表的 sql 执行记录

   python binlog2sql.py -h[host] -P[port] -u[user] -p&＃39;[password]&＃39; --start-file=&＃39;[binlog_file_name]&＃39; -d[table_schema] -t[table_name] --start-datetime=&＃39;[%Y-%m-%d %H:%M:%S]&＃39; --stop-datetime=&＃39;[%Y-%m-%d %H:%M:%S]&＃39;


2. 结合1命令确定事务及其提交 position ，并生产目标事务的回滚 sql

   python binlog2sql.py -h[host] -P[port] -u[user] -p&＃39;[password]&＃39; --start-file=&＃39;[binlog_file_name]&＃39; -d[table_schema] -t[table_name] --start-position=&＃39;[position]&＃39; --stop-position=&＃39;[position]&＃39; -B


五、案例测试

# 创建测试表 t1
mysql> show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`employee` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_employee` (`employee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)# 插入测试数据
mysql> insert into t1 (employee) values (&＃39;111&＃39;),(&＃39;222&＃39;),(&＃39;333&＃39;);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0# 查看 t1 表当前数据
mysql> select * from t1;
+----+----------+
| id | employee |
+----+----------+
| 1 | 111 |
| 2 | 222 |
| 3 | 333 |
+----+----------+
3 rows in set (0.01 sec)# 刷新 binlog 日志，生成新的日志
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)# 删除 t1 表所有数据
mysql> delete from t1;
Query OK, 3 rows affected (0.00 sec)# 查看 t1 表数据
mysql> select * from t1;
Empty set (0.00 sec)# 查看 binlog 文件
mysql> show master status\G
*************************** 1. row ***************************File: mysql-bin.000013Position: 520Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 7c54e776-807d-11eb-b035-fa163ee04d48:1-1331,
7d5edd3b-807d-11eb-ae54-fa163ee4c1f8:1
1 row in set (0.00 sec)# 使用前文的命令示例，补全信息，查看对 t1 表的 sql 执行记录
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p&＃39;******&＃39; --start-file=&＃39;mysql-bin.000013&＃39; -ddbtest01 -tt1 --start-datetime=&＃39;2021-08-18 14:00:00&＃39; --stop-datetime=&＃39;2021-08-18 15:00:00&＃39;
DELETE FROM `dbtest01`.`t1` WHERE `employee`=&＃39;111&＃39; AND `id`=1 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26
DELETE FROM `dbtest01`.`t1` WHERE `employee`=&＃39;222&＃39; AND `id`=2 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26
DELETE FROM `dbtest01`.`t1` WHERE `employee`=&＃39;333&＃39; AND `id`=3 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26# 根据执行记录中的 position 生成回滚 sql
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p&＃39;******&＃39; --start-file=&＃39;mysql-bin.000013&＃39; -ddbtest01 -tt1 --start-position=&＃39;299&＃39; --stop-position=&＃39;489&＃39; -B
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES (&＃39;333&＃39;, 3); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES (&＃39;222&＃39;, 2); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES (&＃39;111&＃39;, 1); #start 299 end 489 time 2021-08-18 14:21:26# 根据误删时间范围生成回滚 sql
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p&＃39;******&＃39; --start-file=&＃39;mysql-bin.000013&＃39; -ddbtest01 -tt1 --start-datetime=&＃39;2021-08-18 14:00:00&＃39; --stop-datetime=&＃39;2021-08-18 15:00:00&＃39; -B
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES (&＃39;333&＃39;, 3); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES (&＃39;222&＃39;, 2); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES (&＃39;111&＃39;, 1); #start 299 end 489 time 2021-08-18 14:21:26# 将上述回滚 sql 导出文件 rollback.sql，导入数据库
mysql>source /backup/rollback.sql;# 查看 t1 表数据
mysql> select * from t1;
+----+----------+
| id | employee |
+----+----------+
| 1 | 111 |
| 2 | 222 |
| 3 | 333 |
+----+----------+
3 rows in set (0.01 sec)


六、其他

1. 它本身的核心代码比较少，主要是在 pymysqlreplication 的基础上进行了二次开发。
   pymysqlreplication 实现了 MySQL 复制协议，可捕捉不同类型的EVENT事件。
   具体可参考：https://github.com/noplay/python-mysql-replication

2. 注意事项

   1） binlog2sql 强烈依赖于 MySQL 复制协议，如果复制协议发生改变，则该工具将不可用。虽然，复制协议发生改变的可能性很小（一般都会保持向前兼容），但相对而言，自带的 mysqlbinlog 肯定更懂 binlog ，基于 mysqlbinlog 解析后的结果进行处理，可完全屏蔽复制协议等底层细节。
   2）用 python 来解析文本格式的 binlog ，本身也不是件难事。比如， update 语句在 binlog 中的对应的文本，在得到表结构的情况下，基本上可离线解析。

七、参考

MySQL下实现闪回的设计思路 (MySQL Flashback Feature)

八、帮助

python binlog2sql.py --help
usage: binlog2sql.py [-h HOST] [-u USER] [-p PASSWORD] [-P PORT][--start-file STARTFILE] [--start-position STARTPOS][--stop-file ENDFILE] [--stop-position ENDPOS][--start-datetime STARTTIME] [--stop-datetime STOPTIME][--stop-never] [--help] [-d [DATABASES [DATABASES ...]]][-t [TABLES [TABLES ...]]] [-K] [-B]Parse MySQL binlog to SQL you wantoptional arguments:
--stop-never Wait for more data from the server. default: stopreplicate at the last binlog when you start binlog2sql
--help help infomation
-K, --no-primary-key Generate insert sql without primary key if exists
-B, --flashback Flashback data to start_postition of start_fileconnect setting:
-h HOST, --host HOST Host the MySQL database server located
-u USER, --user USER MySQL Username to log in as
-p PASSWORD, --password PASSWORDMySQL Password to use
-P PORT, --port PORT MySQL port to userange filter:
--start-file STARTFILEStart binlog file to be parsed
--start-position STARTPOS, --start-pos STARTPOSStart position of the --start-file
--stop-file ENDFILE, --end-file ENDFILEStop binlog file to be parsed. default: &＃39;--start-file&＃39;
--stop-position ENDPOS, --end-pos ENDPOSStop position of --stop-file. default: latest positionof &＃39;--stop-file&＃39;
--start-datetime STARTTIMEStart reading the binlog at first event having adatetime equal or posterior to the argument; theargument must be a date and time in the local timezone, in any format accepted by the MySQL server forDATETIME and TIMESTAMP types, for example: 2004-12-2511:25:56 (you should probably use quotes for yourshell to set it properly).
--stop-datetime STOPTIMEStop reading the binlog at first event having adatetime equal or posterior to the argument; theargument must be a date and time in the local timezone, in any format accepted by the MySQL server forDATETIME and TIMESTAMP types, for example: 2004-12-2511:25:56 (you should probably use quotes for yourshell to set it properly).schema filter:
-d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]dbs you want to process
-t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]tables you want to process