<Test>[xuanzhi]> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#
我们查看输出的文件:/data/t.sql
[root@localhost mysql3306]# cat /data/t.sql
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='user2' AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='user1' AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#
再次查看输出文件:
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `test`.`user` SET `id`=1, `name`='user1', `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='user2' AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='user1' AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
[root@localhost mysql3306]#
刚刚DELETE的2条记录已经生成了反向INSERT语句,这样恢复就简单多啦:
INSERT INTO `test`.`user` SET `id`=1, `name`='user1', `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
下面我们模拟修改数据的时候,误修改了,如下:
<Test>[xuanzhi]> select * from xuanzhi.tb1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
+------+------+
1 row in set (0.00 sec)
<Test>[xuanzhi]> select * from test.user; +–----±------±-----+ | id | name | age | +–----±------±-----+ | 2 | user2 | 30 | +–----±------±-----+ 1 row in set (0.00 sec)
<Test>[xuanzhi]> update xuanzhi.tb1 set name = ‘MySQL’ where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
<Test>[xuanzhi]> update test.user set age = 20 where id = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#
再查看输出文件:
[root@localhost mysql3306]# cat /data/t.sql
UPDATE `test`.`user` SET `id`=2, `name`='user2', `age`=30 WHERE `id`=2 AND `name`='user2' AND `age`=20;
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='MySQL';
INSERT INTO `test`.`user` SET `id`=1, `name`='user1', `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='user2' AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='user1' AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
[root@localhost mysql3306]#
可以看到生成了反向的UPDATE语句:
UPDATE `test`.`user` SET `id`=2, `name`='user2', `age`=30 WHERE `id`=2 AND `name`='user2' AND `age`=20;
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='MySQL';
下面进行指定库的反向解析,参数为(-d)
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456' -d 'xuanzhi'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='MySQL';
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
<Test>[xuanzhi]> select * from tb2;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+------+------+
3 rows in set (0.04 sec)
<Test>[xuanzhi]> delete from xuanzhi.tb2 where id <2; Query OK, 1 row affected (0.02 sec)
<Test>[xuanzhi]>
这个时候应该如果只指定xuanzhi库,那么tb1和tb2的DML操作的反向操作都会记录下来:
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456' -d 'xuanzhi'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`='aa';
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='MySQL';
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb';
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`='aa';
[root@localhost mysql3306]#
指定单个表tb2:
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456' -d 'xuanzhi' -T 'tb2'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`='aa';
# at 1557
#160308 4:27:23 server id 1283306 end_log_pos 1632 CRC32 0xb67ef6ba Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1457382443/*!*/;
BEGIN
/*!*/;
# at 1632
#160308 4:27:23 server id 1283306 end_log_pos 1683 CRC32 0x219a127c Table_map: `test`.`user` mapped to number 74
# at 1683
#160308 4:27:23 server id 1283306 end_log_pos 1749 CRC32 0xf5e0d39e Update_rows: table id 74 flags: STMT_END_F
#160308 4:27:23 server id 1283306 end_log_pos 1780 CRC32 0x1e62cb77 Xid = 101 COMMIT/!/;
at 1780
#160308 4:40:32 server id 1283306 end_log_pos 1855 CRC32 0x04dfe1f0 Query thread_id=11 exec_time=1 error_code=0 SET TIMESTAMP=1457383232/!/; BEGIN /!/;
at 1855
#160308 4:40:32 server id 1283306 end_log_pos 1907 CRC32 0x897ae6bd Table_map: xuanzhi.tb2 mapped to number 70
at 1907
#160308 4:40:32 server id 1283306 end_log_pos 1950 CRC32 0xea61aff0 Delete_rows: table id 70 flags: STMT_END_F
BINLOG ’ QOfdVhPqlBMANAAAAHMHAAAAAEYAAAAAAAEAB3h1YW56aGkAA3RiMgACA/4C/goDveZ6iQ== QOfdViDqlBMAKwAAAJ4HAAAAAEYAAAAAAAEAAgAC//wBAAAAAmFh8K9h6g== '/!/; ### DELETE FROM xuanzhi.tb2
WHERE
@1=1
@2=‘aa’
at 1950
#160308 4:40:32 server id 1283306 end_log_pos 1981 CRC32 0x49e1ce9c Xid = 113 COMMIT/!/;
View Code
从上面的binlog可以看到开始的--start-position=1557 结束的--stop-position=1981,这一段binlog里做了UPDATE `test`.`user` ... 和 DELETE FROM `xuanzhi`.`tb2` ... 的操作,那么用binlog-rollback.pl应该会生成一个UPDATE和一个INSERT语句
[root@localhost mysql3306]# perl binlog-rollback.pl -f 'localhost-bin.000023' -o '/data/t.sql' -u 'recovery' -p '123456' --start-position=1557 --stop-position=1981 mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql3306]# cat /data/t.sql INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`='aa'; UPDATE `test`.`user` SET `id`=2, `name`='user2', `age`=30 WHERE `id`=2 AND `name`='user2' AND `age`=20;