{"id":37,"date":"2014-02-28T20:36:26","date_gmt":"2014-02-28T19:36:26","guid":{"rendered":"http:\/\/188.166.119.205\/?p=37"},"modified":"2026-11-10T05:52:04","modified_gmt":"2026-11-10T05:52:04","slug":"syncing-differences-in-mysql-cluster","status":"publish","type":"post","link":"https:\/\/banyek.net\/?p=37&lang=en","title":{"rendered":"Syncing differences in MySQL cluster"},"content":{"rendered":"<p>In my last post, I showed how to fix replication errors on slaves, but I\u2019ve made a mistake: my current example wasn\u2019t 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\u2019t 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.<\/p>\n<p>For this, we can use the \u2018pt-table-checksum\u2019 and \u2018pt-table-sync\u2019 utilities from Percona toolkit.<\/p>\n<p><!--more--><\/p>\n<p>So the process will be the following:<\/p>\n<ul>\n<li>Create a user which will be used checksumming and syncing<\/li>\n<li>Check for differences<\/li>\n<li>Sync them<\/li>\n<\/ul>\n<p>Let\u2019s see it:<\/p>\n<pre><code>master &gt; GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'%' IDENTIFIED BY 'checksumpass';\nmaster &gt; FLUSH PRIVILEGES;\n\n[root@master vagrant]# pt-table-checksum h=192.168.50.50,u=checksum,p=checksumpass --recursion-method=processlist;\n            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE\n02-28T16:40:43      0      0        0       1       0   0.031 mysql.columns_priv\n02-28T16:40:43      0      0        2       1       0   0.035 mysql.db\n02-28T16:40:43      0      0        0       1       0   0.023 mysql.event\n02-28T16:40:44      0      0        0       1       0   1.026 mysql.func\n02-28T16:40:44      0      0       40       1       0   0.023 mysql.help_category\n02-28T16:40:44      0      0      485       1       0   0.020 mysql.help_keyword\n02-28T16:40:44      0      0     1090       1       0   0.025 mysql.help_relation\n02-28T16:40:44      0      0      533       1       0   0.021 mysql.help_topic\n02-28T16:40:44      0      0        0       1       0   0.022 mysql.ndb_binlog_index\n02-28T16:40:44      0      0        0       1       0   0.025 mysql.plugin\n02-28T16:40:44      0      0        0       1       0   0.027 mysql.proc\n02-28T16:40:44      0      0        0       1       0   0.029 mysql.procs_priv\n02-28T16:40:44      0      0        2       1       0   0.024 mysql.proxies_priv\n02-28T16:40:44      0      0        0       1       0   0.021 mysql.servers\n02-28T16:40:44      0      0        0       1       0   0.022 mysql.tables_priv\n02-28T16:40:44      0      0        0       1       0   0.023 mysql.time_zone\n02-28T16:40:44      0      0        0       1       0   0.026 mysql.time_zone_leap_second\n02-28T16:40:44      0      0        0       1       0   0.025 mysql.time_zone_name\n02-28T16:40:44      0      0        0       1       0   0.024 mysql.time_zone_transition\n02-28T16:40:44      0      0        0       1       0   0.026 mysql.time_zone_transition_type\n02-28T16:40:44      0      0        8       1       0   0.021 mysql.user\n02-28T16:40:44      0      1       13       1       0   0.032 test.testdata\n<\/code><\/pre>\n<p>You can see there is 1 chunk which isn\u2019t the same on the master and the slave. So we have to sync them with pt-table-sync.<\/p>\n<pre><code>[root@master vagrant]# pt-table-sync h=192.168.50.51,u=checksum,p=checksumpass --print --sync-to-master\nREPLACE 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*\/;\nREPLACE 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*\/;\nREPLACE 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*\/;\n[root@master vagrant]# pt-table-sync h=192.168.50.51,u=checksum,p=checksumpass --execute --sync-to-master\n[root@master vagrant]# pt-table-checksum h=192.168.50.50,u=checksum,p=checksumpass\n            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE\n02-28T16:42:43      0      0        0       1       0   0.024 mysql.columns_priv\n02-28T16:42:44      0      0        2       1       0   1.038 mysql.db\n02-28T16:42:44      0      0        0       1       0   0.022 mysql.event\n02-28T16:42:44      0      0        0       1       0   0.027 mysql.func\n02-28T16:42:44      0      0       40       1       0   0.024 mysql.help_category\n02-28T16:42:44      0      0      485       1       0   0.024 mysql.help_keyword\n02-28T16:42:44      0      0     1090       1       0   0.025 mysql.help_relation\n02-28T16:42:45      0      0      533       1       0   0.027 mysql.help_topic\n02-28T16:42:45      0      0        0       1       0   0.019 mysql.ndb_binlog_index\n02-28T16:42:45      0      0        0       1       0   0.023 mysql.plugin\n02-28T16:42:45      0      0        0       1       0   0.022 mysql.proc\n02-28T16:42:45      0      0        0       1       0   0.025 mysql.procs_priv\n02-28T16:42:45      0      0        2       1       0   0.027 mysql.proxies_priv\n02-28T16:42:45      0      0        0       1       0   0.019 mysql.servers\n02-28T16:42:45      0      0        0       1       0   0.028 mysql.tables_priv\n02-28T16:42:45      0      0        0       1       0   0.018 mysql.time_zone\n02-28T16:42:45      0      0        0       1       0   0.026 mysql.time_zone_leap_second\n02-28T16:42:45      0      0        0       1       0   0.026 mysql.time_zone_name\n02-28T16:42:45      0      0        0       1       0   0.024 mysql.time_zone_transition\n02-28T16:42:45      0      0        0       1       0   0.025 mysql.time_zone_transition_type\n02-28T16:42:45      0      0        8       1       0   0.025 mysql.user\n02-28T16:42:45      0      0       13       1       0   0.028 test.testdata\n<\/code><\/pre>\n<p>It seems there are no diffs there. Check this out with md5sum too<\/p>\n<pre><code>master &gt; pager md5sum\nPAGER set to 'md5sum'\nmaster &gt; SELECT * FROM testdata;\nc24796703bbe1aaac51bb7cca97c1a3c  -\n13 rows in set (0.00 sec)\n\nmaster &gt; \\P\nDefault pager wasn't set, using stdout.\n\nslave &gt; SELECT * FROM testdata;\nc24796703bbe1aaac51bb7cca97c1a3c  -\n13 rows in set (0.00 sec)\n\nslave &gt; \\P\nDefault pager wasn't set, using stdout.\n<\/code><\/pre>\n<p>YAY!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my last post, I showed how to fix replication errors on slaves, but I\u2019ve made a mistake: my current example wasn\u2019t 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[134],"tags":[144,126,200],"class_list":["post-37","post","type-post","status-publish","format-standard","hentry","category-tech-en","tag-mysql-en","tag-oldblog-en","tag-percona-toolkit-en"],"_links":{"self":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/37","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=37"}],"version-history":[{"count":1,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/37\/revisions"}],"predecessor-version":[{"id":438,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/37\/revisions\/438"}],"wp:attachment":[{"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}