Comparing master and slave MySQL WordPress DBs

My VPS host – Rackspace contacted me the other day to tell me that my host server became unresponsive. After it came back online I went to my MySQL replicated slave to check the status. The /var/log/mysqld.log said:

Error reading packet from server: Client requested master to start replication from impossible position

This article siad

“9 times out of 10 it’s because the master crashed and when it came back online a new binlog file was made”.

That sounds about right, my virtual host was probably force rebooted, and my VM crashed. I recovered my DBs by re-mirroring my DBs over an AutoSSH connection between my two servers.

The article goes on to say:

Now if your super sensitive of lost events because a row or two could of been lost from this replication event, do some spot testing for tables written to often”

I wanted to verify my DBs were the same on both master and slave, so I wrote this BASH function:

function CompareDBs {
if [ $1 ]; then
DB=$1;
RDBDUMP=$(mysqldump -h <a href="tel:127.0.0.1">127.0.0.1</a> -P 7777 --order-by-primary --skip-extended-insert --skip-opt --skip-comments $DB)
LDBDUMP=$(mysqldump --order-by-primary --skip-extended-insert --skip-opt --skip-comments $DB)
diff -y --suppress-common-lines &lt;(echo &quot;$RDBDUMP&quot;) &lt;(echo &quot;$LDBDUMP&quot;)
fi
}

Call the function with the DB you want to compare. This script assumes that you have an SSH Tunnel between your two servers.

********* MAKE SURE YOUR TIME IS RIGHT ON BOTH SERVERS*********
This will save you some hair pulling!