MySQL主从复制是MySQL 高可用架构中重要的组成部分，该技术可以用于实现负载均衡，高可用和故障切换，以及提供备份等等。对于主从复制的监控，仅仅依赖于MySQL自身提供的show slave status并不可靠。pt-heartbeat是主从复制延迟监控的不错选择，本文描述了主从复制情形下的延迟监控并给出相应示例。
pt-heartbeat为percona-toolkit工具包中的一个，因此使用前需要先安装percona-toolkit，请参考：percona-toolkit的安装及简介
1、pt-heartbeat的作用
pt-heartbeat is a two-part MySQL and PostgreSQL replication delay monitoring system that measures delay by looking at actual replicated data. This avoids reliance on the replication mechanism itself, which is unreliable. (For example, SHOW SLAVE STATUS on MySQL).
2、pt-heartbeat的原理
You must either manually create the heartbeat table on the master or use --create-table. See --create-table for the proper heartbeat table structure. The MEMORY storage engine is suggested, but not re-quired of course, for MySQL.
pt-heartbeat depends only on the heartbeat record being replicated to the slave, so it works regardless of the replication mechanism (built-in replication, a system such as Continuent Tungsten, etc). It works at any depth in the replication hierarchy; for example, it will reliably report how far a slave lags its master’s master’s master. And if replication is stopped, it will continue to work and report (accurately!) that the slave is falling further and further behind the master.
3、获取pt-heartbeat帮助信息
b、几个重要的参数
--frames
4、演示使用pt-heartbeat
a、首先添加表
[root@DBMASTER01 ~]# pt-heartbeat --user=root --password=xxx -S /tmp/mysql.sock -D test \
> --master-server-id=11 --create-table --update
MASTER> select * from heartbeat;
+----------------------------+-----------+------------------+-----------+-----------------------+---------------------+
| ts | server_id | file | position | relay_master_log_file | exec_master_log_pos |
+----------------------------+-----------+------------------+-----------+-----------------------+---------------------+
| 2014-12-01T09:48:14.003020 | 11 | mysql-bin.000390 | 677136957 | mysql-bin.000179 | 120 |
+----------------------------+-----------+------------------+-----------+-----------------------+---------------------+
b、更新主库上的heartbeat
[root@DBMASTER01 ~]# pt-heartbeat --user=root --password=xxx -S /tmp/mysql.sock -D test \
> --master-server-id=11 --update &
[1] 31249
c、从库上监控延迟
[root@DBBAK01 ~]# pt-heartbeat --user=root --password=xxx -S /tmp/mysql.sock -D test \
> --master-server-id=11 --monitor --print-master-server-id
1.00s [ 0.02s, 0.00s, 0.00s ] 11 #实时延迟，1分钟延迟，5分钟延迟，15分钟延迟
1.00s [ 0.03s, 0.01s, 0.00s ] 11 # Author : Leshami
1.00s [ 0.05s, 0.01s, 0.00s ] 11 # Blog : http://blog.csdn.net/leshami
1.00s [ 0.07s, 0.01s, 0.00s ] 11
1.00s [ 0.08s, 0.02s, 0.01s ] 11
1.00s [ 0.10s, 0.02s, 0.01s ] 11
1.00s [ 0.12s, 0.02s, 0.01s ] 11
1.00s [ 0.13s, 0.03s, 0.01s ] 11
d、其他操作示例
#将主库上的update使用守护进程方式调度
[root@DBMASTER01 ~]# pt-heartbeat --user=root --password=xxx -S /tmp/mysql.sock -D test \
> --master-server-id=11 --update --daemonize
#修改主库上的更新间隔为2s
[root@DBMASTER01 ~]# pt-heartbeat --user=root --password=xxx -S /tmp/mysql.sock -D test \
> --master-server-id=11 --update --daemonize --interval=2
#停止主库上的pt-heartbeat守护进程
[root@DBMASTER01 ~]# pt-heartbeat --stop
Successfully created file /tmp/pt-heartbeat-sentinel
[root@DBMASTER01 ~]# rm -rf /tmp/pt-heartbeat-sentinel
#单次查看从库上的延迟情况
[robin@DBBAK01 ~]$ pt-heartbeat --user=root --password=xxx -S /tmp/mysql.sock -D test \
> --ma