MySQL誤刪資料？試試資料閃回工具binlog2sql

# 建立測試表 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)