Monitoring MySQL Master and Slave Nodes

In a MySQL master-slave high availability setup, you should continuously verify that both nodes are functioning correctly. A monitoring script or program should alert the high availability framework if any health check fails so it can trigger corrective actions and maintain service availability.

Master node checks

Run these checks on the master at frequent intervals. If your monitoring script executes locally, you can inspect service status, connectivity, and write capability.

First, confirm the MySQL process is alive:

pgrep mysqld
# or
service mysqld status

Next, verify you can establish a connection and execute a simple query. Use a short timeout so an unresponsive server is detected quickly:

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

Check the command's exit value:

  • 0: success
  • 1: failure; MySQL's return code indicates the reason, e.g. the 'Too many connections' error when the connection count exceeds the max_connections setting
  • 124: timeout, meaning the service is unresponsive; retry after a short delay to avoid false negatives

Finally, ensure the master is running in read-write mode:

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

The read_only value should be OFF. This query can replace the simple test query in the connectivity check to combine steps two and three.

Slave node checks

Run slave health checks less frequently than on the master, since slaves handle no writes. The first three checks mirror those on the master, except that read_only should report ON. Then add replication-specific checks confirming that:

  • The slave replicates from the correct master
  • The connection to the master is healthy
  • The slave applies events received from the master

All of these can be verified with the SHOW SLAVE STATUS command, whose relevant output fields include Master_Host, Slave_IO_Running, and Slave_SQL_Running. A Yes in Slave_IO_Running indicates the slave is connected and receiving the replication stream. A Yes in Slave_SQL_Running means the applier is running and processing events from the master. The Master_Host field confirms which server the slave is configured to replicate from.