MySQL复制滞后、延迟问题及解决方法-创新互联

确认复制延迟的方法

执行

mysql>show slave status\G

如果"seconds_behind_master"不为"0"，需要对其关注，并调查其产生的根本原因

首先需要确认滞后的原因会来自两方面，来自IO_Thread （比如网络连接速度慢，磁盘慢）或 SQL_Thread（施放中继日志里面的SQL过慢）

执行

mysql>show master status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

确认一下两个线程是否运行，如果没有正常运行，确认一下错误日志里面的内容，然后使其运行。

接下来需要确认，是否

Master_log_file

Relay_Master_Log_File

Read_Master_Log_Pos

Exec_Master_Log_Pos

值相等，但是Seconds_Behind_Master从未减少。

然后在主服务上执行SHOW MASTER STATUS，确认当前主服务的日志文件和位置，Exec_Master_Log_Pos 是否落后 Read_Master_Log_Pos。

解决方法

如果从服务有多台的情况下，检查：

全部的主机是否使用相同的硬件

是否使用同一版本的MySQL（从服务器的版本应等于或高于主服务器版本）

检查路由、网络防火墙

IO 线程滞后

通常是网络原因或者饱和原因

使用大容量的binlog

检查网络和磁盘的速度

SQL 线程滞后

1 、检查在从服务器上执行过长的事务

2 、检查在从服务器上活动集中的IO操作

停止IO线程，确认问题是否解决

改变innodb_flush_log_at_trx_commit参数，确认是否解决，尝试改为0，确认一下结果，最终可以尝试改为2。

减少max_relay_log_size值 避免读取负载过大，例如8M mysql> set global max_relay_log_size = 8*1024*1024;

检查从服务器没有使用 log_slave_updates = 1

3 、考虑使用  Multi Threaded Slave (MTS)

4 、确认是否过多的表没有使用主键

如果日志采用row或MIXED格式，如果表没有主键，会引起延迟。

这是因为当在主服务器上执行事务时，可以使用任何可用的键或直接的表扫描，

当在从服务器SQL上应用行事件时不使用这些。

从主服务器的二进制日志中的行事件被逐行地应用到从服务器的匹配行映像时，

如果使用主键惟一地标识每一行时，就可以快速地将更改应用到从服务器的适当的行映像。

然而，当没有定义主键时，对于主服务器上的每一个受影响的行，整个行映像都必须逐行进行比较。

5 、在MySQL 5.7中如果持续的统计特性会对MySQL复制产生负面影响就会禁用它并检查是否有任何改进。