主从复制延迟推荐解决方案

目录

• 主从复制延迟推荐解决方案
  • 现象描述
  • 2个解决方案
    • 方案一
    • 方案二

主从复制延迟推荐解决方案

MySQL 的复制是主库主动推送日志到从库去的，是属于“推”日志的方式来做同步,然后从库的I/O线程等待主库上的Binlog Dump线程发送
事件并更新到中继日志Relay Log，SQL线程读取中继日志Relay Log并应用变更到数据库。这个过程中是属于异步操作，所以从库上的数据和
主库会存在一定的延迟，导致一定的性能问题。
如何提高复制的性能

现象描述

​ MySQL从库上通过SHOW PROCESSLIST可以看到有且仅有一个SQL线程在解析中继日志Relay Log并应用，例如：

mysql> show processlist G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: 192.168.211.1:63440
     db: ssm
Command: Sleep
   Time: 2185
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: 192.168.211.1:63488
     db: ssm
Command: Sleep
   Time: 2190
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 15
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 4. row ***************************
     Id: 16
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 17
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

​ 那么写入压力特别大的场景下，考虑到主库是多线程并发在写入（应用服务器并发连接写入），而从库仅仅只有一个SQL线程在应用日志，就容易出现从库追不上主库的情况，可以在从库上通过SHOW SLAVE STATUS来查看从库落后主库的时间：

mysql> show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.211.136
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 1271
               Relay_Log_File: mysqld-relay-bin.000011
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1271
              Relay_Log_Space: 620
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 136
                  Master_UUID: cb248a05-3538-11eb-8893-005056253f37
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

​ 其中 Seconds_Behind_Master 显示了预估从库落后主库的秒数，不是特别精准，只是一个预估值。

​ 从库的数据落后主库的问题，当然可以通过提高从库配置的硬件来解决。但更推荐通过架构设计来解决这个问题，通过减少从库需要做的写入操作或者在从库上实现多线程写入操作都能够解决。

2个解决方案

方案一

​ 通过拆分减少一个从库上需要数据同步的表来解决。首先考虑配置一主多从的架构，然后在不同的从库上，通过设置不同 replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table或replicate-wild-do-table参数，使得不同的从库复制不同的库/表，减少每个从库上需要写入的数据。
​ 例如，假设主库为M1，从库为S1、S2、S3，其中设置从库S1仅需要复制databaseA，而从库S2仅需要复制databaseB，从库S3仅需要复制databaseC，那么每个从库只需要执行自己需要复制的库/表相关的SQL就可以了，如下图所示。

​ 这时，由于主库M1需要给S1、S2、S3三个从库（或者更多从库）都发送完整的Binlog日志，I/O 和网络压力较大，再改进一下架构：配置 MySQL 多级主从架构减轻主库压力，如下图所示。

1. 主库M1首先给二级主库M2推送完整的Binlog。
2. 二级主库M2打开log-slave-updates配置，保证主库M1传送过来的Binlog能够被记录在二级主库M2的RelayLog和Binlog中；二级主库M2选择BLACKHOLE引擎作为表引擎，降低二级主库上I/O的压力。
3. 为二级主库M2配置3个从库S1、S2、S3，三个从库通过配置不同replicate-do-db等参数，让S1、S2、S3复制不同的库/表。

​ 通过多级主从的方式，提高从库的复制性能，同时尽量降低对主库的影响。

​ 注意：BLACKHOLE引擎就是一个“黑洞”引擎，在创建表的时候，选择BLACKHOLE引擎，那么写入表的数据不会真实地写入磁盘，仅仅记录Binlog日志，极大降低了磁盘的I/O。

​ 方案一的优点在于能够自由拆分从库，方便地把热点数据分散开来；缺点在于维护起来不够简洁，并且由于从库S1、S2、S3上都没有主库完整的数据，在主库M1出现意外宕机的情况，应用处理较为麻烦。需要提前和应用沟通好异常的处理解决方案。

方案二

​ MySQL 5.6提供了基于 Schema的多线程复制，允许从库并行更新。例如，主库上存在 2个Schema，即ssm和replication。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replication        |
| ssm                |
+--------------------+
5 rows in set (0.00 sec)

​ MySQL 5.6的从库在同步主库时，通过设置参数 slave_parallel_workers为 2，让MySQL从库在复制时启动两个SQL线程。参数设置前：

mysql> show variables like '%slave_parallel_workers%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.50    |
+-----------+
1 row in set (0.00 sec)

mysql> show processlist G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: 192.168.211.1:63440
     db: ssm
Command: Sleep
   Time: 3480
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: 192.168.211.1:63488
     db: ssm
Command: Sleep
   Time: 3485
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 15
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 4. row ***************************
     Id: 16
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1297
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 17
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1297
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
5 rows in set (0.00 sec)

设置参数后如下：注意需要先stop slave,再start slave才有效果

mysql> set global slave_parallel_workers=2;
Query OK, 0 rows affected (0.00 sec)

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

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show processlist G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: 192.168.211.1:63440
     db: ssm
Command: Sleep
   Time: 3603
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: 192.168.211.1:63488
     db: ssm
Command: Sleep
   Time: 3608
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 15
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 4. row ***************************
     Id: 18
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 19
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 6. row ***************************
     Id: 20
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 7. row ***************************
     Id: 21
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for an event from Coordinator
   Info: NULL
7 rows in set (0.00 sec)

​ 通过设置slave_parallel_workers 参数，让ssm和replication两个Schema拥有自己独立的SQL线程，这样也大大提高了从库的复制速度。

​ 复制是MySQL数据库中经常使用的一个功能，它可以有效地保证主数据库的数据安全，并减轻主数据库的备份压力，以及分担主数据库的一部分查询压力。

​ -------学自《深入浅出MySQL》