mysql 复制延迟诊断_MySQL之 从复制延迟问题排查

一、从库复制延迟问题

1、可能的原因如下

(1)主从服务器处于不同的网络之中，由于网络延迟导致；

(2)主从服务器的硬件配置不同，从服务器的硬件配置(包括内存，CPU，网卡等)远低于主服务器；

(3)主库上有大量的写入操作，导致从库无法实时重放主库上的binlog；

(4)主库上存在着大事务操作或者慢SQL，导致从库在应用主库binlog的过程过慢，形成延迟；

(5)数据库实例的参数配置问题导致，如：从库开启了binlog，或者配置了每次事务都去做刷盘操作；

2、主从同步延迟问题判断

(1)根据从库上的状态参数判断

mysql-server-3307> SHOW SLAVE STATUS \G

在输出结果中找到Seconds_Behind_Master参数，这个参数表示的是从库上的IO线程和SQL线程相差的时间，然后根据该参数值判断，这个值只是初步判断，不能由这个值来下结论，有如下几种情况：

a、0：表示无延迟，理想状态；

b、NULL：表示从库上的IO线程和SQL线程中，有某一个线程出现问题，可以再次查看Slave_IO_Running和Slave_SQL_Running的值是否都为Yes；

c、大于0：表示主从已经出现延迟，这个值越大，表示从库和主库之间的延迟越严重；

d、小于0：这个值在官方文档中没有说明，通常不会出现。如果出现，那恭喜你中奖了，撞见MySQL的bug了；

(2)根据主从库上面当前应用的二进制日志文件名称或者重放日志的位置来判断

① 同时打开两个MySQL的命令行窗口，分别打开主库和从库，在第一个窗口上执行查看主库当前状态的命令

mysql-server-3306> SHOW MASTER STATUS \G

*************************** 1. row ***************************

File: mysql-bin.000017

Position: 120

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

② 在第二个从库的命令行窗口执行如下命令

mysql-server-3307> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

...

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 120

Relay_Log_File: relay-log.000016

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 120

Relay_Log_Space: 613

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

...

Seconds_Behind_Master: 0

...

Replicate_Ignore_Server_Ids:

Master_Server_Id: 3

Master_UUID: 2dbbf79b-5d9f-11e8-8004-000c29e28409

Master_Info_File: /mysql_data/3307/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

③ 比较从库上的Master_Log_File和Relay_Master_Log_File文件之间是否有差异

a、如果有差异，则说明主从延迟很严重；

b、如果没有差异，则比较Read_Master_Log_Pos和Exec_Master_Log_Pos的差异，这俩参数分别表示从库当前读取到的主库的二进制日志文件位置点和已经执行到的位置点；

c、如果上述输出都没有差异，可以通过主库上"show master status"和从库上"show slave status"的结果作比较。主要比较主库的"File"和从库的"Master_Log_File"，主库上的"Position"和从库上的"Read_Master_Log_Pos"；

3、主从延迟解决办法

(1)判断是否由于网络导致

方法：测试主从库之间的网络延迟，比如测试ping延迟。同时可以检查主从同步的时候是否使用了主库的域名来同步，而域名解析速度可能会特别慢。或者使用其他测试工具；

(2)判断是否由于硬件环境导致

方法：确认主从库的硬件配置是否相差较大，如果配置参数相差较大，可以排查从库上的CPU，内存，IO使用率来判断是否因为硬件配置导致；

(3)判断是否在主库上有大量的DML操作

方法：可以再主库上通过"show full processlist"命令查看当前正在执行的sql，查看是否有大量正在执行的SQL，或者观察主库的CPU和内存使用率，判断是否有高并发操作；

(4)判断是否有慢SQl，可以再主库上临时打开慢SQL记录，临时打开方法如下

#开启慢SQL功能并查看是否生效

mysql-server-3306> SET @@GLOBAL.slow_query_log = ON;

mysql-server-3306> SHOW VARIABLES LIKE 'slow_query_log';

#设置慢SQL的时间并查看是否生效，单位为s，表示大于多少秒的SQL会被记录

mysql-server-3306> SET @@GLOBAL.long_query_time = 5;

mysql-server-3306> SHOW VARIABLES LIKE 'long_query_time';

#设置慢SQL记录日志路径并查看是否生效。注意，这个目录必须对MySQL用户有读写权限

mysql-server-3306> SET @@GLOBAL.slow_query_log_file = '/mysql_data/mysql-slow.log';

mysql-server-3306> SHOW VARIABLES LIKE 'slow_query_log_file';

(5)检查从服务器参数配置是否合理

① 查看从库是否开启了binlog日志，从库上执行如下命令查看

mysql-server-3307> SHOW VARIABLES LIKE 'log_bin';

如果开启了binlog日志，而且从库未充当其他库的主库时，可以将从库上的binlog关闭，否则会增加从库负担，每次重放完成主库的binlog还要记录到自身的binlog

② 查看从库上的sync_binlog参数的值，这个参数表示的是事务提交多少次之后，由MySQL来将binlog_cache中的数据刷新到磁盘，有以下几种值：

0：表示事务提交之后，MySQL不做刷新binlog_cache到磁盘的操作，而是由操作系统来定时自动完成刷盘操作，这种操作对性能损耗最少，但是也最不安全；

n：表示提交n次事务之后，由MySQL将binlog_cache中的数据刷新到磁盘，如果开启，会对性能有一定程度的损耗。所以，从库上如果延迟很严重，可以考虑将该参数的值设为0；

mysql-server-3307> SET @@GLOBAL.sync_binlog = 0;

mysql-server-3307> SHOW VARIABLES LIKE 'sync_binlog';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sync_binlog | 0 |

+---------------+-------+

1 row in set (0.00 sec)

③ 如果从库中要同步的数据库使用的是InnoDB存储引擎，可以查看innodb_flush_log_at_trx_commit参数。这个参数表示事务执行完成之后，多久的频率刷新一次日志到磁盘上，可用的值有如下几种：

0：表示MySQL会将日志缓冲区中的数据每秒一次地写入日志文件中，并且日志文件的刷盘操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作，效率最搞，但是安全性也比较低，可能会丢失数据；

1：每一次事务提交都需要把日志写入磁盘，这个过程是特别耗时的操作；

2：每一次事务提交之后，不会自动触发日志刷盘的操作，而是由操作系统来决定什么时候来做刷新日志的操作，在操作系统挂了的情况下才会丢失数据；

如果在主从延迟非常严重的情况下，可以将从库的该参数设置为0，以提高从库上重放主库二进制日志的效率。

mysql-server-3307> SET @@GLOBAL.innodb_flush_log_at_trx_commit = 0;

mysql-server-3307> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 0 |

+--------------------------------+-------+

1 row in set (0.00 sec)

注意：上述设计到修改MySQL数据库实例的操作中，修改之后会立刻生效，但是重启实例之后，会失效，如果要永久修改，则需要编辑mysql配置文件，然后重启。