如何监控mysql主从延迟

[TOC]

为什么 Seconds_Behind_Master 不能用来监控主从延迟

来看一个存在主从延迟的场景

对于主库，执行 show master status，重点关注 File 和 Position 指标

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 |  1631495 |              |                  | bd6b3216-04d6-11ec-b76f-000c292c1f7b:1-5588 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

对于从库，执行 SHOW SLAVE STATUS，重点关注以 File 和 Pos 结尾的几个指标

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
              Master_Log_File: mysql-bin.000004     #.当前I/O线程正在读取的主服务器二进制日志文件的名称
          Read_Master_Log_Pos: 1631495              #.当前I/O线程正在读取的二进制日志的位置
        Relay_Master_Log_File: mysql-bin.000004     #.当前slave SQL线程从relay log中读取的正在执行的sql语句，对应主库的sql语句记录在主库的哪个binlog日志中
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 1631237              #.slave的SQL线程当前执行的事件，对应在 Relay_Master_Log_File 中的position
        Seconds_Behind_Master: 1749                 #.slave的SQL线程与I/O线程的时间差

• IO延迟：如果主库的（File，Position）大于（Master_Log_File，Read_Master_Log_Pos），则意味着 IO 线程存在延迟（受网络、从库磁盘io性能影响）
• SQL延迟：如果（Master_Log_File，Read_Master_Log_Pos）大于（Relay_Master_Log_File，Exec_Master_Log_Pos），则意味着 SQL 线程存在延迟（主要受从库磁盘io性能影响）

主库：（File，Position）记录了主库 binlog 的位置
从库：（Master_Log_File，Read_Master_Log_Pos）记录了 IO 线程当前正在接收的二进制日志事件在主库 binlog 中的位置
从库：（Relay_Master_Log_File，Exec_Master_Log_Pos）记录了 SQL 线程当前正在重放的二进制日志事件在主库 binlog 的位置

对于 Seconds_Behind_Master，来自 MySQL 官方手册的解释

• 当从库正在不断地处理更新时（持续不断有event被SQL线程或者I/O线程处理时），此字段显示从库主机当前时间戳和来自主库（原始）的二进制日志中记录的时间戳之间的差异
• 当从库没有任何需要处理的更新时，如果I/O和SQL线程状态都为Yes，则此字段显示为0，如果有任意一个线程状态不为Yes，则此字段显示为NULL
• 本质上，这个字段是度量从库SQL线程和I/O线程之间的时间差，单位为秒。如果主备之间的网络非常快，那么从库的I/O线程读取的主库binlog会与主库中最新的binlog非常接近，所以这样计算得来得值就可以作为主备之间的数据延迟时间，但是如果主备之间的网络非常慢，同时从库的磁盘io非常好，可能导致从库SQL线程正在重放的主库binlog非常接近从库I/O线程读取的主库binlog，而I/O线程因为网络慢的原因可能读取的主库binlog远远落后于主库最新的binlog，此时，这么计算得来的值是不可靠的，尽管这个时候有可能该字段显示为0，但实际上可能从库已经落后于主库非常多了。所以，对于网络比较慢的情况，该值并不可靠。
• 如果主库与从库的server自身的时间不一致，那么，只要从库复制线程启动之后，没有做过任何时间变更，那么这个字段的值也可以正常计算，但是如果修改了server的时间，则可能导致时钟偏移，从而导致这个计算值不可靠。

补充说明

• 当SQL线程重放大事务时，SQL线程的时间戳更新相当于被暂停了（因为一个大事务的event在重放时需要很长时间才能完成，虽然这个大事务也可能会有很多event，但是这些event的时间戳可能全都相同），此时，根据计算公式可以得出，无论主库是否有新的数据写入，从库复制延迟仍然会持续增大（也就是说此时的复制延迟值是不可靠的）。所以就会出现主库停止写入之后，从库复制延迟逐渐增大到某个最高值之后突然变为0的情况
• 举个例子，在主库上执行了一个非常大的event，在这个event在主库上没执行完毕的时候，从库的 Seconds_Behind_Master=0，而当主库执行完毕传到从库上开始执行的时候，Seconds_Behind_Master就会瞬间极大，而当这个大的event在从库重放结束之后又很快恢复。

如何正确的监控mysql主从延迟

利用 heartbeat 心跳表

• 第1步，在主库上创建一个带有时间戳的表（最好设置为内存表ENGINE=MEMORY），比如 heartbeat，该表永远只有一条最新的时间数据
• 第2步，部署一个定时任务，每秒更新主库 heartbeat 表中的时间字段，即该字段记录的时间 = 主库的服务器时间
• 第3步，读取从库中 heartbeat 表中的时间，再与当前时间做判断，相差的时间即为延迟

利用 pt-heartbeat 监控主从延迟

• pt-heartbeat 是Percona开发的一个专门用来监控MySQL和PostgreSQL复制延迟的工具，技术成熟，例如Uber等大型公司都在使用。下面是配置使用过程：

#.环境介绍
主库：mysql -udba_monitor -pAdmin_147 -h 192.168.3.201 -P 3406
从库：mysql -udba_monitor -pAdmin_147 -h 192.168.3.202 -P 3406

• 第1步，在主库上创建监控数据库，比如 monitor

mysql -udba_monitor -pAdmin_147 -h 192.168.3.201 -P 3406 -e"create database monitor;"

• 第2步，对主库创建心跳表（--update表示要实时更新时间戳的数据，--database指定受监控的数据库，--create-table会在主库创建一个带有时间戳的heartbeat表，--daemonize放到后台执行）

pt-heartbeat --database=monitor --update -h 192.168.3.201 -P 3406 -udba_monitor -pAdmin_147 --create-table --daemonize

• 第3步，使用 --check 检查从库的延迟，检查一次就退出，用于简单验证

pt-heartbeat --database=monitor -h 192.168.3.202 -P 3406 -udba_monitor -pAdmin_147 --check

• 第4步，使用 --monitor 持续检查从库的延迟

pt-heartbeat --database=monitor -h 192.168.3.202 -P 3406 -udba_monitor -pAdmin_147 --monitor