导语 在日常工作中可能会存在误删数据的情况,今天就简单介绍下如何利用延迟库进行数据库的快速恢复。 步骤1.环境准备 建立一个测试的主从库,写入一些测试数据,非本文要点,过程略。 2.设置延迟同步 在原有同步信息的基础上进行如下操作,设置延迟同步1小时 # 设置延迟1小时 mysql> stop slave; mysql> CHANGE REPLICATION SOURCE TO SOURCE_DELAY=3600; mysql> start slave; mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.5.160 Master_User: repl Master_Port: 3314 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 6536 SQL_Delay: 3600 -> 设置后,这里可以看到延迟的信息 SQL_Remaining_Delay: NULL Retrieved_Gtid_Set: 4b4539dd-2fc1-11ec-949b-70b5e873a570:2-53662 Executed_Gtid_Set: 4b4539dd-2fc1-11ec-949b-70b5e873a570:1-36546, d2c64073-2cb5-11ec-b4d1-70b5e873a570:1-2 1 row in set, 1 warning (0.00 sec) 3.假设在主库上进行了一个误删的操作# 误删一条id=9998的数据 mysql> delete from t1 where id=9998; Query OK, 1 row affected (0.32 sec) # 主库已经没有了 mysql> select * from t1 where id=9998; Empty set (0.00 sec) # 从库还能查到数据 mysql> select * from t1 where id=9998; +-----+------+------+------+------+ | id | c1 | c2 | c3 | c4 | +-----+------+------+------+------+ | 9998| 983 | xAP9 | mQeN | 8Eu2 | +-----+------+------+------+------+ 1 row in set (0.00 sec) 4.解析主库的binlog文件 这个步骤目的是找到主库执行删除操作时候相应的GTID值的上一个GTID值 # 先解析出binlog mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000001 > 01.sql # 找到被删那条记录的GTID,再往上一条记录 SET @@SESSION.GTID_NEXT= "4b4539dd-2fc1-11ec-949b-70b5e873a570:54230"/*!*/; # at 15959245 #211103 14:43:25 server id 33145160 end_log_pos 15959316 Query thread_id=53817 exec_time=0 error_code=0 # at 15959377 #211103 14:43:25 server id 33145160 end_log_pos 15959436 Delete_rows: table id 270 flags: STMT_END_F ### DELETE FROM `test`.`t1` ### WHERE ### @1=9998 /* INT meta=0 nullable=0 is_null=0 */ ### @2="983" /* VARSTRING(256) meta=256 nullable=1 is_null=0 */ ### @3="xAP9" /* VARSTRING(256) meta=256 nullable=1 is_null=0 */ ### @4="mQeN" /* VARSTRING(256) meta=256 nullable=1 is_null=0 */ ### @5="8Eu2" /* VARSTRING(256) meta=256 nullable=1 is_null=0 */ # at 15959436 #211103 14:43:25 server id 33145160 end_log_pos 15959463 Xid = 163705 COMMIT/*!*/; # at 15959463 5.从库设置同步停止的时间点 通过步骤4找到的删除操作的GTID值,我们修改下从库的同步状态,需要说明的是,当主库出现误删数据的时候,延迟库一定要第一时间停止同步。 # 从库同步到删数据的gtid值,再往上一条gtid,设置同步截止点 mysql> STOP SLAVE; mysql> START REPLICA UNTIL SQL_AFTER_GTIDS="4b4539dd-2fc1-11ec-949b-70b5e873a570:54229"; mysql> START SLAVE; 6.复制同步停止# 等待同步到对应截止点后,同步的SQL线程会停止 mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.5.160 Master_User: repl Master_Port: 3314 Connect_Retry: 60 Slave_IO_Running: Yes Slave_SQL_Running: No -> 到达设定的GTID值后,SQL线程会中断 Until_Condition: SQL_AFTER_GTIDS -> 设置后这里会出现同步截止的关键信息 Master_Server_Id: 33145160 Master_UUID: 4b4539dd-2fc1-11ec-949b-70b5e873a570 Master_Info_File: mysql.slave_master_info SQL_Delay: 3600 SQL_Remaining_Delay: NULL Master_Retry_Count: 86400 Retrieved_Gtid_Set: 4b4539dd-2fc1-11ec-949b-70b5e873a570:2-54230 Executed_Gtid_Set: 4b4539dd-2fc1-11ec-949b-70b5e873a570:1-54229, d2c64073-2cb5-11ec-b4d1-70b5e873a570:1-3 Auto_Position: 1 7.数据恢复 后续我们可以对这个表进行相应操作,例如把这个表导出再导入到主库,然后再恢复中间的logbin数据。 总结 以上只是模拟一条数据误删的恢复过程,通过闪回工具甚至手动找到相应误删的数据进行恢复会更快,但是对于 truncate ,drop ,delete忘了带where条件 的删除,用闪回工具可能就没办法了,相比备份恢复用延迟库效率会更高。 另外需要注意,如果从库开启了MTS,需要注意开启 slave_preserve_commit_order=1 防止从库误删操作先执行了。