MySQL show 语句

总览show 语句

show tables from database_name; -- 显示当前数据库中所有表的名称。 show databases; -- 显示mysql中所有数据库的名称。 show columns from database_name.table_name; -- 显示表中列名称。 show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。 show index from table_name; -- 显示表的索引。 show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。 show variables; -- 显示系统变量的名称和值。 show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。 show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。 show privileges; -- 显示服务器所支持的不同权限。 show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。 show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 show engines; -- 显示安装以后可用的存储引擎和默认引擎。 show innodb status; -- 显示innoDB存储引擎的状态。 show logs; -- 显示BDB存储引擎的日志。 show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。 show errors; -- 只显示最后一个执行语句所产生的错误。 show [storage] engines; --显示安装后的可用存储引擎和默认引擎。

查看进程 

show processlist;

查看目录配置

 show variables like '%dir%';+-----------------------------------------+----------------------------+| Variable_name                           | Value                      |+-----------------------------------------+----------------------------+| basedir                                 | /usr/                      || binlog_direct_non_transactional_updates | OFF                        || character_sets_dir                      | /usr/share/mysql/charsets/ || datadir                                 | /var/lib/mysql/            || ignore_db_dirs                          |                            || innodb_data_home_dir                    |                            || innodb_log_group_home_dir               | ./                         || innodb_max_dirty_pages_pct              | 75                         || innodb_max_dirty_pages_pct_lwm          | 0                          || innodb_tmpdir                           |                            || innodb_undo_directory                   | .                          || lc_messages_dir                         | /usr/share/mysql/          || plugin_dir                              | /usr/lib64/mysql/plugin/   || slave_load_tmpdir                       | /tmp                       || tmpdir                                  | /tmp                       |+-----------------------------------------+----------------------------+15 rows in set (0.00 sec)

查看字符集配置

mysql> show variables like 'char%';+--------------------------+----------------------------+| 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 privileges;+-------------------------+---------------------------------------+-------------------------------------------------------+| Privilege               | Context                               | Comment                                               |+-------------------------+---------------------------------------+-------------------------------------------------------+| Alter                   | Tables                                | To alter the table                                    || Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          || Create                  | Databases,Tables,Indexes              | To create new databases and tables                    || Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      || Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         || Create view             | Tables                                | To create new views                                   || Create user             | Server Admin                          | To create new users                                   || Delete                  | Tables                                | To delete existing rows                               || Drop                    | Databases,Tables                      | To drop databases, tables, and views                  || Event                   | Server Admin                          | To create, alter, drop and execute events             || Execute                 | Functions,Procedures                  | To execute stored routines                            || File                    | File access on server                 | To read and write files on the server                 || Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   || Index                   | Tables                                | To create or drop indexes                             || Insert                  | Tables                                | To insert data into tables                            || Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   || Process                 | Server Admin                          | To view the plain text of currently executing queries || Proxy                   | Server Admin                          | To make proxy user possible                           || References              | Databases,Tables                      | To have references on tables                          || Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      || Replication client      | Server Admin                          | To ask where the slave or master servers are          || Replication slave       | Server Admin                          | To read binary log events from the master             || Select                  | Tables                                | To retrieve rows from table                           || Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              || Show view               | Tables                                | To see views with SHOW CREATE VIEW                    || Shutdown                | Server Admin                          | To shut down the server                               || Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   || Trigger                 | Tables                                | To use triggers                                       || Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      || Update                  | Tables                                | To update existing rows                               || Usage                   | Server Admin                          | No privileges - allow connect only                    |+-------------------------+---------------------------------------+-------------------------------------------------------+31 rows in set (0.00 sec)

Log_Error

mysql> show variables like '%log_error%';+---------------------+---------------------+| Variable_name       | Value               |+---------------------+---------------------+| binlog_error_action | IGNORE_ERROR        || log_error           | /var/log/mysqld.log |+---------------------+---------------------+2 rows in set (0.00 sec)

Show Grants

mysql> show grants for 'root'@'bogon';+------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@bogon                                                                                                              |+------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'bogon' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'bogon' WITH GRANT OPTION                                                                           |+------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)