Inconsistent MySQL master check and repair

Pt-table-checksum installation:

: Wget www.percona.com/downloads/percona-toolkit/2.2.2/percona-toolkit-2.2.2.tar.gz

Tar xf percona-toolkit-{version}.tar.gz

Cd percona-toolkit-{version} # enter the decompressed directory

Vim README # view the installation manual

Prerequisites:

Install yum install perl mysql perl-DBD-MySQL-y

Install pt-table-checksum:

Perl

Makefile. PL

Make

Make install

1. operate on the master:

1. Authorize on the Master database:

GRANT update, insert, delete, SELECT, PROCESS, SUPER, replication slave on *. * TO checksum @ 'master ip' identified by '123 ';

 

2. manually create the checksums table under the specified database (such as pts) and save the pt-table-checksum detection data:

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,

Primary key (db, tbl, chunk ),

INDEX ts_db_tbl (ts, db, tbl)

) ENGINE = InnoDB;

A. Run pt-table-checksum to check the Master/Slave Data table:

/Usr/bin/pt-table-checksum h = '1. 1.1.2 ', u = 'checksum', p = '000000 ', P = 3306 -- databases test -- tables t -- nocheck-replication-filters -- create-replicate-table -- replicate = pts. checksums -- no-check-binlog-format -- lock-wait-timeout = 120

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

06-08T10: 41: 16 0 0 2 1 0 0.338 test. t

The parameters shown above are described as follows:

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.

 

B. Execute pt-table-sync to print tables with inconsistent Master/Slave Data:

/Usr/bin/pt-table-sync -- replicate = pts. checksums h = 1.1.1.2, u = checksum, p = 123 h = 1.1.1.4, u = checksum, p = 123 -- print

 

C. Restore data from the Master (executed on the Master and the result of the Master prevails)

/Usr/bin/pt-table-sync -- replicate = pts. checksums h = 1.1.1.2, u = checksum, p = 123 h = 1.1.1.4, u = checksum, p = 123 -- execute

 

Pt-table-checksum is a tool for online verification of master-slave data consistency. It is mainly used in the following scenarios:

1. Check data consistency before and after data migration

2. When there is a problem with master-slave replication, check the consistency of the master-slave data after the repair is completed.

3. The slave database is used as the master database for data updates, resulting in "dirty data"

4. Regular Verification

Working principle:

Pt-table-checksum executes the check Statement on the master to check the consistency of mysql replication online. It splits the table into blocks (hunk-size), generates the replace statement, and passes the copy statement to the slave, update the value of master_src. Check the value of this_src and master_src to determine whether the replication is consistent.