percona-toolkit系列-pt-find

pt-find - Find MySQL tables and execute actions, like GNU find.

用法：
pt-find [OPTION...] [DATABASE...]

例子：

找出创建于一天之前，并且是myisam存储引擎的表
[root@goolen ~]# pt-find --ctime +1 --engine MyISAM -uroot -proot
`mysql`.`db`
`mysql`.`event`
`mysql`.`func`
`mysql`.`help_category`
`mysql`.`help_keyword`
`mysql`.`help_relation`
`mysql`.`help_topic`
`mysql`.`host`
`mysql`.`ndb_binlog_index`
`mysql`.`plugin`
`mysql`.`proc`
`mysql`.`procs_priv`
`mysql`.`proxies_priv`
`mysql`.`servers`
`mysql`.`tables_priv`
`mysql`.`time_zone`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone_name`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_transition_type`
`mysql`.`user`

找出goolen库中的innodb引擎的表，并且把他们转换为myisam表：
mysql> select table_name,engine from information_schema.tables where table_schema='goolen';
+-----------------+--------+
| table_name      | engine |
+-----------------+--------+
| a               | InnoDB |
| b               | InnoDB |
| c               | InnoDB |
| g1              | InnoDB |
| topic_indicator | InnoDB |
+-----------------+--------+
[root@goolen ~]# pt-find --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM" -uroot -proot goolen

mysql> select table_name,engine from information_schema.tables where table_schema='goolen';
+-----------------+--------+
| table_name      | engine |
+-----------------+--------+
| a               | MyISAM |
| b               | MyISAM |
| c               | MyISAM |
| g1              | MyISAM |
| topic_indicator | MyISAM |
+-----------------+--------+

找出goolen库中的空表，然后删除：
mysql> use goolen;
Database changed
mysql> show tables;
+------------------+
| Tables_in_goolen |
+------------------+
| a                |
| b                |
| c                |
| g1               |
| topic_indicator  |
+------------------+
5 rows in set (0.00 sec)
[root@goolen ~]# pt-find --empty goolen --exec-plus "DROP TABLE %s"       

mysql> show tables;
+------------------+
| Tables_in_goolen |
+------------------+
| g1               |
+------------------+
1 row in set (0.00 sec)

找出goolen库中size大于200M的表
[root@goolen goolen]# ll -h bigsize_table.*
-rw-rw---- 1 mysql mysql 9.4K Dec  2 16:52 bigsize_table.frm
-rw-rw---- 1 mysql mysql 420M Dec  2 16:54 bigsize_table.ibd

[root@goolen ~]# pt-find --tablesize +200M -uroot -proot goolen
`goolen`.`bigsize_table`

Find all tables and print their total data and index size, and sort largest tables first (sort is a different program, by the way).
列出所有的表，包括表的总行数和索引的size，并按总数倒序排序（排序操作有系统命令sort完成，pt-find本身没排序功能）
[root@goolen ~]# pt-find --printf "%T\t%D.%N\n" -uroot -proot | sort -rn
425639936  `goolen`.`bigsize_table`
461592  `mysql`.`help_topic`
108816  `mysql`.`help_keyword`
32768   `test`.`goolen3`
32768   `test`.`goolen2`
27675   `mysql`.`help_relation`
25150   `mysql`.`help_category`
16384   `test`.`t1`
16384   `test`.`goolen`
6880    `mysql`.`db`
6506    `mysql`.`proxies_priv`
。。。
。。。

As above, but this time, insert the data back into the database for posterity:
列出所有的表，把输出信息保存到goolen库里的tblsize表里：
mysql> create table tblsize(db varchar(20),tbl varchar(35) ,size int);  
Query OK, 0 rows affected (0.12 sec)

[root@goolen ~]# pt-find --noquote --exec "INSERT INTO goolen.tblsize(db, tbl, size) VALUES('%D', '%N', %T)" -uroot -proot

mysql> select * from tblsize;
+--------------------+-------------------------------------+-----------+
| db                 | tbl                                 | size      |
+--------------------+-------------------------------------+-----------+
| goolen             | bigsize_table                       | 425639936 |
| goolen             | g1                                  |      2108 |
| goolen             | tblsize                             |     16384 |
| mysql              | columns_priv                        |      4096 |
| mysql              | db                                  |      6880 |
| mysql              | event                               |      2048 |
| mysql              | func                                |      1024 |
| mysql              | general_log                         |         0 |
+--------------------+-------------------------------------+-----------+

参数说明：
--ask-pass
Prompt for a password when connecting to MySQL.
连接的时候提示输出密码

例：
[root@goolen ~]#
[root@goolen ~]# pt-find --printf "%T\t%D.%N\n" -uroot --ask-pass goolen 
Enter password: 
425639936       `goolen`.`bigsize_table`
2108    `goolen`.`g1`
16384   `goolen`.`tblsize`

--case-insensitive
Specifies that all regular expression searches are case-insensitive.

例：
默认匹配区分大小写，innodb全部小写，没有输出匹配信息 
[root@goolen ~]# pt-find --engine innodb --printf "ALTER TABLE %D.%N ENGINE=MyISAM" -uroot -proot goolen      
指定忽略大小写：
[root@goolen ~]# pt-find --engine innodb --printf "ALTER TABLE %D.%N ENGINE=MyISAM" -uroot -proot goolen --case-insensitive
ALTER TABLE `goolen`.`bigsize_table` ENGINE=MyISAMALTER TABLE `goolen`.`tblsize` ENGINE=MyISAM

[root@goolen ~]# pt-find --engine InnoDB --printf "ALTER TABLE %D.%N ENGINE=MyISAM" -uroot -proot goolen        
ALTER TABLE `goolen`.`bigsize_table` ENGINE=MyISAMALTER TABLE `goolen`.`tblsize` ENGINE=MyISAM
[root@goolen ~]#

--help
Show help and exit.

--host
short form: -h; type: string
Connect to host.

--password
short form: -p; type: string
Password to use when connecting.

--pid
type: string

--port
short form: -P; type: int
Port number to use for connection.

--[no]quote
default: yes
Quotes MySQL identifier names with MySQL’s standard backtick character.
Quoting happens after tests are run, and before actions are run.

--set-vars
type: string; default: wait_timeout=10000
Set these MySQL variables. Immediately after connecting to MySQL, this string will be appended to SET and executed.

--socket
short form: -S; type: string
Socket file to use for connection.

--user
short form: -u; type: string
User for login if not current user.

--version
Show version and exit.

--createopts
type: string; group: Tests

Table create option matches pattern. This tests the Create_options column.

--ctime
type: size; group: Tests

Table was created n days ago. This tests the Create_time column.

--datafree
type: size; group: Tests

Table has n bytes of free space. This tests the Data_free column. The specified size can be “NULL” to test where Data_free IS NULL.

--datasize
type: size; group: Tests

Table data uses n bytes of space. This tests the Data_length column. The specified size can be “NULL” to test where Data_length IS NULL.

--dblike
type: string; group: Tests

Database name matches SQL LIKE pattern.

--dbregex
type: string; group: Tests

Database name matches this pattern.

--empty
group: Tests

Table has no rows. This tests the Rows column.

--engine
type: string; group: Tests

Table storage engine matches this pattern. This tests the Engine column, or in earlier versions of MySQL, the Type column.

--function
type: string; group: Tests

Function definition matches pattern.

--indexsize
type: size; group: Tests

Table indexes use n bytes of space. This tests the Index_length column. The specified size can be “NULL” to test where Index_length IS NULL.

--kmin
type: size; group: Tests

Table was checked n minutes ago. This tests the Check_time column.

--ktime
type: size; group: Tests

Table was checked n days ago. This tests the Check_time column.

--mmin
type: size; group: Tests

Table was last modified n minutes ago. This tests the Update_time column.

--mtime
type: size; group: Tests

Table was last modified n days ago. This tests the Update_time column.

--procedure
type: string; group: Tests

Procedure definition matches pattern.

--rowformat
type: string; group: Tests

Table row format matches pattern. This tests the Row_format column.

--rows
type: size; group: Tests

Table has n rows. This tests the Rows column. The specified size can be “NULL” to test where Rows IS NULL.

--server-id
type: string; group: Tests

--tablesize
type: size; group: Tests

Table uses n bytes of space. This tests the sum of the Data_length and Index_length columns.

--tbllike
type: string; group: Tests

Table name matches SQL LIKE pattern.

--tblregex
type: string; group: Tests

Table name matches this pattern.

--tblversion
type: size; group: Tests

Table version is n. This tests the Version column.

--trigger
type: string; group: Tests

Trigger action statement matches pattern.

--trigger-table
type: string; group: Tests

--trigger is defined on table matching pattern.

--view
type: string; group: Tests

CREATE VIEW matches this pattern.

--exec
type: string; group: Actions

Execute this SQL with each item found. The SQL can contain escapes and formatting directives (see --printf).

--exec-dsn
type: string; group: Actions

--exec-plus
type: string; group: Actions

You might use this, for example, to drop all the tables you found:

DROP TABLE %s
This is sort of like GNU find’s “-exec command {} +” syntax. Only it’s not totally cryptic. And it doesn’t require me to write a command-line parser.

--print
group: Actions

Print the database and table name, followed by a newline. This is the default action if no other action is specified.

--printf
type: string; group: Actions

Print format on the standard output, interpreting ‘’ escapes and ‘%’ directives. Escapes are backslashed characters, like n and t. 
Perl interprets these, so you can use any escapes Perl knows about. Directives are replaced by %s, and as of this writing, 
you can’t add any special formatting instructions, like field widths or alignment (though I’m musing over ways to do that).

Here is a list of the directives. Note that most of them simply come from columns of SHOW TABLE STATUS. If the column is NULL 
or doesn’t exist, you get an empty string in the output. A % character followed by any character not in the following list is discarded 
(but the other character is printed).

CHAR DATA SOURCE        NOTES
---- ------------------ ------------------------------------------
a    Auto_increment
A    Avg_row_length
c    Checksum
C    Create_time
D    Database           The database name in which the table lives
d    Data_length
E    Engine             In older versions of MySQL, this is Type
F    Data_free
f    Innodb_free        Parsed from the Comment field
I    Index_length
K    Check_time
L    Collation
M    Max_data_length
N    Name
O    Comment
P    Create_options
R    Row_format
S    Rows
T    Table_length       Data_length+Index_length
U    Update_time
V    Version
DSN OPTIONS

These DSN options are used to create a DSN. Each option is given like option=value. The options are case-sensitive, 
so P and p are not the same option. There cannot be whitespace before or after the = and if the value contains whitespace it must be quoted. 
DSN options are comma-separated. See the percona-toolkit manpage for full details.

A
dsn: charset; copy: yes
Default character set.

D
dsn: database; copy: yes
Default database.

F
dsn: mysql_read_default_file; copy: yes
Only read default options from the given file

h
dsn: host; copy: yes
Connect to host.

p
dsn: password; copy: yes
Password to use when connecting.

P
dsn: port; copy: yes
Port number to use for connection.

S
dsn: mysql_socket; copy: yes
Socket file to use for connection.

u
dsn: user; copy: yes
User for login if not current user.

ENVIRONMENT

The environment variable PTDEBUG enables verbose debugging output to STDERR. To enable debugging and capture all output to a file, run the tool like:

PTDEBUG=1 pt-find ... > FILE 2>&1
Be careful: debugging output is voluminous and can generate several megabytes of output.

SYSTEM REQUIREMENTS

You need Perl, DBI, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1354308/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1354308/