MySQL 主从架构-延时从库

延时时间，根据你DBA的 反应时间， 如果在30分钟内可以解决，就设置30分钟。

概念介绍说明：

表示人为主动方式将一个从库进行配置，使从库可以按照指定的时间延时后，再进行和主库完成相应数据信息同步；

功能作用说明：

通常对于数据库服务中的数据信息产生损坏，可能有两方面因素造成：

物理损坏：主机故障、磁盘异常、数据文件损坏...，可以利用传统主从复制方式，规避此类问题，利用从库替代主库工作任务；

逻辑损坏：误删除操作（drop truncate delete），可以利用备份数据+binlog日志方式，可以实现数据信息的修复，但是代价比较高；

利用延时从库同步功能，主要是对逻辑原因造成的数据损坏进行弥补修复，从而避免全备数据恢复业务产生的代价较高问题；

当出现逻辑损坏操作时，可以利用延时从库的延时同步特性，将异常操作不做同步，将从库未做破坏的数据信息恢复到主库中

功能应用实践：

① 创建新的从库环境

[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3309.sock
mysql> set global server_id=9;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)
-- 调整从库server_id信息，避免和主库产生冲突
​
# 可以将主库上的部分数据在从库上先进行同步
[root@baimeidashu-01 ~]# mysqldump -uroot -A -S /tmp/mysql3307.sock --master-data=2 --single-transaction >/tmp/full.sql
-- 在3307主库上进行数据的全备（模拟企业环境的历史数据全备）
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3309.sock
mysql> source /tmp/full.sql;
-- 在3309从库上进行数据的恢复（模拟企业环境的历史数据恢复）
-- 将原有主机的数据先备份，然后从库中进行恢复一部分数据，随后再进行数据信息同步追加
-- 可以利用同步方式有很多：mysqldump xtrabackup clone_plugin
​
# 设置从库连接主库信息，定义从库连接主库同步位置点自动复制
mysql> help change master to
-- 获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@baimeidashu-01 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=156;
-- 通过备份文件获取同步位置点信息
mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.30.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='binlog.000004',
  MASTER_LOG_POS=156,
  MASTER_CONNECT_RETRY=10;
-- 以上配置主从同步信息在从库进行执行；
​
# 利用相应线程实现主从数据库的数据同步复制
mysql> start slave;
-- 在从库上激活数据复制同步功能
​
# 进行核实主从同步功能是否实现
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3307.sock
mysql> create database xiaoh;
-- 在主库模拟创建数据信息
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3307.sock
mysql> show databases;
-- 在从库模拟查看数据信息（确认是否同步数据）
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.30.101
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 347
               Relay_Log_File: baimeidashu-01-relay-bin.000002
                Relay_Log_Pos: 512
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
-- 从库上查看数据同步状态情况，看到上面的两个Yes信息，就表示主从数据同步功能设置成功了

② 配置延时从库功能

# 在从库上配置应用延时同步功能
mysql> stop slave;
mysql> change master to master_delay=300;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
SQL_Delay: 300
SQL_Remaining_Delay: NULL
-- 设置延时时间为300s后同步数据（生产建议延时3~6小时），以及最近事件要做同步的延时剩余时间；

③ 延时从库应用过程

延时从库的应用思路分析：

延时的根本效果是主库执行操作完成后，会经过指定的时间后，从库在执行主库曾经执行的操作；

基于主从同步原理分析，延时同步效果是在SQL线程上进行控制实现的，并非在IO线程上进行控制实现的；

SQL线程的延时控制机制，主要是需要识别同步操作任务的时间戳信息，根据时间戳和延时时间信息结合，判断相关任务是否同步执行；

简述：基于主从同步原理，IO线程同步主库操作事件是持续同步的，只是SQL线程在进行事件信息回放时，进行了延时控制；

案例

企业应用延时从库事件模拟：

| 事件序号 | 操作语句 | 解释说明 | |------|-------------|----------------------------| | 01 | 插入语句 insert | 假设在09:59时，持续有插入操作行为，需要进行同步 | | 02 | 删除语句 drop | 假设在10:00时，产生了删除操作行为，需要避免同步 |

企业异常情况处理过程说明：

1）网站页面需要挂维护页面进行说明；

2）从库服务关闭SQL线程，停止事件任务回放；

3）将从库出现故障前的数据信息，即由于延时配置没有执行的操作回放，到出现故障点的时刻停止回放；

# 核实当前主从同步是完整状态
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+------------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+------------------------+
| binlog.000004 |         347 |                           |                                 |                                  |
+------------------+-----------+-------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
-- 核实主库应用日志文件和事件位置点情况；
mysql> show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 347
Relay_Log_File: baimeidashu-01-relay-bin.000003
Relay_Log_Pos: 277
-- 核实从库应用日志文件和事件位置点情况，确认和主库应用日志信息和事件位置点情况一致；
​
# 延时从库应用效果环境模拟
mysql > create database relaydb;
mysql > use relaydb;
mysql > create table t1 (id int);
mysql > insert into t1 values(1),(2),(3);
mysql > commit;
mysql > insert into t1 values(11),(12),(13);
mysql > commit;
mysql > insert into t1 values(111),(112),(113);
mysql > commit;
mysql > drop database relaydb;
-- 以上操作语句在主库上进行执行；
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+------------------------+
| File                   | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+------------------------+
| binlog.000004 |        1793 |                           |                                 |                                  |
+------------------+-----------+-------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
-- 核实主库应用日志文件和事件位置点情况；
mysql> show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 1793
Relay_Log_File: baimeidashu-01-relay-bin.000003
Relay_Log_Pos: 277
SQL_Delay: 300
SQL_Remaining_Delay: 91
-- 核实从库应用日志文件和事件位置点情况，
​
# 数据信息修复方式一：手工截取日志信息进行回放数据，恢复业务；
# 操作过程01：停止从库SQL线程回放日志事件
mysql > stop slave sql_thread;
-- 停止从库SQL线程，终止持续同步操作，使从库不再回放同步数据；
mysql > show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 3239
Relay_Log_File: baimeidashu-01-relay-bin.000003
Relay_Log_Pos: 1723
Slave_IO_Running: Yes
Slave_SQL_Running: No
SQL_Delay: 300
SQL_Remaining_Delay: N
-- 核实从库SQL线程状态是否为NO，以及获取读取的relay_log日志文件信息
​
# 操作过程02：根据relaylog起点信息以及异常操作位置点信息，截取日志内容信息
起点信息：
Relay_Log_File: baimeidashu-01-relay-bin.000003
Relay_Log_Pos: 1723
​
mysql> show relaylog events in 'baimeidashu-01-relay-bin.000003';
.. 省略部分...
| baimeidashu-01-relay-bin.000003 | 3056 | Query          |         1 |        3239 | drop database relaydb /* xid=745 */ 
-- 获取终点信息 3056
[root@baimeidashu-01 ~]# cd /data/3309/data/
[root@baimeidashu-01 data]# mysqlbinlog --start-position=1723 --stop-position=3056  baimeidashu-01-relay-bin.000003 >/tmp/relay.sql
-- 在从库服务器上完成日志信息的截取操作
​
# 操作过程03：从库中恢复截取日志数据
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| relaydb              |
+--------------------+
-- 核实数据库以及数据表信息已恢复，并且原有主从关系已经彻底奔溃，需要进行主从关系重构
mysql> stop slave;
mysql> reset slave all;
-- 从库身份解除
​
# 数据信息修复方式二：持续延时从库数据回放同步过程，但同步过程停止在异常操作前；
# 操作过程01：停止从库SQL线程回放日志事件
mysql > stop slave sql_thread;
-- 停止从库SQL线程，终止持续同步操作，使从库不再回放同步数据；
mysql > show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 4685
Relay_Log_File: baimeidashu-01-relay-bin.000003
Relay_Log_Pos: 3169
Slave_IO_Running: Yes
Slave_SQL_Running: No
SQL_Delay: 300
SQL_Remaining_Delay: NULL
-- 核实从库SQL线程状态是否为NO，以及获取读取的relay_log日志文件信息
​
# 操作过程02：回放日志事件在异常操作位置点前
mysql> show relaylog events in 'baimeidashu-01-relay-bin.000003';
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
| Log_name                             | Pos   | Event_type     | Server_id | End_log_pos | Info                                                  |
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
...忽略部分..
| baimeidashu-01-relay-bin.000003 | 4394 | Xid            |         1 |        4495 | COMMIT /* xid=757 */                                        |
| baimeidashu-01-relay-bin.000003 | 4425 | Anonymous_Gtid |         1 |        4572 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                  |
| baimeidashu-01-relay-bin.000003 | 4502 | Query       |         1 |        4685 | drop database relaydb /* xid=759 */               |
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
-- 获取异常操作日志文件信息和事件位置点信息，其中位置点信息以Pos列显示的为准，并且是提前一个事务位置点；
mysql > change master to master_delay=0;
-- 在从库重启进行日志回放操作前，关闭从库延迟回放的功能
mysql > start slave until relay_log_file="log_name", relay_log_pos=log_pos;
mysql > start slave until relay_log_file='baimeidashu-01-relay-bin.000003', relay_log_pos=4425;
-- 启动日志信息回放功能，直到指定位置点结束日志信息回放
mysql > start slave until sql_before_gtids="xxxx3:4";
-- 如果开启了GTID功能，也可以按照GTID位置点进行数据信息回放（参考）
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Until_Log_File: baimeidashu-01-relay-bin.000003
Until_Log_Pos: 4425
-- 从库重新回放操作恢复数据后，从库状态信息中SQL还是为NO，是正常的，因为直到指定位置点就终止回放；
​
# 操作过程03：核实异常数据信息是否恢复
mysql > show databases;
+--------------------+
| Database           |
+--------------------+
| relaydb              |
+--------------------+
-- 核实数据库以及数据表信息已恢复，并且原有主从关系已经彻底奔溃，需要进行主从关系重构
mysql> stop slave;
mysql> reset slave all;
-- 从库身份解除
-- 参考官方资料：https://dev.mysql.com/doc/refman/8.0/en/start-replica.html