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

Source: Internet
Author: User
Tags install perl percona

Verify MySQL master-slave consistency (pt-table-checksum & 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.

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.