Of Stack

Summary of the practical commands used to operate MySQL using ES0en ES1en

  • 2020-12-07 04:30:00
  • OfStack

1.pt-archiver
Function Introduction:
Archive the records from the table in the mysql database to another table or file
Usage Introduction:
pt-archiver [OPTION...] --source DSN --where WHERE
This tool is only for archiving old data and will not affect the OLTP query for online data. You can insert data into other tables on another server or write it to a file for easy import using the load data infile command. You can also use it to perform the delete operation. By default, this tool deletes data from the source. Please pay attention when using.
Use examples:
Example 1: Transfer oss_log table id less than 100,000 from sanmao library on 192.168.3.135 to sanmao library on 192.168.3.92 and archive to es21EN_ES22en_ES23en_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: File the record of oss_log less than 160000 in sanmao library on 192.168.3.135 into es33EN_ES34en_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 oss_log table from sanmao library on 192.168.3.135 if id is less than 167050: 


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 ES53en-ES54en-ES55en = utf8 under [client] in ES50en.cnf, otherwise the content of the exported file will be confused.

2.pt-find
Function Introduction:
Finds the mysql table and executes the specified command, similar to gnu's 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 and print the table of InnoDB created 1 day ago in 192.168.3.135. 


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

Example 2: Find the table in 192.168.3.135 with the database name changed 1 day ago matching %hostsops% and engine MYISAM, and change the engine of the table to InnoDB engine. 


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: Find the empty tables in the aaa and zhang libraries 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: Find tables over 100M 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 drop matches the specified condition mysql statement
Usage Introduction:
pt-kill [OPTIONS]
Add pt-ES105en to mysql without a specified file and find the specified statement via SHOW PROCESSLIST, whereas ES109en-ES110en reads mysql statement from the file containing the result of SHOW PROCESSLIST
Use examples:
Example 1: Find the statement with server 192.168.3.135 running time exceeding 60s and print it 


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

Example 2: Find the statement of 192.168.3.135 server running time exceeding 60s and kill 


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

Example 3: Find statements in the proccesslist file that take longer than 60s 


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

4.pt-config-diff
Function Introduction:
Compare the mysql configuration file with the server parameters
Usage Introduction:
pt-config-diff [OPTION...] CONFIG CONFIG [CONFIG...]
CONFIG can be either a file or a data source name, and at least two configuration file sources must be specified, as shown in the diff command 1 under unix. If the configuration is completely 1, nothing is output.
Use examples:
Example 1: View configuration file differences between local and remote servers: 


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

The comparison is as follows: 


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


Example 2: Compare the differences between local profiles and remote servers: 


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


The comparison is as follows: 


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


Example 3: Compare the differences between two local profiles: 


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


5.pt-mysql-summary
Function Introduction:
The mysql configuration and sataus information are carefully summarized so that you can see it directly in one eye.
Usage Introduction:
pt-mysql-summary [OPTIONS] [-- MYSQL OPTIONS]
How it works: After connecting to mysql, query status and save the configuration information to a temporary directory, and then format it with awk and other scripting tools. OPTIONS can be found on its website.
Use examples:
Example 1: Summarize status and configuration information for the local mysql server: 


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

Example 2: Summarize status and configuration information for local mysql server 192.168.3.92: 


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

6.pt-variable-advisor
Function Introduction:
The parameter variables of mysql are analyzed and suggestions for possible problems are proposed
Usage Introduction:
pt-variable-advisor [OPTION...] [DSN]
Principle: Checks for misconfigured Settings and values in show variables against predefined rules.
Use examples:
Example 1: Get the variable value from localhost 


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

Example 2: Reads the configuration from the specified file, which requires formatting 


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

Related articles: