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

Percona-toolkit-2.2.8-1.noarch.rpm has two tools to verify the consistency of MySQL master-slave data.

Some dependency packages are required to install tookkit

Yum install perl perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes-y experimental environment

Initialize experimental data on Master

Create database mvbox

Use mvbox

Create table test (id int primary key,name varchar (20))

Insert into test values (1), (2), (3), (4), because the master-slave environment has been set up, the data will be automatically synchronized to the Slave.

Add a data to the Slave slave database to simulate the scenario where the master and slave data are inconsistent.

Insert into test values (5 pt-table-checksum'); execute the pt-table-checksum command in the main Master library.

It uses the concat_ws function to merge the data into a row, then uses the crc32 function to generate a check code, and finally inserts it into the checksums table of the percona library.

Because of the master-slave environment, this data will be copied to Slave

In other words, the percona.checksums table of Slave stores the check code of the main database data.

So perform the same check on the data in Slave, and then compare the data in the checksums table to verify that the master and slave are consistent.

Therefore, the account that executes the pt-table-checksum command needs at least read-only access to the entire library and read and write access to the percona library.

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'

Check the consistency between master and subordinate.

TS: the time it took to complete the check. ERRORS: the number of errors and warnings that occurred during the check. DIFFS: 0 means consistent, 1 means inconsistent. When-- no-replicate-check is specified, it will always be 0, and when-- replicate-check-only is specified, different information will be displayed. ROWS: the number of rows in the table. CHUNKS: the number of blocks divided into the table. SKIPPED: the number of blocks skipped due to errors or warnings or too large. TIME: time of execution. TABLE: the name of the table being checked. Common parameters

-- nocheck-replication-filters: do not check the replication filter. It is recommended to enable it. Later, you can use-- databases to specify the database to be checked. -- no-check-binlog-format: does not check the replicated binlog mode. If the binlog mode is ROW, an error will be reported. -- replicate-check-only: only messages that are out of sync are displayed. -- replicate=: write the information of checksum to the specified table. It is recommended to write it directly to the database being checked. -- databases=: specifies the databases to be checked, separated by commas. -- tables=: specify the tables that need to be checked. Multiple addresses u=root: username pendant 123456: password Pron3306: separated by commas for html 127.0.0.1: Master: the port can see that the tool has detected primary inconsistencies.

If inconsistencies occur, you can use the pt-table-sync command to fix them.

It is important to note that this command needs to be executed in the Slave slave library.

Using the print parameter, he displays the fixed SQL statement on the screen. It can then be manually confirmed and executed.

It can also be executed automatically through this command, but this will modify the data from the library, which does not feel too secure.

It is important to pay special attention to the shared lock on the table during the execution of these two commands, so the production environment should choose the execution time carefully.

Reference:

Http://nettedfish.sinaapp.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/

Http://www.cnblogs.com/zhoujinyi/archive/2013/05/09/3067045.html

Http://blog.chinaunix.net/uid-16844903-id-3360228.html

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.