每日MySQL之019：使用SHOW命令查看数据库信息

MySQL中的 SHOW 命令是一个数据库管理语句，能获取MySQL server上很多关于数据库、表、列、运行状态的信息，常见命令如下

对象创建语句：
SHOW CREATE DATABASE/EVENT/FUNCTION/PROCEDURE/TABLE/TRIGGER/USER/VIEW

查看日志：
SHOW {BINARY| MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

查看对象列表:
SHOW DATABASE
SHOW TABLES
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name]
SHOW INDEX FROM tbl_name

查看所支持的存储引擎、所有权限以及单个用户的权限
SHOW ENGINES
SHOW PRIVILEGES
SHOW GRANTS FOR user

查看状态
SHOW [FULL] PROCESSLIST
SHOW TABLE STATUS
SHOW VARIABLES
SHOW MASTER STATUS

SHOW SLAVE STATUS

示例1. SHOW BINLOG EVENTS 查看binary log中的事件：

mysql> SHOW BINLOG EVENTS IN 'binerr.000001';
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------+
| binerr.000001 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.18-log, Binlog ver: 4               |
| binerr.000001 |  123 | Previous_gtids |         1 |         154 |                                                     |
| binerr.000001 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binerr.000001 |  219 | Query          |         1 |         300 | BEGIN                                               |
| binerr.000001 |  300 | Query          |         1 |         414 | use `test1`; insert into B values('name1', default) |
| binerr.000001 |  414 | Xid            |         1 |         445 | COMMIT /* xid=212 */                                |
| binerr.000001 |  445 | Anonymous_Gtid |         1 |         510 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binerr.000001 |  510 | Query          |         1 |         591 | BEGIN                                               |
| binerr.000001 |  591 | Query          |         1 |         705 | use `test1`; insert into B values('name2', default) |
| binerr.000001 |  705 | Xid            |         1 |         736 | COMMIT /* xid=213 */                                |
| binerr.000001 |  736 | Anonymous_Gtid |         1 |         801 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binerr.000001 |  801 | Query          |         1 |         882 | BEGIN                                               |
| binerr.000001 |  882 | Query          |         1 |         996 | use `test1`; insert into B values('name3', default) |
| binerr.000001 |  996 | Xid            |         1 |        1027 | COMMIT /* xid=214 */                                |
| binerr.000001 | 1027 | Anonymous_Gtid |         1 |        1092 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binerr.000001 | 1092 | Query          |         1 |        1173 | BEGIN                                               |
| binerr.000001 | 1173 | Query          |         1 |        1287 | use `test1`; insert into B values('name4', default) |
| binerr.000001 | 1287 | Xid            |         1 |        1318 | COMMIT /* xid=215 */                                |
| binerr.000001 | 1318 | Anonymous_Gtid |         1 |        1383 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binerr.000001 | 1383 | Query          |         1 |        1464 | BEGIN                                               |
| binerr.000001 | 1464 | Query          |         1 |        1578 | use `test1`; insert into B values('name5', default) |
| binerr.000001 | 1578 | Xid            |         1 |        1609 | COMMIT /* xid=216 */                                |
| binerr.000001 | 1609 | Rotate         |         1 |        1653 | binerr.000002;pos=4                                 |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------+
23 rows in set (0.00 sec)

示例2：SHOW ENGINES 查看系统支持的存储引擎，可以看到，InnoDB是默认的，并且是唯一支持事务的引擎：

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

示例3：SHOW MASTER STATUS查看MASTER状态：

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binerr.000011 |     1716 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

示例4：SHOW INDEXES 查看表t2的索引

mysql> SHOW INDEXES FROM t2 \G
*************************** 1. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: idx2
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

示例5：SHOW GRANTS 查看用户'root'@'localhost'的权限：

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

示例6：SHOW TABLE STATUS 查看表t2的状态，可以看到存储引擎、行数、创建时间、更新时间等

mysql> SHOW TABLE STATUS LKIE 't2' \G
*************************** 1. row ***************************
           Name: t2
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-08-11 03:57:35
    Update_time: 2017-08-11 03:58:05
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

示例7：SHOW PROCESSLIST 查看MySQL进程列表，和mysqladmin的PROCESSLIST效果一样，类似于DB2中的list applications：

mysql> SHOW PROCESSLIST;
+----+------+------------+-------+---------+------+----------+------------------+
| Id | User | Host       | db    | Command | Time | State    | Info             |
+----+------+------------+-------+---------+------+----------+------------------+
|  3 | root | localhost  | test1 | Sleep   |  243 |          | NULL             |
|  4 | root | db2b:33630 | NULL  | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+------------+-------+---------+------+----------+------------------+
2 rows in set (0.01 sec)

示例8：SHOW PLUGINS 查看所有的PLUGIN:

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------+---------+
| Name                       | Status   | Type               | Library        | License |
+----------------------------+----------+--------------------+----------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL           | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL           | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL           | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL           | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL           | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL           | GPL     |
| auth_socket                | ACTIVE   | AUTHENTICATION     | auth_socket.so | GPL     |
+----------------------------+----------+--------------------+----------------+---------+
45 rows in set (0.01 sec)

原文链接：https://blog.csdn.net/qingsong3333/article/details/77131686