Mysql查詢數(shù)據(jù)庫(kù)連接狀態(tài)以及連接信息詳解
更新時(shí)間：2023年04月14日 08:41:53 作者：Super_龍
這篇文章主要給大家介紹了關(guān)于Mysql查詢數(shù)據(jù)庫(kù)連接狀態(tài)以及連接信息的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
Mysql查詢數(shù)據(jù)庫(kù)連接狀態(tài)及連接信息
使用MySQL時(shí)，需要了解當(dāng)前數(shù)據(jù)庫(kù)的情況，例如當(dāng)前的數(shù)據(jù)庫(kù)大小、字符集、用戶等等。下面總結(jié)了一些查看數(shù)據(jù)庫(kù)相關(guān)信息的命令
查看顯示所有數(shù)據(jù)庫(kù)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | INVOICE | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql>
查看當(dāng)前使用的數(shù)據(jù)庫(kù)
mysql> select database(); +------------+ | database() | +------------+ | INVOICE | +------------+ 1 row in set (0.00 sec) mysql>
查看數(shù)據(jù)庫(kù)使用端口
mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.00 sec)
查看當(dāng)前數(shù)據(jù)庫(kù)大小
例如，我要查看INVOICE數(shù)據(jù)庫(kù)的大小，那么可以通過(guò)下面SQL查看
mysql> use information_schema
Reading table information for completion of table and column 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'
-> from tables
-> where table_schema='INVOICE';
+-----------+
| DB Size |
+-----------+
| 7929.58MB |
+-----------+
1 row in set, 1 warning (0.00 sec)
查看數(shù)據(jù)所占的空間大小
mysql> use information_schema;
Reading table information for completion of table and column 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'
-> from tables
-> where table_schema='INVOICE';
+-----------+
| DB Size |
+-----------+
| 6430.26MB |
+-----------+
1 row in set, 1 warning (0.00 sec)
mysql>
查看索引所占的空間大小
mysql> select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size'
-> from tables
-> where table_schema='INVOICE';
+-----------+
| DB Size |
+-----------+
| 1499.32MB |
+-----------+
1 row in set, 1 warning (0.13 sec)
mysql>
查看數(shù)據(jù)庫(kù)編碼
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/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
- character_set_client 為客戶端編碼方式；
- character_set_connection 為建立連接使用的編碼；
- character_set_database 為數(shù)據(jù)庫(kù)的編碼；
- character_set_results 為結(jié)果集的編碼；
- character_set_server 為數(shù)據(jù)庫(kù)服務(wù)器的編碼；
只要保證以上采用的編碼方式一樣，就不會(huì)出現(xiàn)亂碼問(wèn)題。
mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)
status也可以查看數(shù)據(jù)庫(kù)的編碼
mysql> status; -------------- mysql Ver 14.14 Distrib 5.6.20, for Linux (x86_64) using EditLine wrapper Connection id: 1 Current database: INVOICE Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; 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: 1 Open tables: 268 Queries per second avg: 0.568 -------------- mysql>
查看數(shù)據(jù)庫(kù)的表信息
mysql> show tables; +---------------------------------------+ | 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 | +---------------------------------------+ 59 rows in set (0.00 sec)
或者使用下面SQL語(yǔ)句查看某個(gè)數(shù)據(jù)庫(kù)的表信息。
select * from information_schema.tables where table_schema=‘databasename';
查看某種具體表的信息
select * from information_schema.tables where table_name =‘table_name'
查看數(shù)據(jù)庫(kù)的所有用戶信息
mysql> select distinct 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'; |
+-------------------------------------+
4 rows in set (0.00 sec)
mysql>
查看某個(gè)具體用戶的權(quán)限
mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
查看數(shù)據(jù)庫(kù)的最大連接數(shù)
mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec) mysql>
查看數(shù)據(jù)庫(kù)當(dāng)前連接數(shù)，并發(fā)數(shù)。
mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 1 | | Threads_running | 1 | +-------------------+-------+ 4 rows in set (0.00 sec)
Threads_cached : 代表當(dāng)前此時(shí)此刻線程緩存中有多少空閑線程。
Threads_connected :代表當(dāng)前已建立連接的數(shù)量，因?yàn)橐粋€(gè)連接就需要一個(gè)線程，所以也可以看成當(dāng)前被使用的線程數(shù)。
Threads_created :代表從最近一次服務(wù)啟動(dòng)，已創(chuàng)建線程的數(shù)量。
Threads_running :代表當(dāng)前激活的（非睡眠狀態(tài)）線程數(shù)。并不是代表正在使用的線程數(shù)，有時(shí)候連接已建立，但是連接處于sleep狀態(tài)，這里相對(duì)應(yīng)的線程也是sleep狀態(tài)。
查看數(shù)據(jù)文件存放路徑
mysql> show variables like '%datadir%'; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | datadir | /mysqldata/mysql/ | +---------------+-------------------+ 1 row in set (0.00 sec) mysql>
總結(jié)
到此這篇關(guān)于Mysql查詢數(shù)據(jù)庫(kù)連接狀態(tài)以及連接信息的文章就介紹到這了,更多相關(guān)Mysql查詢數(shù)據(jù)庫(kù)連接信息內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
分區(qū)允許根據(jù)可以設(shè)置為任意大小的規(guī)則,跨文件系統(tǒng)分配單個(gè)表的多個(gè)部分,本文主要介紹了Mysql分區(qū)的使用,具有一定的參考價(jià)值,感興趣的可以了解一下2024-06-06
這篇文章主要介紹了MySql命令,結(jié)合實(shí)例分析了MySQL數(shù)據(jù)庫(kù)的創(chuàng)建、連接及增刪改查等各種常用操作的使用方法與相關(guān)注意事項(xiàng),非常具有實(shí)用價(jià)值,需要的朋友可以參考下2015-11-11
這篇文章主要介紹了MySQL update set 和 and的區(qū)別，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2021-05-05
這篇文章主要介紹了mysql數(shù)據(jù)庫(kù)增刪改操作，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04
windows10安裝mysql5.7.17是這樣安裝的嗎？這篇文章主要為大家詳細(xì)介紹了win10下mysql5.7.17安裝配置方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
MySQL如何開(kāi)啟遠(yuǎn)程連接權(quán)限
這篇文章主要介紹了MySQL如何開(kāi)啟遠(yuǎn)程連接權(quán)限問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-09-09
mysql 的 top 方法 limit,用來(lái)獲取數(shù)據(jù)庫(kù)查詢的前幾天記錄。2009-11-11
MySQL中事務(wù)概念的簡(jiǎn)潔學(xué)習(xí)教程
這篇文章主要介紹了MySQL中的事務(wù)概念,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-11-11
MySQL5.7.24版本的數(shù)據(jù)庫(kù)安裝過(guò)程圖文詳解
這篇文章主要介紹了MySQL5.7.24版本的數(shù)據(jù)庫(kù)安裝過(guò)程,需要的朋友可以參考下2018-11-11
解決MySQL數(shù)據(jù)庫(kù)中文模糊檢索問(wèn)題的方法
解決MySQL數(shù)據(jù)庫(kù)中文模糊檢索問(wèn)題的方法...2007-11-11