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

This article mainly introduces how to use percona-toolkit, the introduction in the article is very detailed, has a certain reference value, interested friends must read!

Installation method:

Edit / etc/yum.repos.d/Percona.repo and add the following:

# vi / etc/yum.repos.d/Percona.repo

[percona]

Name = CentOS $releasever-Percona

Baseurl= http://repo.percona.com/centos/$releasever/os/$basearch/

Enabled = 1

Gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-perconapt-query-digest

Gpgcheck = 0

Then you can install it with yum.

# yum-y install percona-toolkit

Summary of main tool uses:

Pt-upgrade

This command is mainly used to compare the differences in SQL execution under different mysql versions, and is usually used to compare before upgrading.

Results, errors, warnings, and so on, are generated from SQL files or individual SQL statements executed on each server.

Pt-online-schema-change

The function is to support online changes to the table structure, and does not lock the original table, does not block the DML operation of the original table.

Pt-summary

Summarize the status and configuration information of the server, run a large number of commands to check the system status and configuration, save the output to a temporary directory file, and then execute Unix commands on these results to elegantly format.

Pt-mysql-summary

Summarize the configuration and sataus information of mysql, and generate a detailed configuration and information for the connected mysql server.

Pt-mext

View the information of multiple samples of SHOW GLOBAL STATUS in parallel.

Pt-mext will execute the COMMAND you specified, read the results one line at a time, save the contents of the blank lines to a temporary file, and finally combine these temporary files to view the results in parallel.

Pt-kill

Kill drop mysql statement that meets the specified condition

Pt-query-advisor

Analyze the sql or the whole sql in the log record and make suggestions

Pt-show-grants

Compare mysql permissions and version control

Pt-ioprofile

The principle of pt-ioprofile is the IO analysis of a pid with a strace process attached to it.

Pt-fingerprint

Used to generate query fingerprints. It is through this tool that the main sql query will be generated into ID in queryID,pt-query-digest.

Similar to SQL_ID in Oracle, involving binding variables, literals, etc.

Pt-find

Use and find the mysql table and execute the specified command, similar to the find command

Pt-fifo-split

Simulate the cutting file and pass it through the pipe to the first-in, first-out queue without the actual cutting file

Pt-fk-error-logger

Log the foreign key related error information to the log or table, and extract and save the recent foreign key error information in the mysql database through SHOW INNODB STATUS. You can print error messages directly through parameter control or store them in a table in the database.

Pt-deadlock-logger

It is used to monitor the deadlock on the mysql server and output it to the log file. The log contains details such as the time when the deadlock occurred, the deadlock thread id, the transaction id of the deadlock, the transaction execution time when the deadlock occurred, and so on. Collect and save the most recent deadlock information on mysql

Deadlock information can be directly printed and stored in the database. Deadlock information includes the server where the deadlock occurred, the time when the deadlock occurred, the deadlock thread id, the transaction id of the deadlock, how long the transaction executed when the deadlock occurred, and so on.

For cases where deadlocks occur frequently, it is recommended to open for a period of time to collect information.

Pt-archiver

Archive the records of a table in the mysql database to another table or file

The tool only archives old data and has little impact on the OLTP query of online data.

Data can be inserted into other tables on another server or written to a file, making it easy to import data using the load data infile command.

Pt-agent

A client Agent tool based on Percona Cloud

Pt-visual-explain

Output for formatting explain

Pt-variable-advisor

It is used to analyze some problems that may exist in mysql system variables, and can be used to evaluate whether the relevant parameters are set correctly or not.

Pt-stalk

It is used to collect the relevant information of mysql database failure to facilitate subsequent diagnosis and processing.

Pt-slave-delay

Used to set the interval at which the slave server lags behind the master server.

The command line sets the slave to lag behind the master by starting and stopping the replication sql thread.

Pt-sift

Used to browse files generated by pt-stalk.

Pt-show-grants

All the user permissions of the current instance are output, which can be used to rebuild users during database migration.

Pt-query-digest

Used to analyze the slow query log of the mysql server and format the output for easy viewing and analysis.

Pt-pmp

Perform an aggregated GDB stack trace for the query program, an advanced stack trace, and then summarize the trace information.

Pt-index-usage

Read the query statement from the log file and use to analyze how the current index is used.

After the analysis is completed, a report is generated that the index has not been used by the query, which can be used to analyze the report to consider eliminating useless indexes.

Pt-heartbeat

Used to monitor the latency of the mysql replication architecture.

Mainly through the update thread on the main database to continuously update a timestamp on the specified table, from the library-monitor thread or-check thread to check the timestamp of the update of the main database and compare it with the current system time to get the delay value.

Pt-duplicate-key-checker

Find duplicate indexes and foreign keys from the mysql table. This tool lists duplicate indexes and foreign keys.

Pt-diskstats

Similar to iostat, print disk io statistics, but this tool is interactive and more detailed than iostat. You can analyze data collected from remote machines.

Pt-config-diff

Used to compare mysql profiles and server variables on multiple servers

At least 2 configuration sources need to be specified and can be used to compare configuration files before and after migration or upgrade

Pt-align

This tool is used to format the output of the file format

Pt-slave-find

Connect to the mysql master server and find all its slaves, and then print out the hierarchical relationships of all the slave servers.

Pt-slave-restart

Monitor one or more mysql replication errors and try to restart replication when the slave stops.

Pt-table-checksum

Used to verify the consistency of mysql replication.

The main purpose of this tool is to find data differences efficiently, and if there are differences, it can be solved through pt-table-sync.

Pt-table-sync

Through the data obtained by pt-table-checksum, list or directly execute the modified replicated differential data to resynchronize it.

The above is all the contents of this article "how to use percona-toolkit". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.