MySQL常用show命令使用总结
MySQL中有很多的基本命令，show命令也是其中之一，在很多使用者中对show命令的使用还容易产生混淆，本文汇集了show命令的众多用法。show命令可以提供关于数据库、表、列，或关于服务器的状态信息。
#总结 # 显示二进制文件以及文件大小（需要开启二进制日志记录功能） SHOW {BINARY | MASTER} LOGS
# 显示二进制文件的执行过程 SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
# 显示MySQL当前支持哪些字符集 SHOW CHARACTER SET [like_or_where]
# 显示MySQL支持字符集的排序规则 SHOW COLLATION [like_or_where]
# 显示表的列信息（等同于DESC，需要先创建表） SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
# 显示已经创建的库，创建时的语句 SHOW CREATE DATABASE db_name
# 显示已经创建的事件，创建时的语句 SHOW CREATE EVENT event_name
# 显示已经创建的函数，创建时的语句 SHOW CREATE FUNCTION func_name
# 显示已经创建的存储过程，创建时的语句 SHOW CREATE PROCEDURE proc_name
# 显示已经创建的表，创建时的语句 SHOW CREATE TABLE tbl_name
# 显示已经创建的触发器，创建时的语句 SHOW CREATE TRIGGER trigger_name
# 显示已经创建的视图，创建时的语句 SHOW CREATE VIEW view_name
# 显示mysql中所有数据库的名称 SHOW DATABASES [like_or_where]
# 显示存储引擎的详细信息 SHOW ENGINE engine_name {STATUS | MUTEX}
# 显示数据库支持的存储引擎和默认存储引擎 SHOW [STORAGE] ENGINES
# 显示最后一个执行语句所产生的错误信息 SHOW ERRORS [LIMIT [offset,] row_count]
# 显示事件信息 SHOW EVENTS
# 服务器内部调试，显示一个指定存储的内部实现的表示形式过程 SHOW FUNCTION CODE func_name
# 显示存储函数信息（需要先创建存储函数） SHOW FUNCTION STATUS [like_or_where]
# 显示指定用户拥有的权限 SHOW GRANTS FOR user
# 显示表索引信息（需要先创建索引） SHOW INDEX FROM tbl_name [FROM db_name]
# 显示Master当前正在使用的二进制信息 SHOW MASTER STATUS
# 列举在表缓存中当前被打开的非TEMPORARY表 SHOW OPEN TABLES [FROM db_name] [like_or_where]
# 显示MySQL插件信息 SHOW PLUGINS
# 服务器内部调试，显示一个指定存储的内部实现的表示形式过程 SHOW PROCEDURE CODE proc_name
# 显示存储过程信息（需要先创建存储过程） SHOW PROCEDURE STATUS [like_or_where]
# 显示MySQL所支持的所有权限，及权限可操作的对象 SHOW PRIVILEGES
# 显示系统中正在运行的所有进程，普通用户只能查看自己的进行信息 SHOW [FULL] PROCESSLIST
# 显示当前会话执行语句资源使用情况 SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
# 显示当前会话执行语句资源使用情况 SHOW PROFILES
# 显示relaylog事件信息（需要先做主从复制） SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
# 显示Master主机上已注册的复制主机列表（需要先做主从复制） SHOW SLAVE HOSTS
# 显示Slave主机状态信息（需要先做主从复制） SHOW SLAVE STATUS [FOR CHANNEL channel]
# 显示MySQL状态信息 SHOW [GLOBAL | SESSION] STATUS [like_or_where]
# 显示表属性信息 SHOW TABLE STATUS [FROM db_name] [like_or_where]
# 显示当前数据库中所有表的名称 SHOW [FULL] TABLES [FROM db_name] [like_or_where]
# 显示触发器信息（需要先创建触发器） SHOW TRIGGERS [FROM db_name] [like_or_where]
# 显示MySQL变量信息 SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
# 显示最后一个执行语句所产生的警告信息 SHOW WARNINGS [LIMIT [offset,] row_count]
#实例
显示建表建库语句 mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pubmmrpg | | student | | sys | +--------------------+ 6 rows in set (0.01 sec)
mysql> SHOW CREATE DATABASE MYSQL; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | MYSQL | CREATE DATABASE `MYSQL` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> USE mysql;
Database changed mysql> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE mysql.user\G; *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password', `authentication_string` text COLLATE utf8_bin, `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `password_last_changed` timestamp NULL DEFAULT NULL, `password_lifetime` smallint(5) unsigned DEFAULT NULL, `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' 1 row in set (0.00 sec)
mysql> SHOW COLUMNS FROM user; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | NO | | mysql_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint(5) unsigned | YES | | NULL | | | account_locked | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 45 rows in set (0.01 sec)
显示二进制文件相关 mysql> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.002022 | 817345 | | mysql-bin.002023 | 817932 | | mysql-bin.002024 | 816758 | | mysql-bin.002025 | 664678 | +------------------+-----------+ 4 rows in set (0.01 sec)
mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.002022 | 817345 | | mysql-bin.002023 | 817932 | | mysql-bin.002024 | 816758 | | mysql-bin.002025 | 665265 | +------------------+-----------+ 4 rows in set (0.01 sec)
显示主从相关 mysql> SHOW SLAVE HOSTS; Empty set (0.00 sec)
mysql> SHOW SLAVE STATUS; Empty set (0.00 sec)
mysql> SHOW RELAYLOG EVENTS limit 10; +--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | slave-relay.001351 | 4 | Format_desc | 1339158577 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | slave-relay.001351 | 123 | Previous_gtids | 1339158577 | 194 | 4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3111622 | | slave-relay.001351 | 194 | Rotate | 2241191473 | 0 | mysql-bin.001063;pos=4 | | slave-relay.001351 | 241 | Format_desc | 2241191473 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | slave-relay.001351 | 360 | Rotate | 0 | 407 | mysql-bin.001063;pos=234 | | slave-relay.001351 | 407 | Gtid | 2241191473 | 299 | SET @@SESSION.GTID_NEXT= '4941480b-c2fa-11e8-850c-506b4bbe1cf4:3111623' | | slave-relay.001351 | 472 | Query | 2241191473 | 539 | /* rds internal mark */ CREATE TABLE IF NOT EXISTS mysql.ha_health_check ( id BIGINT DEFAULT 0, type CHAR(1) DEFAULT '0', PRIMARY KEY (type) ) ENGINE = InnoDB | | slave-relay.001351 | 712 | Gtid | 2241191473 | 604 | SET @@SESSION.GTID_NEXT= '4941480b-c2fa-11e8-850c-506b4bbe1cf4:3111624' | | slave-relay.001351 | 777 | Query | 2241191473 | 672 | BEGIN | | slave-relay.001351 | 845 | Table_map | 2241191473 | 734 | table_id: 81659 (mysql.ha_health_check) | +--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec)
mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.120.26 Master_User: replicator Master_Port: 3069 Connect_Retry: 60 Master_Log_File: mysql-bin.001063 Read_Master_Log_Pos: 3564725 Relay_Log_File: slave-relay.001352 Relay_Log_Pos: 844419 Relay_Master_Log_File: mysql-bin.001063 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3564725 Relay_Log_Space: 3565260 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2241191473 Master_UUID: 4941480b-c2fa-11e8-850c-506b4bbe1cf4 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Last_SQL_Error_Gtid: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3119404 Executed_Gtid_Set: 3c09db04-c2fa-11e8-b5cc-506b4bff2084:1-2275307, 4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3119404 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.01 sec)
显示字符集变量相关 mysql> SHOW CHARACTER SET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.02 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
显示用户权限相关 mysql> SHOW GRANTS FOR test; +-------------------------------------------+ | Grants for test@% | +-------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' | +-------------------------------------------+ 1 row in set (0.00 sec)
显示运行进程 mysql> SHOW PROCESSLIST; +----+------+-----------+-------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+----------+------------------+ | 6 | root | localhost | mysql | Query | 0 | starting | SHOW PROCESSLIST | +----+------+-----------+-------+---------+------+----------+------------------+ 1 row in set (0.00 sec)
腾讯云开发者
