MySQL主从延迟复制实践及生产故障案例恢复实践

主库插入完数据1秒以后,从库执行show databases;查看数据是否及时同步了,结果如下：

在从库上并没有看到在主库上创建的数据库lanlan,此时执行间歇性的执行show vps云服务器 slave status\G查看延迟的参数状态如下输出.

在从库没有更新数据处于延迟复制没到时间期间,查看从库的中继日志.

3.MysqL延迟复制原理解析

@H_549_16@mysqL的延迟复制实际上影响的只是sql线程将数据应用到从数据库,而I/O线程早已经把主库更新的数据写入到了从库的中继日志中,因此,在延迟复制期间即使主库宕机了,从库到了延迟复制的时间,依然会把数据更新到和主库宕机时一致.

特别提示：其实MysqL的延迟复制的功能早在几年前,老男孩老师就已经用思想实现了这个功能,并应用于企业生产备份和恢复中了,方法如下：

1)15.2节已经介绍过的,执行MysqL> stop slave sql_thread;把sql线程停掉,然后进行备份,备份期间主库宕机,但是主库的binlog依然会及时发到从库,最终从库依然可以恢复到和主库宕机前的状态.

2)写一个脚本,利用定时任务控制sql_thread的停止和运行,进而库就可以控制实现简单的从库延迟复制功能了,这就是思想的重要性.当然了5.6版本就用软件提供的功能吧,5.6以前的数据库要想实现延迟复制,可以思考下老男孩曾经用过的延迟备份以及延迟复制的思路.

4.MysqL延迟复制恢复案例实践

在企业中,我们要根据业务需求给延迟复制指定一个时间段,例如1个小时后进行该从库复制,那么在这一个小时内,如果主库误更新了数据,那么其他的从库也都傻傻地误更新了数据,如何将这个延迟的从库恢复正常没有误更新数据前的完整状态呢?且看下文的实践.

(1)将从库延迟调整为1小时

模拟环境,将从库延迟调整为3600秒；

(2)模拟在主库写入数据

每隔5秒写入1个库,就当模拟用户写入数据了.

(3)模拟人为破坏数据

模拟人为破坏数据也可以是不带where的update语句.

(4)停止写库恢复数据

当数据库出现误删数据情况时,要想完整恢复数据(特别是update不加条件破坏数据),最好选择对外停止访问措施,需要牺牲用户体验,除非业务可以忍受数据不一致,并且不被二次破坏.从库可以适当继续开放给用户读访问,但是也可能会导致用户读到的数据是坏的数据,需要读者去衡量数据一致性和用户体验的问题.本例使用iptables封堵用户对主库的所有访问.

(5)检查binlog发送和接收是否完成

登录主库执行show processlist;对binlog是否全部发送到该延迟从库进行确认,当然了,也可以登录延迟从库执行show processlist;对从库IO线程是否接收完全部binlog进行状态查询确认.

(6)从库暂停主从复制,并检查数据

从库上执行stop slave;暂停主从复制,并查看数据库是否同步过来.

(7)定位恢复数据对应中继日志?

根据relay-log.info记录的sql线程读取relay-log的的位置解析未应用到从库的relay-bin日志.

(8)解析需要的中继日志

解析sql线程未解析的全部剩余relay-bin中继日志数据,由于模拟数据量不够大,因此本例里只有db02-relay-bin.000002一个中继日志,实际工作中可能有多个,一并解析到一个指定文件或者分不同的文件存放也可.

(9)从解析文件中删除问题sql

将破坏数据库数据的sql语句找到并从已解析的sql语句中删除,这里就是“drop database oldboy5”.

(10)将处理好的sql文件恢复到数据库

将解析后并处理好的relay.sql数据文件恢复到延迟从库.

到此,利用延迟数据库恢复数据完毕,将此库提升为主库(见手工实现主从角色切换章节内容),将VIP指向该“延迟从库”,即新主库提供用户访问,然后,在对其他的破坏的主从数据库进行修复.