Pt-table-checksum Use Practice

Pt-table-checksum Use Practice

The most contact in the work is the MySQL replication, because now the company is also using the MySQL 5.1.x version, in the replication is still a lot of problems, such as the main library down or from the library downtime will cause replication interruption, usually we need to do artificial repair (MySQL The 5.5 version solves most of the problems), or a lot of times it is necessary to promote a master library from the library, but the data consistency from the library and the main library is not guaranteed, so use pt-table-checksum work to check for master-slave consistency and through Pt-table-sync How to fix these inconsistent data. Of course, if you have a small amount of data, slave is only used as a backup, then inconsistent data can be completely redo, or by other means. If the amount of data is very large, redo is one of the things that makes the egg break. ^_^

Tool Installation:

1. Software Download:

[Root@mysql-01 ~]# wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.7-1.noarch.rpm

2. Install the software package that the tool relies on:

[root@mysql-01 ~]# yum install Perl-io-socket-ssl perl-dbd-mysql

3. Software Installation:

[root@mysql-01 ~]# RPM-IVH percona-toolkit-2.2.7-1.noarch.rpm 
preparing ...                ########################################### [100%]
   1:percona-toolkit        ################################## ######### [100%]

How to use:

pt-table-checksum [OPTIONS] [DSN]

Pt-table-checksum: Check the consistency of the copy by performing a check on the main (master), comparing the master-slave checksum to produce the result. The DSN points to the main address, the exit state of the tool is not zero, and if any differences are found, or if there are any warnings or errors, please check the official information for more information.

The following is a practical example to explain how the tool is used:

Main Library:

Mysql> SELECT * from  yayun.t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | Yayun |
|  2 | Atlas |
|  3 | MySQL |
+----+-------+
3 rows in Set (0.00 sec)

Prepare library:

Mysql> select * from Yayun.t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | Yayun    |
|  2 | Atlas    |
|  3 | MySQL    |
|  4 | Dengyy   |
|  5 | Love SQL |
+----+----------+
5 rows in Set (0.00 sec)

Obviously the master and slave data are inconsistent, then we use tools to detect:

[root@mysql-01 ~]# pt-table-checksum--nocheck-replication-filters--replicate=yayun.checksums--databases=yayun-- Tables=t1 h=127.0.0.1,u=root,p=123456,p=3306
Replica MySQL-02 has binlog_format MIXED 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 are understand the risks, specify--no-check-binlog-format to disable this check.

From the error message, if you do not change the Binlog mode, then in the execution of the above command to specify:
--no-check-binlog-format, namely:

[root@mysql-01 ~]# pt-table-checksum--nocheck-replication-filters  --no-check-binlog-format--replicate= Yayun.checksums--databases=yayun--tables=t1 h=127.0.0.1,u=root,p=123456,p=3306
Cannot Connect to P=3306,h= 192.168.0.20,p=...,u=root
diffs cannot be detected because no slaves were.  Please read the--recursion-method documentation for information.
            TS ERRORS  diffs     ROWS  CHUNKS skipped time    TABLE
04-13t15:44:23      0      0        3       1       0   0.044 yayun.t1

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

What is the error? A lot of articles are not said, I really do not know how the article written out, embarrassed ...

The message above is clear, because it cannot be found, so execution fails. You can specify pattern resolution with parameter--recursion-method, and the settings for--recursion-method parameters are:

method USES ============= Processlist show Processlist hosts show SLAVE hosts cluster show STATUS like ' wsrep\_incoming\_ Addresses ' Dsn=dsn DSNs from a table none does not find slaves