Note: this applies to build 2442 of the Maatkit tools the syntax has changed slightly in newer versions.
The replication feature built into MySQL is very useful to have at your disposal and relatively simple to get going. But how do you know if your slave has an accurate copy of the master’s data? All manor of things can happen to your data in transit and having a tool to verify its status should be one to keep handy. Checking a slave’s accuracy would be particularly relevant if you are using a slave to run your backups from.
Maatkit could be what you need. Maakit is a collection of tools that help you with some of the admin tasks of looking after MySQL. This time around well look at the mk-table-checksum tool to check if a slave database contains the same data as its master.
mk-table-checksum generates table checksums for MySQL tables, typically
useful for verifying your slaves are in sync with the master. The
checksums are generated by a query on the server, and there is very
little network traffic as a result.
Maakit is included in a number of distributions or can be downloaded from its Google Code page. If you’re using Ubuntu you can install it with:
sudo apt-get install maatkit
To start with you’ll need to have a master-slave replication set up and running. I won’t go into it here, but if you don’t already have it running go through this guide and I’ll see you in a few minutes.
We’ll need to set up a table on our master and slave servers for mk-table-checksum to store its results in. For this example we’ll create a new database. On your master and all slaves log into mysql and:
mysql> create database test
Then create the table structure:
CREATE TABLE `checksum` ( `db` char(64) NOT NULL, `tbl` char(64) NOT NULL, `chunk` int(11) NOT NULL, `boundaries` char(64) NOT NULL, `this_crc` char(40) NOT NULL, `this_cnt` int(11) NOT NULL, `master_crc` char(40) default NULL, `master_cnt` int(11) default NULL, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`db`,`tbl`,`chunk`) );
Now we’re ready to run our checksums. Run the following on the master MySQL server:
mk-table-checksum -u root --askpass localhost --replicate=test.checksum --emptyrepltbl
-u is the username you want to log in as
–askpass will prompt you for a password before executing. Using -p instead you can type the password in before you run.
localhost is the master node (we’re running this command on the master node).
–replicate is the table that will be used to store our results in. Note the database.tablename syntax.
–emptyrepltbl will remove any records pre-existing in the database referenced by –replicate
After running this, you should get an output listing your tables and their checksums and your test.checksum table on your master and slave should also be populated with some data. To verify, on your master and slave nodes log into MySQL and show the contents of the test.checksum table:
$ mysql -u root -p mysql> use test mysql> select * from checksum;
If you want, you can stop there and manually check the data in the tables to work out if you have any discrepancies. However running the mk-table-checksum tool again with the –replcheck parameter will allow you to get a listing of any discrepancies. Before running this command on the master make sure your slaves are connected as it will use the
SHOW PROCESSLIST to gather information about slaves.
On your master run:
$ mk-table-checksum -u root -askpass localhost --replicate=test.checksum --replcheck 1
The replcheck parameter specifies the depth of slaves to check, i.e 0 will just look at the master, 1 will look at the slaves of the master, 2 will look at the slaves of the slaves of the master and so on. If there are no differences then mk-table-checksum won’t won’t write anything back.
To force a difference between slave and master you can write a new record to the slave database. Then re-run the my-table-checksum commands which should then show what differences exist.
There are lots of other options to play with and if you’re using this in production you should understand the implications of the command. Useful places to look are:
Run mk-table-checksum with the –help option
The maatkit documentation site.
If you find your replication is out of sync you can use the mk-table-sync tool too get things back in order.