MySQL主从复制(Replication)架构特殊从库之延时从库部署实战

一.主从复制中的延迟和延迟从库的区别

    场景说明:
        MySQL的主从复制在处理主库的数据被物理删除(比如磁盘损坏，数据被删除等)时是很容易能起到备份作用。但如果MySQL的主从复制在处理主库逻辑删除(比如"DROP DATABASE ...","DROP TABLE ..."等)是就很难起到备份作用了。
        为了解决主库逻辑删除数据，从库能起到一定的备份作用，我们可以使用延迟从库的方式来实现主从复制。比如有人在主库"9:00"删除了某个业务的数据，而使用延迟从库技术，可以让从库延迟3小时执行，也就是说从库会在"12:00"时才会删除数据。

    延迟从库:
        主库做了某项操作之后，从库的SQL线程延时多长时间"回放"中继日志。

    主从复制中的延迟和延迟从库的区别:
        (1)延迟从库主要是指SQL线程推迟"回放"中继日志的时间，但IO线程该从主库接收数据还是会持续接收并落地到从库的中继日志;
        (2)而主从复制延迟是存在很多原因的，除了SQL线程延迟"回放"日志外，还包括IO线程拉取日志是否有延迟，以及主库，从库的内部或外部的因素(在前面的章节已经介绍过，我这里就不啰嗦了);

    温馨提示:
        生产环境其实没有配置延迟从库也是可以的，我们之前也学习过基于逻辑备份和物理备份的方式来恢复数据，但这样做可能会导致花费的时间比较长。因此在一些核心业务需要快速恢复业务数据时，延迟从库是一个不错的方案哟~

二.延迟从库的配置实战案例

    延迟从库的配置还是比较简单的哟，只需要在从库配置即可。下面两种方式均可以配置延迟从库。

    方案一:(配置延迟从库时，同时停掉IO线程和SQL线程),MySQL 5.6支持该方案。
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
                            SQL_Delay: 0
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "STOP SLAVE;" 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "CHANGE MASTER TO MASTER_DELAY=300;"  # 我这里为了方便看到实验效果，就只设置了5分钟，生产环境中建议大家可以设置在3-6小时左右。
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "START SLAVE;" 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
                            SQL_Delay: 300
        [root@docker201.oldboyedu.com ~]# 

    方案二:(配置延迟从库时，只停掉SQL线程，在此过程中IO线程还是会继续拉取数据的，因此这种方案是我推荐大家使用的哟~)，MySQL5.6不支持该方案。
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
                            SQL_Delay: 0
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "STOP SLAVE SQL_THREAD;" 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "CHANGE MASTER TO MASTER_DELAY=300;" 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "START SLAVE SQL_THREAD;" 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
                            SQL_Delay: 300
        [root@docker201.oldboyedu.com ~]# 

    温馨提示:
        (1)延迟从库的延迟时间取决于运维人员对处理故障时间的容忍时间，如果监控做的比较好，当有人删除数据时，你的微信，钉钉，短信会第一时间得到通知;
        (2)在白天的时候，可以设置在2小时左右是比较合理的，但在晚上你睡着的时候有人删除数据时，可能2小时就不太合适，因此建议延迟时间设置在8-10小时是比较合理的;
        综上所述，生产环境中，我们可以做一主两从的架构，一个普通的主从复制，另一个是延迟从库的复制(延迟时间可以设置在8小时)。如果业务繁忙的情况下，也可以考虑一主一从，而后在从库上开启二进制日志功能，再找一台及其基于从库做延迟从库即可。

三.延迟从库的故障模拟及恢复实战案例

1.模拟数据

    从库开启延时复制:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay  # 查看SQL线程延迟的时间
                            SQL_Delay: 300
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Remaining_Delay  # 查看执行下一条SQL延迟的剩余时间，是根据"SQL_Delay"时间来进行倒计时的。
                  SQL_Remaining_Delay: NULL
        [root@docker201.oldboyedu.com ~]# 

    主库执行以下代码:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 22
        Server version: 5.7.31-log MySQL Community Server (GPL)

        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.

        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

        mysql> 
        mysql> CREATE DATABASE devops CHARSET utf8mb4;
        Query OK, 1 row affected (0.00 sec)

        mysql> 
        mysql> CREATE TABLE devops.cmdb(id int);
        Query OK, 0 rows affected (0.29 sec)

        mysql> 
        mysql> BEGIN;
        Query OK, 0 rows affected (0.00 sec)

        mysql> 
        mysql> INSERT INTO devops.cmdb VALUES (10),(20),(30);
        Query OK, 3 rows affected (0.00 sec)

        mysql> 
        mysql> COMMIT;
        Query OK, 0 rows affected (0.01 sec)

        mysql> 
        mysql> BEGIN;
        Query OK, 0 rows affected (0.00 sec)

        mysql> 
        mysql> INSERT INTO devops.cmdb VALUES (11),(22),(33);
        Query OK, 3 rows affected (0.00 sec)

        mysql> 
        mysql> COMMIT;
        Query OK, 0 rows affected (0.00 sec)

        mysql> 
        mysql> DROP DATABASE devops;
        Query OK, 1 row affected (0.02 sec)

        mysql> 
        mysql> SHOW MASTER STATUS;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000002 |     2772 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
        1 row in set (0.00 sec)

        mysql> 

    观察从库有计时器开始执行，5分钟后会执行主库的执行过的SQL:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
                            SQL_Delay: 300
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Remaining_Delay  # 不难发现，已经开始倒计时了
                  SQL_Remaining_Delay: 228
        [root@docker201.oldboyedu.com ~]# 

2.恢复思路

    在做数据恢复之前，我们先来整理一下恢复思路，以下思路可供参考:
        (1)先停止业务，挂维护页面(提示用户:"我们正在做维护操作，敬请谅解，我们会在xx:xx:xx时恢复正常使用")，给用户一个友好的体验;
        (2)停止从库SQL线程("STOP SLAVE SQL_THREAD");
        (3)手动"回放"后续延迟部分的中继日志到从库，注意回放的范围，比如我们只恢复"DROP DATABASE devops"之前的操作;
        (4)恢复业务的方案:
            方案一:
                在从库中导出数据并恢复到主库，相对来说比较耗时。
            方案二:
                直接将从库直接承当原来的主库，以节省业务的恢复时间。

    温馨提示:
        (1)生成环境中要注意恢复的位置点，假设用户是10:30删除的数据，延迟从库的时间是2小时，而运维人员发现数据库被删除是在2分钟内发现的，即10:32，并通知部门领导和开发部门，而后才会着手恢复数据。
        (2)如果用户是10:32发现的，但手动执行"STOP SLAVE SQL_THREAD"命令时是在10:45，这个时候从库执行的数据还是主库在8:45执行的数据，这个时候我们应该去看一下"relay-log.info"文件记录的中继日志目前执行的位置点，以便于知道从哪恢复数据;
        (3)根据上一步获取中继日志的位置点后，手动"回放"后续延迟部分的中继日志到从库，需要确定恢复数据的范围，如果只恢复"devops"数据库相对来说比较简单，只需要在中继日志中找到删除对应的位置点即可，但我们发现数据被删除时是10:32，而手动执行停止SQL线程执行是在10:45，也就是说，这个时候主库在10:30到10:45之前可能还有别的操作，也被IO线程拉取到从库中来了，到底要回复到哪里需要我们根据实际的业务来确定恢复的位置点哟，当然，在做这件事情的时候你可以找整个运维团队来一起商量，到时候背锅也方便，但一定要注意你得起到主导作用，毕竟你才是DBA哟~

3.恢复过程

    (1)停止SQL线程，注意观察"SQL_Remaining_Delay"计时器效果也会终止
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
                            SQL_Delay: 300
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Remaining_Delay  # 不难发现，已经开始倒计时了
                  SQL_Remaining_Delay: 228
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "STOP SLAVE SQL_THREAD;"  # 注意哈，此处我仅停止了SQL线程哟~I/O线程我此处并没有停止
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
                            SQL_Delay: 300
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep SQL_Remaining_Delay  # 不难发现，当我们停止SQL线程后，就没有计时的效果啦~
                  SQL_Remaining_Delay: NULL
        [root@docker201.oldboyedu.com ~]# 

    (2)获取当前从库正在执行"回放"的中继日志文件名称及其位置点
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep "Relay_Log_File"
                       Relay_Log_File: docker201-relay-bin.000002
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep "Relay_Log_Pos"
                        Relay_Log_Pos: 1359
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# cat /oldboyedu/data/mysql3308/relay-log.info  # 除了使用上面的命令截取目前正在执行的中继日志文件名及其对应的位置信息外，还可以基于该文件查看哟~
        7
        ./docker201-relay-bin.000002
        1359
        mysql-bin.000002
        1733
        300
        0
        1

        [root@docker201.oldboyedu.com ~]# 

    (3)根据上一步得到的中继日志文件名称，查看该中继日志发生的事件信息。
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW RELAYLOG EVENTS IN 'docker201-relay-bin.000002';" 
        +----------------------------+------+----------------+-----------+-------------+----------------------------------------+
        | Log_name                   | Pos  | Event_type     | Server_id | End_log_pos | Info                                   |
        +----------------------------+------+----------------+-----------+-------------+----------------------------------------+
        | docker201-relay-bin.000002 |    4 | Format_desc    |         8 |         123 | Server ver: 5.7.31-log, Binlog ver: 4  |
        | docker201-relay-bin.000002 |  123 | Previous_gtids |         8 |         154 |                                        |
        | docker201-relay-bin.000002 |  154 | Rotate         |         7 |           0 | mysql-bin.000002;pos=694               |
        | docker201-relay-bin.000002 |  201 | Format_desc    |         7 |           0 | Server ver: 5.7.31-log, Binlog ver: 4  |
        | docker201-relay-bin.000002 |  320 | Anonymous_Gtid |         7 |         759 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 |  385 | Query          |         7 |         875 | CREATE DATABASE devops CHARSET utf8mb4 |
        | docker201-relay-bin.000002 |  501 | Anonymous_Gtid |         7 |         940 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 |  566 | Query          |         7 |        1044 | CREATE TABLE devops.cmdb(id int)       |
        | docker201-relay-bin.000002 |  670 | Anonymous_Gtid |         7 |        1109 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 |  735 | Query          |         7 |        1177 | BEGIN                                  |
        | docker201-relay-bin.000002 |  803 | Table_map      |         7 |        1226 | table_id: 108 (devops.cmdb)            |
        | docker201-relay-bin.000002 |  852 | Write_rows     |         7 |        1276 | table_id: 108 flags: STMT_END_F        |
        | docker201-relay-bin.000002 |  902 | Xid            |         7 |        1307 | COMMIT /* xid=109 */                   |
        | docker201-relay-bin.000002 |  933 | Anonymous_Gtid |         7 |        1372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 |  998 | Query          |         7 |        1440 | BEGIN                                  |
        | docker201-relay-bin.000002 | 1066 | Table_map      |         7 |        1489 | table_id: 108 (devops.cmdb)            |
        | docker201-relay-bin.000002 | 1115 | Write_rows     |         7 |        1539 | table_id: 108 flags: STMT_END_F        |
        | docker201-relay-bin.000002 | 1165 | Xid            |         7 |        1570 | COMMIT /* xid=112 */                   |
        | docker201-relay-bin.000002 | 1196 | Anonymous_Gtid |         7 |        1635 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 | 1261 | Query          |         7 |        1733 | DROP DATABASE devops                   |
        | docker201-relay-bin.000002 | 1359 | Anonymous_Gtid |         7 |        1798 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 | 1424 | Query          |         7 |        1914 | CREATE DATABASE devops CHARSET utf8mb4 |
        | docker201-relay-bin.000002 | 1540 | Anonymous_Gtid |         7 |        1979 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 | 1605 | Query          |         7 |        2083 | CREATE TABLE devops.cmdb(id int)       |
        | docker201-relay-bin.000002 | 1709 | Anonymous_Gtid |         7 |        2148 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 | 1774 | Query          |         7 |        2216 | BEGIN                                  |
        | docker201-relay-bin.000002 | 1842 | Table_map      |         7 |        2265 | table_id: 110 (devops.cmdb)            |
        | docker201-relay-bin.000002 | 1891 | Write_rows     |         7 |        2315 | table_id: 110 flags: STMT_END_F        |
        | docker201-relay-bin.000002 | 1941 | Xid            |         7 |        2346 | COMMIT /* xid=125 */                   |
        | docker201-relay-bin.000002 | 1972 | Anonymous_Gtid |         7 |        2411 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 | 2037 | Query          |         7 |        2479 | BEGIN                                  |
        | docker201-relay-bin.000002 | 2105 | Table_map      |         7 |        2528 | table_id: 110 (devops.cmdb)            |
        | docker201-relay-bin.000002 | 2154 | Write_rows     |         7 |        2578 | table_id: 110 flags: STMT_END_F        |
        | docker201-relay-bin.000002 | 2204 | Xid            |         7 |        2609 | COMMIT /* xid=128 */                   |
        | docker201-relay-bin.000002 | 2235 | Anonymous_Gtid |         7 |        2674 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
        | docker201-relay-bin.000002 | 2300 | Query          |         7 |        2772 | DROP DATABASE devops                   |
        +----------------------------+------+----------------+-----------+-------------+----------------------------------------+
        [root@docker201.oldboyedu.com ~]# 

    (4)使用mysqlbinlog来截取中继日志
        根据上两步得到起点和终点:
            起点是：1359，终点是：2300
        根据起始点来截取中继日志:
            [root@docker201.oldboyedu.com ~]# mysqlbinlog --start-position=1359 --stop-position=2300 /oldboyedu/data/mysql3308/docker201-relay-bin.000002 > /tmp/recover_devops.sql
            [root@docker201.oldboyedu.com ~]# 
            [root@docker201.oldboyedu.com ~]# ll /tmp/recover_devops.sql 
            -rw-r--r-- 1 root root 4193 2月  16 12:07 /tmp/recover_devops.sql
            [root@docker201.oldboyedu.com ~]# 

    (5)从库恢复数据
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW DATABASES;" 
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | bigdata            |
        | db01               |
        | mysql              |
        | performance_schema |
        | sys                |
        | oldboyedu        |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SOURCE /tmp/recover_devops.sql" 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW DATABASES;" 
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | bigdata            |
        | db01               |
        | devops             |
        | mysql              |
        | performance_schema |
        | sys                |
        | oldboyedu        |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW TABLES FROM devops;" 
        +------------------+
        | Tables_in_devops |
        +------------------+
        | cmdb             |
        +------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SELECT * FROM devops.cmdb;" 
        +------+
        | id   |
        +------+
        |   10 |
        |   20 |
        |   30 |
        |   11 |
        |   22 |
        |   33 |
        +------+
        [root@docker201.oldboyedu.com ~]# 

    温馨提示:
        抛出问题:
            数据恢复完成之后，就先不要忙着启动从库的SQL线程，因为启动SQL线程时肯定会报错，因为中继日志的指向位置为"1359",SQL线程会继续向下"回放"，而我们以及手动恢复了"devops"数据库，因此会抛出1007的异常哟~
        解决方案:
            当然，你也可以手动修改"relay-log.info"文件，将其位置点由"1359"修改为"2300"，这样从库就启动成功了，但是也意味着即将删除数据。
            综上所述，在做该操作之前，请确保恢复的数据已经被迁移走，或者是确定无用后才看可以这样做哟~