In my last post, 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 an empty transaction the dataset was different because of a timestamp holding date column which is the 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 on 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 are 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!