binlog-rollback.pl基于binlog位置点和时间点恢复delete误删语句
一、基于binlog位置点的恢复
为了演示,刷新一个新的binlog文件,让它单独记录delete删除语句:
MySQL [zhangyou]> flush logs
Query OK, 0 rows affected (0.09 sec)
MySQL [zhangyou]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
MySQL [zhangyou]> select * from dede_scores;
+----+--------+------+----------+-----------+
| id | titles | icon | integral | isdefault |
+----+--------+------+----------+-----------+
| 2 | 列兵 | 1 | 0 | 1 |
| 3 | 班长 | 2 | 1000 | 1 |
| 4 | 少尉 | 3 | 2000 | 1 |
| 5 | 中尉 | 4 | 3000 | 1 |
| 6 | 上尉 | 5 | 4000 | 1 |
| 7 | 少校 | 6 | 5000 | 1 |
| 8 | 中校 | 7 | 6000 | 1 |
| 9 | 上校 | 8 | 9000 | 1 |
| 10 | 少将 | 9 | 14000 | 1 |
| 11 | 中将 | 10 | 19000 | 1 |
| 12 | 上将 | 11 | 24000 | 1 |
| 15 | 大将 | 12 | 29000 | 1 |
+----+--------+------+----------+-----------+
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
delete不加条件删除表数据:
MySQL [zhangyou]> delete from dede_scores;
Query OK, 12 rows affected (0.00 sec)
MySQL [zhangyou]> select * from dede_scores;
Empty set (0.00 sec)
MySQL [zhangyou]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 600
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
按照binlog位置点来生成回滚的sql语句:
[root@git-server opt]# perl binlog-rollback.pl -f '/data/mysql/data/mysql-bin.000009' -o '/tmp/t.sql' -h '127.0.0.1' -u 'admin' -p 'admin' --start-position=120 --stop-position=600
Warning: Using a password on the command line interface can be insecure.
[root@git-server data]# less /tmp/t.sql
INSERT INTO `zhangyou`.`dede_scores` SET `id`=15, `titles`='大将', `icon`=12, `integral`=29000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=12, `titles`='上将', `icon`=11, `integral`=24000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=11, `titles`='中将', `icon`=10, `integral`=19000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=10, `titles`='少将', `icon`=9, `integral`=14000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=9, `titles`='上校', `icon`=8, `integral`=9000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=8, `titles`='中校', `icon`=7, `integral`=6000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=7, `titles`='少校', `icon`=6, `integral`=5000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=6, `titles`='上尉', `icon`=5, `integral`=4000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=5, `titles`='中尉', `icon`=4, `integral`=3000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=4, `titles`='少尉', `icon`=3, `integral`=2000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=3, `titles`='班长', `icon`=2, `integral`=1000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=2, `titles`='列兵', `icon`=1, `integral`=0, `isdefault`=1;
MySQL [zhangyou]> source /tmp/t.sql
MySQL [zhangyou]> select * from dede_scores;
+----+--------+------+----------+-----------+
| id | titles | icon | integral | isdefault |
+----+--------+------+----------+-----------+
| 2 | 列兵 | 1 | 0 | 1 |
| 3 | 班长 | 2 | 1000 | 1 |
| 4 | 少尉 | 3 | 2000 | 1 |
| 5 | 中尉 | 4 | 3000 | 1 |
| 6 | 上尉 | 5 | 4000 | 1 |
| 7 | 少校 | 6 | 5000 | 1 |
| 8 | 中校 | 7 | 6000 | 1 |
| 9 | 上校 | 8 | 9000 | 1 |
| 10 | 少将 | 9 | 14000 | 1 |
| 11 | 中将 | 10 | 19000 | 1 |
| 12 | 上将 | 11 | 24000 | 1 |
| 15 | 大将 | 12 | 29000 | 1 |
+----+--------+------+----------+-----------+
12 rows in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
到此处基于binlog位置点的恢复成功
二、基于binlog时间点的恢复
[root@git-server opt]# less /tmp/t1.sql
INSERT INTO `zhangyou`.`dede_scores` SET `id`=15, `titles`='大将', `icon`=12, `integral`=29000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=12, `titles`='上将', `icon`=11, `integral`=24000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=11, `titles`='中将', `icon`=10, `integral`=19000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=10, `titles`='少将', `icon`=9, `integral`=14000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=9, `titles`='上校', `icon`=8, `integral`=9000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=8, `titles`='中校', `icon`=7, `integral`=6000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=7, `titles`='少校', `icon`=6, `integral`=5000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=6, `titles`='上尉', `icon`=5, `integral`=4000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=5, `titles`='中尉', `icon`=4, `integral`=3000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=4, `titles`='少尉', `icon`=3, `integral`=2000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=3, `titles`='班长', `icon`=2, `integral`=1000, `isdefault`=1;
INSERT INTO `zhangyou`.`dede_scores` SET `id`=2, `titles`='列兵', `icon`=1, `integral`=0, `isdefault`=1;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.