There are several possibilities to end up with inconsistent MySQL replication, This could be accidental or intentional. In this blog I would like to discuss on how to identify the inconsistent slaves with master and fix them. I used here\u00a0pt-table-checksum<\/strong> (to find the difference between master and slave) and pt-table-sync<\/strong> (to sync. between MySQL master and slave) from Percona Toolkit, The detailed documentation of Percona toolkit is available here<\/a>\u00a0for your reference. I expect \/ recommend you to be careful (as I mentioned above, sometimes records are inserted \/ deleted on MySQL slave intentionally) before using\u00a0pt-table-checksum\u00a0<\/em>to sync. slave with master because rollbacking this task is even more expensive. The objective of this blog is to show you how to find differences between master and slave in an MySQL replication infrastructure, If you have decided to sync. slave with master then please proceed with pt-table-sync\u00a0<\/em>tool. Both pt-table-checksum<\/em>\u00a0and\u00a0pt-table-sync <\/em>are highly customizable tools, I have used very simple form of them in the examples below:<\/p>\n

Master<\/strong> – 192.168.56.3<\/p>\n

Slave<\/strong> – 192.168.56.4<\/p>\n

Percona Toolkit Version<\/strong> –\u00a03.0.12<\/p>\n

MySQL Version<\/strong> -MySQL GA\u00a05.7.23<\/p>\n

I have created a user in the master to check and repair:<\/p>\n

GRANT ALL ON *.* to chksum@'%' identified by 'Password@2018';<\/pre>\n
In case you have non default ports (3306) for MySQL, Please set the following variables on the slaves:<\/p>\n
# my.cnf\r\n[mysqld]\r\nreport_host = slave\r\nreport_port = 3307<\/pre>\n
Confirm data inconsistencies in Master (192.168.56.3) and Slave (192.168.56.4):<\/p>\n
Master<\/strong><\/p>\n
mysql> select count(1) from titles; \r\n+----------+\r\n| count(1) |\r\n+----------+\r\n|   443308 |\r\n+----------+\r\n1 row in set (0.09 sec)\r\n<\/pre>\n
Slave<\/strong><\/p>\n
mysql> select count(1) from titles; \r\n+----------+\r\n| count(1) |\r\n+----------+\r\n|   443311 |\r\n+----------+\r\n1 row in set (0.09 sec)\r\n<\/pre>\n
Check data inconsistencies using\u00a0pt-table-checksum:<\/em><\/strong><\/p>\n
Check for data inconsistencies by executing the following command** on the Master:<\/p>\n
** command I have executed below\u00a0:-\u00a0pt-table-checksum h=192.168.56.3,u=chksum,p=Password@2018,P=3306 –set-vars innodb_lock_wait_timeout=30 –no-check-binlog-format –databases=employees –tables=titles<\/em><\/p>\n
[root@localhost ~]# pt-table-checksum h=192.168.56.3,u=chksum,p=Password@2018,P=3306 --set-vars innodb_lock_wait_timeout=30 --no-check-binlog-format --databases=employees --tables=titles\r\nChecking if all tables can be checksummed ...\r\nStarting checksum ...\r\n            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE\r\n09-20T22:53:02      0      2   443308          5       6       0   1.319 employees.titles\r\n<\/pre>\n
Fixing data inconsistencies using pt-table-checksum<\/em><\/strong><\/p>\n
We are checking data inconsistency from Master (192.168.56.3) to Slave\u00a0(192.168.56.3) by executing the following command** on the Master:<\/p>\n
** command I have executed below\u00a0:-\u00a0pt-table-sync –sync-to-master h=192.168.56.4,u=chksum,p=Password@2018,P=3306 –databases=employees –tables=titles –print<\/em><\/p>\n
[root@localhost ~]# pt-table-sync --sync-to-master h=192.168.56.4,u=chksum,p=Password@2018,P=3306 --databases=employees --tables=titles --print\r\nREPLACE INTO `employees`.`titles`(`emp_no`, `title`, `from_date`, `to_date`) VALUES ('10144', 'Senior Staff', '1992-10-14', '1993-08-10') \/*percona-toolkit src_db:employees src_tbl:titles src_dsn:P=3306,h=192.168.56.3,p=...,u=chksum dst_db:employees dst_tbl:titles dst_dsn:P=3306,h=192.168.56.4,p=...,u=chksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3789 user:root host:localhost.localdomain*\/;\r\nREPLACE INTO `employees`.`titles`(`emp_no`, `title`, `from_date`, `to_date`) VALUES ('10144', 'Staff', '1985-10-14', '1992-10-14') \/*percona-toolkit src_db:employees src_tbl:titles src_dsn:P=3306,h=192.168.56.3,p=...,u=chksum dst_db:employees dst_tbl:titles dst_dsn:P=3306,h=192.168.56.4,p=...,u=chksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3789 user:root host:localhost.localdomain*\/;\r\nDELETE FROM `employees`.`titles` WHERE `emp_no`='87000' AND `title`='Staff Engineer' AND `from_date`='1990-01-01' LIMIT 1 \/*percona-toolkit src_db:employees src_tbl:titles src_dsn:P=3306,h=192.168.56.3,p=...,u=chksum dst_db:employees dst_tbl:titles dst_dsn:P=3306,h=192.168.56.4,p=...,u=chksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3789 user:root host:localhost.localdomain*\/;\r\nDELETE FROM `employees`.`titles` WHERE `emp_no`='97000' AND `title`='Database Engineer' AND `from_date`='1991-01-01' LIMIT 1 \/*percona-toolkit src_db:employees src_tbl:titles src_dsn:P=3306,h=192.168.56.3,p=...,u=chksum dst_db:employees dst_tbl:titles dst_dsn:P=3306,h=192.168.56.4,p=...,u=chksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3789 user:root host:localhost.localdomain*\/;\r\nDELETE FROM `employees`.`titles` WHERE `emp_no`='97500' AND `title`='Project Manager' AND `from_date`='1983-04-11' LIMIT 1 \/*percona-toolkit src_db:employees src_tbl:titles src_dsn:P=3306,h=192.168.56.3,p=...,u=chksum dst_db:employees dst_tbl:titles dst_dsn:P=3306,h=192.168.56.4,p=...,u=chksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3789 user:root host:localhost.localdomain*\/;\r\nDELETE FROM `employees`.`titles` WHERE `emp_no`='97501' AND `title`='Project Manager' AND `from_date`='1983-04-11' LIMIT 1 \/*percona-toolkit src_db:employees src_tbl:titles src_dsn:P=3306,h=192.168.56.3,p=...,u=chksum dst_db:employees dst_tbl:titles dst_dsn:P=3306,h=192.168.56.4,p=...,u=chksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3789 user:root host:localhost.localdomain*\/;\r\nDELETE FROM `employees`.`titles` WHERE `emp_no`='97502' AND `title`='Project Engineer' AND `from_date`='1993-04-11' LIMIT 1 \/*percona-toolkit src_db:employees src_tbl:titles src_dsn:P=3306,h=192.168.56.3,p=...,u=chksum dst_db:employees dst_tbl:titles dst_dsn:P=3306,h=192.168.56.4,p=...,u=chksum lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3789 user:root host:localhost.localdomain*\/;\r\n[root@localhost ~]# \r\n<\/pre>\n
To fix inconsistencies on MySQL Master against the Slave execute the following command on the Master:<\/p>\n
[root@localhost ~]# pt-table-sync --sync-to-master h=192.168.56.4,u=chksum,p=Password@2018,P=3306 --databases=employees --tables=titles --execute\r\n<\/pre>\n
Confirm the data inconsistencies in Master (192.168.56.3) and Slave (192.168.56.4) are fixed:<\/p>\n
Master<\/strong><\/p>\n
mysql> select count(1) from titles;\r\n+----------+\r\n| count(1) |\r\n+----------+\r\n|   443308 |\r\n+----------+\r\n1 row in set (0.09 sec)\r\n<\/pre>\n
Slave<\/strong><\/p>\n
mysql> select count(1) from titles;\r\n+----------+\r\n| count(1) |\r\n+----------+\r\n|   443308 |\r\n+----------+\r\n1 row in set (0.09 sec)\r\n<\/pre>\n
Conclusion\u00a0<\/strong><\/p>\n
I recommend \/ encourage my customers to perform\u00a0pt-table-checksum <\/em>exercise regularly in their MySQL replication infrastructure to avoid unpleasant experiences due to data consistency issues.<\/p>\n","protected":false},"excerpt":{"rendered":"
There are several possibilities to end up with inconsistent MySQL replication, This could be accidental or intentional. In this blog I would like to discuss on how to identify the inconsistent slaves with master and […]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,156,154,155],"tags":[30,157,158],"yoast_head":"\n