Run the percona-toolkit command to operate MySQL.

This article mainly introduces the summary of practical commands for using percona-toolkit to operate MySQL. percona-toolkit is a powerful MySQL auxiliary tool. For more information, see 1.pt-archiver
Function introduction:
Archive the records of tables in the mysql database to another table or file
Usage:
Pt-archiver [OPTION...] -- source DSN -- where WHERE
This tool only archives old data and does not significantly affect OLTP query of online data. you can insert the data into other tables on another server or write it into a file, it is convenient to use the load data infile command to import data. You can also use it to perform the delete operation. By default, this tool deletes data from the source. Please note when using it.
Example:
Example 1: Transfer the oss_log table id less than 100000 of the sanmao database on 192.168.3.135 to the sanmao database on 192.168.3.92 and archive it to the oss_log_archive_20120605.log file:

pt-archiver --source h=192.168.3.135,D=sanmao,t=oss_log --user=root --password=zhang@123 --dest

h=192.168.3.92,D=sanmao,t=oss_log --file '/var/log/oss_log_archive_20120605.log' --where "id<=100000" --commit-each

Example 2: archive the oss_log less than 160000 of the sanmao database on 192.168.3.135 to the oss_log_archive_20120607.log file:

pt-archiver --source h=192.168.3.135,D=sanmao,t=oss_log --user=root --password=zhang@123 --file '/var/log/oss_log_archive_20120607.log' --where "id<=160000" --commit-each

Example 3: delete records with IDs less than 167050 in the oss_log table of the sanmao database on 192.168.3.135:

pt-archiver --source h=192.168.3.135,D=sanmao,t=oss_log --user=root --password=zhang@123 --purge --where 'id<=167050'

Note: If the character set is utf8, add default-character-set = utf8 under [client] in my. cnf. Otherwise, the exported file contains garbled characters.

2.pt-find
Function introduction:
Query the mysql table and run the Specified Command, which is similar to the find command of gnu.
Usage:
Pt-find [OPTION...] [DATABASE...]
The default action is to print the database name and table name.
Example:
Example 1: Search for the InnoDB table created one day before 192.168.3.135 and print it.

pt-find --ctime +1 --host=192.168.3.135 --engine InnoDB --user=root --password=zhang@123

Example 2: find the table whose name matches % hostsops % and whose engine is MYISAM in 192.168.3.135 and change the engine of the table to InnoDB.

pt-find --mtime +1 --dblike hostsops --engine MyISAM --host=192.168.3.135 --user=root --password=zhang@123 --exec "ALTER TABLE %D.%N ENGINE=InnoDB"

Example 3: Search for the empty tables in the aaa and zhang databases in 192.168.3.135 and delete them.

pt-find --empty aaa zhang --host=192.168.3.135 --user=root --password=zhang@123 --exec-plus "DROP TABLE %s"

Example 4: Search for tables with more than MB in 192.168.3.135:

pt-find --tablesize +100M --host=192.168.3.135 --user=root --password=zhang@123

3.pt-kill
Function introduction:
Kill the mysql statement that meets the specified conditions
Usage:
Pt-kill [OPTIONS]
If no specified file is added, pt-kill connects to mysql and finds the specified statement through show processlist. otherwise, pt-kill reads the mysql statement from the file containing the show processlist result.
Example:
Example 1: Search for statements with the server running time of 192.168.3.135 exceeding 60 s and print

pt-kill --busy-time 60 --print --host=192.168.3.135 --user=root --password=zhang@123

Example 2: Search for statements with the server 192.168.3.135 running time exceeding 60 s and kill

pt-kill --busy-time 60 --kill --host=192.168.3.135 --user=root --password=zhang@123

Example 3: Search for statements whose execution time exceeds 60 s from the proccesslist file

mysql -uroot -pzhang@123 -h192.168.3.135 -e "show processlist" > processlist.txtpt-kill --test-matching processlist.txt --busy-time 60 --print

4.pt-config-diff
Function introduction:
Compare mysql configuration files and server parameters
Usage:
Pt-config-diff [OPTION...] CONFIG [CONFIG...]
CONFIG can be a file or a data source name. at least two configuration file sources must be specified, just like the diff command under unix. if the configuration is exactly the same, nothing will be output.
Example:
Example 1: view the differences between the local and remote server configuration files:

pt-config-diff h=localhost h=192.168.3.92 --user=root --password=zhang@123

The comparison content is as follows:

22 config differencesVariable         localhost.localdomain localhost.localdomain========================= ===================== =====================binlog_cache_size     8388608        2097152have_ndbcluster      DISABLED       NOinnodb_additional_mem_... 16777216       33554432innodb_buffer_pool_size  1677721600      1073741824

Example 2: compare the differences between local configuration files and remote servers:

pt-config-diff /etc/my.cnf h=192.168.3.92 --user=root --password=zhang@123

The comparison content is as follows:

12 config differencesVariable         /etc/my.cnf localhost.localdomain========================= =========== =====================binlog_cache_size     8388608   2097152binlog_format       mixed    MIXED

Example 3: compare the differences between two local configuration files:

pt-config-diff /usr/local/mysql/share/mysql/my-large.cnf /usr/local/mysql/share/mysql/my-medium.cnf

5.pt-mysql-summary
Function introduction:
The configuration of mysql and the sataus information are summarized in detail. After the summary, you can see it at a glance.
Usage:
Pt-mysql-summary [OPTIONS] [-- mysql options]
Working Principle: Connect to mysql and query the status and configuration information and save it to the temporary directory. Then, use awk and other script tools for formatting. OPTIONS can be found on the official website.
Example:
Example 1: summarize the status and configuration information of the local mysql server:

pt-mysql-summary -- --user=root --password=zhang@123 --host=localhost

Example 2: summarize the status and configuration information of the local mysql server 192.168.3.92:

pt-mysql-summary -- --user=root --password=zhang@123 --host=192.168.3.92

6.pt-variable-advisor
Function introduction:
Analyze mysql parameter variables and provide suggestions for possible problems
Usage:
Pt-variable-advisor [OPTION...] [DSN]
Principle: check the configuration error settings and values in show variables according to predefined rules.
Example:
Example 1: Get the variable value from localhost

pt-variable-advisor --user=root --password=zhang@123 localhost

Example 2: Read the configuration from the specified file. the format is required.

pt-variable-advisor --user=root --password=zhang@123 --source-of-variables my.cnf

The above is a summary of the practical commands for using percona-toolkit to operate MySQL _ MySQL. For more information, see The PHP Chinese website (www.php1.cn )!