Here is a simple way to check if your database on master and slave server have the same data. We will use mk-table-checksum from maatkit tools for this purpose.
Note! Never do it on the production server! Or use it on your own risk! As stated:
mk-table-checksum executes queries that cause the MySQL server to checksum its data. This can cause significant server load. It is read-only unless you use the –replicate option, in which case it inserts a small amount of data into the specified table.
This trick works only for statement-based replication and with big databases can lead to significant overhead. To install mk-table-checksum run:
wget http://www.maatkit.org/trunk/mk-table-checksum chmod u+x ./mk-table-checksum
./mk-table-checksum -uroot -pstong_password --create-replicate --replicate YOUR_DB.checksums --ignore-databases information_schema,mysql --replicate-database=YOUR_DB --empty-replicate-table --chunk-size=1M nocheck-replication-filters master.example.com
This commands will execute CHECKSUM TABLE statement on all tables and will create table YOUR_DB.checksums to store sums. Due to the replication the same statements will be processed on the slave and it will calculate its own versions of checksums. After that mk-table-checksum can perform some query which will be used to define if databases are the same. More information on that can be found at maatkit documentation.
Now we are ready to do last check (at this time ‘–replicate-check 1’ are being used) :
./mk-table-checksum -uroot -pstrong_password --create-replicate --replicate YOUR_DB.checksums --ignore-databases information_schema,mysql --replicate-database=YOUR_DB --empty-replicate-table --chunk-size=1M --nocheck-replication-filters --replicate-check 1 master.example.com
If there’s no output it means there’s no differences between the master and slave.
One more thing. If master performs write operations during checksumming, you can get incorrect result so you might need to consider -lock option. And once again, you really don’t want to do all of these actions on live server.
Didn’t find the answer to your question? Ask it our administrators to reply we will publish on website.