mysql延时从库配置 mysql从库延迟优化

延时从库

介绍

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

延时从库的使用场景

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

配置多久合适

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

1、配置延迟从库

先搭建一个主从

# 停止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

2、延迟从库恢复数据

2-1、故障恢复思路

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

2-2、误删除库恢复

也可以像2-3 误删除表去操作

# 误删除库  // 主库操作
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

2-3、误删除表恢复

截取单表relay log 从延时从库的当前同步位置，到删除前一个事务，恢复到延时从库中即可。还可以再复制时跳过删除表的事物继续进行复制。

常用参数

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

误删除表 // 主库操作

drop table t1;

停止sql线程 // 延时从库操作

stop slave sql_thread;

获取当前binlog日志 // 主库操作

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

将延时设置为0 // 延时从库操作

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;

关闭GTID 不关闭报错 1782 报错后在执行也没问题 // 延时从库操作

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

跳过删除的那条sql的位置点 // 延时从库操作

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

开启主从 // 延时从库操作

start slave;

开启DTID // 延时从库操作

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

这里会有两种情况，一种是直接把延时从库提升为主库，直接修改代码里的IP即可。一种为将单表备份恢复到主库，详解如下：

备份延时从库 // 延时从库操作

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'

2-4、主库宕机起不来或者数据库起不来恢复

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

2-5、误操作sql恢复

binlog2sql 或者 MyFlsh 回滚 或者 见2-3、误删除表恢复