一、工具下载地址
二、简单操作
1.程序权限修改
chmod u+x ./script/mysqlbinlog
2.数据准备
mysql> select * from box_time;
+----+--------+---------+---------------------+-------------+
| id | box_id | box_key | start_time | push_status |
+----+--------+---------+---------------------+-------------+
| 1 | 1 | | 2016-10-25 12:41:02 | 0 |
| 2 | 1 | | 2016-10-25 12:41:57 | 0 |
| 3 | 1 | | 2016-10-25 14:55:49 | 0 |
| 4 | 1 | | 2016-10-25 15:02:24 | 0 |
| 5 | 1 | | 2016-10-25 15:05:16 | 0 |
| 6 | 1 | | 2016-10-25 15:17:42 | 0 |
| 7 | 1 | | 2016-10-25 17:08:17 | 0 |
| 8 | 1 | | 2016-10-25 19:28:20 | 0 |
| 9 | 1 | | 2016-10-25 22:00:24 | 0 |
+----+--------+---------+---------------------+-------------+
9 rows in set (0.03 sec)
mysql> insert into box_time (box_id) values (2);
mysql> insert into box_time (box_id) values (2);
mysql> insert into box_time (box_id) values (2);
mysql> insert into box_time (box_id) values (2);
mysql> select * from box_time;
+----+--------+---------+---------------------+-------------+
| id | box_id | box_key | start_time | push_status |
+----+--------+---------+---------------------+-------------+
| 1 | 1 | | 2016-10-25 12:41:02 | 0 |
| 2 | 1 | | 2016-10-25 12:41:57 | 0 |
| 3 | 1 | | 2016-10-25 14:55:49 | 0 |
| 4 | 1 | | 2016-10-25 15:02:24 | 0 |
| 5 | 1 | | 2016-10-25 15:05:16 | 0 |
| 6 | 1 | | 2016-10-25 15:17:42 | 0 |
| 7 | 1 | | 2016-10-25 17:08:17 | 0 |
| 8 | 1 | | 2016-10-25 19:28:20 | 0 |
| 9 | 1 | | 2016-10-25 22:00:24 | 0 |
| 10 | 2 | | 2016-10-26 00:08:56 | 0 |
| 11 | 2 | | 2016-10-26 00:08:57 | 0 |
| 12 | 2 | | 2016-10-26 00:08:58 | 0 |
| 13 | 2 | | 2016-10-26 00:08:59 | 0 |
+----+--------+---------+---------------------+-------------+
13 rows in set (0.00 sec)
3.查找pos点 -- box_id开始为2
/home/larry/script/mysqlbinlog -vv mysql-bin.000020
...
# at 1267
#161025 22:00:24 server id 133 end_log_pos 1325 CRC32 0xfd6d158e Table_map: `test`.`box_time` mapped to number 70
# at 1325
#161025 22:00:24 server id 133 end_log_pos 1379 CRC32 0x4f38459f Write_rows: table id 70 flags: STMT_END_F
BINLOG '
eGUPWBOFAAAAOgAAAC0FAAAAAEYAAAAAAAEABHRlc3QACGJveF90aW1lAAUDAw8RAwMAAQAAjhVt
/Q==
eGUPWB6FAAAANgAAAGMFAAAAAEYAAAAAAAEAAgAF/+AJAAAAAQAAAAAAWA9leAAAAACfRThP
'/*!*/;
### INSERT INTO `test`.`box_time`
### SET
### @1=9 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=0 is_null=0 */
### @3='' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @4=1477404024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
# at 1379
#161025 22:00:24 server id 133 end_log_pos 1410 CRC32 0x605b4fe9 Xid = 169
COMMIT/*!*/;
# at 1410
#161026 0:08:56 server id 133 end_log_pos 1482 CRC32 0x7e0840aa Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1477411736/*!*/;
BEGIN
/*!*/;
# at 1482
#161026 0:08:56 server id 133 end_log_pos 1540 CRC32 0x9beb41c0 Table_map: `test`.`box_time` mapped to number 70
# at 1540
#161026 0:08:56 server id 133 end_log_pos 1594 CRC32 0x186b8f9c Write_rows: table id 70 flags: STMT_END_F
BINLOG '
mIMPWBOFAAAAOgAAAAQGAAAAAEYAAAAAAAEABHRlc3QACGJveF90aW1lAAUDAw8RAwMAAQAAwEHr
mw==
mIMPWB6FAAAANgAAADoGAAAAAEYAAAAAAAEAAgAF/+AKAAAAAgAAAAAAWA+DmAAAAACcj2sY
'/*!*/;
### INSERT INTO `test`.`box_time`
### SET
### @1=10 /* INT meta=0 nullable=0 is_null=0 */
###
@2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @4=1477411736 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
# at 1594
#161026 0:08:56 server id 133 end_log_pos 1625 CRC32 0xec02626e Xid = 185
COMMIT/*!*/;
# at 1625
...
4.查看逆转sql --重要参数 -B
[root@mhamaster mysql]# /home/larry/script/mysqlbinlog
-B -vv mysql-bin.000020 --start-position=1410
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#161025 15:01:40 server id 133 end_log_pos 120 CRC32 0x379495f0 Start: binlog v 4, server v 5.6.27-76.0-log created 161025 15:01:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
VAMPWA+FAAAAdAAAAHgAAAABAAQANS42LjI3LTc2LjAtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABUAw9YEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfCV
lDc=
'/*!*/;
#161026 0:08:56 server id 133 end_log_pos 1540 CRC32 0x9beb41c0 Table_map: `test`.`box_time` mapped to number 70
#161026 0:08:56 server id 133 end_log_pos 1625 CRC32 0xec02626e Xid = 185
COMMIT/*!*/;
#161026 0:08:57 server id 133 end_log_pos 1755 CRC32 0x129d99e4 Table_map: `test`.`box_time` mapped to number 70
#161026 0:08:57 server id 133 end_log_pos 1840 CRC32 0xf31cd720 Xid = 186
COMMIT/*!*/;
#161026 0:08:58 server id 133 end_log_pos 1970 CRC32 0x7f0f6bc5 Table_map: `test`.`box_time` mapped to number 70
#161026 0:08:58 server id 133 end_log_pos 2055 CRC32 0x6549c13c Xid = 187
COMMIT/*!*/;
#161026 0:08:59 server id 133 end_log_pos 2185 CRC32 0xdfc6d35d Table_map: `test`.`box_time` mapped to number 70
#161026 0:08:59 server id 133 end_log_pos 2270 CRC32 0x0b2e085b Xid = 188
COMMIT/*!*/;
#161026 0:08:59 server id 133 end_log_pos 2239 CRC32 0x19043d62 Write_rows: table id 70 flags: STMT_END_F
BINLOG '
m4MPWBOFAAAAOgAAAIkIAAAAAEYAAAAAAAEABHRlc3QACGJveF90aW1lAAUDAw8RAwMAAQAAXdPG
3w==
m4MPWCCFAAAANgAAAL8IAAAAAEYAAAAAAAEAAgAF/+ANAAAAAgAAAAAAWA+DmwAAAABiPQQZ
'/*!*/;
### DELETE FROM `test`.`box_time`
### WHERE
### @1=13 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @4=1477411739 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
#161026 0:08:58 server id 133 end_log_pos 2024 CRC32 0x66996852 Write_rows: table id 70 flags: STMT_END_F
BINLOG '
moMPWBOFAAAAOgAAALIHAAAAAEYAAAAAAAEABHRlc3QACGJveF90aW1lAAUDAw8RAwMAAQAAxWsP
fw==
moMPWCCFAAAANgAAAOgHAAAAAEYAAAAAAAEAAgAF/+AMAAAAAgAAAAAAWA+DmgAAAABSaJlm
'/*!*/;
### DELETE FROM `test`.`box_time`
### WHERE
### @1=12 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @4=1477411738 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
#161026 0:08:57 server id 133 end_log_pos 1809 CRC32 0x99a7f6cd Write_rows: table id 70 flags: STMT_END_F
BINLOG '
mYMPWBOFAAAAOgAAANsGAAAAAEYAAAAAAAEABHRlc3QACGJveF90aW1lAAUDAw8RAwMAAQAA5Jmd
Eg==
mYMPWCCFAAAANgAAABEHAAAAAEYAAAAAAAEAAgAF/+ALAAAAAgAAAAAAWA+DmQAAAADN9qeZ
'/*!*/;
### DELETE FROM `test`.`box_time`
### WHERE
### @1=11 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=0 is_null=0 */
### @3='' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @4=1477411737 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
5.回滚前表数据
mysql> select * from box_time;
+----+--------+---------+---------------------+-------------+
| id | box_id | box_key | start_time | push_status |
+----+--------+---------+---------------------+-------------+
| 1 | 1 | | 2016-10-25 12:41:02 | 0 |
| 2 | 1 | | 2016-10-25 12:41:57 | 0 |
| 3 | 1 | | 2016-10-25 14:55:49 | 0 |
| 4 | 1 | | 2016-10-25 15:02:24 | 0 |
| 5 | 1 | | 2016-10-25 15:05:16 | 0 |
| 6 | 1 | | 2016-10-25 15:17:42 | 0 |
| 7 | 1 | | 2016-10-25 17:08:17 | 0 |
| 8 | 1 | | 2016-10-25 19:28:20 | 0 |
| 9 | 1 | | 2016-10-25 22:00:24 | 0 |
| 10 | 2 | | 2016-10-26 00:08:56 | 0 |
| 11 | 2 | | 2016-10-26 00:08:57 | 0 |
| 12 | 2 | | 2016-10-26 00:08:58 | 0 |
| 13 | 2 | | 2016-10-26 00:08:59 | 0 |
+----+--------+---------+---------------------+-------------+
13 rows in set (0.01 sec)
6.执行回滚
/home/larry/script/mysqlbinlog -B -vv mysql-bin.000020 --start-position=1410|mysql -uroot -p test
7.回滚后表数据
mysql> select * from box_time;
+----+--------+---------+---------------------+-------------+
| id | box_id | box_key | start_time | push_status |
+----+--------+---------+---------------------+-------------+
| 1 | 1 | | 2016-10-25 12:41:02 | 0 |
| 2 | 1 | | 2016-10-25 12:41:57 | 0 |
| 3 | 1 | | 2016-10-25 14:55:49 | 0 |
| 4 | 1 | | 2016-10-25 15:02:24 | 0 |
| 5 | 1 | | 2016-10-25 15:05:16 | 0 |
| 6 | 1 | | 2016-10-25 15:17:42 | 0 |
| 7 | 1 | | 2016-10-25 17:08:17 | 0 |
| 8 | 1 | | 2016-10-25 19:28:20 | 0 |
| 9 | 1 | | 2016-10-25 22:00:24 | 0 |
+----+--------+---------+---------------------+-------------+
9 rows in set (0.00 sec)
总结：
5.6不支持--table选项
回滚操作是个高危险的操作，最好先将解析或者反解析好的结果管道到文件，确认无误后再线上执行
查找pos点
执行回滚
备注:inside君的5.7版本
