Monitor MySQL Master/Slave synchronization latency

Monitor MySQL Master/Slave synchronization latency

There are usually two ways to monitor the latency between MySQL master and slave: Seconds_Behind_Master and mk-heartbeat.

Method 1: monitor the value of the Seconds_Behind_Master parameter output by the show slave status \ G command to determine whether there is a master-slave latency.

Show slave status; # The replcation client permission is required.

The following are some key parameters for the output result of show slave status \ G:

Slave_IO_Running: this parameter can be used as a monitoring item for io_thread. Yes indicates that io_thread is connected to the master database normally and can be copied. If No, the communication with the master database is abnormal, in most cases, it is caused by the master-slave network;

Slave_ SQL _Running: this parameter indicates whether SQL _thread is normal, specifically, whether the statement is executed successfully. If the primary key is repeated or a table does not exist.

Seconds_Behind_Master: Compares the timestamp of the event executed by SQL _thread with the timestamp (ts) of the event copied by io_thread, and obtains the difference value;

NULL-indicates that either io_thread or SQL _thread has a fault, that is, the Running status of the thread is No, rather than Yes.

0-the value is zero, which is a situation we are eager to see. It indicates that the master-slave replication is good and lag does not exist.

Positive Value: The Master/Slave database has a latency. A larger number indicates that the slave database lags behind the master database.

Negative value-rarely seen. I have heard from some senior DBAs. In fact, this is a BUG value. This parameter does not support negative values, that is, it should not appear.

Method 2: mk-heartbeat: a tool in the Maatkit universal toolkit, is considered to be a method for accurately determining the replication latency.

The implementation of mk-heartbeat is also achieved by comparing timestmp. It must first ensure that the master and slave servers must be consistent and synchronize the clock with the same NTP server. It needs to create a heartbeat table on the master database, which has at least two fields: id and ts. The id is server_id, and ts is the current timestamp now (), this structure will also be copied to the slave database. After the table is created, the update command will be executed in the later process mode of the master database to regularly insert data to the table, the default period is 1 second. At the same time, the slave database will execute a monitoring command in the background to compare the period with that of the master database, the ts value of the copied record is the same as that of the master database. The difference of 0 indicates no delay. The larger the difference, the more seconds the delay occurs. We all know that the ts for asynchronous replication is not completely consistent, so the tool allows a gap of half a second, and the difference within this can be ignored and considered as no delay. This tool uses timestamp to check latency through real-time replication;

This article permanently updates the link address: