mysql的pt-heartbeat监控备库延迟记录

q6542125

　　在mysql的主备架构中,我们很关心的一种情况是，备库到底落后了主库多少，因为只有知道了具体的情况，我们才能知道当前系统的复制是否正常。如果一个在主上插入的数据，可能要等到上分钟后才能在从库插入，那这样的设计肯定是有问题的。在备库上执行show slave status\G;可以看到其中有一列为Seconds_Behind_Master，这个值理论上显示出了备库的延时，但是他不准确，比如在我的实验环境下大量并发插入数据的时候Seconds_Behind_Master的值一直为0。


　　原因如下：（这里借用High performance mysql一书中的原文）



     1.备库Seconds_Behind_Master值是通过将服务器当前的时间戳与二进制日志中的事件时间戳相对比得到的，所以只有在执行事件时才能报告延时。

     2.如果备库复制线程没有运行,就会报延迟null。

     3.一些错误（例如主备的max_allowed_packet不匹配，或者网络不稳定）可能中断复制并且/或者停止复制线程，备库有时可能无法计算延时。如果发生这种情况备库会报0或者null

     4.一个大事务可能会导致延迟波动，例如，有一个事务更新数据长达一个小时，最后提交。这条更新将比它实际发生时间要晚一个小时才记录到二进制日志中。当备库执行这条语句时，会临时地报告备库延迟为一个小时，然后又很快变成0

     5.如果分发主库落后了，并且其本身也有已经追赶上它的备库，备库的延迟将显示为0，而事实上和源主库是有延迟的。



     所以用下面的pt-heartbeat这款脚本工具来监控心跳的记录。


　　首先要在主服务器下创建一个定期更新heartbeat表的命令如下：



  /usr/local/bin/pt-heartbeat -D test --update -u mysql -p 123456 -P 3306 -h 192.168.1.106 --create-table --daemonize     

　　

     参数说明如下：-D 需要监控的数据库，--update会在指定的时间更新一个时间戳,-u 用户，-p 密码 -P端口 -h 主机 --create-table自动生成heartbeat表,--daemonize在后台运行

     参数的大致意思还是很明显，如果想查看更多的参数可以/usr/local/bin/pt-heartbeat --help

     执行完毕以后可以在test库下看到新生成了一张heartbeat的表

   

     有了这张表以后我们就可以监控复制在slave上的落后程度了

  /usr/local/bin/pt-heartbeat -D test --monitor -u mysql -p 123456 -P 3306 -h 192.168.1.111     参数说明:--monitor会连接到从上检查复制的心跳记录,并且与当前的时间相比，得出复制的延迟,-h是备库的地址



     如果没有heartbeat表会出现

     Table 'test.heartbeat' doesn't exist [for Statement "SELECT ts/*!50038, @@hostname AS host*/, server_id FROM `test`.`heartbeat` WHERE server_id='1' LIMIT 1"]

     当有了heartbeat表的时候会出现

   0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]     因为此时没有往主库中添加数据，备库不需要更新，所以一直为0。现在新开一个端口，往主库中插入大量数据的时候

   2.06s [  0.13s,  0.03s,  0.01s ]
2.16s [  0.16s,  0.03s,  0.01s ]
0.18s [  0.16s,  0.03s,  0.01s ]
1.52s [  0.19s,  0.04s,  0.01s ]
2.03s [  0.22s,  0.04s,  0.01s ]     可以看到备库已经有了明显的延迟



     当然我们也可以把上面的--monitor换成--check，这样在监控一次后就会退出



     在将定期更新heartbeat表的命令放在后台线程中的时候，二进制日志会不断的变大。用下面的命令查看下二进制日志中到底记录了什么：

   /usr/local/mysql/bin/mysqlbinlog --no-defaults /data/mysql/mysql-bin.000010 | tail -n 30     BEGIN

     /*!*/;

     # at 588960

     #130822  6:44:01 server id 1  end_log_pos 589202        Query   thread_id=28    exec_time=0     error_code=0

     SET TIMESTAMP=1377168241/*!*/;

      UPDATE `test`.`heartbeat` SET ts='2013-08-22T06:44:01.005550', file='mysql-bin.000010', position='588555', relay_master_log_file=NULL, exec_master_log_pos=NULL   



   WHERE server_id='1'

    /*!*/;

    # at 589202

    #130822  6:44:01 server id 1  end_log_pos 589229        Xid = 6980

     COMMIT/*!*/;



    可以看到heratbeat表一直在更改ts和position,而ts是我们检查复制延迟的关键