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

查看显示所有数据库

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

mysql>
    

查看当前使用的数据库

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

mysql>
    

查看数据库使用端口

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

查看当前数据库大小

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

mysql>
    useinformation_schema
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>
    selectconcat(round(sum(data_length)/(1024*1024),2)+round(sum(index_length)/(1024*1024),2),'MB')as'DBSize'
->
    fromtables
->
    wheretable_schema='INVOICE';
    
+-----------+
|DBSize|
+-----------+
|7929.58MB|
+-----------+
1rowinset,1warning(0.00sec)

查看数据所占的空间大小

mysql>
    useinformation_schema;
    
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>
    selectconcat(round(sum(data_length)/(1024*1024),2),'MB')as'DBSize'
->
    fromtables
->
    wheretable_schema='INVOICE';
    
+-----------+
|DBSize|
+-----------+
|6430.26MB|
+-----------+
1rowinset,1warning(0.00sec)

mysql>
    

查看索引所占的空间大小

mysql>
    selectconcat(round(sum(index_length)/(1024*1024),2),'MB')as'DBSize'
->
    fromtables
->
    wheretable_schema='INVOICE';
    
+-----------+
|DBSize|
+-----------+
|1499.32MB|
+-----------+
1rowinset,1warning(0.13sec)

mysql>
    

查看数据库编码

mysql>
    showvariableslike'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.00sec)

• character_set_client 为客户端编码方式；

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

• character_set_database 为数据库的编码；

• character_set_results 为结果集的编码；

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

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

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

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

mysql>
    status;
    
--------------
mysqlVer14.14Distrib5.6.20,forLinux(x86_64)usingEditLinewrapper

Connectionid:1
Currentdatabase:INVOICE
Currentuser:root@localhost
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
    
Serverversion:5.6.20-enterprise-commercial-advancedMySQLEnterpriseServer-AdvancedEdition(Commercial)
Protocolversion:10
Connection:LocalhostviaUNIXsocket
Servercharacterset:latin1
Dbcharacterset:latin1
Clientcharacterset:utf8
Conn.characterset:utf8
UNIXsocket:/var/lib/mysql/mysql.sock
Uptime:5hours18min51sec

Threads:1Questions:10884Slowqueries:0Opens:650Flushtables:1Opentables:268Queriespersecondavg:0.568
--------------
mysql>
    

查看数据库的表信息

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.00sec)

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

select*frominformation_schema.tableswheretable_schema=‘databasename';
    

查看某种具体表的信息

select*frominformation_schema.tableswheretable_name=‘table_name'

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

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

mysql>
    

查看某个具体用户的权限

mysql>
    showgrantsfor'root'@'localhost';
    
+---------------------------------------------------------------------------------------------------------------------------------+
|Grantsforroot@localhost|
+---------------------------------------------------------------------------------------------------------------------------------+
|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'IDENTIFIEDBYPASSWORD'*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23'WITHGRANTOPTION|
|GRANTPROXYON''@''TO'root'@'localhost'WITHGRANTOPTION|
+---------------------------------------------------------------------------------------------------------------------------------+
2rowsinset(0.00sec)

查看数据库的最大连接数

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

mysql>
    

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

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

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

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

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

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

查看数据文件存放路径

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

mysql>
    

读到这里，这篇“Mysql怎么查询数据库连接状态及连接信息”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注行业资讯频道。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！