Pt-table-checksum, Pt-table-sync Check master-Slave database consistency

A Download and install Tools
http://www.percona.com/downloads/percona-toolkit/
Currently the latest version is percona-toolkit_2.2.12.tar.gz
After uploading to the server, unzip and set to environment variables
Add path to MySQL user's environment variable file
VI. bash_profile
Export path= $PATH:/mysqldata/soft/percona-toolkit-2.2.12/bin

Two. Use the Pt-table-checksum command to find inconsistent data
The primary concern is diffs, 0 is consistent, 1 is inconsistent
-bash-4.1$ pt-table-checksum-- Nocheck-replication-filters--no-check-binlog-format h=localhost,u=root,p=passwd,p=3306
TS ERRORS DIFFS ROWS CHUNKS skipped time TABLE
01-21t14:00:20 0 0 1 0 0.304 test.test_inv_log
01-21t14:00:20 0 0 9 1 0 0.304 Test.test _inventory
01-21t14:00:21 0 0 2 1 0 0.061 test.test_lastexpressno
01-21t14:00:21 0 0 1 0 0.070 test.test_location
01-21t14:00:21 0 0 1 0 0.068 test.test_login_history
01-21t14:00:21 0 1 1 0 0.065 test.test_menu
01-21t14: 00:21 0 0 1 0 0.061 test.test_pkd
01-21t14:00:21 0 0 1 1 0 0.061 test.test_promotion
01-21t14:00:21 0 0 1 1 0 0.0 Test.test_promotion_condition
01-21t14:00:21 0 0 2 1 0 0.060 test.test_promotion_gift
...
Experiment 1: Insert a record from escudo, make a difference
the line marked red is the one I deliberately inserted from the library
to create inconsistent data, I stopped the reverse copy from the library to the main library in the dual master copy, closed the read-only parameter, and manually inserted a record.

Pt-table-checksum can also take many parameters, the more important usage is as follows:
-bash-4.1$ pt-table-checksum--nocheck-replication-filters--no-check-binlog-format [--replicate=rep_ Test.checksums--databases=rep_test--tables=test1] h=localhost,u=root,p=passwd,p=3306

The black part in the middle, the part inside the brackets is optional,
--nocheck-replication-filters: Do not check for replication filters, it is recommended to enable. You can use--databases later to specify the database you want to check.
--no-check-binlog-format: Do not check the copy of the Binlog mode, if the Binlog mode is row, will be an error.
--replicate-check-only: Displays only information that is not synchronized.
--replicate=: The checksum information is written to the specified table, it is recommended to write directly to the database being inspected, if not specified, the default is to create a new database called Percona in the main library, the check results are stored in the checksums table of Percona.
--databases=: Specifies the database that needs to be checked, and multiple are separated by commas.
--tables=: Specifies the table to be inspected, separated by commas

Let's try again, add 2 useful parameters, Replicate-check-only and database parameters
-bash-4.1$ pt-table-checksum--nocheck-replication-filters--no-check-binlog-format--replicate-check-only-- Databases=test h=localhost,u=root,p=passwd,p=3306

Using this command does not work well, it is not recommended to take the replicate-check-only parameter, because the output of the column is not full
only use the-databases parameter to specify the database to check better
-bash-4.1$ pt-table-checksum --nocheck-replication-filters--no-check-binlog-format--databases=test h=localhost,u=root,p=passwd,p=3306

TS ERRORS diffs ROWS CHUNKS skipped time TABLE
01-21t14:34:30 0 0 1 0 0.303 test.test_inv_log
01-21t14:34:30 0 0 9 1 0 0.061 test.test_inventory
01-21t14:34:30 0 0 2 1 0 0.306 test.test_lastexpressno
01-21t14:34:30 0 0 38 1 0 0.322 test.test_location
01-21t14:34:31 0 0 $1 0 0.307 test.test_login_history
01-21t14:34:31 0 1 1 0 0.305 te St.test_menu
01-21t14:34:31 0 0 1 0 0.060 test.test_pkd
01-21t14:34:31 0 0 1 1 0 0.055 test.test_promotion
01- 21t14:34:31 0 0 1 1 0 0.057 test.test_promotion_condition
01-21t14:34:31 0 0 2 1 0 0.068 test.test_promotion_gift
01 -21t14:34:31 0 0 2 1 0 0.058 test.test_promotion_rule
01-21t14:34:31 0 0 1 0 0.056 test.test_role
01-21t14:34:31 0 0 1 0 0.059 test.test_search_tab
01-21t14:34:32 0 0 1 0 0.303 test.test_send_template
...

After the implementation, you can find the difference, but the verification results table Percona.checksums, but found that there are bugs, the results table shows no difference, the results table focused on two columns THIS_CRC and MASTER_CRC
Mysql> Show tables;
+-------------------+
| Tables_in_percona |
+-------------------+
| checksums |
+-------------------+
1 row in Set (0.00 sec)

Mysql> select Db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums;
+-------+---------------------------+-------+----------+----------+------------+------------+------------------ ---+
| db | TBL | Chunk | THIS_CRC | this_cnt | MASTER_CRC | master_cnt | ts |
+-------+---------------------------+-------+----------+----------+------------+------------+------------------ ---+
| Test | Test_inv_adjust_header | 1 | 2a7935e8 | 1 | 2a7935e8 | 1 | 2015-01-21 14:34:29 |
| Test | Test_inv_log | 1 | efc2d050 | 34 | efc2d050 | 34 | 2015-01-21 14:34:29 |
| Test | Test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 14:34:30 |
| Test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 14:34:30 |
| Test | Test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 14:34:30 |
| Test | Test_menu | 1 | 6046676 | 33 | 6046676 | 33 | 2015-01-21 14:34:31 |
| Test | TEST_PKD | 1 | EE959BC2 | 11 | EE959BC2 | 11 | 2015-01-21 14:34:31 |
| Test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 14:34:31 |
| Test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 14:34:31 |
| Test | Test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 14:34:31 |
| Test | Test_promotion_rule | 1 | 76DAF3AA | 2 | 76DAF3AA | 2 | 2015-01-21 14:34:31 |
...
The red section shows no differences between master and slave libraries
Therefore, I infer that when checking the difference, the command output is the order, the data of the result table is not accurate, and the subsequent experiment negates the conclusion.

Lab 2: Modifying one data in the main library, making differences
Stops slave from the library, modifies a data in the main library, and does not conform to the contents of the library
From library:
mysql> stop Slave;
Query OK, 0 rows affected (0.02 sec)

Main Library:
Mysql> SELECT * from Test.test_menu where id=60 \g
1. Row ***************************
Id:60
Createtime:2015-01-21 13:58:18
Updatetime:2015-01-21 13:58:20
DOMAIN_ID:-1
Creator_id:null
Updator_id:null
Status_id:null
version:0
Menuname:menu_test2
parent_id:54
role_id:57
Sortindex:6
Menudescr:test2
Remark:null
Udf1:null
Udf2:null
Udf3:null
Udf4:null
1 row in Set (0.00 sec)

mysql> Update test.test_menu set menudescr= ' TEST2222 ' where id=60;
Query OK, 1 row affected (0.01 sec)
Rows matched:1 changed:1 warnings:0

Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)

With the Pt-table-check command check, unexpectedly did not find the difference, is because slave did not open the reason, but once opened from the library, synchronized Binlog, the difference is not, so plan to re-build slave, skip a few transacation, try

Execute from Library
mysql> Change Master to master_host= ' 192.168.2.195 ', master_user= ' repl ', master_password= ' slave123 ', Master_log_ File= ' mysql-bin.000006 ', master_log_pos=692149;

mysql> start slave;

After the coordination, the primary and standby replication is normal, the primary and standby differences exist
Main Library:
mysql> select * from Test.test_menu where id=60 \g
*************************** 1. Row ***************************
id:60
createtime:2015-01-21 13:58:18
updatetime:2015-01-21 13:58:20
DOMAIN_ID:-1
creator_id:null
updator_id:null
status_id:null
version:0
Menuname:menu_test2
parent_id:54
role_id:57
sortindex:6
menudescr:test2222
remark:null
udf1:null
Udf2:null
Udf3:null
Udf4:null
1 row in Set (0.00 sec)

From library:
Mysql> SELECT * from Test.test_menu where id=60 \g
1. Row ***************************
Id:60
Createtime:2015-01-21 13:58:18
Updatetime:2015-01-21 13:58:20
DOMAIN_ID:-1
Creator_id:null
Updator_id:null
Status_id:null
version:0
Menuname:menu_test2
parent_id:54
role_id:57
Sortindex:6
Menudescr:test2
Remark:null
Udf1:null
Udf2:null
Udf3:null
Udf4:null
1 row in Set (0.00 sec)
Red is the difference between master and slave libraries

Execute the pt-table-checksum command again in the main library
-bash-4.1$ pt-table-checksum--nocheck-replication-filters--no-check-binlog-format--databases=test h=localhost,u= root,p=passwd,p=3306
TS ERRORS diffs ROWS CHUNKS skipped time TABLE
01-21t15:17:41 0 0 4440 4 0 0.283 test.test_address
01-21T15:17:42 0 0 340 1 0 0.304 Test.test_allocation
01-21t15:17:42 0 0 2 1 0 0.059 Test.test_allocationexp
01-21t15:17:42 0 0 1 0 0.055 test.test_asn_detail
01-21t15:17:42 0 0 9 1 0 0.051 Test.test_asn_header
01-21t15:17:42 0 0 8 1 0 0.051 Test.test_biz_inventory
01-21t15:17:42 0 0 0 1 0 0.300 test.test_combination_map
01-21t15:17:42 0 0 1 0 0.055 test.test_common_property
01-21T15:17:42 0 0 103 1 0 0.055 Test.test_doc_log
01-21t15:17:42 0 0 0 1 0 0.047 test.test_etrackno_used
01-21t15:17:42 0 0 1 0 0.051 test.test_express_company
01-21t15:17:42 0 0 2 1 0 0.051 Test.test_inter_promotion_rule
01-21t15:17:42 0 0 1 1 0 0.062 Test.test_inv_adjust_detail
01-21t15:17:43 0 0 1 1 0 0.056 Test.test_inv_adjust_header
01-21t15:17:43 0 0 1 0 0.058 test.test_inv_log
01-21t15:17:43 0 0 9 1 0 0.054 test.test_inventory
01-21t15:17:43 0 0 2 1 0 0.058 Test.test_lastexpressno
01-21t15:17:43 0 0 1 0 0.056 test.test_location
01-21t15:17:43 0 0 1 0 0.058 test.test_login_history
01-21t15:17:43 0 1 1 0 0.067 Test.test_menu
01-21t15:17:43 0 0 1 0 0.161 TEST.TEST_PKD
01-21t15:17:44 0 0 1 1 0 0.347 test.test_promotion
01-21t15:17:44 0 0 1 1 0 0.409 test.test_promotion_condition
01-21t15:17:44 0 0 2 1 0 0.334 Test.test_promotion_gift
01-21t15:17:45 0 0 2 1 0 0.163 Test.test_promotion_rule
01-21t15:17:45 0 0 1 0 0.059 test.test_role
01-21t15:17:45 0 0 1 0 0.055 test.test_search_tab
01-21t15:17:45 0 0 1 0 0.309 test.test_send_template
01-21T15:17:45 0 0 4 1 0 0.057 test.test_sequence
01-21t15:17:45 0 0 1 0 0.300 test.test_sku
01-21t15:17:46 0 0 1 0 0.298 test.test_so_detail
01-21t15:17:46 0 0 1 0 0.069 test.test_so_header
01-21t15:17:46 0 0 2 1 0 0.056 test.test_soh_ee_relation
01-21t15:17:46 0 0 1 0 0.308 test.test_soh_eo_relation
01-21t15:17:46 0 0 1 0 0.060 test.test_store
01-21t15:17:46 0 0 1 0 0.059 test.test_sys_code
01-21t15:17:46 0 0 3 1 0 0.054 Test.test_sys_config
01-21t15:17:47 0 0 1 0 0.055 test.test_sys_domain
01-21t15:17:47 0 0 1 0 0.061 test.test_taobao_so_detail
01-21t15:17:47 0 0 1 0 0.312 test.test_taobao_so_header
01-21t15:17:47 0 0 1 0 0.063 test.test_trackprint_template
01-21t15:17:47 0 0 1 0 0.057 test.test_user
01-21t15:17:47 0 0 1 0 0.053 test.test_user_role
01-21t15:17:47 0 0 1 0 0.060 test.test_warehouse
Finally found the difference

In the main library check results table Percona.checksums, do not show the difference, there is still a bug?
Mysql> select Db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums;
+-------+---------------------------+-------+----------+----------+------------+------------+------------------ ---+
| db | TBL | Chunk | THIS_CRC | this_cnt | MASTER_CRC | master_cnt | ts |
+-------+---------------------------+-------+----------+----------+------------+------------+------------------ ---+
| Test | Test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| Test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| Test | Test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| Test | Test_menu | 1 | d9d69755 | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| Test | TEST_PKD | 1 | EE959BC2 | 11 | EE959BC2 | 11 | 2015-01-21 15:17:43 |
| Test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| Test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |
...
----Rows in Set (0.00 sec)

Check the results table from the library percona.checksums
Mysql> select Db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums;
+-------+---------------------------+-------+----------+----------+------------+------------+------------------ ---+
| db | TBL | Chunk | THIS_CRC | this_cnt | MASTER_CRC | master_cnt | ts |
| Test | Test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| Test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| Test | Test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| Test | Test_menu | 1 | 631FA3BF | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| Test | TEST_PKD | 1 | EE959BC2 | 11 | EE959BC2 | 11 | 2015-01-21 15:17:43 |
| Test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| Test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |
| Test | Test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 15:17:44 |
...
+-------+---------------------------+-------+----------+----------+------------+------------+------------------ ---+
----rows in Set (0.01 sec)
Finally show the difference, the original view results table record differences, to be seen in the slave library.
The previous record inserted from the escudo of the experiment, in the view from the library, there should be no problem, is that I checked the incorrect library to view the resulting
Conclusion: To view the difference results table, you should query on the slave library.

The following result is an experiment I later redo, in the query from the library
Select Db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where THIS_CRC! = MASTER_CRC;
Mysql> Select Db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where THIS_CRC! = Master_ CRC;
+-----+----------+-------+----------+----------+------------+------------+---------------------+
| db | TBL | Chunk | THIS_CRC | this_cnt | MASTER_CRC | master_cnt | ts |
+-----+----------+-------+----------+----------+------------+------------+---------------------+
| test | Test_menu | 1 | 3be44ed2 | 35 | d9d69755 | 34 | 2015-01-21 16:41:25 |
+-----+----------+-------+----------+----------+------------+------------+---------------------+
1 row in Set (0.00 sec)
Three. Repairing inconsistent data with Pt-table-sync
-bash-4.1$ pt-table-sync--print--replicate=percona.checksums h=localhost,u=root,p= passwd h=192.168.2.196,u=root,p=passwd

REPLACE into ' test '. ' Test_menu ' (' id ', ' createtime ', ' updatetime ', ' domain_id ', ' creator_id ', ' updator_id ', ' status_ ') Id ', ' version ', ' Menuname ', ' parent_id ', ' role_id ', ' sortindex ', ' menudescr ', ' remark ', ' udf1 ', ' udf2 ', ' udf3 ', ' udf4 ') VA Lues (' Max ', ' 2015-01-21 13:58:18 ', ' 2015-01-21 13:58:20 ', '-1 ', NULL, NULL, NULL, ' 0 ', ' menu_test2 ', ' si ', ' ', ' 6 ', ' TES T2222 ', NULL, NULL, NULL, NULL, NULL)/*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h=localhost,p=...,u=root Dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=...,u=root lock:1 transaction:1 changing_src: Percona.checksums replicate:percona.checksums bidirectional:0 pid:8016 user:mysql host:open_mysql1*/;

-bash-4.1$ pt-table-sync--print--sync-to-master h=192.168.2.196,u=root,p=passwd--databases=test--tables= Test_menu
REPLACE into ' test '. ' Test_menu ' (' id ', ' createtime ', ' updatetime ', ' domain_id ', ' creator_id ', ' updator_id ', ' status_id ', ' Version ', ' Menuname ', ' parent_id ', ' role_id ', ' sortindex ', ' menudescr ', ' remark ', ' udf1 ', ' udf2 ', ' udf3 ' , ' Udf4 ') VALUES (' All ', ' 2015-01-21 13:58:18 ', ' 2015-01-21 13:58:20 ', '-1 ', NULL, NULL, NULL, ' 0 ', ' menu_test2 ', ' 54 ', ' 57 ', ' 6 ', ' TEST2222 ', NULL, NULL, NULL, NULL, NULL)/*percona-toolkit src_db:test src_tbl:test_menu src_dsn:p=3306,h=192.16 8.2.195,p=...,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=...,u=root lock:1 transaction:1 Changing_src:1 replicate:0 bidirectional:0 pid:8022 user:mysql host:open_mysql1*/;

The above two commands are equivalent:
The meaning of the parameter:
--replicate=: Specifies the table obtained through Pt-table-checksum, which will almost always be used for all 2 tools.
--databases=: Specifies the database in which synchronization is performed, with multiple commas separated.
--tables=: Specifies the table for which synchronization is performed, with multiple commas separated.
--sync-to-master: Specifies a DSN, which is the IP from which he will go through show processlist or show slave status to Auto Brahma.
h=127.0.0.1: Server address, command has 2 IP, the first occurrence is the address of M, the 2nd time is the address of slave.
U=root: Account number.
p=123456: Password.
--print: print, but do not execute the command.
--execute: Executes the command.

FIX: The statements generated above are executed directly in the slave library

Note If you record more from the Cubby Master library, you get a DELETE statement that is also executed from the library
-bash-4.1$ pt-table-sync--print--replicate=percona.checksums h=localhost,u=root,p=passwd h=192.168.2.196,u=root,p =passwd
DELETE from ' Test '. ' Test_menu ' WHERE ' id ' = ' 1 ' LIMIT of/*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h= Localhost,p=...,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=...,u=root lock:1 transaction:1 Changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:8029 user:mysql host:open_mysql1*/;

Pt-table-checksum, Pt-table-sync Check master-Slave database consistency