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

mysql> create database lanlan;

Query OK, 1 row affected (0.00 sec)

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

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| alex_python        |

| mysql              |

| performance_schema |

+——————–+

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

mysql> show slave status\G

…省略若干…

SQL_Delay: 20

SQL_Remaining_Delay: 13

#剩于13秒执行复制。

Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

…省略若干…

1 row in set (0.00 sec)

mysql> show slave status\G

…省略若干…

SQL_Delay: 20

SQL_Remaining_Delay: 9

#剩于9秒执行复制。

Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

…省略若干…

1 row in set (0.00 sec)

mysql> show slave status\G

…省略若干…

SQL_Delay: 20

SQL_Remaining_Delay: NULL

#复制完成后，没有新数据更新的状态。

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

…省略若干…

1 row in set (0.00 sec)

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

[root@db02 data]# pwd

/application/mysql/data

[root@db02 data]# mysqlbinlog db02-relay-bin.000002

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create database lanlan

#中继日志确已经有了创建的语句，说明IO线程还是实时在工作的。

3.MySQL延迟复制原理解析

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秒；

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_DELAY = 3600;

Query OK, 0 rows affected (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

mysql> show slave status\G

…省略若干…

SQL_Delay: 3600

SQL_Remaining_Delay: 2414

Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

…省略若干…

1 row in set (0.00 sec)

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

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

[root@db01 ~]# for n in {1..5}

> do

>     mysql -e “create database oldboy$n”

>     sleep 5

> done

提示：Shell脚本知识可参考《跟老男孩学习Linux运维:Shell编程实战》一书。

(3)模拟人为破坏数据

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

mysql> drop database oldboy5;   

#删除oldboy5数据库，后面就是把这个数据恢复回来，别的数据还得有。

Query OK, 0 rows affected (0.00 sec)

mysql> show databases like ‘oldboy%’;

+——————–+

| Database (oldboy%) |

+——————–+

| oldboy1            |

| oldboy2            |

| oldboy3            |

| oldboy4            |

+——————–+

4 rows in set (0.00 sec)

#此时，所有的从库都已经是坏数据了，只有延迟从库是好的，但是是一小时前的数据。

(4)停止写库恢复数据

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

[root@db01 ~]# iptables -I INPUT -p tcp –dport 3306 ! -s 172.16.1.51 -j DROP 

#非172.16.1.51禁止访问数据库3306端口,51是主库IP。

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

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

mysql> show processlist;

+—-+—————————————+—————+

| 12 | rep  | 172.16.1.52:39043 | NULL | Binlog Dump |  709 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |

+—-+————————————+——————+

2 rows in set (0.00 sec)

#上述提示表示主库已经发送完所有Binlog日志到从库了。

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

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

mysql> stop slave;

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| alex_python        |

| mysql              |

| performance_schema |

+——————–+

4 rows in set (0.00 sec)

#提示：因为延迟时间还未到，因此数据不会同步到该延迟从库。

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

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

[root@db02 data]# pwd           

#进入到中继日志所在的目录。

/application/mysql/data

[root@db02 data]# ls -l *relay* 

#查看中继日志相关信息。

-rw-rw—-. 1 mysql mysql 172 9月  13 17:32 db02-relay-bin.000001

-rw-rw—-. 1 mysql mysql 993 9月  13 17:37 db02-relay-bin.000002

-rw-rw—-. 1 mysql mysql  48 9月  13 17:32 db02-relay-bin.index

-rw-rw—-. 1 mysql mysql  61 9月  13 17:32 relay-log.info

#SQL线程读取中继日志位置信息。

[root@db02 data]# cat relay-log.info  

#查看中继日志应用的位置信息。

7

./db02-relay-bin.000002  #SQL线程读取中继日志的文件名信息。

284                      #SQL线程读取中继日志位置点信息。

oldboy-bin.000024

309

3600

0

1

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

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

[root@db02 data]# mysqlbinlog –start-position=284 db02-relay-bin.000002 >relay.sql 

#根据上述的relay-log.info的中级日志文件和位置信息进行解析中继日志，此命令的用法前面章节已经讲解过了，此不累述。

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

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

[root@db02 data]# egrep “drop database oldboy5” relay.sql   

#检查是否存在误删的SQL语句。

drop database oldboy5

[root@db02 data]# sed -i ‘/drop database oldboy5/d’ relay.sql

#删除，注意别删多了。

[root@db02 data]# egrep “^drop database oldboy5” relay.sql   

#检查删除结果。

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

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

[root@db02 data]# mysql<relay.sql   

 #这步就是从停止slave复制开始，根据relay-log.info位置手工将剩下的所有日志数据恢复到数据库中，需要注意就是提前要清理破坏数据库的语句，在恢复。

[root@db02 data]# mysql -e “show databases like ‘oldboy%’;”

+——————–+

| Database (oldboy%) |

+——————–+

| oldboy1            |

| oldboy2            |

| oldboy3            |

| oldboy4            |

| oldboy5            |   #被删除的oldboy5数据库已经找回。

+——————–+

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