MySQL Utilities (mysqlmetagrep)
Reference: http://thinkit.co.jp/story/2014/02/10/4814
mysqlmetagrep search metadata
DBのメタデータを表示してくれるコマンド
mysqluc> mysqlmetagrep --help MySQL Utilities mysqlmetagrep.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0) License type: GPLv2 Usage: mysqlmetagrep.exe --server=user:pass@host:port:socket [options] pattern mysqlmetagrep - search metadata Options: --version show program's version number and exit --help display a help message and exit --license display program's license and exit --server=SERVER connection information for the server in the form: <user>[:<password>]@<host>[:<port>][:<socket>] or <login-path>[:<port>][:<socket>]. --character-set=CHARSET sets the client character set. The default is retrieved from the server variable 'character_set_client'. -b, --body search the body of routines, triggers, and events as well --search-objects=OBJECT_TYPES, --object-types=OBJECT_TYPES the object type to search in: a comma-separated list of one or more of: 'database', 'trigger', 'user', 'routine', 'column', 'table', 'partition', 'event', 'view' -G, --basic-regexp, --regexp use 'REGEXP' operator to match pattern. Default is to use 'LIKE'. -p, --print-sql, --sql print the statement instead of sending it to the server -e PATTERN, --pattern=PATTERN pattern to use when matching. Required if the pattern looks like a connection specification. --database=DATABASE_PATTERN only look at objects in databases matching this pattern -f FORMAT, --format=FORMAT display the output in either grid (default), tab, csv, or vertical format mysqluc>
データベースオブジェクトは、もちろんinformation_schemaをselectすれば確認出来るが、
mysqlmetagrepコマンドを利用しても確認出来る。
運用によっては、色々なケースが考えられるので使い分けても良いかと思います。
SQLコマンドの場合
mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE,ROW_FORMAT,CREATE_TIME from information_schema.tables where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA like 't%'; +--------------+------------+--------+------------+---------------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | ROW_FORMAT | CREATE_TIME | +--------------+------------+--------+------------+---------------------+ | test | db8 | MyISAM | Fixed | 2014-07-11 16:49:34 | | test | language | InnoDB | Compact | 2014-07-29 10:23:26 | | test | lck | InnoDB | Compact | 2014-07-07 17:43:20 | | test | montable | InnoDB | Compact | 2014-07-23 12:58:38 | | test | t | InnoDB | Compact | 2014-07-24 15:09:26 | +--------------+------------+--------+------------+---------------------+ 5 rows in set (0.01 sec) mysql>
MySQL Utilities
mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 'test' --database='t%' +------------------------+--------------+--------------+-----------+-------------+----------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+--------------+-----------+-------------+----------+ | root:*@localhost:3306 | SCHEMA | test | test | DATABASE | test | +------------------------+--------------+--------------+-----------+-------------+----------+ mysqluc> mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 't%' --database='t%' +------------------------+--------------+--------------+-----------+-------------+---------------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+--------------+-----------+-------------+---------------+ | root:*@localhost:3306 | SCHEMA | test | test | DATABASE | test | | root:*@localhost:3306 | TABLE | db8 | test | COLUMN | Trigger_priv | | root:*@localhost:3306 | TABLE | t | test | TABLE | t | +------------------------+--------------+--------------+-----------+-------------+---------------+ mysqluc> mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 't%' --database='t%' --format=vertical ************************* 1. row ************************* Connection: root:*@localhost:3306 Object Type: SCHEMA Object Name: test Database: test Field Type: DATABASE Matches: test ************************* 2. row ************************* Connection: root:*@localhost:3306 Object Type: TABLE Object Name: db8 Database: test Field Type: COLUMN Matches: Trigger_priv ************************* 3. row ************************* Connection: root:*@localhost:3306 Object Type: TABLE Object Name: t Database: test Field Type: TABLE Matches: t 3 rows. mysqluc> mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 'd%' --database='t%' +------------------------+--------------+--------------+-----------+-------------+---------------------------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+--------------+-----------+-------------+---------------------------+ | root:*@localhost:3306 | TABLE | db8 | test | COLUMN | Drop_priv,Delete_priv,Db | | root:*@localhost:3306 | TABLE | db8 | test | TABLE | db8 | +------------------------+--------------+--------------+-----------+-------------+---------------------------+ mysqluc> mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 'd%' --search-objects=table,view --database='t%' +------------------------+--------------+--------------+-----------+-------------+----------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+--------------+-----------+-------------+----------+ | root:*@localhost:3306 | TABLE | db8 | test | TABLE | db8 | +------------------------+--------------+--------------+-----------+-------------+----------+ mysqluc>