Mysql Master-Slave database consistency check and repair

Use Pt-table-checksum to check master-slave consistency, pt-table-sync to achieve master-slave data consistency repair

A. Percona-toolkit Download installation:
Need to install other dependent environment packages first ...
shell> perl-mcpan-e ' Install DBI '
shell> perl-mcpan-e ' Install Dbd::mysql '
shell> perl-mcpan-e ' Install Term::readkey '
By the way, when I install some Perl modules, I get an error similar to the following:
Can ' t Locate object method "install via package" ... "
If you also encounter a similar problem, you can go to the Perl command-line installation:
Yum Install YAML
Shell> perl-mcpan-e Shell
Cpan> Install ...

: wget http://www.percona.com/downloads/percona-toolkit/2.2.14/tarball/percona-toolkit-2.2.14.tar.gz
Installation method: Perl makefile.pl;make;make Install

two. Do Authorization on Master: (IP as the primary library address)
mysql> Grant Select, Process,super,replication slave on * * to ' user ' @ ' 192.168.9.140 ' identified by ' USERPW ';   ;
mysql> Flush privileges; 
  NOTE: Select: View all library tables; process: Perform show processlist; Super: Set binlog_format= ' Statement '  
         replication Slave:show slavehosts 
Database master-Slave structure:
Primary: 192.168.9.140 (binlog_format=mixed)
From: 192.168.9.142 (binlog_format=mixed)

three. Perform data consistency checks on the master and slave libraries
[[email protected]:/]# Pt-table-checksum h= ' 192.168.9.140 ', u= ' user ', p= ' USERPW ', p=3306--databases db1--tables t1 --max-load= "Threads _running=25 "--nocheck-replication-filters--create-replicate-table--replicate=test.checksums-- No-check-binlog-format--no-check-slave-tables
             TS errors  diffs     rows  CHUNKS skipped    time TABLE
05-13t15:42:58      0      1         8       1       0   0.030 db1.t1 The
Query results display diffs =1 indicates that there is an exception to the Db1.t1 table master and slave library data.

Execution Results display parameter meaning: 
 TS       : Time to complete . 
errors     : The number of errors and warnings that occur when checking . 
diffs     : 0 means consistent, 1 indicates an inconsistency. When--no-replicate-check is specified, it will always be 0 when the specified--replicate-check-only displays different information . 
rows       : Number of rows in the table . 
chunks    : The number of blocks that are divided into tables . 
skipped   : Number of skipped blocks due to error or warning or too large . 
time      : Execution time . 
table      : The table name being checked

Detailed parameters:
[[Email protected]:/]# pt-table-checksum--help See all the parameter information.]
H= ' 192.168.9.140 ', u= ' user ', p= ' USERPW ', p=3306 connect the primary database IP, username and password, port.
--create-replicate-table: This parameter is only added at the first run, it can be used to build the checksums table, and subsequent additions do not need to add this parameter if added will recreate the checksums table.
--nocheck-replication-filters: Do not check the copy filter (that is, the rules set in the parameter file repliacte-do-waild-table)
--no-check-binlog-format: Do not check the copy of the Binlog format (this parameter in, Binlog_format=row, must add, or error)
--replicate=kz.checksums: Writes checksum information to the specified library, specifies the table, and recommends writing directly to the checked library.
--ignore-tables=mysql.user: Ignoring checks on a table
--tables Specifies the name of the table to be checked; Multiple tables can be separated by commas. Example:--tables t1,t2
----databases Specifies the name of the library being checked, and if none of these parameters will check all libraries, you can specify multiple library names, for example:--databases db1,db2

More ways to use parameters:
--recursion-method processlist,hosts

If the process of executing the command encounters an error from the server that was not found:
Diffs cannot be detected because no slaves were found. Please read the--recursion-method documentation for information.
The above message is clear because it cannot be found from, so execution fails. With parameter--recursion-method you can specify a pattern resolution, and the settings for the--recursion-method parameter are:

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

The

Default is to find the value of host through show Processlist, and when--recursion-method=hosts, it takes show slave hosts to find the value of host.
using the--recursion-method=hosts parameter also can be resolved, because from the cubby more, but across the room from the library to check the slow problem. You can specify one of the checks from the library.
Use Workaround:
in the configuration file from the library: Report_host = 192.168.9.142   #设置成从库本机IP地址, restart the database
and then execute show slave hosts on the main library

>show slave hosts;
+-----------+----------------+------+----------+------+-------------------+-----------+
| server_id | host           | User | Password | Port | Rpl_recovery_rank | master_id |
+-----------+----------------+------+----------+------+-------------------+-----------+
|          2 | 192.168.9.142  |      |          | 3306 |                 0 |         140|
+-----------+----------------+------+----------+------+-------------------+-----------+
1 row in Set (0.00 SEC)

Finally execute the above command (add--recursion-method=hosts parameter)
Pt-table-checksum--recursion-method=hosts h= ' 192.168.9.140 ', u= ' user ', p= ' USERPW ', p=3306--databases db1--tables T1- -max-load= "threads_running=25"--nocheck-replication-filters--replicate=test.checksums--no-check-binlog-format- -no-check-slave-tables

Four. Repair of master-slave data inconsistencies
Syntax structure: pt-table-sync [OPTIONS] DSN [DSN]
Pt-table-sync: Efficient synchronization of data between MySQL tables, he can do one-way and two-way synchronized table data. He can synchronize a single table, or synchronize the entire library. It does not synchronize table structures, indexes, or any other schema objects. It is necessary to ensure that their tables exist before fixing the consistency.
Pt-table-sync--help View detailed parameters.
1. Merge according to the result database of the Pt-table-checksum check. Main designation--replicate=test.checksums
2. Merge the table data directly from the main library to the slave library. (The table above the main library does not exist from the library)

The

recommends merging based on the result database of the Pt-table-checksum check.
then the above replication, and the Test1 data from the inconsistent, need to repair, if there is a Chinese need to add:--charset=utf8, to prevent garbled.
1> The specified library, the table for data merging:
Pt-table-sync--recursion-method=hosts--replicate test.checksums--databases=db1--tables =t1--sync-to-master H=192.168.9.142,P=3306,U=USER,P=USERPW--charset=utf8--print;  //Print data with exception of master and slave
Pt-table-sync--recursion-method=hosts--replicate test.checksums--databases=db1--tables=t1--sync-to-master h= 192.168.9.142,P=3306,U=USER,P=USERPW--charset=utf8--execute; Perform data consistency synchronization
2> only merges data based on the results of Test.checksums.
Pt-table-sync--replicate test.checksums--sync-to-master H=192.168.9.142,P=3306,U=USER,P=USERPW--charset=utf8-- print; //Print master-slave exception data  
Pt-table-sync--replicate test.checksums--sync-to-master h=192.168.9.142,p= 3306,U=USER,P=USERPW--charset=utf8--execute;//Performing data consistency synchronization

Pt-table-sync--replicate=test.checksums h=192.168.9.140--user=user--password=userpw h=192.168.9.142,u=user,p= ' USERPW '--charset=utf8--print//print data with a master-slave exception
Pt-table-sync--replicate=test.checksums h=192.168.9.140--user=user--password=userpw h=192.168.9.142,u=user,p= ' USERPW '--charset=utf8--excute//Perform data consistency synchronization

Synchronizes the master test database to 192.168.9.142, which has the same data from the
Pt-table-sync--execute--sync-to-master--user=user--password=userpw h=192.168.9.142--database test

Synchronize only the specified table (AAA table)
Pt-table-sync--execute--sync-to-master--user=user--password=userpw h=192.168.9.142 D=test,t=aaa

Explanation of the parameters:
--replicate=: Specifies the table that is obtained through pt-table-checksum.
--databases=: Specifies the database where synchronization is performed, with multiple libraries separated by commas.
--tables=: Specifies the table where synchronization is performed, with multiple tables separated by commas.
--sync-to-master: Specifies a DSN, which is the IP from which he will go through show processlist or show slave status to Auto Brahma.
h=127.0.0.1: Server address, command has 2 IP, the first occurrence is the address of M, the 2nd time is the address of slave.
U=root: Account number.
p=123456: Password.
--print: print, but do not execute the command.
--execute: Executes the command.

For more parameters, please visit our website.
Https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html
Https://www.percona.com/doc/percona-toolkit/2.2/

This article is from the "Spin Wood Technology Blog" blog, please be sure to keep this source http://mrxiong.blog.51cto.com/287318/1651349

Mysql Master-Slave database consistency check and repair