![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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>