+------+-------+----------+
| id | name | time |
+------+-------+----------+
| 1 | lalal | 16:32:21 |
| 2 | ababa | 16:32:34 |
| 3 | jgjgj | 16:32:43 |
| 4 | opopo | 16:32:52 |
| 5 | jljlj | 16:33:06 |
+------+-------+----------+
5 rows in set (0.00 sec)
mysql> delete from test; --清空表--
Query OK, 5 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000013 | 2016 | | | f90ca153-b46d-11e6-ab68-0800279ecefa:1-35 |
+------------------+----------+--------------+------------------+-------------------------------------------+
一般情况下都是知道个大概的时间,先从大概的时间找起
[root@master binlog2sql]# python /opt/binlog2sql/binlog2sql/binlog2sql.py -uxiaomage -pxiaomage -dtest -ttest --start-file=mysql-bin.000013 --start-datetime='2017-05-05 16:30:00' --stop-datetime='2017-05-05 16:40:00'
USE test;
create table test (id int, name varchar(20),time time);
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:21', 1, 'lalal'); #start 478 end 659 time 2017-05-05 16:32:21
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:34', 2, 'ababa'); #start 690 end 871 time 2017-05-05 16:32:34
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:43', 3, 'jgjgj'); #start 902 end 1083 time 2017-05-05 16:32:43
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:52', 4, 'opopo'); #start 1114 end 1295 time 2017-05-05 16:32:52
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:33:06', 5, 'jljlj'); #start 1326 end 1507 time 2017-05-05 16:33:06
USE test;
alter table test modify time datetime;
USE test;
alter table test modify time time;
DELETE FROM `test`.`test` WHERE `time`='16:32:21' AND `id`=1 AND `name`='lalal' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27 ----这就是delete的数据---
DELETE FROM `test`.`test` WHERE `time`='16:32:34' AND `id`=2 AND `name`='ababa' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27
DELETE FROM `test`.`test` WHERE `time`='16:32:43' AND `id`=3 AND `name`='jgjgj' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27
DELETE FROM `test`.`test` WHERE `time`='16:32:52' AND `id`=4 AND `name`='opopo' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27
DELETE FROM `test`.`test` WHERE `time`='16:33:06' AND `id`=5 AND `name`='jljlj' LIMIT 1; #start 1756 end 1985 time 2017-05-05 16:37:27
生成回滚语句 -B,上面看到的具体的位置start 1756 end 1985(这里一定要过滤好了,一定要细心,配合grep什么的进行匹配)
[root@master binlog2sql]# python /opt/binlog2sql/binlog2sql/binlog2sql.py -uxiaomage -pxiaomage -dtest -ttest --start-file=mysql-bin.000013 --start-position=1756 --stop-position=1985 -B
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:33:06', 5, 'jljlj'); #start 1756 end 1985 time 2017-05-05 16:37:27
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:52', 4, 'opopo'); #start 1756 end 1985 time 2017-05-05 16:37:27
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:43', 3, 'jgjgj'); #start 1756 end 1985 time 2017-05-05 16:37:27
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:34', 2, 'ababa'); #start 1756 end 1985 time 2017-05-05 16:37:27
INSERT INTO `test`.`test`(`time`, `id`, `name`) VALUES ('16:32:21', 1, 'lalal'); #start 1756 end 1985 time 2017-05-05 16:37:27
重定向到/opt/rollback.sql文件中
恢复:
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.05 sec)
mysql> source /opt/rollback.sql
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.07 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+-------+----------+
| id | name | time |
+------+-------+----------+
| 5 | jljlj | 16:33:06 |
| 4 | opopo | 16:32:52 |
| 3 | jgjgj | 16:32:43 |
| 2 | ababa | 16:32:34 |
| 1 | lalal | 16:32:21 |
+------+-------+----------+
5 rows in set (0.00 sec) ok恢复会来了
解析模式--stop-never
持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。 -K, --no-primary-key 对INSERT语句去除主键。可选。
-B, --flashback 生成回滚语句,可解析大文件,不受内存限制,每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。
解析范围控制--start-file 起始解析文件。必须。 --start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。 --stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。
若解析模式为stop-never,此选项失效。 --stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置;若解析模式为stop-never,此选项失效。 --start-datetime 从哪个时间点的binlog开始解析,
格式必须为datetime,如'2016-11-11 11:11:11'。可选。默认不过滤。 --stop-datetime 到哪个时间点的binlog停止解析,格式必须为datetime,如'2016-11-11 11:11:11'。可选。默认不过滤。
对象过滤-d, --databases 只输出目标db的sql。可选。默认为空。 -t, --tables 只输出目标tables的sql。可选。默认为空
参考:https://github.com/danfengcao/binlog2sql