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).

Some options

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.

Detecting slaves

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.
Example:

insert into dsns values ('','h=1.1.1.2,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.

Example usage

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

Example output

            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

Leave a Reply