Kinja OPS

Gawker ops team
Gawker ops team
This is a platform for User Generated Content. G/O Media assumes no liability for content posted by Kinja users to this platform.

Syncing differences in MySQL cluster

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.

Advertisement

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  
Advertisement

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 
Advertisement

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

YAY!

Share This Story

Get our newsletter