Mysql查询数据库连接状态及连接信息

Mysql查询数据库连接状态及连接信息

使用MySQL时，需要了解当前数据库的情况，例如当前的数据库大小、字符集、用户等等。下面总结了一些查看数据库相关信息的命令

1. 查看显示所有数据库

mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || INVOICE            || mysql              || performance_schema || test               |+--------------------+5rowsinset(0.00 sec)
 
mysql>

1. 查看当前使用的数据库

mysql>selectdatabase();+------------+|database()|+------------+| INVOICE    |+------------+1rowinset(0.00 sec)
 
mysql>

1. 查看数据库使用端口

mysql>show variables  like'port';+---------------+-------+| Variable_name |Value|+---------------+-------+| port          |3306|+---------------+-------+1rowinset(0.00 sec)

1. 查看当前数据库大小

例如，我要查看INVOICE数据库的大小，那么可以通过下面SQL查看

mysql>use  information_schema
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
 
Database changed
mysql>select concat(round(sum(data_length)/(1024*1024),2)+round(sum(index_length)/(1024*1024),2),'MB')as'DB Size'->fromtables->where table_schema='INVOICE';+-----------+| DB Size   |+-----------+|7929.58MB |+-----------+1rowinset,1 warning (0.00 sec)

1. 查看数据所占的空间大小

mysql>use information_schema;
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
 
Database changed
mysql>select concat(round(sum(data_length)/(1024*1024),2),'MB')as'DB Size'->fromtables->where table_schema='INVOICE';+-----------+| DB Size   |+-----------+|6430.26MB |+-----------+1rowinset,1 warning (0.00 sec)
 
mysql>

1. 查看索引所占的空间大小

mysql>select concat(round(sum(index_length)/(1024*1024),2),'MB')as'DB Size'->fromtables->where table_schema='INVOICE';+-----------+| DB Size   |+-----------+|1499.32MB |+-----------+1rowinset,1 warning (0.13 sec)
 
mysql>

1. 查看数据库编码

mysql>show 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     | latin1                     || character_set_system     | utf8                       || character_sets_dir       |/usr/share/mysql/charsets/|+--------------------------+----------------------------+8rowsinset(0.00 sec)

character_set_client 为客户端编码方式；

character_set_connection 为建立连接使用的编码；

character_set_database 为数据库的编码；

character_set_results 为结果集的编码；

character_set_server 为数据库服务器的编码；

只要保证以上采用的编码方式一样，就不会出现乱码问题。

mysql>show variables like'collation%';+----------------------+-------------------+| Variable_name        |Value|+----------------------+-------------------+| collation_connection | utf8_general_ci   || collation_database   | utf8_general_ci   || collation_server     | latin1_swedish_ci |+----------------------+-------------------+3rowsinset(0.00 sec)

status也可以查看数据库的编码

mysql>status;--------------
mysql  Ver 14.14 Distrib 5.6.20,for Linux (x86_64)using  EditLine wrapper
 
Connection id:          1Currentdatabase:       INVOICE
Currentuser:           root@localhost
SSL:                    NotinuseCurrent pager:          stdout
Usingoutfile:          ''Usingdelimiter:        ;
Server version:         5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 5 hours 18 min 51 sec
 
Threads: 1  Questions: 10884  Slow queries: 0  Opens: 650  Flush tables: 1Opentables: 268  Queries per second avg: 0.568--------------
mysql>

1. 查看数据库的表信息

mysql>showtables;+---------------------------------------+| Tables_in_information_schema          |+---------------------------------------+| CHARACTER_SETS                        || COLLATIONS                            || COLLATION_CHARACTER_SET_APPLICABILITY ||COLUMNS|| COLUMN_PRIVILEGES                     || ENGINES                               || EVENTS                                || FILES                                 || GLOBAL_STATUS                         || GLOBAL_VARIABLES                      || KEY_COLUMN_USAGE                      || OPTIMIZER_TRACE                       || PARAMETERS                            || PARTITIONS                            || PLUGINS                               || PROCESSLIST                           || PROFILING                             || REFERENTIAL_CONSTRAINTS               || ROUTINES                              || SCHEMATA                              || SCHEMA_PRIVILEGES                     || SESSION_STATUS                        || SESSION_VARIABLES                     ||STATISTICS||TABLES|| TABLESPACES                           || TABLE_CONSTRAINTS                     || TABLE_PRIVILEGES                      || TRIGGERS                              || USER_PRIVILEGES                       || VIEWS                                 || INNODB_LOCKS                          || INNODB_TRX                            || INNODB_SYS_DATAFILES                  || INNODB_LOCK_WAITS                     || INNODB_SYS_TABLESTATS                 || INNODB_CMP                            || INNODB_METRICS                        || INNODB_CMP_RESET                      || INNODB_CMP_PER_INDEX                  || INNODB_CMPMEM_RESET                   || INNODB_FT_DELETED                     || INNODB_BUFFER_PAGE_LRU                || INNODB_SYS_FOREIGN                    || INNODB_SYS_COLUMNS                    || INNODB_SYS_INDEXES                    || INNODB_FT_DEFAULT_STOPWORD            || INNODB_SYS_FIELDS                     || INNODB_CMP_PER_INDEX_RESET            || INNODB_BUFFER_PAGE                    || INNODB_CMPMEM                         || INNODB_FT_INDEX_TABLE                 || INNODB_FT_BEING_DELETED               || INNODB_SYS_TABLESPACES                || INNODB_FT_INDEX_CACHE                 || INNODB_SYS_FOREIGN_COLS               || INNODB_SYS_TABLES                     || INNODB_BUFFER_POOL_STATS              || INNODB_FT_CONFIG                      |+---------------------------------------+59rowsinset(0.00 sec)

或者使用下面SQL语句查看某个数据库的表信息。

select * from information_schema.tables where table_schema=‘databasename’;

查看某种具体表的信息

select * from information_schema.tables where table_name =‘table_name’

1. ：查看数据库的所有用户信息

mysql>selectdistinct concat('user: ''',user,'''@''',host,''';')as query from mysql.user;+-------------------------------------+| query                               |+-------------------------------------+|user: 'root'@'127.0.0.1';||user: 'root'@'::1';||user: 'root'@'gettesx20.test.com';||user: 'root'@'localhost';|+-------------------------------------+4rowsinset(0.00 sec)
 
mysql>

1. 查看某个具体用户的权限

mysql>show grants for'root'@'localhost';+---------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost|+---------------------------------------------------------------------------------------------------------------------------------+|GRANTALLPRIVILEGESON*.*TO'root'@'localhost' IDENTIFIED BY PASSWORD '*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23'WITHGRANTOPTION||GRANT PROXY ON''@'' TO 'root'@'localhost' WITHGRANTOPTION|+---------------------------------------------------------------------------------------------------------------------------------+2rowsinset(0.00 sec)

1. 查看数据库的最大连接数

mysql>show variables like'%max_connections%';+-----------------+-------+| Variable_name   |Value|+-----------------+-------+| max_connections |151|+-----------------+-------+1rowinset(0.00 sec)
 
mysql>

1. 查看数据库当前连接数，并发数。

mysql>showstatuslike'Threads%';+-------------------+-------+| Variable_name     |Value|+-------------------+-------+| Threads_cached    |0|| Threads_connected |1|| Threads_created   |1|| Threads_running   |1|+-------------------+-------+4rowsinset(0.00 sec)

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。

Threads_created :代表从最近一次服务启动，已创建线程的数量。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

1. 查看数据文件存放路径

mysql>show variables like'%datadir%';+---------------+-------------------+| Variable_name |Value|+---------------+-------------------+| datadir       |/mysqldata/mysql/|+---------------+-------------------+1rowinset(0.00 sec)
 
mysql>