1. Show Differences Found by Pt-Table-Checksum

Show Differences Found by Pt-Table-Checksum

Louvre Museum

First of all,pt-table-checksum is perfect for checking if the master and its slaves are in-sync. It answers the question “Are the slaves consistent with the master?”. However, if they’re not, pt-table-checksum doesn’t actually tell you what exactly is wrong.

[root@master vagrant]# pt-table-checksum -q
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-03T22:21:10 0 1 2 1 0 0.013 mysql.proxies_priv
09-03T22:21:10 0 1 9 1 0 0.010 mysql.user

Frompt-table-checksum to pt-table-sync

Nevertheless, pt-table-sync may give you a partial answer. It can print SQL statements to sync the replication cluster. Thus, reading the SQL code, you may guess what records were missing/extra or differ.

[root@master vagrant]# pt-table-sync --print --replicate percona.checksums localhost
DELETE FROM `mysql`.`proxies_priv` WHERE `host`='slave.box' AND `user`='root' AND `proxied_host`='' AND `proxied_user`='' LIMIT 1 /*percona-toolkit src_db:mysql src_tbl:proxies_priv src_dsn:h=localhost dst_db:mysql dst_tbl:proxies_priv dst_dsn:h=ip-192-168-35-251.ec2.internal lock:1 transaction:0 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:21261 user:root host:master.box*/;
REPLACE INTO `mysql`.`proxies_priv`(`host`, `user`, `proxied_host`, `proxied_user`, `with_grant`, `grantor`, `timestamp`) VALUES ('localhost', 'root', '', '', '1', '', '2016-09-03 20:02:28') /*percona-toolkit src_db:mysql src_tbl:proxies_priv src_dsn:h=localhost dst_db:mysql dst_tbl:proxies_priv dst_dsn:h=ip-192-168-35-251.ec2.internal lock:1 transaction:0 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:21261 user:root host:master.box*/;
REPLACE INTO `mysql`.`proxies_priv`(`host`, `user`, `proxied_host`, `proxied_user`, `with_grant`, `grantor`, `timestamp`) VALUES ('master.box', 'root', '', '', '1', '', '2016-09-03 20:02:28') /*percona-toolkit src_db:mysql src_tbl:proxies_priv src_dsn:h=localhost dst_db:mysql dst_tbl:proxies_priv dst_dsn:h=ip-192-168-35-251.ec2.internal lock:1 transaction:0 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:21261 user:root host:master.box*/;
...

This is not very convenient. I’d note that pt-table-sync serves its main purpose – syncing two or more servers.

Using TwinDB Table Compare

TwinDB Table Compare is a tool that shows the exact difference between a slave and its master. It’s published on PyPi, therefore, you can install it right away:

[root@master vagrant]# pip install twindb_table_compare
Collecting twindb_table_compare
  Using cached twindb_table_compare-0.3.2-py2.py3-none-any.whl
Collecting mysql (from twindb_table_compare)
  Using cached mysql-0.0.1.tar.gz
Collecting Click>=6.0 (from twindb_table_compare)
  Using cached click-6.6.tar.gz
Collecting MySQL-python (from mysql->twindb_table_compare)
  Using cached MySQL-python-1.2.5.zip
Installing collected packages: MySQL-python, mysql, Click, twindb-table-compare
  Running setup.py install for MySQL-python ... done
  Running setup.py install for mysql ... done
  Running setup.py install for Click ... done
Successfully installed Click-6.6 MySQL-python-1.2.5 mysql-0.0.1 twindb-table-compare-0.3.2

TwinDB Table Compare depends on MySQLdb. In turn, MySQLdb requires several dependencies you need to install to get it compiled.

yum install gcc python-devel zlib-devel openssl-devel

Once you install them, you can see how your master and slave differ.

[root@master vagrant]# twindb_table_compare --user=dba --password=qwerty 192.168.35.251
2016-09-03 22:48:01,732: INFO: twindb_table_compare.get_inconsistencies():127: Executing: SELECT chunk FROM `percona`.`checksums` WHERE (this_crc<>master_crc OR this_cnt<>master_cnt) AND db='mysql' AND tbl='proxies_priv'
2016-09-03 22:48:01,734: INFO: twindb_table_compare.get_inconsistencies():138: Found 1 inconsistent chunk
2016-09-03 22:48:01,734: INFO: twindb_table_compare.get_inconsistencies():141: # mysql.proxies_priv, chunk 1
2016-09-03 22:48:01,736: INFO: twindb_table_compare.get_inconsistencies():143: # chunk index: None
2016-09-03 22:48:01,736: INFO: twindb_table_compare.get_inconsistencies():215: Executing: SELECT * FROM `mysql`.`proxies_priv` WHERE 1
2016-09-03 22:48:01,743: INFO: twindb_table_compare.get_inconsistencies():257: Differences between slave 192.168.35.251 and its master:
--- /tmp/master.GZ8S7V 2016-09-03 22:48:01.737762174 +0000
+++ /tmp/slave.9t4HhV 2016-09-03 22:48:01.738761674 +0000
@@ -1,2 +1,2 @@
-localhost root 1 2016-09-03 20:02:28
-master.box root 1 2016-09-03 20:02:28
+localhost root 1 2016-09-03 20:10:04
+slave.box root 1 2016-09-03 20:10:04

2016-09-03 22:48:01,746: INFO: twindb_table_compare.get_inconsistencies():127: Executing: SELECT chunk FROM `percona`.`checksums` WHERE (this_crc<>master_crc OR this_cnt<>master_cnt) AND db='mysql' AND tbl='user'
2016-09-03 22:48:01,747: INFO: twindb_table_compare.get_inconsistencies():138: Found 1 inconsistent chunk
2016-09-03 22:48:01,747: INFO: twindb_table_compare.get_inconsistencies():141: # mysql.user, chunk 1
2016-09-03 22:48:01,747: INFO: twindb_table_compare.get_inconsistencies():143: # chunk index: None
2016-09-03 22:48:01,748: INFO: twindb_table_compare.get_inconsistencies():215: Executing: SELECT * FROM `mysql`.`user` WHERE 1
2016-09-03 22:48:01,757: INFO: twindb_table_compare.get_inconsistencies():257: Differences between slave 192.168.35.251 and its master:
--- /tmp/master.l_zYw7 2016-09-03 22:48:01.749756174 +0000
+++ /tmp/slave.39qG9N 2016-09-03 22:48:01.752754674 +0000
@@ -1,9 +1,9 @@
 localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N
-master.box root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N
+slave.box root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N
 127.0.0.1 root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N
 ::1 root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N
 localhost N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password None N
-master.box N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password None N
+slave.box N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password None N
 % dba *AA1420F182E88B9E5F874F6FBE7459291E8F4601 Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password N
 localhost dba *AA1420F182E88B9E5F874F6FBE7459291E8F4601 Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0mysql_native_password N
 % repl *809534247D21AC735802078139D8A854F45C31F3 N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N 0 0 0 0 mysql_native_password N

We published the code on GitHub. And we’d love to see your feedback and contributions.

Previous Post Next Post