MySQL master-slave synchronization check and resynchronization

Master-slave replication environment, there may be a variety of reasons for the main, from the library data inconsistency, master-slave consistency has always been a concern of the DBA, check MySQL master-slave consistency generally have a variety of tools, such as the MySQL comes with checksum, Mysqldiff, Pt-table-checksum and so on, each tool has a good shortage, this article on the Pt-table-checksum tools to introduce and record the experimental link.

Download binary version of Percona-toolkit Toolbox

Https://www.percona.com/downloads/percona-toolkit

This article is using the 2.2.18 version

[Email protected]~]# tar xvf percona-toolkit-2.2.18.tar.gz

[[Email protected]~]# CD percona-toolkit-2.2.18

[Email protected]percona-toolkit-2.2.18]# perl makefile.pl

[[email protected] percona-toolkit-2.2.18]# make && make install

Main Library Creation checksums user is used to verify the consistency of master and slave

Mysql>grant SELECT, PROCESS, SUPER, REPLICATION SLAVE on * * to ' checksums ' @ '% ' identified by ' MANAGER ';

The validation begins, and after the checksum is completed, the Test under Library, create a table named checksums table for storing master-slave consistency information

[Email protected] ~]#/usr/local/bin/pt-table-checksum h= ' 192.168.1.250 ', u= ' checksums ', p= ' MANAGER ', p=3306-d www-- Nocheck-replication-filters--replicate=test.checksums--no-check-binlog-format

This article can be executed from the library SQL to see which tables have the same primary and non-uniform situation, where the master-slave is consistent so there is no result

Mysql> SELECT db,tbl, SUM (this_cnt) as Total_rows, COUNT (*) as chunks from Test.checksums WHERE (master_cnt <> t his_cnt or MASTER_CRC <> THIS_CRC or ISNULL (MASTER_CRC) <> ISNULL (THIS_CRC)) GROUP by DB, tbl; Empty Set (0.03 sec)

Let's delete it first . www A row of a table under the library

[Sql]delete from Decorate_order where id=10; affected rows: 1 time: 0.003s

Run the synchronization check again

[Email protected] ~]#/usr/local/bin/pt-table-checksum h= ' 192.168.1.250 ', u= ' checksums ', p= ' MANAGER ', p=3306-d www-- Nocheck-replication-filters--replicate=test.checksums--no-check-binlog-format

You can find the execution of this article from the library SQL you can see the name of the table that the master never matches

Mysql> SELECT db,tbl, SUM (this_cnt) as Total_rows, COUNT (*) as chunks from Test.checksums WHERE (master_cnt <> th is_cnt or MASTER_CRC <> THIS_CRC or ISNULL (MASTER_CRC) <> ISNULL (THIS_CRC)) GROUP by DB, tbl;+-----+---------- ------+------------+--------+| db | TBL | Total_rows | Chunks |+-----+----------------+------------+--------+|      www |decorate_order |      25356 | 1 |+-----+----------------+------------+--------+1 row in Set (0.00SEC)

In the same vein, we erase ERP A row of a table under the library

[Sql]delete from Erp_mard id=104; affected line: 1 time: 0.002s

Run the synchronization check again

[Email protected] ~]#/usr/local/bin/pt-table-checksum h= ' 192.168.1.250 ', u= ' checksums ', p= ' MANAGER ', p=3306-d www-- Nocheck-replication-filters--replicate=test.checksums--no-check-binlog-format

you can find just the deleted Erp_mard The table has entered our master-slave inconsistency record list.

Mysql> select db,tbl, sum (this_cnt)  as total_rows, count (*)  AS  chunks from test.checksums where  ( master_cnt <> this_cnt or  master_crc <> this_crc or isnull (MASTER_CRC)  <> isnull (this_ CRC))  group by db, tbl;+-----+----------------+------------+--------+| db   | tbl            | total_rows |  chunks |+-----+----------------+------------+--------+| erp |erp_mard        |      11183 |      1  | |  www |decorate_order |      25356 |       1 |+-----+----------------+------------+--------+2 rows in set  (0.02sec)

we perform in the main library Pt-table-sync command to Resynchronize

[Email protected] ~]#/usr/local/bin/pt-table-sync--execute--replicate test.checksums h= ' 192.168.1.250 ', u= ' Sys_ Admin ', p= ' MANAGER '

Re-verify after synchronization is complete

[Email protected] ~]#/usr/local/bin/pt-table-checksum h= ' 192.168.1.250 ', u= ' checksums ', p= ' MANAGER ', p=3306-d www-- Nocheck-replication-filters--replicate=test.checksums--no-check-binlog-format[[email protected] ~]#/usr/local/ Bin/pt-table-checksum h= ' 192.168.1.250 ', u= ' checksums ', p= ' MANAGER ', p=3306-d ERP--nocheck-replication-filters-- Replicate=test.checksums--no-check-binlog-format

can find checksums The table already has no unsynchronized tables.

Mysql> SELECT db,tbl, SUM (this_cnt) as Total_rows, COUNT (*) as chunks from Test.checksums WHERE (master_cnt <> t his_cnt or MASTER_CRC <> THIS_CRC or ISNULL (MASTER_CRC) <> ISNULL (THIS_CRC)) GROUP by DB, tbl; Empty Set (0.03 sec)

At this point, master-slave verification, synchronization completed

This article is from the "Age volt" blog, please make sure to keep this source http://suifu.blog.51cto.com/9167728/1836551

MySQL master-slave synchronization check and resynchronization