Running the Tool

Log in to the primary database and create the pt database and checksums table for storing check information. (Secondary databases can automatically replicate the database and table.)

/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

create database pt;
show databases;
use pt;
create table checksums(
db             CHAR(64)     NOT NULL,
tbl            CHAR(64)     NOT NULL,
chunk          INT          NOT NULL,
chunk_time     FLOAT            NULL,
chunk_index    VARCHAR(200)     NULL,
lower_boundary TEXT             NULL,
upper_boundary TEXT             NULL,
this_crc       CHAR(40)     NOT NULL,
this_cnt       INT          NOT NULL,
master_crc     CHAR(40)         NULL,
master_cnt     INT              NULL,
ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
show tables;

1. Run the following commands on the primary database to create a user and grant permissions to the user (Secondary databases can automatically replicate the user and permissions.):

   create user 'checksums'@'%' identified by '123456';
   grant select,process,super,replication slave on *.* to 'checksums'@'%';
   grant all on pt.* to 'checksums'@'%';
   flush privileges;
   select user,host from mysql.user;

2. Query the permissions of the checksums user.

   show grants for checksums;
   select * from mysql.user where user='checksums' \G;

1. Run the following command on the client to compare the data in all tables of tpcc in the primary and secondary databases. By default, pt-table-checksum automatically discovers and connects to all secondary databases.

   1	pt-table-checksum h=192.168.220.58,u=checksums,p=123456,P=3306 --databases=tpcc --no-check-binlog-format --replicate=pt.checksums --max-lag=3 --recursion-method=hosts

2. Run the following command on the client to compare the data in the bmsql_district table of tpcc in the primary and secondary databases:

   1	pt-table-checksum h=192.168.220.58,u=checksums,p=123456,P=3306 --databases=tpcc --tables=bmsql_district --no-check-binlog-format --replicate=pt.checksums --max-lag=3 --recursion-method=hosts

   

   If the error message "Cannot connect to P=3307,h=,p=...,u=checksums.Diffs cannot be detected because no slaves were found" is displayed when you perform a consistency check on the client, rectify the fault by referring to "no slaves were found" Is Displayed During Data Consistency Verification Between MySQL Primary and Secondary Databases and log in to the client again to perform the consistency check.

   Table 1 Command parameter description

   Parameter	Description
   h=192.168.220.58	Specifies the IP address of the primary database.
   u=checksums	Specifies the user name.
   p=123456	Specifies the password.
   P=3306	Specifies the database port.
   --databases	Specifies the databases to be checked. Separate multiple databases by commas (,).
   --tables	Specifies the tables to be checked. Separate multiple tables by commas (,).
   --no-check-binlog-format	Disables the check of the binlog mode of primary-secondary replication. Set this parameter if the binlog mode is row.
   --replicate=pt.checksums	Writes check information into the checksums table.
   --max-lag=3	Specifies the maximum delay (in seconds) of secondary databases. When the delay of secondary databases exceeds the set value, the pt-table-checksum tool suspends.
   --nocheck-replication-filters	Disables the check for whether the filter is specified in the configuration of the primary database.
   --replicate-check-only	Displays only the information about primary/secondary data inconsistency. If the data between the primary and secondary databases is consistent, no information is displayed.

1. View the command output of the previous step. In the DIFFS column, 0 indicates that the data in all tables of tpcc in the primary database is consistent with that in secondary databases, while 1 indicates that the data is inconsistent.
   Figure 1 Comparison result of data in all tables of tpcc in the primary and secondary databases
   

   Table 2 Command output parameter description

   Parameter	Description
   TS	Time when the check is complete.
   ERRORS	Number of errors and warnings that occur during the check.
   DIFFS	Data consistency check result. The value 0 indicates consistent, and the value 1 indicates inconsistent.
   ROWS	Number of rows in the table.
   CHUNKS	Number of blocks that are allocated to the table.
   SKIPPED	Number of blocks skipped due to errors, warnings, or oversize.
   TIME	Execution time.
   TABLE	Name of the checked table.

2. Log in to databases and verify the data consistency check result.

   Log in to the primary and secondary databases.

   1	/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

   Query the check information of the primary and secondary databases.

   select * from pt.checksums;

   Query the check results of the primary and secondary databases.

   select * from pt.checksums where master_cnt <> this_cnt or master_crc <> this_crc or isnull(master_crc) <> isnull(this_crc);

   If the command output is empty, the data in the primary database is consistent with that in secondary databases. Otherwise, the data is inconsistent.