主从延迟的常见原因、判断方法和解决方案

一、主从延迟的常见原因?

1、大事务，从库回放时间较长，导致主从延迟

2、主库写入过于频繁，从库回放跟不上

3、参数配置不合理

4、主从硬件差异

5、网络延迟

6、表没有主键或者索引大量频繁的更新

7、一些读写分离的架构，从库的压力比较大

8、大量的DDL导致的延迟

show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.137.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2070
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 618
        Relay_Master_Log_File: mysql-bin.000003
             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: 2070
              Relay_Log_Space: 827
              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: 2666
                  Master_UUID: 0b796d2d-431b-11ef-a867-000c29012dba
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 0b796d2d-431b-11ef-a867-000c29012dba:9
            Executed_Gtid_Set: 0b796d2d-431b-11ef-a867-000c29012dba:1-9
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

二、怎么判断主从延迟

1.使用Seconds_Behind_Master(从库延迟的秒数)判断:

Seconds_Behind_Master为0,可能没有延迟

比如网络异常时,Seconds_Behind_Master为0,并不代表主从没有延迟

2.对比位点

(1)对比master_log_file和relay_master_log_file(基于位点的复制)

master_log_file: IO线程正在读取的主库binlog文件名

relay_master_log_file: SQL线程正在执行的事务对应的主库的binlog文件

如果这2个不同的话说明主从是落后很多的

(2)Read_Master_Log_Pos对比Exec_Master_Log_Pos(基于位点的复制)

Read_Master_Log_Pos: IO线程正在读取的主库的binlog文件中的位点

Exec_Master_Log_Pos: SQL线程正在读取和执行的事务对应主库binlog文件中的位点

如果两者差的很多,说明是延迟的

3.对比GTID(基于GTID复制)

Retrieved_Gtid_Set和Executed_Gtid_Set对比"-"右边最后的那个数字,如果Executed_Gtid_Set落后很多,说明存在延迟

三、主从延迟的解决方案?

1.避免大事务

把大事务拆分为小事务

2.开启多线程

3.调整一些mysql参数

(1)innodb_flush_log_at_trx_commit: 控制redolog刷新到磁盘的策略

mysql> show global variables like "innodb_flush_log_at_trx_commit";

若为0: 把redo log buffer写到操作系统缓存,再刷新到磁盘

若为1: 每次提交事务都将redo log buffer写到操作系统缓存,再刷新到磁盘(最慢)

若为2:每次事务提交都将redo log buffer写到操作系统缓存,再由操作系统来管理刷盘

设置为0或者2,可以临时缓解一下从库的延迟

(2)sync_binlog

mysql> show global variables like "sync_binlog";

若为0: 表示二进制日志从不同步到磁盘,依赖操作系统刷盘

若为1: 表示二进制日志每组事务提交都会刷盘(尽管很安全,但是是最慢的)

若为n: 每n组事务提交落盘一次(比如:若为100: 每100组事务提交落盘一次)

建议设置为大于1的值,比如100

4.调整从库机器配置

5.网络问题解决方案

（1）调整复制延迟参数

（2）提高网络宽带和稳定性

（3）使用复制链

在网络延迟较大的情况下，可以考虑多级主从复制，也称为复制链。

在这种情况下，一个从节点可以成为另一个从节点的主节点，这样可以分散同步压力并降低网络延迟对整个系统的影响

（4）监控和报警

设置监控和警报以及实时监控网络延迟，以便可以采取措施来应对问题。一些监控工具如Prometheus、Grafana等可以帮助你实现这一目标

（5）网络优化

使用网络优化技术，例如使用专用VPN，SD-WAN等,来减少延迟和丢包.

（6）备份和恢复策略

考虑实施定期的备份和恢复策略，以便在数据同步出现问题，可以迅速恢复丢失的数据。

6.增加主键，索引

7.调整架构

(1)将大表单独使用一个从库复制,其他从库忽略这张表的复制

(2)具体看业务场景

8.使用PT工具执行耗时长的DDL

比如增加字段,在写好的表结构中插入字段,在某个字段之前/后

特别是5.6,强烈建议使用这个工具,因为5.6直接执行就会锁表的,

主库执行完之后,再到从库执行,会导致很长的时间延迟