Use of runtime tools in Percona Toolkit series

Percona-toolkit tutorial tools

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 <= 100'

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 + 100 M -- 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.txt

Pt-kill -- test-matching processlist.txt -- busy-time 60 -- print

Percona Toolkit: click here
Percona Toolkit: click here