How To Monitor MySQL Slaves with mysql-checksum

These instructions show you how to set-up MySQL replication monitoring on Debian. If any of your slaves get out of sync with your master you'll get an email with a checksum error.

First, configure your slave to report it's hostname to your master by editing /etc/mysql/my.cnf:

report-host             = slave.example.com

/etc/init.d/mysql restart

Now on the MASTER, download and install Maatkit (formerly MySQL Toolkit). You'll also need perl and perl-dbi:

cd ~
wget http://ovh.dl.sourceforge.net/sourceforge/mysqltoolkit/mysqltoolkit_1053-1_all.deb
dpkg -i mysqltoolkit_1053-1_all.deb

Note that at time of writing the latest version was not compatible with the perl-dbi package installed with Etch, but the previous version worked fine.

On the master, run the following command to generate checksums for all tables on both your master and your slaves:

mysql-table-checksum h=db1.example.com,u=mysqlchecksum,p=password h=db2.example.com,u=mysqlchecksum,p=password

You can then manually compare the checksums shown to verify that the tables are the same.

To show only checksums which differ pipe the output through mysql-checksum-filter as shown below:

mysql-table-checksum h=db1.example.com,u=root,p=apassword h=db2.example.com,u=root,p=apassword | mysql-checksum-filter

To run this automatically from cron create a script named /etc/cron.daily/mysql_table_checksum:

#!/bin/sh
#
#  Call mysql-table-checksum to verify master-slave data
#

MYSQLPASS="password"
export MYSQLPASS
mysql-table-checksum h=db1.example.com,u=mysqlchecksum,p=$MYSQLPASS h=db2.example.com,u=mysqlchecksum,p=$MYSQLPASS | mysql-checksum-filter | mail -s "mysql-table-checksum for db1/db2.example.com" root >/dev/null 2>&1
exit 0