You have finished setting up your MySQL replication, next you need to periodically monitor your replication to ensure that MySql replication continues to work seamlessly. You can monitor the MySql replication with some basic commands, I have summarized them below.
SLAVE_RUNNING: This is a global status variable and its value can be checked using SHOW GLOBAL STATUS like 'slave_running'. It can either be ‘ON’ or ‘OFF’.
If slave_running is ‘ON’, then the slave is up and working fine, which means both the SQL thread and the IO thread are running.
If either the SQL thread or the IO thread is not running then this variable would be ‘OFF’.
Use SHOW SLAVE STATUS and try to determine whether there was an error or the slave was stopped manually.
Check the last 100 lines of the slave’s error log to see if that gives a clue. Look at Last_Error_Number and Last_Error_Message for specific error information and fix your slave.
The following variables are a part of SHOW SLAVE STATUS
SLAVE_IO_RUNNING: It tells us if the Slave’s IO thread is able to connect to its master and is running fine. The possible values for this variable are ‘Yes’ or ‘No’ or ‘connecting’.
If this variable reads ‘NO’ then you will have to check the Last_Error_Number and Last_Error_Message and fix your slave.
SLAVE_SQL_RUNNING: It tells if the Slave’s SQL thread has started and is working fine. The possible values of this variable are ‘Yes’ or ‘No’.
If this variable reads ‘No’, then the IO thread was caused to stop. You will have to check theLast_SQL_Errno and Last_SQL_Err for more information on the cause. An error number of 0 and the message of the empty string mean “no error.” The Last_SQL_Error appears in the slave’s error log.
SECONDS_BEHIND_MASTER: As the name suggests, this field tells how late your slave is. In other words, it tells the time in seconds that the Slave’s SQL thread lags while processing the Master’s binary log. A continuous increase in this value is not a very good sign as it means that the slave is not able to catch up with its master. There is no threshold value for this variable to compare against, to determine if the value is high or low. It completely depends on your application, network speed, etc.
To continuously monitor the replication, you can setup a cron job that executes the SHOW GLOBAL STATUS like ‘slave_running’ and SHOW SLAVE STATUS periodically and store it in a file and even configure to send an email alert if slave_running value is ‘NO’.
Other tools to monitor MySql Replication
There are some more sophisticated tools that you can use to monitor your MySql –
Percona Toolkit: Percona Toolkit comes with a bunch of very useful tools that help in monitoring as well as managing MySQL replication.
check_mysql_health is a plugin for Nagios that allows you to monitor a MySQL database. Among the list of metrics is time to log in, index usage, buffer pool hit rate, query cache hit rate, slow queries, temp tables on disk, table cache hit rate, connected threads, and many more.
MONyog – MySQL Monitor and Advisor: A “MySQL DBA in a box” by Webyog is an agentless GUI based tool that helps MySQL DBAs manage MySQL servers.
MySQL Enterprise Monitor: A “Virtual DBA Assistant” by Oracle is an agent-based monitoring tool which has a neat web-based GUI.
Depending on your needs and budget, you can choose the tool of your choice.
Leave a Reply