Verify MySQL master-slave consistency (pt-table-checksum & amp; pt-table-s_MySQL

Verify MySQL master-slave consistency (pt-table-checksumamp; amp; pt-table-sync) percona-toolkit-2.2.8-1.noarch.rpm has two tools to verify MySQL master-slave data consistency

Installing tookkit requires some dependent packages

Yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes-y

Lab environment

Initialize lab data on Master

Create database mvbox;

Use mvbox;

Create table test (id int primary key, name varchar (20 ));

Insert into test values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D ');

Because the master-Slave environment has been set up, the data will be automatically synchronized to the Slave.

Add a data entry to the Slave database to simulate master-Slave data inconsistency.

Insert into test values (5, 'E ');

Run the pt-table-checksum command in the Master database.

It uses the concat_ws function to merge data into one row, uses the crc32 function to generate a verification code, and inserts it into the checksums table of the percona database.

Because of the master-Slave environment, this data will be copied to the Slave

That is to say, the Slave percona. checksums table stores the verification code of the master database data.

Therefore, the server load balancer performs the same verification on the data and compares the data in the checksums table to verify whether the master and Slave databases are consistent.

Therefore, the account that executes the pt-table-checksum command must have at least the read-only permission of the entire database and the read and write permissions of the percona database.

Create user xx;

Grant select, PROCESS, SUPER, replication slave on *. * TO 'XX' @ '% 'identified BY 'XX ';

Grant all privileges on percona. * TO 'XX' @ '% 'identified BY 'XX ';

View Master-slave consistency

TS: the time when the check is completed.

ERRORS: number of ERRORS and warnings during the check.

DIFFS: 0 indicates consistency, and 1 indicates inconsistency. When -- no-replicate-check is specified, it is always 0. when -- replicate-check-only is specified, different information is displayed.

ROWS: number of ROWS in the table.

CHUNKS: number of CHUNKS divided into tables.

SKIPPED: Number of SKIPPED blocks due to errors or warnings.

TIME: the execution TIME.

TABLE: The name of the TABLE to be checked.

Common parameters

-- Nocheck-replication-filters: do not check the replication filter. we recommend that you enable it. You can use -- databases to specify the database to be checked.

-- No-check-binlog-format: the copied binlog mode is not checked. if the binlog mode is ROW, an error is returned.

-- Replicate-check-only: only information that is not synchronized is displayed.

-- Replicate =: write the checksum information to the specified table. we recommend that you directly write the information to the database to be checked.

-- Databases =: specifies the database to be checked. multiple databases are separated by commas.

-- Tables =: specifies the table to be checked. multiple tables are separated by commas (,).

H = 127.0.0.1: Master address

U = root: User name

P = 123456: password

P = 3306: Port

We can see that this tool has detected the inconsistency of the master.

If any inconsistency occurs, you can use the pt-table-sync command to fix it.

Note that this command must be executed in the Slave database.

When the print parameter is used, the fixed SQL statement is displayed on the screen. Then you can manually confirm and execute.

You can also use this command to automatically execute it. However, this will modify the data in the slave database, and it does not feel too secure.

Note that the two commands share locks on the table during execution. Therefore, you must carefully select the execution time in the production environment.