Use pt-table-checksum and pt-table-sync to fix Master-slave data inconsistency in mysql.

In the application of the MySql master-slave replication architecture, after various exceptions occur in the running DB for a long time, there may be inconsistency between the master and slave databases, and occasionally an error occurs in the same step, affects normal business access. So when we encounter this

In the application of the MySql master-slave replication architecture, after various exceptions occur in the running DB for a long time, there may be inconsistency between the master and slave databases, and occasionally an error occurs in the same step, affects normal business access. So when we encounter this

In the application of the MySql master-slave replication architecture, after various exceptions occur in the running DB for a long time, there may be inconsistency between the master and slave databases, and occasionally an error occurs in the same step, affects normal business access. So how can we handle this situation when we encounter this kind of master-slave data inconsistency? There are also many tools for mysql master-slave data verification, however, I often use some tools provided by percona-toolkit. By the way, I will simply record them.

1. About pt-table-checksum and pt-table-sync?

Pt-table-checksum and pt-table-sync are some useful performance diagnostic tools provided by percona for mysql DBAs. In addition, XtraBackup is often used for backup, this physical backup tool is used to provide hot backup for innodb of mysql and warm backup for myisam.

Pt-table-checksum is used to verify master-slave data consistency, while pt-table-sync is used to synchronize data between MySQL tables. It can synchronize data between a single table or the entire database, however, table structures, indexes, or any other schema objects are not synchronized.

Ii. Install percona-toolkit:

# Wget

# Cd ~ /

# Zxvf percona-toolkit_2.2.6.tar.gz

# Cd percona-toolkit-2.2.6/

# Perl Makefile. PL

# Make

# Make test

# Make install

3. Use pt-table-checksum to check whether the data is consistent (executed in the master database ):

Usage: pt-table-checksum [OPTIONS] [DSN]

Pt-table-checksum: used to verify master-slave data consistency. The replication consistency is checked through the query that executes the verification, and the Master/Slave check value is compared to generate the result. DSN points to the Master Address. If no parameters are specified, the system directly checks the tables of all local databases.

For example: pt-table-checksum -- nocheck-replication-filters -- no-check-binlog-format -- databases = db_test1 -- replicate = db_test1.checksums -- host = 172.16.5.100 -- port = 3306 -- user = root -- password = 123456

-- 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. (Note: exercise caution when using this parameter. This parameter will not generate new checksums data, but will only be displayed based on the existing data in the checksums table .)

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

Description of result parameters displayed during execution:

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.

4. Use pt-table-sync to repair inconsistent data:

Usage: pt-table-sync [OPTIONS] DSN [DSN]

Pt-table-sync: efficient synchronization of data between MySQL tables, which can be used for single-and bidirectional synchronization of table data. It can synchronize a single table or the entire database. It does not synchronize table structures, indexes, or any other schema objects. Therefore, ensure that their tables exist Before restoring consistency.

Example: pt-table-sync -- replicate = db_test1.checksums -- databases = db_test1 -- tables = table1, table2, table3 ,... -- charset = utf8 h = 127.0.0.1, u = root, p = 123456 h = 172.16.5.38, u = root, p = 123 -- print (we recommend that you print the statement to check the execution first, copy the statement and execute again)

-- Replicate =: Specifies the table obtained through pt-table-checksum, which will be used almost all the time.

-- Databases =: Specifies the database to be synchronized. Multiple databases are separated by commas.

-- Tables =: Specifies the table for synchronization. Multiple tables are separated by commas.

-- Sync-to-master: Specifies a DSN, that is, the slave IP address. It will automatically find the master through show processlist or show slave status.

H = 127.0.0.1: server address. There are two ip addresses in the command. The first ip address is M and the second ip address is Slave.

U = root: account.

P = 123456: password.

-- Print: print, but do not execute the command.

-- Execute: execute the command.