The pt-heartbeat of MySQL management tools

I have been using seconds_behind_master to measure the master-slave delay, today I see the document, how unreliable! The following is a description of the official documentation:

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

In other words, this variable describes the time difference between I/O thread and SQL thread from the library.

The following uses Pt-heartbeat to monitor master-slave delay:

"Principle":

1: Create a heartbeat table on the main library, update the field of the table at a certain time (update the time in).

2: Connect to the time record from the library to check for replication, and compare the current system time from the library to get the difference in time.

"Usage":

pt-heartbeat [OPTIONS] [DSN]--update|--monitor|--check|--stop

"Parameter description"

--Check checks from the delay, checks out once and exits unless specified--The recurse will recursively check all the slave servers. --Monitor continuously monitors the delay from the situation. Through--interval Specify the interval time, print out the delay information from the--fileYou can print this information to the specified file. --daemonize execution, put into the background execution--filePrint--monitor the latest records to the specified file, very good to prevent full screen is the trouble of data. --Frames in--monitor in the output of the [] record segment, the default is 1m,5m,15m. You can specify 1, such as:--frames=1s, multiple separated by commas. Available units have seconds (s), minutes (m), hours (h), days (d). --interval the time between checking and updating. The default is to see 1s. The smallest unit is 0.01s and the maximum precision is two digits after the decimal point, so 0.015 will be adjusted to 0. Geneva. --log all logs that turn on daemonized mode will be printed to the file you made. --Update updates the heartbeat table of our Lord. --Replace use--replace instead--Update mode updates the Time field in the Heartbeat table, which has the advantage of not having rows in the control table. --Stop stops running the tool (--daemonize), create a "pt-heartbeat-sentinel" file under the/tmp/directory. If you want to turn it back on, you need to delete the temporary file to open (--daemonize). --Table Specifies the name of the heartbeat table, default heartbeat. --create-Table in the Lord creates a heartbeat monitor if the table does not exist. You can build it yourself and suggest that the storage engine be changed to memory. By updating the table, you know the difference between master and slave delays. CREATE TABLE Heartbeat (ts varchar ( -) not NULL, server_idintunsigned not NULL PRIMARY KEY,filevarchar255) DEFAULT NULL,--SHOW MASTER STATUS position bigint unsigned DEFAULT NULL,--SHOW MASTER STATUS relay_master_log_file varchar (255) DEFAULT NULL,--SHOW SLAVE STATUS exec_master_log_pos bigint unsigned DEFAULT NULL--SHOW SLAVE STATUS); 

"Use case"

Main Library: 10.10.101.11 Port 3306

From library: 10.10.101.11 Port 3307

1. Execute the command on the main library:

10.10. 101.11 3306 ' ******* ' --update--daemonize

-D: Indicates the database being monitored
At this point, the following table heartbeat has been created in the main library:

Mysql>show tables;+----------------+| Tables_in_test |+----------------+| checksums | | Heartbeat |+----------------+2RowsinchSet (0.00sec) MySQL>Select*From Heartbeat;+----------------------------+-----------+------------------+----------+-----------------------+--------------- ------+| ts | server_id |file| Position | Relay_master_log_file | Exec_master_log_pos |+----------------------------+-----------+------------------+----------+------------------ -----+---------------------+| -- One-02T15: on:45.001340|113306| Mysql-bin.000021|1240408| Mysql-bin.000021| -|+----------------------------+-----------+------------------+----------+-----------------------+-------------- -------+1RowinchSet (0.00Sec

2. Delay monitoring from the library:

#执行一次就退出
[Email protected] backup]#/usr/local/bin/pt-heartbeat-d test-h 10.10.101.11-p 3307-uroot-p ' 4rfv%tgb^ '--check
0.00

Continuous monitoring:

[[Email protected]Ten-Ten-101- Onebackup]#/usr/local/bin/pt-heartbeat-d test-h10.10.101.11-P3307-uroot-p'*******'--Monitor0.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.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.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.00s [0.00s,0.00s,0.00s]

For more details, please refer to official documentation: Https://www.percona.com/doc/percona-toolkit/2.2/pt-heartbeat.html

The pt-heartbeat of MySQL management tools