Check MySQL Master-Slave replication

A lot of guides explain that you can use MySQL master-slave replication to scale your database performance. But nobody explains how to keep the database instances in synch. MySQL replications are very shaky and tend to break and mess up stuff very easily. From my standpoint the whole replication scheme looks like a smart hack into the MySQL system.

Till today I used some self written scripts to check our systems. Nobody told me that there was a perfect toolchain for this problem already existing: Maatkit. Maatkit offers a few commandline tools that help you to keep the slaves in synch with your master.

Install it using brew:

brew install maatkit

Check tables for differences :

mk-table-checksum h=HOSTMASTER,u=USERMASTER,p=PASSWORDMASTER h=HOSTSLAVE,u=USERSLAVE,p=PASSWORDSLAVE -d DATABASE | mk-checksum-filter

And after you found differences you can even use Maatkit to repair those tables:

mk-table-sync --execute --sync-to-master h=SLAVEIP,u=USER,p=PASSWORD,D=DATABASE,t=TABLE

Besides repairing damaged tables Maatkit helps you to check if the slave replication is still working. Mk-heartbeat updates a heartbeat table in the master database and checks if the slave database has been updated. I have written a small script that wraps the heartbeat check and sends a mail if the slave replication has stopped.

More documentation can be found here and a few examples in this presentation.

And yes, using couchdb would make this toolchain obsolete :smile: .

Portrait photo of Bodo Tasche
Bodo Tasche
Polyglot Developer

I am a freelance polyglot developer and love HTML5, testing, JavaScript, Ruby and Elixir. In the last 20 years I have been in lots of different roles, from Java to Elixir, from backend developer at a 3 people team in an early phase startup to the CTO of a web agency.

Need help developing your MVP or to add new features into your current app? Need a CTO or a front/backend developer for hire? Send me an email.