Mysql管理必备工具Maatkit详解之七（mk-find）
Mysql管理必备工具Maatkit详解之七（mk-find）
2009年05月21日 作者： 大头刚
mk-find - 找到指定表，并批量执行操作。安装方法可以参考
这里
。
如果你想批量的执行一系列命令，例如我想把TEST库所有的myisam表都Optimize下，以前你可能会使用这样的方法：
mysql -u sg -p'xxxx' -e "select concat('optimize table ',table_schema,'.',table_name,';')
into outfile '/u01/data/opti.sql' from information_schema.tables where ENGINE='MyISAM' and table_schema='TEST';"
cat /u01/data/opti.sql
optimize table test.test;
optimize table test.test2;
optimize table test.zzz;
mysql -u sg -p'xxxx'/u01/data/opti.sql
test.test optimize statusOK
Table Op Msg_type Msg_text
test.test2 optimize statusOK
Table Op Msg_type Msg_text
如果使用mk-find 工具，只需要如下：
mk-find -u sg -p xxxx -h localhost --dblike test --engine MyISAM --print
`test`.`test`
`test`.`test2`
`test`.`zzz`
我可以先看看符合条件的有哪些表，确认没问题后在执行，
mk-find -u sg -p xxxx -h localhost –dblike test –engine MyISAM –exec “optimize table %D.%N”
如果你要删除上面的表，也只需要执行
mk-find -u sg -p xxxx -h localhost –dblike test –engine MyISAM –exec “drop table %D.%N”
看下这些格式的定义：
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
一条命令就行了，简单吧。
OK,还有其他的一些参数，可以看下帮助文件。
mk-find --help
mk-find searches for MySQL tables and executes actions, like GNU find.The
default action is to print the database and table name.For more details,
please use the --help option, or try 'perldoc mk-find' for complete
documentation.
Usage: mk-find ...
Options:
--askpass Prompt for password for connections
--case-insensitiveRegular expression patterns ignore case
--charset -ADefault character set
--daystart Measure times from the beginning of the day
--defaults-file -FOnly read mysql options from the given file
--help Show this help message
--host -hConnect to host
--or Combine tests with OR, not AND
--password -pPassword to use when connecting
--port -PPort number to use for connection
--quote Quote database and table names (default)
--setvars Set these MySQL variables (default wait_timeout=10000)
--socket -SSocket file to use for connection
--user -uUser for login if not current user
--version Output version information and exit
Tests:
--autoinc Table next AUTO_INCREMENT is n
--avgrowlen Table avg row len is n bytes
--checksum Table checksum is n
--cmin Table was created n minutes ago
--collation Table collation matches pattern
--comment Table comment matches pattern
--createopts Table create option matches pattern
--ctime Table was created n days ago
--datafree Table has n bytes of free space
--datasize Table data uses n bytes of space
--dblike Database name matches SQL LIKE pattern
--dbregex Database name matches this pattern
--empty Table has no rows
--engine Table storage engine matches this pattern
--indexsize Table indexes use n bytes of space
--kmin Table was checked n minutes ago
--ktime Table was checked n days ago
--mmin Table was last modified n minutes ago
--mtime Table was last modified n days ago
--pid Table name has nonexistent MySQL connection ID
--rowformat Table row format matches pattern
--rows Table has n rows
--sid Table name matches server ID
--tablesize Table (data+index) uses n bytes of space
--tbllike Table name matches SQL LIKE pattern
--tblregex Table name matches this pattern
--tblversion Table version is n
Actions:
--exec Execute this SQL with each item found
--exec_plus Execute this SQL with all items at once
--print Print the database and table name
--printf Print format, with escapes and directives
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/111930/showart_2184878.html
页:
[1]