mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'flashuser'@'127.0.0.1' identified by 'flash123';
Query OK, 0 rows affected (0.00 sec)
我们可以看看现在有的数据:
mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> show master status; +–----------------±---------±-------------±-----------------±------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +–----------------±---------±-------------±-----------------±------------------+ | mysql-bin.000107 | 120 | | | | +–----------------±---------±-------------±-----------------±------------------+ 1 row in set (0.00 sec)
mysql> select * from tb1; +–----±-----+ | id | name | +–----±-----+ | 1 | aa | | 2 | bb | +–----±-----+ 2 rows in set (0.00 sec)
[root@db_server_xuanzhi ~]#python binlog2sql.py -uflashuser -h127.0.0.1 -pflash123 -dxuanzhi --start-file='mysql-bin.000107' > xuanzhi.sql
[root@db_server_xuanzhi ~]#cat xuanzhi.sql
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (3, 'cc'); #start 4 end 290 time 2017-03-23 10:41:34
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (4, 'dd'); #start 321 end 491 time 2017-03-23 10:41:38
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='new_aa' WHERE `id`=1 AND `name`='aa' LIMIT 1; #start 522 end 705 time 2017-03-23 10:41:42
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb' LIMIT 1; #start 736 end 906 time 2017-03-23 10:41:50
[root@db_server_xuanzhi ~]#
[root@db_server_xuanzhi ~]#python binlog2sql.py -uflashuser -h127.0.0.1 -pflash123 -dxuanzhi --start-file='mysql-bin.000107' -B > rollback_xuanzhi.sql
[root@db_server_xuanzhi ~]#cat rollback_xuanzhi.sql
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (2, 'bb'); #start 736 end 906 time 2017-03-23 10:41:50
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='new_aa' LIMIT 1; #start 522 end 705 time 2017-03-23 10:41:42
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=4 AND `name`='dd' LIMIT 1; #start 321 end 491 time 2017-03-23 10:41:38
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=3 AND `name`='cc' LIMIT 1; #start 4 end 290 time 2017-03-23 10:41:34
[root@db_server_xuanzhi ~]#
可以看到生成了跟上面标准SQL相反的SQL了,通过这些反向SQL可以进行误操的数据恢复。
下面我们模拟对线上数据进行误操及恢复的过程:
模拟一:误操把一个表的某些重要记录删除了,进行恢复
我们把tb1的id>=3的数据删除:
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
4 rows in set (0.00 sec)
mysql> delete from tb1 where id >= 3; Query OK, 2 rows affected (0.00 sec)
mysql> select * from tb1; +––±-----+ | id | name | +––±-----+ | 1 | aa | | 2 | bb | +––±-----+ 2 rows in set (0.00 sec)
[root@db_server_xuanzhi ~]#mysql -uroot -p123456 <./rollback_tb1.sql
Warning: Using a password on the command line interface can be insecure.
[root@db_server_xuanzhi ~]#
登录查看一下数据:
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
4 rows in set (0.00 sec)
mysql>
可以看到数据可以正常的恢复。
模拟二:误操作把一个表的数据删除了,经常出现的就是delete没带where条件
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
4 rows in set (0.00 sec)
mysql> show master status; +–----------------±---------±-------------±-----------------±------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +–----------------±---------±-------------±-----------------±------------------+ | mysql-bin.000110 | 345 | | | | +–----------------±---------±-------------±-----------------±------------------+ 1 row in set (0.00 sec)
mysql>
我们用bin2log对这个表进行恢复:
[root@db_server_xuanzhi ~]# python binlog2sql.py -uflashuser -h127.0.0.1 -pflash123 -dxuanzhi -ttb1 --start-file='mysql-bin.000110' -B > rollback_tb1.sql
[root@db_server_xuanzhi ~]# cat rollback_tb1.sql
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (4, 'dd'); #start 4 end 314 time 2017-03-23 13:37:29
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (3, 'cc'); #start 4 end 314 time 2017-03-23 13:37:29
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (2, 'bb'); #start 4 end 314 time 2017-03-23 13:37:29
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (1, 'aa'); #start 4 end 314 time 2017-03-23 13:37:29
[root@db_server_xuanzhi ~]# mysql -uroot -p123456 <./rollback_tb1.sql
Warning: Using a password on the command line interface can be insecure.
再查询一下,数据是否把数据恢复了:
mysql> select * from tb1;
Empty set (0.00 sec)
<Slave_1>[xuanzhi]> show master status; +–----------------±---------±-------------±-----------------±------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +–----------------±---------±-------------±-----------------±------------------+ | mysql-bin.000110 | 345 | | | | +–----------------±---------±-------------±-----------------±------------------+ 1 row in set (0.00 sec)
mysql> select * from tb1; +––±-----+ | id | name | +––±-----+ | 1 | aa | | 2 | bb | | 3 | cc | | 4 | dd | +––±-----+ 4 rows in set (0.00 sec)