pt-table-checksum is an artifact to check the consistency of MySQL data. With the pt-table-sync tool, it is perfect. There are many articles about this on the Internet, so I won’t introduce too much here. < br>Only specific steps are listed for reference when using:
1. Create a user for pt-table-checksum:
grant select,super,process,reload, show databases,replication client,replication slave on . to’ptchecksum’@’%’ identified by’passtchecksum’;
GRANT all on percona.* to’ptchecksum’@’%’ identified by ‘passtchecksum’;
2. Some table information used to create pt-table-checksum:
CREATE DATABASE IF NOT EXISTS percona;
CREATE TABLE IF NOT EXISTS percona.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;
CREATE DATABASE IF NOT EXISTS percona;
CREATE TABLE percona.dsns (id int(11) NOT NULL AUTO_INCREMENT,
< code>parent_id int(11) DEFAULT NULL,dsn varchar(255) NOT NULL,
PRIMARY KEY (id)
);
3. If the environment is one master and multiple slaves and only want to verify the consistency of the specified slave library when there are multiple slave libraries, the master library cannot automatically find the slave library, and the master-slave detection connection user information is different , You can use dsns to specify the standby database:
Configure the dsn information of the slave database:
insert into percona.dsns(dsn) values(‘h=192.168.56.102,P=3306,u=ptchecksum,p=passtchecksum ‘);
The first host h=192.168.56.101 is the database information of the main database, and the second host dsn=h=192.168.56.102 is the database information of the standby database. Multiple databases use commas. Separate:
pt-table-checksum –replicate=percona.checksums –nocheck-replication-filters –no-check-binlog-format h=192.168.56.101,u=ptchecksum,p=’passtchecksum’,P =3306 –databases=app,app1 –recursion-method dsn=h=192.168.56.102,u=ptchecksum,p=’passtchecksum’,P=3306,D=percona,t=dsns
If dsn is not specified, all slave standby databases that can be found are checked by default:
pt-table-checksum –replicate=percona.checksums –nocheck-replication-filters –no-check-binlog-format h=192.168.56.101 ,u=ptchecksum,p=’passtchecksum ‘,P=3306 –databases=app,app1
Output information:
Checking if all tables can be checksummed …
Starting checksum …
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
12-24T10:15:30 0 1 3 1 0 0.033 app.name
12-24T10:15:30 0 1 1 1 0 0.044 app.t_name
12-24T10:15 :33 0 0 426294 7 0 2.628 app1.t
12-24T10:15:34 0 0 199998 1 0 1.311 app1.t_adress
12-24T10:15:34 0 0 2965 1 0 0.364 app1.t_pd_sto_sku< /p>
Description:
TS: The time stamp of the completion of the inspection.
ERRORS: The number of errors and warnings during the check.
DIFFS: The number of inconsistent chunks. When –no-replicate-check is specified, the check is completed but the result is not output immediately, it will always be 0; when –replicate-check-only is specified, the crc32 is calculated from the checksums table and only inconsistent information is displayed. (After all, most of the output should be consistent, which is easy to cause interference).
ROWS: the number of table rows compared;
CHUNKS: the number of blocks divided into the table, detailed CHUNKS information can be queried in the table percona.checksums;
SKIPPED: due to errors or warnings Or too large, the number of blocks will be skipped;
TIME: execution time;
TABLE: table name to be checked;
If dsn is not specified, the default will be based on the main library Find the replication process from the library in the processlist to identify which slave libraries are there, and then perform pt-table-checksum, but if there are multiple slave libraries, and one of the slave data is inconsistent, the check will not give specific slave information, so At this time, you can use the dsn method to specify a specific slave library for inspection. In addition, if the database uses a non-standard port 3306, the slave database information will not be found. At this time, it is also recommended to use dsn, which specifies a table. In this example, percona.dsns is used. The table row record is the main database. The connection information of a slave library.