How do I monitor the replication latency for MySQL?

Pt-heartbeat

Database master from replication, replication status, data latency is a very important indicator of the normal, then how to monitor it?

Pt-heartbeat is a tool set developed by Percona to monitor the replication latency of MySQL and PostgreSQL.

More mature, such as Uber and other large companies are in use.

Monitoring principle

Build a heartbeat table in master, which has a timestamp field that pt-heartbeat periodically modifies the timestamp value.

Slave Copies the heartbeat table, which contains the time stamp for master to perform the modification action, compares it to the local time of the slave, and obtains a difference, which is the value of the replication delay, to determine whether the replication status is normal and whether the latency is in line with expectations.

Pt-heartbeat is calculated based on the actual copy record, so he doesn't care what way you use it to replicate.

Pt-heartbeat can monitor the level of replication at any depth because there are server_id fields in the heartbeat table, and you can specify which server_id to refer to when monitoring a slave delay, such as the slave and its master's Mas ter delay, specify the server_id of the target master.

Because Pt-heartbeat strictly relies on time, it is important to note that master and slave time must be synchronized.

Using the sample

Performs the creation of the heartbeat table on master and performs a circular update operation to execute the command:

Pt-heartbeat \
--user=root--ask-pass \
--host=127.0.0.1 \
--create-table-d master1 \
--interval=1--update \
--replace--daemonize
Where Master's connection information is specified,--create-table-d Master1 refers to creating a heartbeat table in the Master1 database, followed by a parameter that specifies the time interval to perform the update.

Then you can monitor the slave and execute the command:

Pt-heartbeat \
--user=root--ask-pass \
-H 192.168.31.207 \
-D Master1--table=heartbeat \
--monitor
The--monitor parameter indicates that you want to monitor, before you monitor the target information, including the Slave connection information, database, and table name.

Output Information Sample:

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 represents the current delay information, [0.00s, 0.00s, 0.00s] represents the average of the 1m,5m,15m.

installation process

The following is the installation process under CentOS7:

$ wget percona.com/get/percona-toolkit.tar.gz
$ tar zxf percona-toolkit-2.2.19.tar.gz
$ CD percona-toolkit-2.2.19
$ yum-y Install Perl-extutils-cbuilder perl-extutils-makemaker perl-digest-md5 perl-dbd-mysql
$ Perl makefile.pl
$ make
$ make Test
$ make Install
Summary

Pt-heartbeat's Official document address:

Https://www.percona.com/doc/percona-toolkit/2.1/pt-heartbeat.html
There is a detailed introduction of the principle and the use of parameters, interested can be looked at carefully.