MySQL管理工具MySQL Utilities — mysqlmetagrep (28)

mysqlmetagrep 搜索数据库对象的定义。根据给定的匹配模式来搜索出所有相匹配的对象并打印出。默认情况下，第一个非选项参数视为匹配模式，除非--pattern选项给定。如果--pattern选项给定了，所有非选项参数视为连接参数。

支持两种匹配模式：SQL 字符串匹配（使用LIKE运算符）和POSIX正则表达式（使用正则表达式运算符）。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlmetagrep/

默认情况下，该工具使用LIKE运算符进行名称匹配。要使用正则表达式运算符需要使用--regexp选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlmetagrep/

格式

• grid (default)
• csv
• tab
• vertical

选项

MySQL Utilities mysqlmetagrep version 1.5.3 
License type: GPLv2
Usage: mysqlmetagrep --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>] or <config-
                        path>[<[group]>].
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  --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

实例

查找出名称匹配'd_'的所有对象文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlmetagrep/

# mysqlmetagrep --pattern="d_" --server=instance_3306 
+------------------------+--------------+---------------+---------------------+-------------+----------+
| Connection             | Object Type  | Object Name   | Database            | Field Type  | Matches  |
+------------------------+--------------+---------------+---------------------+-------------+----------+
| root:*@localhost:3306  | TABLE        | PROCESSLIST   | information_schema  | COLUMN      | DB       |
| root:*@localhost:3306  | TABLE        | columns_priv  | mysql               | COLUMN      | Db       |
| root:*@localhost:3306  | TABLE        | db            | mysql               | COLUMN      | Db       |
| root:*@localhost:3306  | TABLE        | db            | mysql               | TABLE       | db       |
| root:*@localhost:3306  | TABLE        | event         | mysql               | COLUMN      | db       |
| root:*@localhost:3306  | TABLE        | func          | mysql               | COLUMN      | dl       |
| root:*@localhost:3306  | TABLE        | plugin        | mysql               | COLUMN      | dl       |
| root:*@localhost:3306  | TABLE        | proc          | mysql               | COLUMN      | db       |
| root:*@localhost:3306  | TABLE        | procs_priv    | mysql               | COLUMN      | Db       |
| root:*@localhost:3306  | TABLE        | servers       | mysql               | COLUMN      | Db       |
| root:*@localhost:3306  | TABLE        | slow_log      | mysql               | COLUMN      | db       |
| root:*@localhost:3306  | TABLE        | tables_priv   | mysql               | COLUMN      | Db       |
+------------------------+--------------+---------------+---------------------+-------------+----------+

查找出所有含有'bbs'的对象，包括存储过程、触发器和事件。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlmetagrep/

# mysqlmetagrep --pattern="%bbs%" --server=instance_3306
+------------------------+--------------+--------------------+------------+-------------+--------------------+
| Connection             | Object Type  | Object Name        | Database   | Field Type  | Matches            |
+------------------------+--------------+--------------------+------------+-------------+--------------------+
| root:*@localhost:3306  | TABLE        | bbs_categories     | ttlsa_com  | TABLE       | bbs_categories     |
| root:*@localhost:3306  | TABLE        | bbs_comments       | ttlsa_com  | TABLE       | bbs_comments       |
| root:*@localhost:3306  | TABLE        | bbs_favorites      | ttlsa_com  | TABLE       | bbs_favorites      |
| root:*@localhost:3306  | TABLE        | bbs_forums         | ttlsa_com  | TABLE       | bbs_forums         |
| root:*@localhost:3306  | TABLE        | bbs_links          | ttlsa_com  | TABLE       | bbs_links          |
| root:*@localhost:3306  | TABLE        | bbs_notifications  | ttlsa_com  | TABLE       | bbs_notifications  |
| root:*@localhost:3306  | TABLE        | bbs_page           | ttlsa_com  | TABLE       | bbs_page           |
| root:*@localhost:3306  | TABLE        | bbs_settings       | ttlsa_com  | TABLE       | bbs_settings       |
| root:*@localhost:3306  | TABLE        | bbs_tags           | ttlsa_com  | TABLE       | bbs_tags           |
| root:*@localhost:3306  | TABLE        | bbs_tags_relation  | ttlsa_com  | TABLE       | bbs_tags_relation  |
| root:*@localhost:3306  | TABLE        | bbs_users          | ttlsa_com  | TABLE       | bbs_users          |
| root:*@localhost:3306  | TABLE        | bbs_user_follow    | ttlsa_com  | TABLE       | bbs_user_follow    |
| root:*@localhost:3306  | TABLE        | bbs_user_groups    | ttlsa_com  | TABLE       | bbs_user_groups    |
+------------------------+--------------+--------------------+------------+-------------+--------------------+

使用正则表达式文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlmetagrep/

# mysqlmetagrep -Gb --pattern="bbs" --server=instance_3306    
+------------------------+--------------+--------------------+------------+-------------+--------------------+
| Connection             | Object Type  | Object Name        | Database   | Field Type  | Matches            |
+------------------------+--------------+--------------------+------------+-------------+--------------------+
| root:*@localhost:3306  | TABLE        | bbs_categories     | ttlsa_com  | TABLE       | bbs_categories     |
| root:*@localhost:3306  | TABLE        | bbs_comments       | ttlsa_com  | TABLE       | bbs_comments       |
| root:*@localhost:3306  | TABLE        | bbs_favorites      | ttlsa_com  | TABLE       | bbs_favorites      |
| root:*@localhost:3306  | TABLE        | bbs_forums         | ttlsa_com  | TABLE       | bbs_forums         |
| root:*@localhost:3306  | TABLE        | bbs_links          | ttlsa_com  | TABLE       | bbs_links          |
| root:*@localhost:3306  | TABLE        | bbs_notifications  | ttlsa_com  | TABLE       | bbs_notifications  |
| root:*@localhost:3306  | TABLE        | bbs_page           | ttlsa_com  | TABLE       | bbs_page           |
| root:*@localhost:3306  | TABLE        | bbs_settings       | ttlsa_com  | TABLE       | bbs_settings       |
| root:*@localhost:3306  | TABLE        | bbs_tags           | ttlsa_com  | TABLE       | bbs_tags           |
| root:*@localhost:3306  | TABLE        | bbs_tags_relation  | ttlsa_com  | TABLE       | bbs_tags_relation  |
| root:*@localhost:3306  | TABLE        | bbs_users          | ttlsa_com  | TABLE       | bbs_users          |
| root:*@localhost:3306  | TABLE        | bbs_user_follow    | ttlsa_com  | TABLE       | bbs_user_follow    |
| root:*@localhost:3306  | TABLE        | bbs_user_groups    | ttlsa_com  | TABLE       | bbs_user_groups    |
+------------------------+--------------+--------------------+------------+-------------+--------------------+

权限

用户需要对mysql数据库的SELECT的权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlmetagrep/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlmetagrep/