In my last article I showed how to fix replication errors on slaves, but I've made a mistake: my current example wasn't good, after skipping the command or inserting and empty transaction the dataset was different because of a timestamp holding date column which is CURRENT_TIMESTAMP default. Fixing the error solved the problem of the running replication thread, but the data wasn't same on the hosts. I decided to leave this as-is, and instead of recreating the test, I rather show how to sync the databases.

For this we can use the 'pt-table-checksum' and 'pt-table-sync' utilities from percona toolkit.

So the process will be the following:

* Create a user which will be used checksumming and syncing

* Check for differences

* Sync them

Let's see it:

master > GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'%' IDENTIFIED BY 'checksumpass'; master > FLUSH PRIVILEGES; [root@master vagrant]# pt-table-checksum h=192.168.50.50,u=checksum,p=checksumpass --recursion-method=processlist; TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 02-28T16:40:43 0 0 0 1 0 0.031 mysql.columns_priv 02-28T16:40:43 0 0 2 1 0 0.035 mysql.db 02-28T16:40:43 0 0 0 1 0 0.023 mysql.event 02-28T16:40:44 0 0 0 1 0 1.026 mysql.func 02-28T16:40:44 0 0 40 1 0 0.023 mysql.help_category 02-28T16:40:44 0 0 485 1 0 0.020 mysql.help_keyword 02-28T16:40:44 0 0 1090 1 0 0.025 mysql.help_relation 02-28T16:40:44 0 0 533 1 0 0.021 mysql.help_topic 02-28T16:40:44 0 0 0 1 0 0.022 mysql.ndb_binlog_index 02-28T16:40:44 0 0 0 1 0 0.025 mysql.plugin 02-28T16:40:44 0 0 0 1 0 0.027 mysql.proc 02-28T16:40:44 0 0 0 1 0 0.029 mysql.procs_priv 02-28T16:40:44 0 0 2 1 0 0.024 mysql.proxies_priv 02-28T16:40:44 0 0 0 1 0 0.021 mysql.servers 02-28T16:40:44 0 0 0 1 0 0.022 mysql.tables_priv 02-28T16:40:44 0 0 0 1 0 0.023 mysql.time_zone 02-28T16:40:44 0 0 0 1 0 0.026 mysql.time_zone_leap_second 02-28T16:40:44 0 0 0 1 0 0.025 mysql.time_zone_name 02-28T16:40:44 0 0 0 1 0 0.024 mysql.time_zone_transition 02-28T16:40:44 0 0 0 1 0 0.026 mysql.time_zone_transition_type 02-28T16:40:44 0 0 8 1 0 0.021 mysql.user 02-28T16:40:44 0 1 13 1 0 0.032 test.testdata

You can see there is 1 chunk which isn't the same in the master and the slave. So we have to sync them with pt-table-sync.

[root@master vagrant]# pt-table-sync h=192.168.50.51,u=checksum,p=checksumpass --print --sync-to-master REPLACE INTO `percona`.`checksums`(`db`, `tbl`, `chunk`, `chunk_time`, `chunk_index`, `lower_boundary`, `upper_boundary`, `this_crc`, `this_cnt`, `master_crc`, `master_cnt`, `ts`) VALUES ('test', 'testdata', '1', 0.002343, NULL, NULL, NULL, '3ef764d6', '13', '3ef764d6', '13', '2014-02-28 16:40:44') /*percona-toolkit src_db:percona src_tbl:checksums src_dsn:P=3306,h=192.168.50.50,p=...,u=checksum dst_db:percona dst_tbl:checksums dst_dsn:h=192.168.50.51,p=...,u=checksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:2867 user:root host:master.local*/; REPLACE INTO `test`.`testdata`(`id`, `text`, `date`) VALUES ('4', 'test', '2014-02-28 15:08:02') /*percona-toolkit src_db:test src_tbl:testdata src_dsn:P=3306,h=192.168.50.50,p=...,u=checksum dst_db:test dst_tbl:testdata dst_dsn:h=192.168.50.51,p=...,u=checksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:2867 user:root host:master.local*/; REPLACE INTO `test`.`testdata`(`id`, `text`, `date`) VALUES ('11', 'test', '2014-02-28 15:49:13') /*percona-toolkit src_db:test src_tbl:testdata src_dsn:P=3306,h=192.168.50.50,p=...,u=checksum dst_db:test dst_tbl:testdata dst_dsn:h=192.168.50.51,p=...,u=checksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:2867 user:root host:master.local*/; [root@master vagrant]# pt-table-sync h=192.168.50.51,u=checksum,p=checksumpass --execute --sync-to-master [root@master vagrant]# pt-table-checksum h=192.168.50.50,u=checksum,p=checksumpass TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 02-28T16:42:43 0 0 0 1 0 0.024 mysql.columns_priv 02-28T16:42:44 0 0 2 1 0 1.038 mysql.db 02-28T16:42:44 0 0 0 1 0 0.022 mysql.event 02-28T16:42:44 0 0 0 1 0 0.027 mysql.func 02-28T16:42:44 0 0 40 1 0 0.024 mysql.help_category 02-28T16:42:44 0 0 485 1 0 0.024 mysql.help_keyword 02-28T16:42:44 0 0 1090 1 0 0.025 mysql.help_relation 02-28T16:42:45 0 0 533 1 0 0.027 mysql.help_topic 02-28T16:42:45 0 0 0 1 0 0.019 mysql.ndb_binlog_index 02-28T16:42:45 0 0 0 1 0 0.023 mysql.plugin 02-28T16:42:45 0 0 0 1 0 0.022 mysql.proc 02-28T16:42:45 0 0 0 1 0 0.025 mysql.procs_priv 02-28T16:42:45 0 0 2 1 0 0.027 mysql.proxies_priv 02-28T16:42:45 0 0 0 1 0 0.019 mysql.servers 02-28T16:42:45 0 0 0 1 0 0.028 mysql.tables_priv 02-28T16:42:45 0 0 0 1 0 0.018 mysql.time_zone 02-28T16:42:45 0 0 0 1 0 0.026 mysql.time_zone_leap_second 02-28T16:42:45 0 0 0 1 0 0.026 mysql.time_zone_name 02-28T16:42:45 0 0 0 1 0 0.024 mysql.time_zone_transition 02-28T16:42:45 0 0 0 1 0 0.025 mysql.time_zone_transition_type 02-28T16:42:45 0 0 8 1 0 0.025 mysql.user 02-28T16:42:45 0 0 13 1 0 0.028 test.testdata

It seems there is no diffs there. Check this out with md5sum too

master > pager md5sum PAGER set to 'md5sum' master > SELECT * FROM testdata; c24796703bbe1aaac51bb7cca97c1a3c - 13 rows in set (0.00 sec) master > \P Default pager wasn't set, using stdout. slave > SELECT * FROM testdata; c24796703bbe1aaac51bb7cca97c1a3c - 13 rows in set (0.00 sec) slave > \P Default pager wasn't set, using stdout.

YAY!