How does MySQL monitor master-slave synchronization

MySQL synchronization function by 3 threads (master 1, slave 2) to implement, simply: Master send log one, slave receive log one, slave run log one.

First, let's explain some of the important parameters in show slave status:
Whether the SLAVE_IO_RUNNING:I/O thread is started and successfully connected to the primary server.

Whether the Slave_sql_running:sql thread is started.

Seconds_behind_master:

This field is an indication of how much the subordinate server "lags". When a dependent SQL thread is running (processing an update), the number of seconds that have elapsed since the time tag of the most recent event performed by this thread on the primary server. This field is zero when this thread is caught by a subordinate server I/O thread and goes into idle state while waiting for more events from the I/O thread. In summary, this field measures the time gap between the secondary server SQL thread and the secondary server I/O thread in seconds.

How do I monitor the uptime of the server?

1. Execute Shell script manually

The code is as follows	Copy Code
Show Slave STATUSG

See if the 3 parameters mentioned above are working correctly.

2. Percona Toolkit

Percona Toolkit provides a number of MySQL database-related tools to manage the MySQL database well.
Percona Toolkit can go here to download: Http://www.percona.com/software/percona-toolkit

Pt-heartbeat: Monitor MySQL's delay time from server.
Pt-slave-restart: Manage MySQL reboot from server.
Pt-table-checksum: Check the consistency of the master-slave synchronization data, such as encountered replication errors, we performed the skip error operation, check the data is very necessary. However, this tool needs to be set up in advance, install the appropriate checksum table, please refer to the relevant information.

3. Third-party tools

MySQL Enterprise Monitor,mysql Enterprise Edition monitoring tools.
Monyog–mysql Monior and Advisor,monyog Everyone is not unfamiliar, windows under the more useful Mysqlgui providers, there are related MySQL monitoring tools.

4. Nagios and Zabbix related plug-ins.

Nagios related plug-ins are still very rich, you can find the relevant MySQL slave monitoring tools.

Finally, here is an open source MySQL slave monitoring script, utility cronjob or other related tools can easily set up their own monitoring tools.

such as our practical Jenkins+shell script, do failure notice can quickly build a simple MySQL monitoring tool.

MySQL Slave Monitoring script:

The code is as follows

Copy Code

#!/bin/bash # (C) 2012-vincent van Scherpenseel, syn-ack.org ### VARIABLES ### Server= ' hostname ' Seconds_behind_master= '/usr/bin/mysql-e ' Show SLAVE STATUSG | grep "Seconds_behind_master" | Awk-f ":" {' Print $ '} ' Sentfile_broken=/tmp/mysql_slaverep_broken.sent Sentfile_behind=/tmp/mysql_slaverep_behind.sent ### CHECK for REPLICATION break ### If ["$SECONDS _behind_master" = = "NULL"]; Then # Slave replication is broken if [!-f $SENTFILE _broken]; Then # This has not been reported before echo "Slave replication broken on $SERVER" Touch $SENTFILE _broken Fi Else # Slave replication is not broken If [f $SENTFILE _broken]; Then # It is broken before which was reported. clear that State echo "Slave replication has been restored on $SERVER" RM $SENTFILE _broken Fi ### CHECK for REPLICATION DELAY ### If ["$SECONDS _behind_master"-GT "60"]; Then # Slave replication is delayed if [!-f $SENTFILE _behind]; Then # This has not been reported before echo "Slave replication is $SECONDS _behind_master SECONDS behind MASTER on $SERVER" Touch $SENTFILE _behind Fi Else # Slave replication is not delayed If [f $SENTFILE _behind]; Then # It is delayed before which was reported. clear that State echo "Slave replication delay has been recovered and is now $SECONDS _behind_master SECONDS behind MASTER on $SERVER" RM $SENTFILE _behind Fi Fi Fi

Recommended reading: Add a new database to MySQL master-slave copy Tutorial solution to problem solving