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

Percona-toolkit is a collection of advanced command-line tools for performing a variety of very complex and cumbersome mysql and system tasks by manual execution, including:

Check the consistency of master and slave data

File records effectively

Find duplicate indexes

Summarize the server information

Analyze queries from logs and tcpdump

Collect important system information when something goes wrong with the system

Here, we will only introduce the most commonly used data consistency detection and repair under the mysql master-slave replication + MHA architecture. When master goes down, one of the slaver is elected as the master node. After the downtime node rejoins the cluster, it needs to synchronize data according to bin log. To be on the safe side, it is necessary to check the data consistency of the cluster. Percona-toolkit will come in handy.

1. Pt-table-checksum checks the consistency of master-slave database data.

Pt-table-checksum validates the specified library and table on MASTER, stores the results in a database table, and the replication process passes the verification sql to slave for execution again. The consistency of the data is determined by comparing the test values of MCMARS. Using master-slave replication to do inspection, there is no need to lock the table to the master-slave database at the same time during the inspection, the data and speed of verification can be controlled, and the normal service is not affected.

Installation:

# dependency package

Yum-y perl-DBI perl-DBD-MySQL perl-TermReadKey

# percona-toolkit package

Wget http://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-2.2.4.tar.gz

Tar xzvf percona-toolkit-2.2.4.tar.gz; cd percona-toolkit-2.2.4; perl Makefile.pl & & make & & make install

How to use it:

Pt-table-checksum [OPTIONS] [DSN]

Pt-table-checksum: check the consistency of the replication on the master through the query that performs the check, compare the check values of the master and slave, and produce the result. DSN points to the main address, and the exit status of the tool is not zero. If you find any difference, or if there are any warnings or errors, please see the official website for more information.

No parameters are specified, and tables in all local databases are checked directly.

Pt-table-checksum-S / tmp/mysqld.sock upright rootball pendant 123456

Environment:

# main library:

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

| | 3 | cc |

| | 4 | dd |

| | 5 | ee |

+-+ +

5 rows in set (0.00 sec)

# from the library:

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

| | 3 | cc |

| | 4 | dd |

+-+ +

4 rows in set (0.00 sec)

Note:

1. According to the test, you need a skill to log in to the master database, to log in to the slave database, and to synchronize the account of the database.

2. Only one host can be specified, which must be the IP of the main library.

3. An S lock will be added to the table during inspection

4. The synchronous IO and SQL processes that need to be run from the slave library before running are in YES state.

You can use this statement to authorize users. Here, you can directly use root users for convenience.

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *. * TO 'checksums'@'x.x.x.x' IDENTIFIED BY' xxxx'

Perform detection (on MASTER):

Pt-table-checksum-- nocheck-replication-filters-- replicate=test.checksum-- databases=test hackers 192.168.68.235, the upright rootjournal pamphlet 123-- empty-replicate-table-- create-replicate-table

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

09-18T12:03:16 01 5 1 0 0.018 test.t1

Parameter description:

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.

Parameter meaning:

-- 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=: specifies the tables to be checked, separated by commas

Html 127.0.0.1: address of Master

U=root: user name

Pair123456: password

Play3306: Port

For more parameters, please see the official website. The above parameters are commonly used and suitable for this scenario.

From the fact that DIFFS is 1, we can see that the data of the master-slave table is inconsistent. You can see the verification information of the master-slave library by looking at the test.checksum table on the slave library.

Mysql > select * from checksum\ G

* * 1. Row *

Db: test

Tbl: t1

Chunk: 1

Chunk_time: 0.001604

Chunk_index: NULL

Lower_boundary: NULL

Upper_boundary: NULL

This_crc: the check value of 13fa7d9d # from

This_cnt: 4 # number of rows from

Master_crc: check value of aa7a56c3 # master

Master_cnt: 5 # number of main lines

Ts: 2013-09-18 12:03:16

1 row in set (0.00 sec)

Their inconsistencies can be seen more clearly through the this_crc master_crc above, and the chunk knows which block of the table has inconsistent records. If the main binlog mode is Row, an error will be reported:

Replica db2 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.

From the error message, if the binlog mode is not changed, the above command should be specified when executing:-- no-check-binlog-format, that is:

Pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate-check-only-- replicate=test.checksum-- databases=test-- tables=t1 hobby 127.0.0.1

Specify-- the replicate-check-only parameter will be compared against the data from the previous pt-table-checksum verification (no more calculations will be performed), showing the SLAVE hostname with inconsistent data:

[root@host125] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate-check-only-- replicate=test.checksum-- databases=test-- tables=t1 hobby 127.0.1

[root@host125] # pt-table-checksum-- nocheck-replication-filters-- replicate=test.checksum-- databases=test hype 192.168.68.235, the publication of upright rootbook pamphlet 123-- empty-replicate-table-- create-replicate-table-- replicate-check-only

Differences on host122

TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY

Test.t1 1-1 1

SLAVE and tables with inconsistent data have been found, so let's use pt-table-sync to fix the data.

2. Pt-table-sync fixes inconsistent data from the slave database.

How to use it:

Pt-table-sync [OPTIONS] DSN [DSN]

Pt-table-sync: efficient synchronization of data between MySQL tables, it can do one-way and two-way synchronization of table data. He can synchronize a single table or an entire library. It does not synchronize table structures, indexes, or any other schema objects. So you need to make sure that their tables exist before fixing the consistency.

Continue the replication environment above, the data of the master and slave T1 tables are inconsistent and need to be repaired.

Execute:

[root@host125] # pt-table-sync-- print-- replicate=test.checksum hobby 192.168.68.235 replicate=test.checksum 3306 hobby 192.168.68.235

# first MASTER's IP, then SLAVE's IP

REPLACE INTO `test`.`t1` (`id`, `name`) VALUES ('5percent,' ee')

/ * percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:test.checksum replicate:test.checksum bidirectional:0 pid:24763 user:root host:host125*/

The meaning of the parameter:

-- replicate=: specifies the table obtained through pt-table-checksum, and both tools will be used almost all the time.

-- databases=: specifies the databases that perform synchronization, separated by commas.

-- tables=: specifies the tables that perform synchronization, separated by commas.

-- sync-to-master: specify a DSN, the slave IP, who will automatically find the master through show processlist or show slave status.

Html 127.0.0.1: server address, there are two ip in the command, the first is the address of M, the second is the address of Slave.

U=root: account number.

Password 123456: password.

-- print: prints, but does not execute commands.

-- execute: execute the command.

For more parameters, please see the official website. The above parameters are commonly used and suitable for this scenario.

A command that has the same effect as the above command:

[root@host125 ~] # pt-table-sync-- print-- sync-to-master hobby 192.168.68.232 sync-to-master tables 3306-- databases test-- tables T1

# just use an IP (SLAVE).

REPLACE INTO `test`.`t1` (`id`, `name`) VALUES ('54th,' ee') / * percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:24798 user:root host:host125*/

You can also let it execute the SQL statement that fixes the data on its own, but there is no output:

[root@host125 ~] # pt-table-sync-- execute-- sync-to-master hobby 192.168.68.232 sync-to-master tables 3306-- databases test-- tables T1

The data has been repaired:

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

| | 3 | cc |

| | 4 | dd |

| | 5 | ee |

+-+ +

5 rows in set (0.00 sec)

It is recommended to print it out with-- print, so that you can know that there is something wrong with the data and can intervene artificially. Otherwise, it will be carried out directly, and it will be even more difficult to deal with when problems arise. In short, the backup of the data should be done before processing.

Note: if there is no unique index in the table or the primary key will report an error:

Can't make changes on the master because no unique index exists at / usr/local/bin/pt-table-sync line 10591.

Add:

If there is data in the slave database, but not in the master database, how to deal with the data? SQL statements are given to delete excess data in SLAVE and to fix missing data in SLAVE.

If you do not want to display the input password in the shell window, you can add:-- ask-pass parameter, such as:

[root@host125] # pt-table-sync-- print-- ask-pass-- sync-to-master hobby 192.168.68.232 copyright 3306-databases test-- tables T1

Enter password for 192.168.68.232:

If you use-- ask-pass, an error is reported:

Cannot read response; is Term::ReadKey installed? Can't locate Term/ReadKey.pm in @ INC

Install the Term/ReadKey.pm module:

[root@host125 ~] # perl-MCPAN-e "shell"

Cpan [1] > install Term::ReadKey

Summary:

The tool performs checklist actions, checking connected accounts requires high permissions, and general permissions need to add SELECT, PROCESS, SUPER, REPLICATION SLAVE and other permissions. Pt-table-checksm is used with pt-table-sync, and be sure to check the pt-table-checksm command before performing pt-table-sync data synchronization.

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.