MySQL Replication Health

MySQL Replication is a powerful tool to help with both disaster recovery and high availability, but many things can go wrong, including network and file corruption.

Where the two servers are in sync is tracked by the binary log files. To check for the current file and position of the binary log, you issue a show master status or show slave status commands, depending on if you are on the master or slave. Comparing these files you can determine if the slave is fully in sync with the master, or if problems have occurred.

Here is a simple bash script, which compares the filename and position of the binary log on both the master and slave. A side effect of the script is also that it logs on to both servers, telling you if the server is unreachable at all.