Install and use ES0en ES1en to assist with the basic MySQL tutorial

1. percona - toolkit profile
percona-toolkit is a collection of 1 high-level command line tools used to perform a variety of mysql and system tasks that are very complex and cumbersome to perform manually, including:

Check the 1 tropism of master and slave data File records effectively Find duplicate indexes Summarize server information Analyze queries from logs and tcpdump Collect important system information when a system goes wrong

percona-toolkit is derived from the Maatkit and Aspersa tools, which are the most famous tools for managing mysql. Now the Maatkit tool is no longer maintained, so please use ES18en-ES19en instead! These tools mainly include six categories: development, performance, configuration, monitoring, replication, system and utility. As an excellent DBA, some of them are very useful. If you can master and flexibly apply them, your work efficiency will be greatly improved.

2. percona-toolkit kit installation
1. Software package download
Visit http: / / www. percona. com software/percona toolkit/download the latest version of Percona Toolkit or by the following command line to get the latest version:

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

I chose to download the latest version directly from the website:

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

From http: / / pkgs. repoforge. org/perl - TermReadKey/download the latest TermReadKey package

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 64 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: You will need to install the Term::ReadKey package, otherwise you will report perl(Term::ReadKey) > = 2.10 is by ES77en-ES78en-2.1.1-1.noarch error
B. percona-toolkit compilation and 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

3. Common functions
1. pt-duplicate-key-checker
Function Introduction:
To find duplicate indexes and foreign keys from the mysql table, this tool lists both duplicate indexes and foreign keys and generates a statement to remove duplicate indexes, which is very convenient
Usage Introduction:
pt-duplicate-key-checker [OPTION...] [DSN]
There are a lot of options, you can use the command ES107en-ES108en-ES109en-ES110en --help to see which options are supported, I won't list 11 here. DNS is a database or table.
Use examples:
To view duplicate indexes and foreign key usage for 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
Function Introduction:
Function for the alter operational changes don't lock the table table structure, that is to say, when performing alter does not block to write and read operations, pay attention to the implementation of the tool must be prepared to back up, when operating under the best read in detail before 1 official documentation http: / / www percona. com/doc/percona - toolkit / 2.1 / pt online - schema - change. html.
It works by creating an empty table structure similar to table 1 that you want to perform alter. Perform the table structure modification, and then move the original table from copy to the modified table. When the data copy is completed, the original table will be removed and replaced by a new table. During the process of the copy data, any update operation in the original table will be updated to the new table, because the tool will create a trigger on the original table, which will update the contents of the original table to the new table. This tool will not work if triggers are defined in the table.
Usage Introduction:
pt-online-schema-change [OPTIONS] DSN
options allows you to view help and DNS for the database and table you want to manipulate.
Here are two parameters to be introduced 1:
The parameter ES153en-ES154en does not create triggers, does not copy data, and does not replace the original table. Just create and change the new table.
The function of the execute parameter, as described in the previous working principle, is to create a trigger to ensure that the most recently changed data will affect the new table. Note: If you do not add this parameter, the tool will exit after performing some checks. The purpose of this step is to get a good understanding of how the tool works and to read the official documentation.
Use examples:
The engine for changing tables online, which is especially useful when organizing innodb tables, is shown below:

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

You can see how it works in the log below:

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 the following example, the statement of adding a field in a large table is 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 the query 1 some rules analysis, puts forward Suggestions for possible problems, you can read these rules on 1 website links: http: / / www percona. com/doc/percona - toolkit / 2.1 / pt - query - advisor. html, not detailed here. Those queries can come from slow query files, general log files, or intercepted queries using pt-ES188en-digest. In the current version, there is bug, which can take a long time or even go through an endless loop when log files are very large.
Usage Introduction:

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

Use examples:
Analyze an example of a statement:

pt-query-advisor --query "select * from aaa"

Analyze an example of a query in general log:

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

0

Analyze an example of a query statement in a slow query:

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

1

4. pt-show-grants
Function Introduction:
Normalize and print mysql permissions, making you more efficient at copying, comparing mysql permissions, and doing version control!
Usage Introduction:
pt-show-grants [OPTION...] [DSN]
See help for the options, and DSN for the help options, which are case sensitive.
Use examples:
View all user permissions for the specified mysql:

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

2

View the permissions to execute the database:

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

View the statement for each user permission to generate revoke to revoke permissions:

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

4

5. pt-upgrade
Function Introduction:
Execute the query on multiple servers and compare what's different! This is very useful when upgrading a server. You can install and take the derivative to the new server, and then use this tool to run sql to see what's different, to find out the differences between the different versions.
Usage Introduction:
pt-upgrade [OPTION...] DSN [DSN...] [FILE]
Compare the results executed on two hosts for every 1 query in the file, and check for results, errors, and warnings executed on each server.
Use examples:
Just look at an example of an sql running on two servers:

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

5

See an example of sql running on two servers in the file:

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

6

See an example of the results of running SQL in a slow query on two servers:

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

7

You can also perform the compare type, which consists of three query_times,results, and warnings, such as the following example, which 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