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        status  OK
Table   Op      Msg_type        Msg_text
test.test2      optimize        status  OK
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 [option]... [database...]
 
Options:
  --askpass           Prompt for password for connections
  --case-insensitive  Regular expression patterns ignore case
  --charset       -A  Default character set
  --daystart          Measure times from the beginning of the day
  --defaults-file -F  Only read mysql options from the given file
  --help              Show this help message
  --host          -h  Connect to host
  --or                Combine tests with OR, not AND
  --password      -p  Password to use when connecting
  --port          -P  Port number to use for connection
  --[no]quote         Quote database and table names (default)
  --setvars           Set these MySQL variables (default wait_timeout=10000)
  --socket        -S  Socket file to use for connection
  --user          -u  User 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