Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to install and use Percona Toolkit". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to install and use Percona Toolkit.

Percona Toolkit, referred to as pt tool, is a tool developed by Percona to manage MySQL. Its functions include checking the consistency of master-slave replicated data, checking duplicate indexes, locating table files occupied by IO, analyzing slow logs, online DDL and so on.

PT installation

Sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm yum list | grep percona-toolkit

After installing PT, let's introduce some practical PT tools and how to use them.

1. Pt-duplicate-key-checker

Description: this tool is mainly used to check the duplicate index of the table and give relevant suggestions.

Pt-duplicate-key-checker-h 127.0.0.1-u root-p 123456-d xuanyuan-t rs_hc_status

H: specify the host to check

U: the user name of the database

P: password of the database

D: name of the database to check

T: table to check

2. Pt-kill

Pt-kill is a simple and practical tool for killing mysql threads and queries, mainly to prevent some large / complex / long-time queries from taking up database and system resources and affecting online business.

The explanation is as follows:

(1) several matching examples are given to match the important parameters of the kill thread or session of processlist:

Follow the command command to kill the thread

-- match-command is separated by | between multiple command, otherwise it will become invalid.

Press state to kill the thread

Press the info keyword to kill the thread test by pressing info to kill the thread, note that the content of info must strictly match case, otherwise it will not be killed.

Check the thread according to the access source host/ip

-- ignore-host/--match-host

Check and kill threads according to DB

-- ignore-db/--match-db

By database user

-- ignore-user/--match-user

(2) behavior parameter Action:

Kill kills the connection and exits

Kill-query only kills the statements executed by the connection, but the thread is not terminated

Print prints statements that meet the conditions

(3) other important parameters

(4) the common uses are as follows:

Check every 10 seconds and kill the process that finds sleeping.

Pt-kill-match-command Sleep-victims all-interval 10-host=localhost-port=3306-user=root-password=*-kill-print-S

For more use, refer to the following figure:

3. Pt-mysql-summary

Query the profile of mysql.

Pt-mysql-summary-- host 127.0.0.1-- user root-- password 123456

Parameter description:

Host: specify host address

User: specifies the user name to connect to the database

Password: specify the password to connect to the database

Output content analysis:

Brief information: mainly includes mysql startup time, version, data directory, number of connections, master and slave information.

Processlist information

Mysql status related information

Table cache

InnoDB information

Variables parameter variable information

Configuration File

4. Pt-query-digest

Pt-query-digest is mainly used to analyze mysql slow logs.

5. Pt-table-checksum check and pt-table-sync repair data

Pt-table-checksum and pt-table-sync are usually used together, the former is mainly used for data verification to verify whether the master and slave are consistent, and the latter is mainly used to repair data, and the combination of the two can repair data inconsistencies.

Pt-table-checksum 's tool for verifying master-slave data consistency returns a non-zero result when an inconsistency is captured. But he can only look for data inconsistencies.

Pt-table-checksum is based on the statement copy format at run time, and if we are in ROW format, we need to add the-- no-check-binlog-format parameter to the execution.

(1) pt-table-checksum

We narrow down the scope of the inspection and examine a certain library separately:

[root@localhost bin] # pt-table-checksum-- nocheck-replication-filters-- replicate=rep_test.checksums-- no-check-binlog-format-- databases=xuanyuan hobby 192.168.1.181

Parameter description:

Output:

The results of the inspection show that:

(2) pt-table-sync

If it is detected that any data is inconsistent with the sender, it needs to be repaired with the help of pt-table-sync.

Restore inconsistent data:

After the repair is completed, the master-slave data will reach a consistent state.

(3) some use cases of pt-table-sync

At this point, I believe you have a deeper understanding of "how to install and use Percona Toolkit". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.