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 checksumsG 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 0 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)
Supplement
1.Drop a table "testl_ddl" on slave when master-slave replication is normally running.
1 (root@localhost mysql3306.sock)[zlm]04:06:36>show tables; 2 +----------------+ 3 | Tables_in_zlm | 4 +----------------+ 5 | checksums | 6 | t1 | 7 | t2 | 8 | t3 | 9 | test_ddl | 10 | test_ddl_no_pk | 11 | test_innodb | 12 | test_myisam | 13 +----------------+ 14 8 rows in set (0.00 sec) 15 16 (root@localhost mysql3306.sock)[zlm]04:07:15>drop table test_ddl; 17 Query OK, 0 rows affected (0.00 sec)
2.Execute pt-table-checksum again.
1 [root@zlm2 04:12:26 ~] 2 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --check-slave-tables -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-22T04:30:32 0 0 0 0 1 0 0.012 mysql.columns_priv 8 06-22T04:30:32 0 0 2 0 1 0 0.015 mysql.db 9 06-22T04:30:32 0 0 2 0 1 0 0.013 mysql.engine_cost 10 06-22T04:30:32 0 0 0 0 1 0 0.016 mysql.event 11 06-22T04:30:32 0 0 0 0 1 0 0.014 mysql.func 12 06-22T04:30:32 0 0 40 0 1 0 0.011 mysql.help_category 13 06-22T04:30:32 0 0 693 0 1 0 0.015 mysql.help_keyword 14 06-22T04:30:32 0 0 1406 0 1 0 0.017 mysql.help_relation 15 06-22T04:30:32 0 0 637 0 1 0 0.018 mysql.help_topic 16 06-22T04:30:32 0 0 0 0 1 0 0.014 mysql.ndb_binlog_index 17 06-22T04:30:32 0 0 1 0 1 0 0.013 mysql.plugin 18 06-22T04:30:32 0 1 48 1 1 0 0.013 mysql.proc 19 06-22T04:30:32 0 0 0 0 1 0 0.013 mysql.procs_priv 20 06-22T04:30:32 0 0 1 0 1 0 0.014 mysql.proxies_priv 21 06-22T04:30:32 0 0 6 0 1 0 0.012 mysql.server_cost 22 06-22T04:30:32 0 0 0 0 1 0 0.012 mysql.servers 23 06-22T04:30:32 0 1 2 0 1 0 0.014 mysql.tables_priv 24 06-22T04:30:32 0 0 0 0 1 0 0.014 mysql.time_zone 25 06-22T04:30:32 0 0 0 0 1 0 0.013 mysql.time_zone_leap_second 26 06-22T04:30:32 0 0 0 0 1 0 0.012 mysql.time_zone_name 27 06-22T04:30:32 0 0 0 0 1 0 0.014 mysql.time_zone_transition 28 06-22T04:30:32 0 0 0 0 1 0 0.014 mysql.time_zone_transition_type 29 06-22T04:30:32 0 1 5 5 1 0 0.015 mysql.user 30 06-22T04:30:32 0 0 6 0 1 0 0.012 sys.sys_config 31 06-22T04:30:32 Skipping table zlm.test_ddl because it has problems on these replicas: 32 Table zlm.test_ddl does not exist on replica zlm3 33 This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check. 34 06-22T04:30:32 Error checksumming table zlm.test_ddl: Error getting row count estimate of table zlm.test_ddl on replica zlm3: DBD::mysql::db selectrow_hashref failed: Table 'zlm.test_ddl' doesn't exist [for Statement "EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6823. 35 36 06-22T04:30:32 1 0 0 0 0 0 0.002 zlm.test_ddl 37 06-22T04:30:32 0 0 2 0 1 0 0.014 zlm.test_ddl_no_pk 38 06-22T04:30:32 0 0 0 0 1 0 0.014 zlm.test_innodb 39 06-22T04:30:32 0 0 0 0 1 0 0.014 zlm.test_myisam 40 41 ###This will always lead to "ERROR 1146" what is shown below.### 42 Last_Errno: 1146 43 Last_Error: Error 'Table 'zlm.test_ddl' doesn't exist' on query. Default database: 'zlm'. Query: 'REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'zlm', 'test_ddl', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`name` using utf8mb4), `tel`, CONCAT(ISNULL(`tel`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `zlm`.`test_ddl` /*checksum table*/' 44 ... 45 Last_SQL_Errno: 1146 46 Last_SQL_Error: Error 'Table 'zlm.test_ddl' doesn't exist' on query. Default database: 'zlm'. Query: 'REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'zlm', 'test_ddl', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`name` using utf8mb4), `tel`, CONCAT(ISNULL(`tel`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `zlm`.`test_ddl` /*checksum table*/'
3.Use parameter "--no-check-slave-tables" when execute pt-table-checksum.
1 [root@zlm2 04:43:06 ~] 2 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --no-check-slave-tables -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-22T04:43:20 0 0 0 0 1 0 0.017 mysql.columns_priv 8 06-22T04:43:20 0 0 2 0 1 0 0.015 mysql.db 9 06-22T04:43:20 0 0 2 0 1 0 0.013 mysql.engine_cost 10 06-22T04:43:20 0 0 0 0 1 0 0.011 mysql.event 11 06-22T04:43:20 0 0 0 0 1 0 0.010 mysql.func 12 06-22T04:43:20 0 0 40 0 1 0 0.011 mysql.help_category 13 06-22T04:43:20 0 0 693 0 1 0 0.015 mysql.help_keyword 14 06-22T04:43:20 0 0 1406 0 1 0 0.012 mysql.help_relation 15 06-22T04:43:20 0 0 637 0 1 0 0.015 mysql.help_topic 16 06-22T04:43:20 0 0 0 0 1 0 0.013 mysql.ndb_binlog_index 17 06-22T04:43:20 0 0 1 0 1 0 0.012 mysql.plugin 18 06-22T04:43:20 0 1 48 1 1 0 0.012 mysql.proc 19 06-22T04:43:20 0 0 0 0 1 0 0.012 mysql.procs_priv 20 06-22T04:43:20 0 0 1 0 1 0 0.015 mysql.proxies_priv 21 06-22T04:43:20 0 0 6 0 1 0 0.013 mysql.server_cost 22 06-22T04:43:20 0 0 0 0 1 0 0.014 mysql.servers 23 06-22T04:43:20 0 1 2 0 1 0 0.013 mysql.tables_priv 24 06-22T04:43:20 0 0 0 0 1 0 0.012 mysql.time_zone 25 06-22T04:43:20 0 0 0 0 1 0 0.013 mysql.time_zone_leap_second 26 06-22T04:43:20 0 0 0 0 1 0 0.013 mysql.time_zone_name 27 06-22T04:43:20 0 0 0 0 1 0 0.011 mysql.time_zone_transition 28 06-22T04:43:20 0 0 0 0 1 0 0.012 mysql.time_zone_transition_type 29 06-22T04:43:20 0 1 5 5 1 0 0.012 mysql.user 30 06-22T04:43:20 0 0 6 0 1 0 0.011 sys.sys_config 31 06-22T04:43:20 Error checksumming table zlm.test_ddl: Error getting row count estimate of table zlm.test_ddl on replica zlm3: DBD::mysql::db selectrow_hashref failed: Table 'zlm.test_ddl' doesn't exist [for Statement "EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6823. 32 33 06-22T04:43:20 1 0 0 0 0 0 0.001 zlm.test_ddl 34 06-22T04:43:20 0 0 2 0 1 0 0.013 zlm.test_ddl_no_pk 35 06-22T04:43:20 0 0 0 0 1 0 0.011 zlm.test_innodb 36 06-22T04:43:20 0 0 0 0 1 0 0.014 zlm.test_myisam 37 38 ###If slave has already down,then it will wait until you handle the issue and restart slave.### 39 [root@zlm2 05:02:37 ~] 40 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --no-check-slave-tables -h192.168.1.101 -P3306 -urepl --ask-pass 41 Enter MySQL password: 42 Checking if all tables can be checksummed ... 43 Starting checksum ... 44 Replica zlm3 is stopped. Waiting. 45 Replica zlm3 is stopped. Waiting. 46 Replica zlm3 is stopped. Waiting.
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.