Shulou(Shulou.com)06/01 Report--

This article is mainly about verifying the consistency of master-slave data using pt-table-checksum tools. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that using pt-table-checksum tools to verify the consistency of master-slave data can bring you some practical help.

Background description:

Because master-slave replication leads to inconsistency of master-slave data, pt-table-checksum tool is used to check the consistency of master-slave data, and pt-table-sync tool is used to make up the difference data. the following is the process, problems encountered and solutions when using the tool, in order to simulate the online environment to the maximum extent.

Environment:

Backup is the master host (192.168.32.3), mydb is the slave host (192.168.32.2)

Port 3316 for backup and port 3306 for mydb

Binlog_format = ROW

Pt-table-checksum version 3.0.10 (please install percona-toolkit by yourself)

Preparatory work:

1. Install the dependency package:

[root@backup ~] # yum-y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL

2. Create a user:

Create a non-root user with permissions on both bakup and mydb hosts for data viewing and recovery operations. For security purposes, note: this user must be the same, the same, the same! To control permissions, you can authorize them only on their respective hosts

Backup host:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *. * TO 'monitor'@'127.0.0.1' IDENTIFIED BY' 123456'

Mydb host:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *. * TO 'monitor'@'192.168.32.3' IDENTIFIED BY' 123456'

Tool usage:

[root@backup] # pt-table-checksum-- help

Usage: pt-table-checksum [OPTIONS] [DSN] Note here: DSN connects to the address of the main library, and here it refers to the address of backup

Rules for using tools:

1. The tested table needs to have a primary key (unique index), because this is how pt-table-checksum works

2. The IO and SQL processes of the slave library should be in YES state, because the slave library needs to synchronize the checksum information of the master library.

3. The DSN when performing the verification is the address of the main library.

4. The S lock will be added when checking.

5. Make sure that the master database and slave database use the same account.

Perform verification (performed on the backup host):

[root@backup ~] # pt-table-checksum-- nocheck-replication-filters-- databases=bailidb hobby 127.0.0.1 retro upright monitorrependant 123456 page3316-- replicate-check-only

Checking if all tables can be checksummed...

Starting checksum...

Cannot connect to Prune 3306, pause, pause, pause.

Diffs cannot be detected because no slaves were found. Please read the-recursion-method documentation for information

Reason for reporting an error:

The master library cannot connect to the slave library. You need to configure the slave library.

Report_host=192.168.32.2

Report_port=3306

Of course, because report_host and report_port are read only variables, you need to restart the database

Perform the verification again:

[root@backup ~] # pt-table-checksum-- nocheck-replication-filters-- databases=bailidb hobby 127.0.0.1 retro upright monitorrependant 123456 page3316-- replicate-check-only

Checking if all tables can be checksummed...

Starting checksum...

Replica mydb has binlog_format ROW which could cause pt-table-checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify-- no-check-binlog-format to disable this check

Reason for reporting an error:

If it is a row-based replication environment (that is, binlog_format=row), percona officials do not recommend using the pt-table-checksum tool for data consistency checking, but it provides an option to skip this check-- no-check-binlog-format

Perform the verification again:

Print out all the check information: (you can save it to a file for post-processing)

[root@backup] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- databases=bailidb hobby 127.0.0.1 direction upright monitorcope pendant 123456 page3316 > [chayi.txt]

Only print check information with discrepancy data:

[root@backup ~] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- databases=bailidb hobby 127.0.0.1 direction upright monitorcope pendant 123456 page3316-- replicate-check-only > [chayi.txt]

Write the check data to the data table:

[root@backup ~] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate=test.checksums-- databases=bailidb hobby 127.0.0.1 retro upright monitorgy pamphlet 123456 [--recursion-method=hosts/--recursion-method=processlist]

-- nocheck-replication-filters does not check the replication filter. This parameter is often used in conjunction with the-- databases parameter to validate a single library.

-- no-check-binlog-format does not check the format of copied binary log files. If you have binlog_format=row, enable this parameter, otherwise an error will be reported.

-- replicate=test.checksums writes the verification information to the checksums table of the test library. If the table does not exist, it will be created automatically, and both master and slave hosts will have it.

-- databases specifies the databases for inspection. Multiple databases are separated by ","

-- replicate-check-only only displays information with inconsistent data

-- recursion-method=hosts if the error message appears "no slaves were found" when using pt-table-checksum check, this parameter is required.

H main library ip

The user name shared by the master library and the slave database

P the user password shared by the master library and the slave database

P main library port number, where the main library is port 3316

Pay special attention to:

-- parameters such as replicate-check-only and-- recursion-method=hosts must be used sequentially and must be used after the DSN used by pt-table-checksum!

[root@backup] # pt-table-checksum-- help

Options and values after processing arguments

Introduction of the verification results:

Checking if all tables can be checksummed...

Starting checksum...

TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE

06-20T11:58:16 0 0 69 0 1 0 0.298 bailidb.bl_admin

06-20T11:58:18 0 1 13 0 1 0 0.022 bailidb.bl_block

TS: time to complete the check

ERRORS: the number of errors and warnings that occurred during the check

DIFFS: the most important column that shows whether the verification results are consistent. 0 indicates consistency and 1 indicates inconsistency.

ROWS: the number of rows in the table, which refers to the master host

CHUNKS: the number of blocks divided into the table

SKIPPED: the number of blocks skipped due to errors or warnings or too large

TIME: the time when the check was performed

TABLE: the verified mark

Use the pt-table-sync tool to view the details of the difference data:

[root@backup] # pt-table-sync-- replicate=test.checksums hobby 127.0.0.1-- ask-pass hobby 192.168.32.2-- ask-pass-- charset=utf8-- print

Enter password for 127.0.0.1:

Enter password for 192.168.32.2:

You can also view information for only one of the tables:

[root@backup] # pt-table-sync-- replicate=test.checksums-- tables=bl_major hobby 127.0.0.1-- ask-pass hobby 192.168.322-- ask-pass-- charset=utf8-- print

Enter password for 127.0.0.1:

Enter password for 192.168.32.2:

Note:

In order to maximize the security of the online database, we should consider the actual online application environment at every step, and try our best to do so: if we can not restart the database, we will not restart the database, and if we can enter the password without plaintext, we will not be plaintext, in a word, for security.

Perform differential data synchronization:

[root@backup] # pt-table-sync-- replicate=test.checksums hobby 127.0.0.1-- ask-pass hobby 192.168.32.2-- ask-pass-- execute

[root@iZ2ze1wy2vjnk07k06p7s5Z] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- databases=bailitop hobby 127.0.0.1 direction upright monitorrePlease 3316-- ask-pass

Can't locate Term/ReadKey.pm

Yum-y install perl-TermReadKey

Use pt-table-checksum tools to verify the consistency of master-slave data. Let's stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.