在MySQL复制环境中，我们通常只根据 Seconds_Behind_Master 的值来判断SLAVE的延迟。这么做大部分情况下尚可接受，但并不够准确，而应该考虑更多因素。
首先，我们先看下SLAVE的状态：
book@imysql.com [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
***
Master_Log_File: mysql-bin.000327
Read_Master_Log_Pos: 668711237
Relay_Log_File: mysql-relay-bin.002999
Relay_Log_Pos: 214736858
Relay_Master_Log_File: mysql-bin.000327
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
***
Skip_Counter: 0
Exec_Master_Log_Pos: 654409041
Relay_Log_Space: 229039311
***
Seconds_Behind_Master: 3296
***
复制可以看到 Seconds_Behind_Master 的值是 3296，也就是SLAVE至少延迟了 3296 秒。
我们再来看下SLAVE上的2个REPLICATION进程状态：
book@imysql.com [(none)]> show full processlist\G
*************************** 1. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 22005006
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 3293
State: Updating
Info: UPDATE ** SET ** WHERE **
复制可以看到SQL线程一直在执行UPDATE操作，注意到 Time 的值是 3293，看起来像是这个UPDATE操作执行了3293秒，一个普通的SQL而已，肯定不至于需要这么久。
实际上，在REPLICATION进程中，Time 这列的值可能有几种情况：
1、SQL线程当前执行的binlog（实际上是relay log）中的timestamp和IO线程最新的timestamp的差值，这就是通常大家认为的 Seconds_Behind_Master 值，并不是某个SQL的实际执行耗时；
2、SQL线程当前如果没有活跃SQL在执行的话，Time值就是SQL线程的idle time；
而IO线程的Time值则是该线程自从启动以来的总时长（多少秒），如果系统时间在IO线程启动后发生修改的话，可能会导致该Time值异常，比如变成负数，或者非常大。
来看下面几个状态：
设置pager，只查看关注的几个status值
book@imysql.com [(none)]> pager cat | egrep -i 'system user|Exec_Master_Log_Pos|Seconds_Behind_Master|Read_Master_Log_Pos'复制
这是没有活跃SQL的情况，Time值是idle time，并且 Seconds_Behind_Master 为 0
book@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004245 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 13 | Has read all relay log;**
Read_Master_Log_Pos: 445167889
Exec_Master_Log_Pos: 445167889
Seconds_Behind_Master: 0
复制和上面一样
book@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004248 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | | Has read all relay log;**
Read_Master_Log_Pos: 445167889
Exec_Master_Log_Pos: 445167889
Seconds_Behind_Master: 0
#这时有活跃SQL了，Time值是和 Seconds_Behind_Master 一样，即SQL线程比IO线程“慢”了1秒
book@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004252 | Waiting for master to send event | NULL |
| 7 | system user | | floweradmin | Connect | | Updating | update **
Read_Master_Log_Pos: 445182239
Exec_Master_Log_Pos: 445175263
Seconds_Behind_Master: 1
复制和上面一样
book@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004254 | Waiting for master to send event | NULL |
| 7 | system user | | floweradmin | Connect | | Updating | update **
Read_Master_Log_Pos: 445207174
Exec_Master_Log_Pos: 445196837
Seconds_Behind_Master: 1
复制好了，最后我们说下如何正确判断SLAVE的延迟情况：
1、首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异；
2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
3、如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；
复制因此，相对更加严谨的做法是：
在第三方监控节点上，对MASTER和SLAVE同时发起SHOW BINARY LOGS和SHOW SLAVE STATUS\G的请求，最后判断二者binlog的差异，以及 Exec_Master_Log_Pos 和Read_Master_Log_Pos 的差异。
例如：
在MASTER上执行SHOW BINARY LOGS 的结果是：
+------------------+--------------+
| Log_name | File_size |
+------------------+--------------+
| mysql-bin.000009 | 1073742063 |
| mysql-bin.000010 | 107374193 |
+------------------+--------------+
复制而在SLAVE上执行SHOW SLAVE STATUS\G 的结果是：
Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 668711237 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes *** Exec_Master_Log_Pos: 654409041 *** Seconds_Behind_Master: 3296 ***复制
这时候，SLAVE实际的延迟应该是：
mysql-bin.000009 这个binlog中的binlog position 1073742063 和 SLAVE上读取到的binlog position之间的差异延迟，即：
1073742063 - 668711237 = 405030826 个binlog event
并且还要加上 mysql-bin.000010这个binlog已经产生的107374193个binlog event，共
107374193 + 405030826 = 512405019 个binlog event
一般情况下，复制延迟大概率是从库的 sql thread 应用 relay log 慢导致的，很少是因为 io thread 慢导致的。io thread 慢的话是一些故障导致的，是罕见的，可能磁盘慢或者网络慢导致。所以下面我总结了 6 种常见的 sql thread 应用复制延迟发生的原因和解决办法。
1. 主从服务器的配置或者压力不一，从实例的IO能力太弱。
这个需要技术管理手段去规避，务必要保证主备库无论从硬件配置、操作系统配置、mysqld 配置都要一致，不会出现主库比备库性能好很多的情况。不建议备库开读写分离，有财力的公司读写分离的读可以放在另外的只读从库上。
2. 主库的TPS太高
在 MySQL5.5 及之前版本，从库是不支持并行复制功能的，主库上会有并发事务，利用多核 CPU，多个连接同时写入数据，但从库 sql thread 线程应用 relay log 时不支持并行回放，只能单线程回放，那么主库只要并发高的时候，从库永远是复制延迟的。
MySQL5.6 版本优化了这个问题，从库只支持基于 database 的并行复制，也就是如果主库上多个并发事务必须在不同 database 上跑，在从库上才能并行复制，这就非常鸡肋，大多数业务的并发 SQL 都在同一个业务库的，所以这并不能并行复制，这个问题是 MySQL5.6 复制延迟的常见原因，请务必升级到 MySQL5.7 以支持基于逻辑时钟的并行复制。
MySQL5.7 版本的基于逻辑时钟的并行复制，是基于组提交的原理来实现的，首先在主库满足能组提交的事务，都是可以并行回放，因为这些事务都已进入到事务的 prepare 阶段，则说明事务之间没有任何冲突（否则就不可能提交）。但他没有完全模拟主库上的并发执行，所以在从库上的回放力度依然没有主库高，但比 MySQL5.6 鸡肋的基于 database 的并行复制强多了，这个时候已经能消灭大多数复制延迟了。MySQL5.7.22 开始官方推出了基于 writeset 的并行复制，他能把并行复制推到了全新的高度，怎么说呢？就是主库原本不是并行的 SQL，只要不冲突在从库上都能并行，极限情况下，从库回放速度甚至比主库还高！这基本上是消灭了这个原因下的复制延迟。
并行复制是有相关参数推荐的，请视情况调整。
skip_slave_start =0
relay_log =/database/mysql/log/relaylog/3307/relay-bin
relay_log_recovery =1
master_info_repository =table
relay_log_info_repository =table
slave_parallel_type =logical_clock
slave_parallel_workers =4
loose-rpl_semi_sync_master_enabled =1
loose-rpl_semi_sync_slave_enabled =1
loose-rpl_semi_sync_master_timeout =1000
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay =500
binlog_group_commit_sync_no_delay_count = 13
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
复制3. 备份导致写入阻塞
我们有一部分的 MySQL 备份计划是采用备库上每天凌晨执行物理全备的方法做备份。物理全备采用的是开源工具 xtrabackup，实际上无论使用 xtrabackup 或者 mysqldump，备份工具都需要执行 FLUSH TABLES WITH READ LOCK (FTWRL)命令，这个命令需要获取一些锁。
如果此时在备库上有大查询，就堵塞 FTWRL 操作，备库 show processlist 显示“Waiting for table flush”，这个 FTWRL 被阻塞会从而导致这张表上的事务无法提交，也就是影响扩大了，影响到表的写入了。这个一般不常见，因为如果这个从库定位为备库一般就规范使用会不作为读写分离的只读库使用，上面不应该有大查询。
如果库里 MyISAM 表比较多，为了保证备份数据的一致性，FTWRL 操作持续时间会较长，直到所有 MyISAM 表拷贝完成，期间任何写入都会被阻塞，卡在 “Waiting for global read lock”，解决这个问题的办法就是永远不要使用 MyISAM 存储引擎的表，请将他们修改为 InnoDB 存储引擎的表。
#设置以下参数保证没有人能使用MyISAM表，保平安。
[mysqld]
...
default_storage_engine=innodb # MySQL5.7以后官方默认值
default_tmp_storage_engine=innodb # MySQL5.7以后官方默认值
disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MyISAM
复制因为MySQL5.7中MySQL元数据还有MyISAM表，所以以上设置会影响MySQL5.7的小版本升级，需要一些小技巧解决，MySQL8.0的话就直接干吧。
这两种情况是备份导致的从库阻塞，
情况 1 不常见，我建议的解决办法就是如果备份 FTWRL 操作 5 秒内获取不到锁就放弃备份，备份脚本/程序一小时后再尝试备份。
情况 2 这种问题要技术管理方法来规避，永远不要使用 MyISAM，只用 InnoDB。
如果能保证数据库只用 InnoDB 存储引擎，并且使用 MySQL8.0 最新版本，那么 FTWRL 不是问题了，因为最新的 xtrabackup8 备份 MySQL 在只有 InnoDB 存储引擎下是不会使用 FTWRL 的，只会使用轻量级的 backup lock(MySQL8.0 新特性)，那就可以避免因为 FTWRL 引发的相关问题。
另外例如 MHA 这种三机高可用架构，
第一个从库可以作为优先切换从，也就是备库，
第二个从库可以设置不参与切换的从库，用于备份专用库，那么情况1、2均不需要解决了。
4. DDL导致（alter table,create index,optimize table,repair table等等）
DDL 基本就是大事务了，大事务操作会导致复制延迟，因为主库执行完提交后从库才可以回放，他运行时间一般较长，所以 DDL 务必在业务低峰期执行。如果您非常介意主库写锁或者从库复制延迟的话，DDL 是需要专业 DBA 评估和操作的，鉴定是否能采用原生 online DDL 或者可以通过开源工具 pt-osc 或 gh-ost 来执行以加快 DDL、避免长时间锁定或者降低复制延迟。
5. 运行大事务
任何数据库都不应该有大事务，无论 MySQL、PostgreSQL 或者 Oracle，而 MySQL 对大事务是非常敏感的，大事务会导致非常多的问题，不单只复制延迟。同事提到无法拆分大事务，我觉得应该不存在无法拆分的大事务，前面提到大事务操作会导致复制延迟，因为主库执行完提交后从库才可以回放，如果一个事务主库执行 1 小时才能完成，那么从库就要 1 小时后才能开始执行，所以复制延迟就高达 1 小时！如果您使用的是 load data local infile 入库，这是安全上不建议的，安全给的规范是建议关闭 local_infile 参数，也就是禁止使用 load data local infile 操作，您可以修改为 insert，每 5000 条数据 insert 一次，做成 insert xxx into values(),()…() (5000 个 values) 这种一句式的 SQL，循环执行直到跑批完成，如果不冲突，您可以做成并行跑批。如果依然希望使用 load data local infile，毕竟他是最快的入库方式，那您可以拆分每次 load data 的数据量，使大事务变小事务，同样的如果没有冲突，您可以考虑并行 load data。每个 SQL 入库数据量建议不大于 10MB。
6. 对无主键的表进行删除或者更新
这条其实和 5 都是开发规范相关的，应该用技术管理手段——《MySQL开发规范》来限制开发人员，表结构必须要有主键。这个要么是上线 SQL 审核工具拒绝没有主键的表(例如 SQLE，没收钱恰饭，纯公益广告。。)，要么就编写《MySQL开发规范》，需要扯皮时甩开发同事脸上。因为无主键的SQL 容易造成复制延迟，而且这种复制延迟导致的结果是灾难性的，这个复制延迟可能高达一个月，甚至直到世界末日。
举个例子:
delete * from XXX limt 5000;
复制这种 delete + limit 的句子设定了一次只删 5000 行数据，可以有效防止大事务，非常棒。
这个 sql 在主库执行很快。row 格式在复制时要求必须有主键的，这才是性能最高的，因为他是需要根据主键去定位每行数据的位置，然后逐条删除的。如果没有主键，每行都定位不到位置，需要对整张表的记录做一次全表扫描。从库每删除一行数据都要全表扫描一次，你只是删除 5000 行啊，他就要全表扫描 5000 次，此开销非常巨大，主从延迟会很严重。如果你表足够大，删的行数足够多，那你复制延迟永远追不平，此时唯一解决办法就是删除从库基于主库重新备份重做从库。
我们生产上的安全设置 binlog_format=row，所以问题就更严重了，不带 limit 的 delete * from XXX 也不行了，很慢。总之，没主键就是不行。
实际上，没主键但有区分度高的索引一定程度可以加快回放速度
除了前面提到的 SQL 上线审核工具外，还可以考虑上 MySQL8.0.30，开启强制主键和自动创建隐式主键的功能参数，这样您的 MySQL 永远不会有没有主键的表。
sql_require_primary_key=on sql_generate_invisible_primary_key=on复制
案例一
Server version: 5.7.18-log MySQL Community Server (GPL)
看下延迟状况
book@imysql.com:mysql3306.sock : (none) > show slave status\G
Master_Log_File: mysql-bin.013225
Read_Master_Log_Pos: 1059111551
Relay_Master_Log_File: mysql-bin.013161
Exec_Master_Log_Pos: 773131396
Master_UUID: e7c35a95-ffb1-11e6-9620-90e2babb5b90
复制我们看到，binlog文件落后了64个，相当的夸张。
MySQL 5.7不是已经实现并行复制了吗，怎么还会延迟这么厉害？
先检查系统负载。
top
看到mysqld进程其实负载还好，不算太高，也不存在严重的SWAP等问题。
再看I/O子系统负载，没看到这方面存在瓶颈（await\svctm%util都不高）。
sar -d 1复制
再看mysqld进程的CPU消耗。
pidstat -u -p `pidof mysqld` 1复制
虽然mysqld进程的CPU消耗总是超过100%，不过也不算太高。
再检查MySQL复制现场，确认了几个频繁更新的表都有主键，以及必要的索引。相应的DML操作也几乎都是基于主键或唯一索引条件执行的，排除无主键、无合理索引方面的因素。
最后只能祭出perf top神器了。
perf top -p `pidof mysqld`复制
看到perf top最后的报告是这样的
Samples: 107K of event 'cycles', Event count (approx.): 29813195000 Overhead Shared Object Symbol 56.19% mysqld [.] bitmap_get_next_set 16.18% mysqld [.] build_template_field 4.61% mysqld [.] ha_innopart::try_semi_consistent_read 4.44% mysqld [.] dict_index_copy_types 4.16% libc-2.12.so [.] __memset_sse2 2.92% mysqld [.] ha_innobase::build_template复制
我们看到， bitmap_get_next_set 这个函数调用占到了 56.19%，非常高，其次是 build_template_field 函数，占了 16.18%。
经过检查MySQL源码并请教MySQL内核开发专家，最后确认这两个函数跟启用表分区有关系。
查询下当前实例有多少个表分区：
book@imysql.com:mysql3306.sock : (none) > select count(*) from partitions where partition_name is not null;
+----------+
| count(*) |
+----------+
| 32128 |
+----------+
1 row in set (11.92 sec)
复制竟然有3万多个表分区，难怪上面那两个函数调用那么高。
这个业务数据库几个大表采用每天一个分区方案，而且把直到当年年底所有分区也都给提前创建好了，所以才会有这么多。
不过，虽然有这么多表分区，在master服务器上却不存在这个瓶颈，看起来是在主从复制以及大量表分区的综合因素下才有这个瓶颈，最终导致主从复制延迟越来越严重。
知道问题所在，解决起来就简单了。把到下个月底前用不到的表分区全部删除，之后约只剩下1.6万个分区。重启slave线程，问题解决，主从复制延迟很快就消失了。
一般而言，slave相对master延迟较大，其根本原因就是slave上的复制线程没办法真正做到并发。简单说，在master上是并发模式（以InnoDB引擎为主）完成事务提交的，而在slave上，复制线程只有一个sql thread用于binlog的apply，所以难怪slave在高并发时会远落后master。
ORACLE MySQL 5.6版本开始支持多线程复制，配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制。不过，它只能支持一个实例下多个 database 间的并发复制，并不能真正做到多表并发复制。因此在较大并发负载时，slave还是没有办法及时追上master，需要想办法进行优化。
另一个重要原因是，传统的MySQL复制是异步（asynchronous）的，也就是说在master提交完后，才在slave上再应用一遍，并不是真正意义上的同步。哪怕是后来的Semi-sync Repication（半同步复制），也不是真同步，因为它只保证事务传送到slave，但没要求等到确认事务提交成功。既然是异步，那肯定多少会有延迟。因此，严格意义上讲，MySQL复制不能叫做MySQL同步（处女座的面试官有可能会在面试时把说成MySQL同步的一律刷掉哦）。
另外，不少人的观念里，slave相对没那么重要，因此就不会提供和master相同配置级别的服务器。有的甚至不但使用更差的服务器，而且还在上面跑多实例。
综合这两个主要原因，slave想要尽可能及时跟上master的进度，可以尝试采用以下几种方法：
采用MariaDB发行版，它实现了相对真正意义上的并行复制，其效果远比ORACLE MySQL好的很多。在我的场景中，采用MariaDB作为slave的实例，几乎总是能及时跟上master。如果不想用这个版本的话，那就老实等待官方5.7大版本发布吧；
每个表都要显式指定主键，如果没有指定主键的话，会导致在row模式下，每次修改都要全表扫描，尤其是大表就非常可怕了，延迟会更严重，甚至导致整个slave库都被挂起，可参考案例：mysql主键的缺少导致备库hang；
应用程序端多做些事，让MySQL端少做事，尤其是和IO相关的活动，例如：前端通过内存CACHE或者本地写队列等，合并多次读写为一次，甚至消除一些写请求；
进行合适的分库、分表策略，减小单库单表复制压力，避免由于单库单表的的压力导致整个实例的复制延迟；
其他提高IOPS性能的几种方法，根据效果优劣，我做了个简单排序：
更换成SSD，或者PCIe SSD等IO设备，其IOPS能力的提升是普通15K SAS盘的数以百倍、万倍，甚至几十万倍计；
加大物理内存，相应提高InnoDB Buffer Pool大小，让更多热数据放在内存中，降低发生物理IO的频率；
调整文件系统为 XFS 或 ReiserFS，相比ext3可以极大程度提高IOPS能力。在高IOPS压力下，相比ext4有更稳健的IOPS表现（有人认为 XFS 在特别的场景下会有很大的问题，但我们除了剩余磁盘空间少于10%时引发丢数据外，其他的尚未遇到）；
调整RAID级别为raid 1+0，它相比raid1、raid5等更能提高IOPS性能。如果已经全部是SSD设备了，可以2块盘做成RAID 1，或者多快盘做成RAID 5（并且可以设置全局热备盘，提高阵列容错性），甚至有些土豪用户直接将多块SSD盘组成RAID 50；
调整RAID的写cache策略为WB或FORCE WB，详情请参考：常用PC服务器阵列卡、硬盘健康监控 以及 PC服务器阵列卡管理简易手册；
调整内核的io scheduler，优先使用deadline，如果是SSD，则可以使用noop策略，相比默认的cfq，个别请客下对IOPS的性能提升至少是数倍的。
