首页 > > 网络编程 > Mysql >

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

2018-06-22 00:49:51来源：未知阅读 ()

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.