Practical commands for using Percona-toolkit to manipulate Mysql summary _mysql

1.pt-archiver
Function Introduction:
Archive records from tables in a MySQL database to another table or file
Usage Introduction:
Pt-archiver [OPTION ...]--source DSN--where where
This tool simply archives old data and does not have much impact on OLTP queries on online data, you can insert data into other tables in another server, or write to a file to facilitate importing data using the Load Data infile command. Alternatively, you can use it to perform a delete operation. This tool deletes the data from the source by default. Please note when using.
Use examples:
Example 1: Transfer a record of the Sanmao library on the 192.168.3.135 to the Sanmao library on the 192.168.3.92 and file the Oss_log table ID less than 100000:

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 Sanmao Library's oss_log less than 160000 on the 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 a record with an ID less than 167050 in the Oss_log table of the Sanmao library 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, you need to add Default-character-set = UTF8 in the my.cnf [client], otherwise the exported file contents will be garbled in Chinese.

2.pt-find
Function Introduction:
Look for the MySQL table and execute the specified command, similar to the GNU Find command.
Usage Introduction:
Pt-find [OPTION ...] [DATABASE ...]
The default action is to print the database name and table name
Use examples:
Example 1: Find the InnoDB table that was created 1 days ago in 192.168.3.135 and print.

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

Example 2: Find the database name that was changed 1 days ago in 192.168.3.135 match%hostsops% and engine MyISAM tables and change the table's engine to the InnoDB engine.

Pt-find--mtime +1--dblike hostsops--engine MyISAM--host=192.168.3.135--user=root--password=zhang@123--exec "ALTER T Able%d.%n Engine=innodb "

Example 3: Find the empty table in AAA and Zhang Library in 192.168.3.135 and delete it.

Pt-find--empty AAA Zhang--host=192.168.3.135--user=root--password=zhang@123--exec-plus "DROP TABLE%s"

Example 4: Find more than 100M tables in the 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 matches the specified conditions
Usage Introduction:
Pt-kill [OPTIONS]
Add no specified file Pt-kill connects to MySQL and finds the specified statement via show processlist, whereas Pt-kill reads the MySQL statement from a file containing show processlist results
Use examples:
Example 1: Find a statement that the 192.168.3.135 server runs longer than 60s and print

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

Example 2: Find a statement that the 192.168.3.135 server is running longer than 60s and kill

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

Example 3: Find statements that execute longer than 60s from the Proccesslist file

Mysql-uroot-pzhang@123-h192.168.3.135-e "Show processlist" > Processlist.txt
pt-kill--test-matching Processlist.txt--busy-time--print 

4.pt-config-diff
Function Introduction:
Compare MySQL configuration files and server parameters
Usage Introduction:
Pt-config-diff [OPTION ...] Config config [config ...]
Config can be either a file or a data source name, and you must specify at least two profile sources, just like the diff command under UNIX, which will not output anything if the configuration is exactly the same.
Use examples:
Example 1: View configuration file differences for local and remote servers:

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

The comparison comes with the following contents:

Config differences
Variable         localhost.localdomain localhost.localdomain
========================= = ==================== =====================
binlog_cache_size     8388608        2097152
have_ndbcluster      DISABLED       NO
innodb_additional_mem_ ... 16777216       33554432
innodb_buffer_pool_size  1677721600      1073741824

Example 2: Compare the differences between a local profile and a remote server:

PT-CONFIG-DIFF/ETC/MY.CNF h=192.168.3.92--user=root--password=zhang@123

The comparison comes with the following contents:

Config differences
Variable         /etc/my.cnf localhost.localdomain
========================= =========== =====================
binlog_cache_size     8388608   2097152
binlog_format    Mixed

Example 3: Compare the differences between local two profiles:

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:
A detailed summary of MySQL configuration and Sataus information, after the summary you can see directly.
Usage Introduction:
pt-mysql-summary [Options] [--MySQL options]
How it works: When you connect to MySQL, the status and configuration information is saved to a temporary directory, and then formatted with awk and other scripting tools. Options can refer to the official website of the relevant pages.
Use examples:
Example 1: Summarizing status and configuration information for a local MySQL server:

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

Example 2: Summarizing status and configuration information for 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 the parameter variables of MySQL and make suggestions for possible problems.
Usage Introduction:
Pt-variable-advisor [OPTION ...] [DSN]
Rationale: Check the settings and values for configuration errors in show variables according to predefined rules.
Use examples:
Example 1: Getting variable values from localhost

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

Example 2: Read the configuration from the specified file, which has a format requirement