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!