[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# grep -i "CHANGE MASTER" xuanzhi.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329;
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]#
查看数据:
<test>(root@localhost) [(none)]> use xuanzhi
Database changed
<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysqlbinlog -v --base64-output=DECODE-ROWS localhost-bin.000002 |grep -C 10 -i "drop database"
### INSERT INTO `xuanzhi`.`tb1`
### SET
### @1=5
### @2='ee'
# at 290
#170327 21:10:55 server id 1313306 end_log_pos 321 CRC32 0x825a2f99 Xid = 78
COMMIT/*!*/;
# at 321
#170327 21:19:25 server id 1313306 end_log_pos 422 CRC32 0x8c139cac Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1490620765/*!*/;
drop database xuanzhi
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]#
从上面可以看到,误操作前的pos点是321,那我们现在通过binlog来进行数据恢复:
[root@localhost mysql-5.6]# mysqlbinlog --start-position=329 --stop-position=321 localhost-bin.000001 localhost-bin.000002 |mysql -uroot -p123456 xuanzhi
Warning: Using a password on the command line interface can be insecure.
[root@localhost mysql-5.6]#
<test>(root@localhost) [xuanzhi]> select * from tb1; +––±-----+ | id | name | +––±-----+ | 1 | aa | | 2 | bb | +––±-----+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]> show master logs; +–--------------------±----------+ | Log_name | File_size | +–--------------------±----------+ | localhost-bin.000001 | 329 | +–--------------------±----------+ 1 row in set (0.00 sec)
<test>(root@localhost) [xuanzhi]>
进行备份操作:
[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# grep -i "change master" xuanzhi.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329;
[root@localhost ~]#
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value (‘dd’); Query OK, 1 row affected (0.01 sec)
<test>(root@localhost) [xuanzhi]> show master logs; ±---------------------±----------+ | Log_name | File_size | ±---------------------±----------+ | localhost-bin.000001 | 589 | | localhost-bin.000002 | 321 | ±---------------------±----------+ 2 rows in set (0.00 sec)
<test>(root@localhost) [xuanzhi]>
下面进行恢复测试,正常来说先把备份导入:
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]#
查看数据,只有备份的那两条记录:
<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)