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>

Comments are closed.

Post Navigation