Basic tutorials for installing and using Percona-toolkit to assist in operating Mysql _mysql

Introduction of Percona-toolkit
Percona-toolkit is a collection of advanced command-line tools for performing various MySQL and system tasks that are complex and cumbersome to perform by hand, including:

• Check the consistency of master and slave data
• Effectively archive records
• Look for Duplicate indexes
• Summarizing Server information
• Analyze queries from logs and tcpdump
• Collect important system information when something goes wrong with the system

Percona-toolkit from the Maatkit and Aspersa tools, the two tools are the most famous tool for managing MySQL, now Maatkit tools are not maintained, please use the Percona-toolkit bar! These tools mainly include development, performance, configuration, monitoring, replication, System, practical six categories, as a good DBA, which tools are very useful, if you can master and flexible application, will greatly improve the efficiency.

Second, Percona-toolkit tool pack installation
1. Package download
Access http://www.percona.com/software/percona-toolkit/Download the latest version of Percona Toolkit or get the latest version from the following command line:

wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm

Here I choose to find the latest version directly from the website download:

wget http://www.percona.com/redir/downloads/percona-toolkit/2.1.1/percona-toolkit-2.1.1-1.noarch.rpm
wget Http://www.percona.com/redir/downloads/percona-toolkit/2.1.1/percona-toolkit-2.1.1.tar.gz

Download the latest Termreadkey package from http://pkgs.repoforge.org/perl-TermReadKey/

wget http://pkgs.repoforge.org/perl-TermReadKey/perl-TermReadKey-2.30-1.el5.rf.x86_64.rpm

2. Package Installation
my environment is CentOS 5.5 BIT
A. Percona-toolkit rpm installation Method

RPM-IVH perl-termreadkey-2.30-1.el5.rf.x86_64.rpm
RPM-IVH percona-toolkit-2.1.1-1.noarch.rpm

Note: The Term::readkey package needs to be installed, otherwise the Perl (Term::readkey) >= 2.10 is needed by Percona-toolkit-2.1.1-1.noarch error will be reported
B. Percona-toolkit Compilation Installation Method
Tar xzvf percona-toolkit-2.1.1.tar.gz
CD percona-toolkit-2.1.1
Perl makefile.pl
Make
Make Test
Make install

Three, commonly used functions
1. Pt-duplicate-key-checker
Function Introduction:
feature to find duplicate indexes and foreign keys from MySQL tables, this tool lists duplicate indexes and foreign keys, and generates statements that delete duplicate indexes, which is convenient
Usage Introduction:
Pt-duplicate-key-checker [OPTION ...] [DSN]
Contains a number of options, specific to the command Pt-duplicate-key-checker--help to see the specific support of those options, I will not be listed here. DNS is a database or table.
Use examples:
View duplicate indexes and foreign key usage of the test database use the following command

Pt-duplicate-key-checker--host=localhost--user=root--password=zhang@123--databases=test

       
2. Pt-online-schema-change
Feature Introduction:
The function is to not lock the table when the alter operation changes the table structure, which means that the write and read operations are not blocked when the alter is executed, and you must do the backup when executing the tool, and you should read the official document in detail before you do it http://www.percona.com/doc/ Percona-toolkit/2.1/pt-online-schema-change.html.
works by creating an empty table structure that is the same as the table you want to perform alter operations on. Modify the table structure, and then from the original table copy of the original data to the table structure modified table, when the data copy is completed will remove the original table, replace the original table with the new table, the default action is to drop the original table. In the process of copy data, any updates to the original table are updated to the new table, because the tool creates a trigger on the original table, and the trigger updates the content that is updated on the original table to the new table. If a trigger is already defined in the table, the tool will not work.
Usage Description:
Pt-online-schema-change [Options] DSN
Options can view the databases and tables you want to manipulate help,dns.
Here are two parameters to introduce:
--dry-run  This parameter does not create triggers, does not copy data, and does not replace the original table. Just create and change new tables.
--execute  The effect of this parameter, as described in the previous work, triggers are created to ensure that the most recent data changes affect the new table. Note: If you do not add this parameter, the tool exits after performing some checks. The move was made to use this to fully understand the rationale of the tool, while reading the official documentation.
Using the example:
to change the engine of the table online, this is especially useful when organizing innodb tables, as follows:

Pt-online-schema-change--user=root--password=zhang@123--host=localhost--lock-wait-time=120--alter= "ENGINE= InnoDB "D=test,t=oss_pvinfo2--execute

The following log shows how it should be performed:

Altering ' test '. ' Oss_pvinfo2 ' ...
Creating new Table ...
Created new Table Test._oss_pvinfo2_new OK.
Altering new Table ...
Altered ' test '. ' _oss_pvinfo2_new ' OK.
Creating Triggers
... Created triggers OK.
Copying approximately 995696 rows ...
Copied rows OK.
Swapping tables ...
Swapped original and new tables OK.
Dropping old table ...
Dropped old table ' test '. ' _oss_pvinfo2_old ' OK.
Dropping triggers
... Dropped triggers OK.
Successfully altered ' test '. ' Oss_pvinfo2 '.

In an example, a large table adds a field, and the statement reads as follows:

Pt-online-schema-change--user=root--password=zhang@123--host=localhost--lock-wait-time=120--alter= "ADD COLUMN domain_id INT "D=test,t=oss_pvinfo2--execute

3. Pt-query-advisor
Function Introduction:
According to some rules to analyze the query statements, to make recommendations on possible issues, these rules of evaluation you can look at the official website link: http://www.percona.com/doc/percona-toolkit/2.1/pt-query-advisor.html, here is not a detailed list. Those query statements can come from slow query files, general log files, or query statements intercepted using Pt-query-digest. The current version of the bug, the log file is very large when it will take a long time or even into the dead loop.
Usage Introduction:

Pt-query-advisor/path/to/slow-query.log
pt-query-advisor--type genlog mysql.log
pt-query-digest Tcpdump.txt--print--no-report | Pt-query-advisor

Use examples:
Analyze an example of a statement:

Pt-query-advisor--query "SELECT * from AAA"

Example of an analysis of query statements in general log:

Pt-query-advisor/data/dbdata/general.log

Example of parsing a query statement in a slow query:

Pt-query-advisor/data/dbdata/localhost-slow.log

4. Pt-show-grants
Function Introduction:
Standardize and print MySQL permissions so that you are more efficient at copying, comparing MySQL permissions, and versioning!
Usage Introduction:
pt-show-grants [OPTION ...] [DSN]
option to view with help, and the DSN option to see Help, the options are case-sensitive.
Use examples:
To view all user rights for the specified MySQL:

Pt-show-grants--host= ' localhost '--user= ' root '--password= ' zhang@123 '

To view permissions to execute a database:

Pt-show-grants--host= ' localhost '--user= ' root '--password= ' zhang@123 '--database= ' hostsops '

To view a statement that generates revoke permissions for each user right:

Pt-show-grants--host= ' localhost '--user= ' root '--password= ' zhang@123 '--revoke

5. Pt-upgrade
Function Introduction:
Execute the query on multiple servers and compare what's different! This is useful when upgrading the server, you can install and guide the data to the new server, and then use the tool to run the SQL to see what the difference is, you can find out the differences between the different versions.
Usage Introduction:
Pt-upgrade [OPTION ...] DSN [DSN ...] [FILE]
Compares the results of each query statement on two hosts in the file and examines the results, errors, and warnings executed on each server.
Use examples:
View only a sample of the results of a SQL Server running on two servers:

Pt-upgrade h= ' localhost ' h=192.168.3.92--user=root--password=zhang@123--query= ' select * from User_data.collect_data Limit 5 "

View a sample of the running results of SQL on two servers in a file:

Pt-upgrade h= ' localhost ' h=192.168.3.92--user=root--password=zhang@123

View the corresponding query SQL in a slow query example of running results on two servers:

Pt-upgrade h= ' localhost ' h=192.168.3.92--user=root--password=zhang@123

You can also perform the type of compare, consisting primarily of three query_times,results,warnings, such as the following example, that only compares the execution time of SQL

Pt-upgrade h=192.168.3.91 h=192.168.3.92--user=root--password=zhang@123--query= "SELECT * from User_data.collect_ Data "--compare Query_times