MySQL主从：延时从库恢复全解

是我们认为配置的一种特殊从库.人为配置从库和主库延时N小时,以秒为单位。
SQL线程延时:数据已经写入relaylog中了,SQL线程延迟写入N小时。

误删除库
误操作表
误删除表

一般企业建议3-6小时,具体看公司相应人员对于故障的反应时间。

# 停止sql线程  // 不让他继续写库
stop slave sql_thread;
# 配置延迟 10800秒(三个小时) 生产环境建议3-6个小时
change master to master_delay=10800;
# 开启sql线程
start slave sql_thread;
# 查看主从状态
show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                    SQL_Delay: 10800
          SQL_Remaining_Delay: 8101

1主1从,从库延时3小时,主库误删除1个表
1. 三小时内之内 相应人员反馈误删除
2. 停从库SQL线程
3. 获取主库binlog postion 位置点
  * 删除前一个事物 commit 后 postion
  * 删除后一个事物 begin  前 postion
4. 设置延迟为0
5. 将binlog恢复到删除前一个事物
6. 关闭主从
7. 指定删除后的 postion
8. 开启主从
9. 将备份恢复到主库

# 误删除库  // 主库操作
drop database  test;
# 延时从库停止sql线程  // 延时从库操作
stop slave sql_thread;
# 查看当前正在使用的 Relay_Log_File & Relay_Log_Pos 的位置点  // 延时从库操作
show slave status\G
# mysqlbinlog工具获取删除前一个位置点  // 延时从库操作
mysqlbinlog --base64-output=decode-rows -vvv db83-relay-bin.000007 |grep -A 50 -B 50 'drop database'
# 截取从库relay log中未写入部分到删除前一个位置点的relay log  // 延时从库操作 
mysqlbinlog --start-position=***  --stop-position=***   db83-relay-bin.000007  >/tmp/test.sql
# 恢复到延时从库中  // 延时从库操作
source /tmp/test.sql
# 解除主从关系 // 延时从库操作
stop salve;
reset slave all;
# 后面可以延时从库切换为主库，或者将延时从库中被删的库进行备份，恢复到主库。具体如下：
# 1、切换为主库
直接将业务的IP地址指定为延时从库，或者将读写分离中的主库改为延时从库即可
# 2、将延时从库进行备份(单库备份)  // 延时从库操作
mysqldump -udba  -p  -h127.0.0.1 -P3306  -R -E --triggers --master-data=2  --single-transaction \
 --set-gtid-purged=OFF  -B  database_name   >/tmp/database_name.sql

change master to
master_host=
master_user=
master_port=
master_password=
master_delay=
master_log_file=
master_log_pos=

drop table t1;

stop slave sql_thread;

show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 2897

mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002|grep -A 50  -B 50  'DROP TABLE'
COMMIT/*!*/;
# at 2430
# at 2495
DROP TABLE `t1` /* generated by server */
# at 2610
# at 2675
BEGIN

change master to master_delay=0;

start slave sql_thread until master_log_file='mysql-bin.000002',master_log_pos=2430;

show slave status\G
          Exec_Master_Log_Pos: 2430

select * from t1;

stop slave;
SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';

change master to
master_log_file='mysql-bin.000002',
master_log_pos=2675;

start slave;

SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON';

mysqldump   -R -E --triggers --master-data=2  --single-transaction \
 --set-gtid-purged=OFF   test t1    >/tmp/test_t1.sql

scp /tmp/test_t1.sql  root@10.10.8.183:/tmp

nohup mysql -e "use test;set sql_log_bin=0;source /tmp/test_t1.sql" &>/tmp/test_t1.log &

tail -f /tmp/test_t1.log  
watch -n 5 'ps -ef|grep [s]ource'

stop slave;
change master to master_delay=0;
start slave;