Important Health Checks for your MySQL Source-Replica Servers

In a MySQL source-replica high availability (HA) setup, it is important to continuously monitor the health of the source and replica servers so you can detect potential issues and take corrective actions. In this blog post, we explain some basic health checks you can do on your MySQL source and replica nodes to ensure your setup is healthy. The monitoring program or script must alert the high availability framework in case any of the health checks fails, enabling the high availability framework to take corrective actions in order to ensure service availability.

MySQL Source Server Health Checks

We recommended that your MySQL source monitoring program or scripts runs at frequent intervals. Assuming that the monitoring script is running on the same server as your MySQL server, you can check for the following:

1. Ensure the MySQL service is running

   This can be done using a simple command like:

   > pgrep mysqld

   OR

   >service mysqld status

2. Ensure you can connect to MySQL and do a simple query

   We recommended having a short timeout for these commands so you can quickly detect if MySQL is unresponsive. This can be achieved from a call like:

   /usr/bin/timeout 5 mysql -u testuser -ptestpswd -e 'select * from mysql.test’

   Be sure to examine the exit value of the above command:

   Exit value=0 ⇒ Success

   Exit value=1 ⇒ Failure

   Exit-value=124 ⇒ Timeout

   If the command times out, it means that the MySQL service is not responsive enough. We advice you retry after some time so as to avoid false negative results. If the exit code indicates a failure, the return code from MySQL will tell us the failure reason. One example of a failure is the ‘Too many connections’ error from MySQL which happens if the number of connections to the server exceeds your ‘max_connections’ configuration value.

3. Ensure the MySQL source is running in read-write mode

   You can use the following command to ensure the MySQL source is running in read-write mode:

   /usr/bin/timeout 5 mysql -u testuser -ptestpswd -e "SELECT @@global.read_only"

   The source is expected to be always running in read-write mode, and hence, the value of  read_only should be ‘OFF’.

   It is also possible to club this step with step 2, and instead of doing the test query ‘select * from mysql.test, we can just do the query to get the read_only value.

MySQL Replica Server Health Checks

You can run the monitoring for your MySQL replicas at a lesser frequency compared to the source, as they are not handling data writes. The first 3 steps for your replica health check can be the same as that of the source, except that we need to ensure the replica is running in read-only mode – the value of the variable read_only should be ‘ON’ in step-3.

In addition, we can do more checks on the replica to ensure its replication status is healthy, such as:

1. The replica is configured to replicate from the right source.

2. The replica’s connection to the source is healthy.

3. The replica is able to apply the source events it has received.

It’s possible to check for all the above using the ‘show replica status’ command. For example:

mysql> show replica status \G;

*************************** 1. row ***************************

Replica_IO_State: Waiting for source to send event

Source_Host: 172.31.17.43

Source_User: repl_user

Source_Port: 3306

Connect_Retry: 10

Source_Log_File: mysql-bin.000001

Read_Source_Log_Pos: 7510

Relay_Log_File: relay-log.000006

Relay_Log_Pos: 414

Relay_Source_Log_File: mysql-bin.000001

Replica_IO_Running: Yes

Replica_SQL_Running: Yes

******************Truncated*********************************

• The Source_Host value indicates the source server is configured for replication.

• For the Replica_IO_Running value, “Yes” indicates that the replica has connected to the source and is receiving the replication stream.

• For the Replica_SQL_Running value, “Yes” indicates that the replica’s applier is running and able to apply all the events received from the source.

In this blog post, we discussed some simple checks that can detect if there are basic issues in your MySQL source and replica servers. In general, the failure detection mechanism in a high availability setup is a complex subject and needs a robust high availability framework through which health check monitoring should be implemented. You can learn more about the details of our high availability framework in our MySQL High Availability Framework Explained – Part I: Introduction blog post.