Percona-Tookit工具包之pt-table-checksum
程序员文章站
2022-06-12 14:12:42
Preface The master-slave replication is commonly used in our product evironment.On account of network lag or replicaton mode,slaves probablly be delay ......
Preface
The master-slave replication is commonly used in our product evironment.On account of network lag or replicaton mode,slaves probablly be delayed with master.How to solve the consistency of tables between master and slaves?pt-table-checksum is a suitable tool which you can use efficiently.I'm gonna use the tool to do some tests today.
Introduce
pt-table-checksum is a memeber of Percona-Toolkit,it's used to check consistency of tables in online replication environment by execute querries on master(put results into a replica called chckesum table).It will indicate whether there're some inconsistent tables bewteen master and slaves by output on screen with parameter "--print".
Procedure
1.Comman parameter introduce.
1 Connection relevent: 2 --ask-pass -- Ask user to input a password when executing. 3 -h hostname/ip 4 -u username 5 -p password(specify the password in command line) 6 -P port 7 -S socket 8 -D database 9 -t tables 10 11 Important parameter: 12 --create-replicate-table -- Create database and table(checksum table) mentioned in "--replicate". 13 --no-check-binlog-format -- Don't check binlog format on all server. 14 --recursion-method -- Specify the prefferd mode to find slaves if you've got multiple slaves. 15 --replicate -- Specify the table(default is "percona.checksums") into which the results will be write. 16 --replicate-check-only -- Check consistency on replica without executing checksum queries on master.Furthermore,it only checks riplica for differences found by previous checksuming. 17 18 Output relevent: 19 --explain -- Show without really execute checksum querries. 20 --progress -- Print progress report(default 30 seconds). 21 --quite -- Print only important informations on screen(will disable "--progress"). 22 23 Safety relevent: 24 --no-check-slave-tables -- Only if you're confirmed that all tables on slave is the same with master's.Then you can set it to avoid breaking of replication when executing pt-table-checksum. 25 --check-replication-filters -- Don't checksum if any replication filters are set on target replica. 26 --chunk-size-limit -- Limit the chunk size to avoid performance issues.
2.Examples.
1 Master:
2 (root@localhost mysql3306.sock)[zlm]10:27:06>show tables;
3 +----------------+
4 | Tables_in_zlm |
5 +----------------+
6 | test_ddl |
7 | test_ddl_no_pk |
8 | test_innodb |
9 | test_myisam |
10 +----------------+
11 4 rows in set (0.00 sec)
12
13 Slave:
14 (root@localhost mysql3306.sock)[zlm]10:27:35>show tables;
15 +----------------+
16 | Tables_in_zlm |
17 +----------------+
18 | t1 |
19 | t2 |
20 | t3 |
21 | test_ddl |
22 | test_ddl_no_pk |
23 | test_innodb |
24 | test_myisam |
25 +----------------+
26 7 rows in set (0.00 sec)
27
28 ###My pt-table-checksum is installed on master server.Execute the command below on master.###
29
30 [root@zlm2 10:42:50 ~]
31 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --check-slave-tables -hzlm2 -P3306 -uroot --ask-pass
32 Enter MySQL password:
33 06-21T10:42:57 DBI connect(';host=zlm2;port=3306;mysql_read_default_group=client','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at /usr/bin/pt-table-checksum line 1623.
34
35 [root@zlm2 10:42:57 ~]
36 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --check-slave-tables -hlocalhost -P3306 -uroot --ask-pass
37 Enter MySQL password:
38 Checking if all tables can be checksummed ...
39 Starting checksum ...
40 Cannot connect to P=3306,h=zlm3,p=...,u=root -- It's due to the "root" user cannot login with TCP/IP mode.
41 Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
42 *******************************************************************
43 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
44 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
45 possibly with SSL_ca_file|SSL_ca_path for verification.
46 If you really don't want to verify the certificate and keep the
47 connection open to Man-In-The-Middle attacks please set
48 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
49 *******************************************************************
50 at /usr/bin/pt-table-checksum line 332.
51 *******************************************************************
52 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
53 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
54 possibly with SSL_ca_file|SSL_ca_path for verification.
55 If you really don't want to verify the certificate and keep the
56 connection open to Man-In-The-Middle attacks please set
57 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
58 *******************************************************************
59 at /usr/bin/pt-table-checksum line 332.
60
61 # A software update is available:
62 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
63 06-21T10:44:08 0 0 0 0 1 0 0.006 mysql.columns_priv
64 06-21T10:44:08 0 0 2 0 1 0 0.012 mysql.db
65 06-21T10:44:08 0 0 2 0 1 0 0.010 mysql.engine_cost
66 06-21T10:44:08 0 0 0 0 1 0 0.008 mysql.event
67 06-21T10:44:08 0 0 0 0 1 0 0.009 mysql.func
68 06-21T10:44:08 0 0 40 0 1 0 0.008 mysql.help_category
69 06-21T10:44:08 0 0 693 0 1 0 0.010 mysql.help_keyword
70 06-21T10:44:08 0 0 1406 0 1 0 0.011 mysql.help_relation
71 06-21T10:44:08 0 0 637 0 1 0 0.032 mysql.help_topic
72 06-21T10:44:08 0 0 0 0 1 0 0.008 mysql.ndb_binlog_index
73 06-21T10:44:08 0 0 1 0 1 0 0.011 mysql.plugin
74 06-21T10:44:08 0 0 48 0 1 0 0.012 mysql.proc
75 06-21T10:44:08 0 0 0 0 1 0 0.009 mysql.procs_priv
76 06-21T10:44:08 0 0 1 0 1 0 0.010 mysql.proxies_priv
77 06-21T10:44:08 0 0 6 0 1 0 0.009 mysql.server_cost
78 06-21T10:44:08 0 0 0 0 1 0 0.010 mysql.servers
79 06-21T10:44:08 0 0 2 0 1 0 0.010 mysql.tables_priv
80 06-21T10:44:08 0 0 0 0 1 0 0.009 mysql.time_zone
81 06-21T10:44:08 0 0 0 0 1 0 0.009 mysql.time_zone_leap_second
82 06-21T10:44:08 0 0 0 0 1 0 0.009 mysql.time_zone_name
83 06-21T10:44:08 0 0 0 0 1 0 0.009 mysql.time_zone_transition
84 06-21T10:44:08 0 0 0 0 1 0 0.008 mysql.time_zone_transition_type
85 06-21T10:44:08 0 0 5 0 1 0 0.007 mysql.user
86 06-21T10:44:08 0 0 6 0 1 0 0.011 sys.sys_config
87 06-21T10:44:08 0 0 1 0 1 0 0.008 zlm.test_ddl
88 06-21T10:44:08 0 0 2 0 1 0 0.008 zlm.test_ddl_no_pk
89 06-21T10:44:08 0 0 0 0 1 0 0.009 zlm.test_innodb
90 06-21T10:44:08 0 0 0 0 1 0 0.009 zlm.test_myisam
91
92 ###See the detail of checksum table.###
93 (root@localhost mysql3306.sock)[zlm]10:27:07>show tables;
94 +----------------+
95 | Tables_in_zlm |
96 +----------------+
97 | checksums | -- the table checksums was created.
98 | test_ddl |
99 | test_ddl_no_pk |
100 | test_innodb |
101 | test_myisam |
102 +----------------+
103 5 rows in set (0.00 sec)
104
105 (root@localhost mysql3306.sock)[zlm]10:45:46>show create table checksums\G
106 *************************** 1. row ***************************
107 Table: checksums
108 Create Table: CREATE TABLE `checksums` (
109 `db` char(64) NOT NULL,
110 `tbl` char(64) NOT NULL,
111 `chunk` int(11) NOT NULL,
112 `chunk_time` float DEFAULT NULL,
113 `chunk_index` varchar(200) DEFAULT NULL,
114 `lower_boundary` text,
115 `upper_boundary` text,
116 `this_crc` char(40) NOT NULL,
117 `this_cnt` int(11) NOT NULL,
118 `master_crc` char(40) DEFAULT NULL,
119 `master_cnt` int(11) DEFAULT NULL,
120 `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
121 PRIMARY KEY (`db`,`tbl`,`chunk`),
122 KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
124 1 row in set (0.00 sec)
125
126 ###Check slave hosts information.###
127 (root@localhost mysql3306.sock)[zlm]10:54:52>show slave hosts;
128 +-----------+------+------+-----------+--------------------------------------+
129 | Server_id | Host | Port | Master_id | Slave_UUID |
130 +-----------+------+------+-----------+--------------------------------------+
131 | 1023306 | | 3306 | 1013306 | 5c77c31b-4add-11e8-81e2-080027de0e0e |
132 +-----------+------+------+-----------+--------------------------------------+
133 1 row in set (0.00 sec)
134
135 ###Change another user 'repl'@'192.168.1.%' and grant all privileges to it.###
136 (root@localhost mysql3306.sock)[zlm]11:07:58>grant all privileges on *.* to 'repl'@'192.168.1.%';
137 Query OK, 0 rows affected (0.00 sec)
138
139 (root@localhost mysql3306.sock)[zlm]11:08:19>show grants for 'repl'@'192.168.1.%';
140 +-----------------------------------------------------+
141 | Grants for repl@192.168.1.% |
142 +-----------------------------------------------------+
143 | GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.1.%' |
144 +-----------------------------------------------------+
145 1 row in set (0.00 sec)
146
147 ###Execute pt-table-checksum again.###
148 [root@zlm2 11:09:46 ~]
149 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --check-slave-tables -h192.168.1.101 -P3306 -urepl --ask-pass
150 Enter MySQL password:
151 Checking if all tables can be checksummed ...
152 Starting checksum ...
153 *******************************************************************
154 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
155 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
156 possibly with SSL_ca_file|SSL_ca_path for verification.
157 If you really don't want to verify the certificate and keep the
158 connection open to Man-In-The-Middle attacks please set
159 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
160 *******************************************************************
161 at /usr/bin/pt-table-checksum line 332.
162 *******************************************************************
163 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
164 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
165 possibly with SSL_ca_file|SSL_ca_path for verification.
166 If you really don't want to verify the certificate and keep the
167 connection open to Man-In-The-Middle attacks please set
168 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
169 *******************************************************************
170 at /usr/bin/pt-table-checksum line 332.
171
172 # A software update is available:
173 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
174 06-21T11:10:00 0 0 0 0 1 0 0.018 mysql.columns_priv
175 06-21T11:10:00 0 0 2 0 1 0 0.020 mysql.db
176 06-21T11:10:00 0 0 2 0 1 0 0.016 mysql.engine_cost
177 06-21T11:10:00 0 0 0 0 1 0 0.017 mysql.event
178 06-21T11:10:00 0 0 0 0 1 0 0.014 mysql.func
179 06-21T11:10:00 0 0 40 0 1 0 0.018 mysql.help_category
180 06-21T11:10:00 0 0 693 0 1 0 0.016 mysql.help_keyword
181 06-21T11:10:00 0 0 1406 0 1 0 0.015 mysql.help_relation
182 06-21T11:10:00 0 0 637 0 1 0 0.019 mysql.help_topic
183 06-21T11:10:00 0 0 0 0 1 0 0.013 mysql.ndb_binlog_index
184 06-21T11:10:00 0 0 1 0 1 0 0.012 mysql.plugin
185 06-21T11:10:00 0 1 48 1 1 0 0.015 mysql.proc
186 06-21T11:10:00 0 0 0 0 1 0 0.012 mysql.procs_priv
187 06-21T11:10:00 0 0 1 0 1 0 0.016 mysql.proxies_priv
188 06-21T11:10:00 0 0 6 0 1 0 0.015 mysql.server_cost
189 06-21T11:10:00 0 0 0 0 1 0 0.015 mysql.servers
190 06-21T11:10:00 0 1 2 0 1 0 0.014 mysql.tables_priv
191 06-21T11:10:00 0 0 0 0 1 0 0.013 mysql.time_zone
192 06-21T11:10:00 0 0 0 0 1 0 0.013 mysql.time_zone_leap_second
193 06-21T11:10:00 0 0 0 0 1 0 0.015 mysql.time_zone_name
194 06-21T11:10:00 0 0 0 0 1 0 0.015 mysql.time_zone_transition
195 06-21T11:10:00 0 0 0 0 1 0 0.012 mysql.time_zone_transition_type
196 06-21T11:10:00 0 1 5 5 1 0 0.013 mysql.user
197 06-21T11:10:00 0 0 6 0 1 0 0.015 sys.sys_config
198 06-21T11:10:00 0 0 1 0 1 0 0.014 zlm.test_ddl
199 06-21T11:10:00 0 0 2 0 1 0 0.015 zlm.test_ddl_no_pk
200 06-21T11:10:00 0 0 0 0 1 0 0.021 zlm.test_innodb
201 06-21T11:10:00 0 0 0 0 1 0 0.016 zlm.test_myisam
202
203 ###Check whether there're records in "checksums" table or not on slave.###
204 (root@localhost mysql3306.sock)[zlm]10:56:50>select * from checksums where this_cnt<>master_cnt;
205 +-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
206 | db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
207 +-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
208 | mysql | proc | 1 | 0.001277 | NULL | NULL | NULL | 9e5a007c | 49 | 4e0f05d9 | 48 | 2018-06-21 11:10:00 |
209 | mysql | user | 1 | 0.000667 | NULL | NULL | NULL | 7de55b47 | 10 | 587dfc7 | 5 | 2018-06-21 11:10:00 |
210 +-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
211 2 rows in set (0.00 sec)
Summary
- pt-table-checksum is a light tool with less influence of performance.
- pt-table-checksum use CRC arlgorism instead of MD5 and SHA1 to reduce consumption of CPU.
- Parameter "--no-check-binlog-format" is necessary while replications are using "row" binlog format.
- You can implement pt-table-checksum tool on either master or slave even other third server which is not belongs to replicaitons at all.
推荐阅读