Preface
We've used pt-table-checksum to checksum the different table data bwtween replication master and slaves yesterday.In this case,i'll use another of of Percona-Toolkit called "pt-table-sync" to sync these difference.These two instruments usually work together well in replication environment.Of course,pt-table-sync can be used independently,too.
Introduce
pt-table-sync is a rather useful tool because replication is commonly implemented everywhere now.On account of replication delay or artificial error,slaves may turn out to be inconsistent with master.It will help use efficiently solving the problems.The machenism is to generate SQL statements and execute them on the specified servers.Let's see some details of it.
Procedure
Usage:
pt-table-sync [OPTIONS] DSN [DSN] --DSN format is key=value[,key=value...]
Parameters introduce:
###Connect parameters.### -h hostname -P port -u username -p password -S socket ###Object Parameters.### -d databases -t tables ###Frequently-used parameters.### --execute -- Really make changes happen accoridng to the SQL statments. --replicate -- sync differences just depend on checksums table generated by pt-table-checksum tool. --sync-to-master -- only used to specify one slave to sync differences with master. --replace -- Turn all the inser & update statments into repalce. ###Output parameters.### --verbose -- Show details of SQL statements. --print -- Print all the relevent differences. ###Other parameters.### --dry-run -- Don't really change data at all.
Generate the newest "checksums" table by pt-table-checksum on master.
1 [root@zlm2 07:55:34 ~] 2 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums -h192.168.1.101 -P3306 -urepl --ask-pass 3 Enter MySQL password: 4 Checking if all tables can be checksummed ... 5 Starting checksum ... 6 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 7 06-22T09:29:17 0 0 0 0 1 0 0.012 mysql.columns_priv 8 06-22T09:29:17 0 0 2 0 1 0 0.015 mysql.db 9 06-22T09:29:17 0 0 2 0 1 0 0.016 mysql.engine_cost 10 06-22T09:29:17 0 0 0 0 1 0 0.016 mysql.event 11 06-22T09:29:17 0 0 0 0 1 0 0.017 mysql.func 12 06-22T09:29:17 0 0 40 0 1 0 0.015 mysql.help_category 13 06-22T09:29:17 0 0 693 0 1 0 0.015 mysql.help_keyword 14 06-22T09:29:17 0 0 1406 0 1 0 0.017 mysql.help_relation 15 06-22T09:29:17 0 0 637 0 1 0 0.022 mysql.help_topic 16 06-22T09:29:17 0 0 0 0 1 0 0.015 mysql.ndb_binlog_index 17 06-22T09:29:17 0 0 1 0 1 0 0.016 mysql.plugin 18 06-22T09:29:17 0 1 48 1 1 0 0.018 mysql.proc 19 06-22T09:29:17 0 0 0 0 1 0 0.014 mysql.procs_priv 20 06-22T09:29:17 0 0 1 0 1 0 0.014 mysql.proxies_priv 21 06-22T09:29:17 0 0 6 0 1 0 0.015 mysql.server_cost 22 06-22T09:29:17 0 0 0 0 1 0 0.015 mysql.servers 23 06-22T09:29:17 0 1 2 0 1 0 0.016 mysql.tables_priv 24 06-22T09:29:17 0 0 0 0 1 0 0.016 mysql.time_zone 25 06-22T09:29:17 0 0 0 0 1 0 0.018 mysql.time_zone_leap_second 26 06-22T09:29:17 0 0 0 0 1 0 0.016 mysql.time_zone_name 27 06-22T09:29:17 0 0 0 0 1 0 0.015 mysql.time_zone_transition 28 06-22T09:29:17 0 0 0 0 1 0 0.016 mysql.time_zone_transition_type 29 06-22T09:29:17 0 1 5 5 1 0 0.017 mysql.user 30 06-22T09:29:17 0 0 6 0 1 0 0.016 sys.sys_config 31 06-22T09:29:17 0 0 1 0 1 0 0.015 zlm.test_ddl 32 06-22T09:29:17 0 0 2 0 1 0 0.015 zlm.test_ddl_no_pk 33 06-22T09:29:17 0 0 0 0 1 0 0.016 zlm.test_innodb 34 06-22T09:29:17 0 0 0 0 1 0 0.018 zlm.test_myisam
Check the details of differet tables on slave(master won't have these records).
1 (root@localhost mysql3306.sock)[zlm]09:30:03>select db,tbl,chunk,chunk_time,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_cnt<>master_cnt; 2 +---------+-----------+-------+------------+----------+----------+------------+------------+---------------------+ 3 | db | tbl | chunk | chunk_time | this_crc | this_cnt | master_crc | master_cnt | ts | 4 +---------+-----------+-------+------------+----------+----------+------------+------------+---------------------+ 5 | mysql | proc | 1 | 0.001536 | 9e5a007c | 49 | 4e0f05d9 | 48 | 2018-06-22 09:29:17 | 6 | mysql | user | 1 | 0.001122 | 7de55b47 | 10 | 587dfc7 | 5 | 2018-06-22 09:29:17 | 7 | percona | checksums | 1 | 0.000952 | 22f7b633 | 25 | d162e2ce | 29 | 2018-06-22 07:31:42 | 8 +---------+-----------+-------+------------+----------+----------+------------+------------+---------------------+ 9 3 rows in set (0.00 sec)
Sync table data by pt-table-sync(use both "replication"&"--sync-to-master").
1 [root@zlm2 10:02:44 ~] 2 #pt-table-sync --execute --replicate zlm.checksums --print --sync-to-master h=192.168.1.102,P=3306,u=repl --ask-pass 3 Enter password for 192.168.1.102: 4 -- Omitted.
Check the differences again.
1 [root@zlm2 10:01:42 ~] 2 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums -h192.168.1.101 -P3306 -urepl --ask-pass 3 Enter MySQL password: 4 Checking if all tables can be checksummed ... 5 Starting checksum ... 6 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 7 06-22T10:02:44 0 0 0 0 1 0 0.014 mysql.columns_priv 8 06-22T10:02:44 0 0 2 0 1 0 0.016 mysql.db 9 06-22T10:02:44 0 0 2 0 1 0 0.018 mysql.engine_cost 10 06-22T10:02:44 0 0 0 0 1 0 0.017 mysql.event 11 06-22T10:02:44 0 0 0 0 1 0 0.017 mysql.func 12 06-22T10:02:44 0 0 40 0 1 0 0.016 mysql.help_category 13 06-22T10:02:44 0 0 693 0 1 0 0.018 mysql.help_keyword 14 06-22T10:02:44 0 0 1406 0 1 0 0.017 mysql.help_relation 15 06-22T10:02:44 0 0 637 0 1 0 0.021 mysql.help_topic 16 06-22T10:02:44 0 0 0 0 1 0 0.018 mysql.ndb_binlog_index 17 06-22T10:02:44 0 0 1 0 1 0 0.016 mysql.plugin 18 06-22T10:02:44 0 0 48 0 1 0 0.018 mysql.proc 19 06-22T10:02:44 0 0 0 0 1 0 0.016 mysql.procs_priv 20 06-22T10:02:44 0 0 1 0 1 0 0.016 mysql.proxies_priv 21 06-22T10:02:44 0 0 6 0 1 0 0.014 mysql.server_cost 22 06-22T10:02:44 0 0 0 0 1 0 0.014 mysql.servers 23 06-22T10:02:44 0 0 2 0 1 0 0.016 mysql.tables_priv 24 06-22T10:02:44 0 0 0 0 1 0 0.016 mysql.time_zone 25 06-22T10:02:44 0 0 0 0 1 0 0.016 mysql.time_zone_leap_second 26 06-22T10:02:44 0 0 0 0 1 0 0.016 mysql.time_zone_name 27 06-22T10:02:44 0 0 0 0 1 0 0.016 mysql.time_zone_transition 28 06-22T10:02:44 0 0 0 0 1 0 0.016 mysql.time_zone_transition_type 29 06-22T10:02:44 0 0 5 0 1 0 0.016 mysql.user 30 06-22T10:02:44 0 0 6 0 1 0 0.015 sys.sys_config 31 06-22T10:02:44 0 0 1 0 1 0 0.015 zlm.test_ddl 32 06-22T10:02:44 0 0 2 0 1 0 0.015 zlm.test_ddl_no_pk 33 06-22T10:02:44 0 0 0 0 1 0 0.015 zlm.test_innodb 34 06-22T10:02:44 0 0 0 0 1 0 0.015 zlm.test_myisam
Since no diffs above,there're no more informations when reexecute pt-table-sync.
1 [root@zlm2 10:04:29 ~] 2 #pt-table-sync --execute --replicate zlm.checksums --print --verbose --sync-to-master h=192.168.1.102,P=3306,u=repl --ask-pass 3 Enter password for 192.168.1.102: 4 # Syncing via replication P=3306,h=192.168.1.102,p=...,u=repl 5 # DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE 6 Unknown database 'percona' [for Statement "USE `percona`"] at line 2832 while doing percona.checksums on 192.168.1.102 7 # 0 0 0 0 0 10:04:48 10:04:48 1 percona.checksums
Make data difference again by modify one record.
1 ###Check Master.### 2 (root@localhost mysql3306.sock)[zlm]10:22:00>select * from mysql.tables_priv; 3 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 4 | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | 5 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 6 | localhost | mysql | mysql.session | user | boot@connecting host | 2018-06-18 10:00:00 | Select | | 7 | localhost | sys | mysql.sys | sys_config | root@localhost | 2018-06-13 04:11:40 | Select | | 8 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 9 2 rows in set (0.00 sec) 10 11 ###Modify slave.### 12 (root@localhost mysql3306.sock)[zlm]10:31:11>update mysql.tables_priv set timestamp='2018-06-20 08:00:00' where db='mysql'; 13 Query OK, 1 row affected (0.00 sec) 14 Rows matched: 1 Changed: 1 Warnings: 0 15 16 (root@localhost mysql3306.sock)[zlm]10:32:04>select * from mysql.tables_priv; 17 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 18 | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | 19 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 20 | localhost | mysql | mysql.session | user | boot@connecting host | 2018-06-18 10:00:00 | Select | | 21 | localhost | sys | mysql.sys | sys_config | root@localhost | 2018-06-20 08:00:00 | Select | | 22 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 23 2 rows in set (0.00 sec) 24 25 (root@localhost mysql3306.sock)[zlm]10:32:08>select * from mysql.tables_priv; 26 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 27 | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | 28 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 29 | localhost | mysql | mysql.session | user | boot@connecting host | 2018-06-18 10:00:00 | Select | | 30 | localhost | sys | mysql.sys | sys_config | root@localhost | 2018-06-20 08:00:00 | Select | | 31 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 32 2 rows in set (0.00 sec)
Check the differences again.
1 [root@zlm2 10:29:55 ~] 2 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums -h192.168.1.101 -P3306 -urepl --ask-pass 3 Enter MySQL password: 4 Checking if all tables can be checksummed ... 5 Starting checksum ... 6 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 7 06-22T10:35:08 0 0 0 0 1 0 0.012 mysql.columns_priv 8 06-22T10:35:08 0 0 2 0 1 0 0.014 mysql.db 9 06-22T10:35:08 0 0 2 0 1 0 0.014 mysql.engine_cost 10 06-22T10:35:08 0 0 0 0 1 0 0.017 mysql.event 11 06-22T10:35:08 0 0 0 0 1 0 0.014 mysql.func 12 06-22T10:35:08 0 0 40 0 1 0 0.016 mysql.help_category 13 06-22T10:35:08 0 0 693 0 1 0 0.016 mysql.help_keyword 14 06-22T10:35:08 0 0 1406 0 1 0 0.017 mysql.help_relation 15 06-22T10:35:08 0 0 637 0 1 0 0.019 mysql.help_topic 16 06-22T10:35:08 0 0 0 0 1 0 0.015 mysql.ndb_binlog_index 17 06-22T10:35:08 0 0 1 0 1 0 0.013 mysql.plugin 18 06-22T10:35:08 0 0 48 0 1 0 0.016 mysql.proc 19 06-22T10:35:08 0 0 0 0 1 0 0.015 mysql.procs_priv 20 06-22T10:35:08 0 0 1 0 1 0 0.015 mysql.proxies_priv 21 06-22T10:35:08 0 0 6 0 1 0 0.015 mysql.server_cost 22 06-22T10:35:08 0 0 0 0 1 0 0.015 mysql.servers 23 06-22T10:35:08 0 1 2 0 1 0 0.014 mysql.tables_priv 24 06-22T10:35:08 0 0 0 0 1 0 0.013 mysql.time_zone 25 06-22T10:35:08 0 0 0 0 1 0 0.014 mysql.time_zone_leap_second 26 06-22T10:35:08 0 0 0 0 1 0 0.018 mysql.time_zone_name 27 06-22T10:35:08 0 0 0 0 1 0 0.016 mysql.time_zone_transition 28 06-22T10:35:08 0 0 0 0 1 0 0.017 mysql.time_zone_transition_type 29 06-22T10:35:08 0 0 5 0 1 0 0.017 mysql.user 30 06-22T10:35:08 0 0 6 0 1 0 0.016 sys.sys_config 31 06-22T10:35:08 0 0 1 0 1 0 0.017 zlm.test_ddl 32 06-22T10:35:08 0 0 2 0 1 0 0.015 zlm.test_ddl_no_pk 33 06-22T10:35:08 0 0 0 0 1 0 0.014 zlm.test_innodb 34 06-22T10:35:09 0 0 0 0 1 0 0.016 zlm.test_myisam
Sync table data by pt-table-sync again(only use "--replication").
1 [root@zlm2 10:35:09 ~]
2 #pt-table-sync --execute --replicate zlm.checksums --print --verbose h=192.168.1.101,P=3306,u=repl --ask-pass
3 Enter password for 192.168.1.101:
4 # Syncing via replication P=3306,h=zlm3,p=...,u=repl
5 # DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
6 REPLACE INTO `mysql`.`tables_priv`(`host`, `db`, `user`, `table_name`, `grantor`, `timestamp`, `table_priv`, `column_priv`) VALUES ('localhost', 'sys', 'mysql.sys', 'sys_config', 'root@localhost', '2018-06-13 04:11:40', 'Select', '') /*percona-toolkit src_db:mysql src_tbl:tables_priv src_dsn:P=3306,h=192.168.1.101,p=...,u=repl dst_db:mysql dst_tbl:tables_priv dst_dsn:P=3306,h=zlm3,p=...,u=repl lock:1 transaction:0 changing_src:zlm.checksums replicate:zlm.checksums bidirectional:0 pid:4514 user:root host:zlm2*/;
7 # 0 1 0 0 Nibble 10:35:59 10:35:59 2 mysql.tables_priv
8 Unknown database 'percona' [for Statement "USE `percona`"] at line 2832 while doing percona.checksums on zlm3
9 # 0 0 0 0 0 10:35:59 10:35:59 1 percona.checksums
Check the differences again.
1 [root@zlm2 10:35:59 ~] 2 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums -h192.168.1.101 -P3306 -urepl --ask-pass 3 Enter MySQL password: 4 Checking if all tables can be checksummed ... 5 Starting checksum ... 6 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 7 06-22T10:36:30 0 0 0 0 1 0 0.012 mysql.columns_priv 8 06-22T10:36:30 0 0 2 0 1 0 0.016 mysql.db 9 06-22T10:36:30 0 0 2 0 1 0 0.014 mysql.engine_cost 10 06-22T10:36:30 0 0 0 0 1 0 0.014 mysql.event 11 06-22T10:36:30 0 0 0 0 1 0 0.017 mysql.func 12 06-22T10:36:30 0 0 40 0 1 0 0.017 mysql.help_category 13 06-22T10:36:30 0 0 693 0 1 0 0.018 mysql.help_keyword 14 06-22T10:36:30 0 0 1406 0 1 0 0.017 mysql.help_relation 15 06-22T10:36:30 0 0 637 0 1 0 0.020 mysql.help_topic 16 06-22T10:36:30 0 0 0 0 1 0 0.018 mysql.ndb_binlog_index 17 06-22T10:36:30 0 0 1 0 1 0 0.017 mysql.plugin 18 06-22T10:36:30 0 0 48 0 1 0 0.017 mysql.proc 19 06-22T10:36:30 0 0 0 0 1 0 0.016 mysql.procs_priv 20 06-22T10:36:30 0 0 1 0 1 0 0.016 mysql.proxies_priv 21 06-22T10:36:30 0 0 6 0 1 0 0.018 mysql.server_cost 22 06-22T10:36:30 0 0 0 0 1 0 0.015 mysql.servers 23 06-22T10:36:30 0 0 2 0 1 0 0.015 mysql.tables_priv 24 06-22T10:36:30 0 0 0 0 1 0 0.015 mysql.time_zone 25 06-22T10:36:30 0 0 0 0 1 0 0.017 mysql.time_zone_leap_second 26 06-22T10:36:30 0 0 0 0 1 0 0.017 mysql.time_zone_name 27 06-22T10:36:30 0 0 0 0 1 0 0.016 mysql.time_zone_transition 28 06-22T10:36:30 0 0 0 0 1 0 0.015 mysql.time_zone_transition_type 29 06-22T10:36:30 0 0 5 0 1 0 0.018 mysql.user 30 06-22T10:36:30 0 0 6 0 1 0 0.015 sys.sys_config 31 06-22T10:36:30 0 0 1 0 1 0 0.018 zlm.test_ddl 32 06-22T10:36:30 0 0 2 0 1 0 0.016 zlm.test_ddl_no_pk 33 06-22T10:36:30 0 0 0 0 1 0 0.016 zlm.test_innodb 34 06-22T10:36:30 0 0 0 0 1 0 0.015 zlm.test_myisam
Check data in table on slave.
1 (root@localhost mysql3306.sock)[zlm]10:35:27>select * from mysql.tables_priv; 2 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 3 | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | 4 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 5 | localhost | mysql | mysql.session | user | boot@connecting host | 2018-06-18 10:00:00 | Select | | 6 | localhost | sys | mysql.sys | sys_config | root@localhost | 2018-06-13 04:11:40 | Select | | 7 +-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+ 8 2 rows in set (0.00 sec)
Summary
-
Take care of the DSN configuration while using diffrent parameter of pt-table-sync.
-
pt-table-sync can be used without pt-table-checksum either(don't specify "--replication" parameter,but need give DSN).
-
pt-table-sync requires statement-based replication while using "--replication" or "--sync-to-master".
-
The user of execute pt-table-sync need SUPER privilege to modify the "binlog_fomat" variable to row.
-
pt-table-sync does not relies on primary key or unique key,but "--replace" parameter does.