# 创建测试表 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 ('111'),('222'),('333');
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.000013
Position: 520
Binlog_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'******' --start-file='mysql-bin.000013' -ddbtest01 -tt1 --start-datetime='2021-08-18 14:00:00' --stop-datetime='2021-08-18 15:00:00'
DELETE FROM `dbtest01`.`t1` WHERE `employee`='111' AND `id`=1 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26
DELETE FROM `dbtest01`.`t1` WHERE `employee`='222' AND `id`=2 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26
DELETE FROM `dbtest01`.`t1` WHERE `employee`='333' AND `id`=3 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26
# 根据执行记录中的 外汇跟单gendan5.com position 生成回滚 sql
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p'******' --start-file='mysql-bin.000013' -ddbtest01 -tt1 --start-position='299' --stop-position='489' -B
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('333', 3); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('222', 2); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('111', 1); #start 299 end 489 time 2021-08-18 14:21:26
# 根据误删时间范围生成回滚 sql
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p'******' --start-file='mysql-bin.000013' -ddbtest01 -tt1 --start-datetime='2021-08-18 14:00:00' --stop-datetime='2021-08-18 15:00:00' -B
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('333', 3); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('222', 2); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('111', 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)