Howto check MySQL replication consistency
If you want to be sure the data on the slave is the same as the data on the master ( yes sometimes is can happen to bbe diffeent ) you can use pt-table-checksum ( part of percona-toolkit ) to compute checksums for the data in the tables and then compare the checksums from the master with the ones on the slave(s).
Use with replication
pt-table-checksum can be used to compare any two databases/tables but if you want to compare everything on master and it's slaves you can use the
--replicate option to connect only on the master and compute checksums. The checksums will then be computed on slaves too by replicating the checksum statements.
If you have slave hosts running on non standard ports use option --recursion-method=hosts to tell pt-table-checksum how to detect the slaves. Tell the slaves to report their hostname and and port with report-host and report-port in my.cnf . This will make them show in "show slave hosts" issued on the master.
If the "hosts" method doesn't work, try with --recursion-method=dsn=t=dbname.dsns_table . Create a table with the name "dsns_table" and the following structure in the "dbname" database:
CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) )
And put the dsns for accessing the slaves in the dsn field.
insert into dsns values ('','h=188.8.131.52,u=root,P=3306,p=slavepassword');
For non innodb plugin
--lock-wait-time is required if using a version of mysql without innodb plugin.
Database for storing checksums
pt-table-checksum stores the checksums in mysql so you have to create a database where to store the checksums table. I named mine "mk" since the tool was called mk-table-checksum before it became part of percona toolkit.
Use the --create-replicate-table option to create the checksums table if it doesn't already exist.
pt-table-checksum --recursion-method=hosts --lock-wait-time=50 --defaults-file=/home/mihai/mysql.pass -u root --create-replicate-table --replicate-check --replicate mk.checksums 127.0.0.1
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 09-13T18:25:13 0 0 361 1 0 0.019 mydb.accounts 09-13T18:25:13 0 0 91 1 0 0.079 mydb.announcements