Use pt-heartbeat to monitor master-slave replication latency

MySQL master-slave replication is an important part of the MySQL high-availability architecture. It can be used to achieve load balancing, high availability and failover, and provide backup. For master-slave replication monitoring, it is not reliable to rely solely on the showslavestatus provided by MySQL itself. Pt-heartbeat is a good choice for master-slave replication latency monitoring. This article describes

MySQL master-slave replication is an important part of the MySQL high-availability architecture. It can be used to achieve load balancing, high availability and failover, and provide backup. For master-slave replication monitoring, it is not reliable to rely solely on the show slave status provided by MySQL itself. Pt-heartbeat is a good choice for master-slave replication latency monitoring. This article describes

MySQL master-slave replication is an important part of the MySQL high-availability architecture. It can be used to achieve load balancing, high availability and failover, and provide backup. For master-slave replication monitoring, it is not reliable to rely solely on the show slave status provided by MySQL itself. Pt-heartbeat is a good choice for master-slave replication latency monitoring. This article describes the latency monitoring in the case of master-slave replication and provides corresponding examples.

Pt-heartbeat is one of the percona-toolkit. Therefore, you need to install percona-toolkit before use. For details, see percona-toolkit installation and introduction.

1. pt-heartbeat
Pt-heartbeat measures replication lag on a MySQL or PostgreSQL server. you can use it to update a master or monitor a replica. if possible, MySQL connection options are read from your. my. cnf file. for more details, please use the -- help option, or try 'perldoc/usr/bin/pt-heartbeat' for complete documentation.

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. Principles of pt-heartbeat
The first part is an -- update instance of pt-heartbeat that connects to a master and updates a timestamp ("heartbeat record") every -- interval seconds. since the heartbeat table may contain records from multiple masters (see "MULTI-SLAVE HIERARCHY"), the server's ID (@ server_id) is used to identify records.
A heartbeat table exists on the master database to check latency. You can manually or automatically create
Pt-heartbeat uses the -- update parameter to connect to the master database and continuously (according to the set -- interval parameter) updates to the table heartbeat With a timestamp.

The second part is a -- monitor or -- check instance of pt-heartbeat that connects to a slave, examines the replicated heartbeat record from its immediate master or the specified -- master-server-id, and computes the difference from the current system time. if replication between the slave and the master is delayed or broken, the computed difference will be greater than zero and otentially increase if -- monitor is specified.
Pt-heartbeat connects to the slave database using -- monitor or -- check, checks the timestamp synchronized from the master database, and compares it with the current system timestamp to generate a difference,
This value is used to determine the latency. (Note: The premise is that the master database and slave database should maintain time synchronization)

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.
The heartbeat table must contain a heartbeat row. by default, a heartbeat row is inserted if it doesn't exist. this feature can be disabled with the -- [no] insert-heartbeat-row option in case the database user does not have INSERT privileges.

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 tunststen, 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. and if replication is stopped, it will continue to work and r Eport (accurately !) That the slave is falling further and further behind the master.
Pt-heartbeat has a maximum resolution of 0.01 second. the clocks on the master and slave servers must be closely synchronized via NTP. by default, -- update checks happen on the edge of the second (e.g. 00:01) and -- monitor checks happen halfway between seconds (e.g. 00:01. 5 ). as long as the servers 'Clocks are closely synchronized and replication events are propagating in less than half a second, pt-heartbeat will report zero seconds of delay.
Pt-heartbeat will try to reconnect if the connection has an error, but will not retry if it can't get a connection when it first starts.
The -- dbi-driver option lets you use pt-heartbeat to monitor PostgreSQL as well. It is reported to work well with Slony-1 replication.

3. Obtain the pt-heartbeat help information.
A. Get help information
[Root @ DBMASTER01 ~] # Pt-heartbeat # Enter pt-heartbeat directly to get a brief description. Use pt-heartbeat -- help to get a complete help information.
Usage: pt-heartbeat [OPTIONS] [DSN] -- update | -- monitor | -- check | -- stop

Errors in command-line arguments:
* Specify at least one of -- stop, -- update, -- monitor or -- check
* -- Database must be specified

B. Several Important Parameters
Specify at least one of -- stop, -- update, -- monitor, or -- check. # Specify at least one
-- Update, -- monitor, and -- check are mutually exclusive. # mutex Parameters
-- Daemonize and -- check are mutually exclusive. # mutex Parameters

-- Check
Check slave delay once and exit. If you also specify -- recurse, the tool will try to discover slave's of
Given slave and check and print their lag, too. The hostname or IP and port for each slave is printed before its
Delay. -- recurse only works with MySQL.

-- Daemonize
Fork to the background and detach from the shell. POSIX operating systems only.

-- Frames
Type: string; default: 1 m, 5 m, 15 m
Timeframes for averages.
Specifies the timeframes over which to calculate moving averages when -- monitor is given. Specify as
Comma-separated list of numbers with suffixes. The suf? X can be s for seconds, m for minutes, h for hours, or d
For days. The size of the largest frame determines the maximum memory usage, as up to the specified number
Of per-second samples are kept in memory to calculate the averages. You can specify as your timeframes
You like.

-- Monitor
Monitor slave delay continuously.
Specifies that pt-heartbeat shocould check the slave's delay every second and report to STDOUT (or if -- file
Is given, to the file instead). The output is the current delay followed by moving averages over the timeframe
Given in -- frames. For example,
5S [0.25 s, 0.05 s, 0.02 s]

-- Stop
Stop running instances by creating the sentinel file.

-- Update
Update a master's heartbeat.

4. Demo pt-heartbeat

A. First add the table [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; + region + ----------- + ------------------ + ----------- + region + ------------------- + | ts | server_id | file | position | relay_master_log_file | exec_master_log_pos | + region + ----------- + -------- ---------- + ----------- + ----------------------- + --------------------- + | 2014-12-01T09: 48: 14.003020 | 11 | mysql-bin.000390 | 677136957 | mysql-bin.000179 | 120 | + bytes + ----------- + -------------------- + ----------- + hour + --------------------- + B, update heartbeat on the master database [root @ DBMASTER01 ~] # Pt-heartbeat -- user = root -- password = xxx-S/tmp/mysql. sock-D test \> -- master-server-id = 11 -- update & [1] 31249c, slave database monitoring latency [root @ DBBAK01 ~] # Pt-heartbeat -- user = root -- password = xxx-S/tmp/mysql. sock-D test \> -- master-server-id = 11 -- monitor -- print-master-server-id1.00s [0.02 s, 0.00 s, 0.00 s] 11 # Real-Time latency, 1 minute latency, 5-minute delay, 15-minute delay of 1.00 s [0.03 s, 0.01 s, 0.00 s] 11 # Author: Leshami1.00s [0.05 s, 0.01 s, 0.00 s] 11 # Blog: http://blog.csdn.net/leshami1.00s [0.07 s, 0.01 s, 0.00 s] 111.00 s [0.08 s, 0.02 s, 0.01 s] 111.00 s [0.10 s, 0.02 s, 0.01 s] 111.00 s [0.12 s, 0.02 s, 0.01 s] 111.00 s [0.13 s, 0.03 s, 0.01 s] 11d, other operation examples # Schedule updates on the master database using daemon [root @ DBMASTER01 ~] # Pt-heartbeat -- user = root -- password = xxx-S/tmp/mysql. sock-D test \> -- master-server-id = 11 -- update -- daemonize # modify the update interval of the master database to 2 s [root @ DBMASTER01 ~] # Pt-heartbeat -- user = root -- password = xxx-S/tmp/mysql. sock-D test \> -- master-server-id = 11 -- update -- daemonize -- interval = 2 # Stop the pt-heartbeat daprocess on the master database [root @ DBMASTER01 ~] # Pt-heartbeat -- stopSuccessfully created file/tmp/pt-heartbeat-sentinel [root @ DBMASTER01 ~] # Rm-rf/tmp/pt-heartbeat-sentinel # view the latency in the slave database at a time [robin @ DBBAK01 ~] $ Pt-heartbeat -- user = root -- password = xxx-S/tmp/mysql. sock-D test \> -- master-server-id = 11 -- check 1.00 # use a daemon to monitor the slave database and output logs [root @ DBBAK01 ~] # Pt-heartbeat -- user = root -- password = xxx-S/tmp/mysql. sock-D test \ -- master-server-id = 11 -- monitor -- print-master-server-id -- daemonize -- log =/tmp/slave-lag.log