Verifying the consistency of master-slave data using Percona-toolkit

Master-Slave data validation Use the following two tools from the Percona-toolkit toolset (used on the main library):

Pt-table-checksum #检查主从数据是否一致,

Pt-table-sync #把主库数据同步到从库

The applicable scenarios are as follows:

1). Data consistency check before and after data migration

2). When the master-slave replication problem occurs, after the completion of the repair, the master-slave data consistency check

3). Error operation performed from the library

4). Periodic check

Principle:

Pt-table-checksum uses the SBR format on the main library to perform hashcode function operations on a piece of data, and then copy it to check from the library to see if the hashcode result is the same.

Common parameter Explanation:

--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=: Writes the checksum information to the specified table, it is recommended to write directly to the database being inspected.

--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

H=127.0.0.1:master's address

U=root: User Name

p=123456: Password

p=3306: Port

--recursion-method #发现从库的参数, recommended processlist

METHOD USES

===========  =============================================

Processlist SHOW Processlist

Hosts SHOW SLAVE hosts

Cluster SHOW STATUS like ' wsrep\_incoming\_addresses '

Dsn=dsn DSNs from a table

None do not find slaves

Attention:

The user who executes the pt-table-checksum command must have super permissions to modify some variable information (for example, the Binlog_format variable is modified to statement format when the command is executed, and if the row format is used, Use the parameter--no-check-binlog-format does not check the row format), also note: Use these two tools if the table must have a primary key

Example:

Environment, has set up a traditional replication of the master-slave environment:

192.168.0.32 Main Library

192.168.0.72 from Library

1. One account is authorized in the master/slave library:

MySQL > GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE on * * to ' checksums ' @ ' 192.168.0.% ' identified by ' xxx ';

MySQL > Flush privileges;

2. Create a table in the main library:

MySQL > CREATE database xiaoboluo;

MySQL > CREATE TABLE ' AA ' (

' AA ' varchar (1) DEFAULT ',

' BB ' varchar (1) DEFAULT NULL,

' id ' int (one) is not NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT Charset=utf8;

3. Insert test data into the main library:

MySQL > INSERT into ' AA ' VALUES (' 1 ', ' 1 ', 1), (' 2 ', ' 2 ', 2), (' 5 ', ' 2 ', 5);

4. After checking the library, table, and data from the library for synchronization to complete, start the following steps after confirming the error:

Delete the id=1 line from the library, insert a row of data, and then modify the id=5 line:

MySQL > Delete from xiaoboluo.aa where id=1;

MySQL > INSERT into XIAOBOLUO.AA values (' 4 ', ' 4 ', 4);

MySQL > Update xiaoboluo.aa set bb=5 where id=5;

5. Execute the pt-table-checksum command to the main library:

Shell > Pt-table-checksum--nocheck-replication-filters--no-check-binlog-format--replicate=xiaoboluo.checksums --create-replicate-table--databases=xiaoboluo-h 192.168.0.38-p 3306-u root-pxxx--recursion-method= "Processlist"

The most important thing is to look at diffs this column (found table AA Master-slave data Inconsistent)

TS ERRORS diffs ROWS CHUNKS skipped time TABLE

12-24T12:17:58 0 1 3 1 0 0.010 Xiaoboluo.aa

The execution result shows the parameter meaning:

TS: Time to complete the check.

ERRORS: The number of errors and warnings that occurred while checking.

diffs:0 is consistent, greater than 0 means not. When--no-replicate-check is specified, it will always be 0 when the specified--replicate-check-only displays different information.

Rows: The number of rows in the table.

CHUNKS: The number of blocks that are divided into tables.

Skipped: The number of blocks skipped due to errors or warnings or too large.

Time: The duration of the execution.

Table: The names of the tables being checked.

6. Then execute the pt-table-sync command for data synchronization, it is best to use the command's--print command to print a little bit to see what data inconsistencies:

Shell > Pt-table-sync--replicate=xiaoboluo.checksums--databases=xiaoboluo--charset=utf8 h=192.168.0.38,u=root, P=xx--print

It is recommended to first use:--print print out (here will print the delete what data, replace into what data, pt-table-checksum in the detection of the main library has data, and from the library does not have the data, it will not be displayed, So it's better to print here and see the data from the main library, similar to the following:

DELETE from ' Xiaoboluo '. ' AA ' WHERE ' id ' = ' 4 ' LIMIT 1/*percona-toolkit src_db:xiaoboluo src_tbl:aa src_dsn:a=utf8,h= 192.168.0.38,p=...,u=root dst_db:xiaoboluo dst_tbl:aa dst_dsn:a=utf8,h=192.168.0.72,p=...,u=root lock:1 Transaction : 1 changing_src:xiaoboluo.checksums replicate:xiaoboluo.checksums bidirectional:0 pid:29301 user:root Host: localhost.localdomain*/;

REPLACE into ' xiaoboluo '. ' AA ' (' AA ', ' BB ', ' id ') VALUES (' 1 ', ' 1 ', ' 1 ')/*percona-toolkit Src_db:xiaoboluo src_tbl:aa src_d Sn:a=utf8,h=192.168.0.38,p=...,u=root Dst_db:xiaoboluo Dst_tbl:aa Dst_dsn:a=utf8,h=192.168.0.72,p=...,u=root Lock : 1 transaction:1 changing_src:xiaoboluo.checksums replicate:xiaoboluo.checksums bidirectional:0 pid:29301 user:root host:localhost.localdomain*/;

REPLACE into ' xiaoboluo '. ' AA ' (' AA ', ' BB ', ' id ') VALUES (' 5 ', ' 2 ', ' 5 ')/*percona-toolkit Src_db:xiaoboluo src_tbl:aa src_d Sn:a=utf8,h=192.168.0.38,p=...,u=root Dst_db:xiaoboluo Dst_tbl:aa Dst_dsn:a=utf8,h=192.168.0.72,p=...,u=root Lock : 1 transaction:1 changing_src:xiaoboluo.checksums replicate:xiaoboluo.checksums bidirectional:0 pid:29301 user:root host:localhost.localdomain*/;

7. Then perform the sync data in the--execute option:

Shell > Pt-table-sync--replicate=xiaoboluo.checksums--databases=xiaoboluo--charset=utf8 h=192.168.0.38,u=root, P=xxx--execute

Then, with the Pt-table-checksum check, if the diffs column is all 0, the master-slave data is consistent.

TS ERRORS diffs ROWS CHUNKS skipped time TABLE

12-24T12:22:31 0 0 3 1 0 0.271 Xiaoboluo.aa

The tool initiates a delete operation on the main library for more data from the library, and for data that is less than the primary key in the master-slave data, but not the same data as the other columns, the replace into operation is initiated on the main library. Note: Both operate on the main library and do not operate from the library. So how to know the master-slave data who is less, based on the calibration information in the checksums table.

Pt-table-sync is also used in the statement format to record the binary log during the operation, preferably in the process of the master and slave general_log are open. This will tell you what the master and slave libraries have done during the repair process.

Verifying the consistency of master-slave data using Percona-toolkit