Percona-Tookit工具包之pt-table-sync

闾丘成双
2023-12-01
 
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.

 

转载于:https://www.cnblogs.com/aaron8219/p/9210206.html

 类似资料: