共計 7133 個字符，預計需要花費 18 分鐘才能閱讀完成。
本文丸趣 TV 小編為大家詳細介紹“Mysql 怎么查詢數據庫連接狀態及連接信息”，內容詳細，步驟清晰，細節處理妥當，希望這篇“Mysql 怎么查詢數據庫連接狀態及連接信息”文章能幫助大家解決疑惑，下面跟著丸趣 TV 小編的思路慢慢深入，一起來學習新知識吧。
查看顯示所有數據庫
mysql show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| INVOICE |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql查看當前使用的數據庫
mysql select database();
+------------+
| database() |
+------------+
| INVOICE |
+------------+
1 row in set (0.00 sec)
mysql查看數據庫使用端口
mysql show variables like port
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)查看當前數據庫大小
例如，我要查看 INVOICE 數據庫的大小，那么可以通過下面 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)查看數據所占的空間大小
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查看數據庫編碼
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 為數據庫的編碼；
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 |
+----------------------+-------------------+
3 rows in set (0.00 sec)status 也可以查看數據庫的編碼
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查看數據庫的表信息
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 語句查看某個數據庫的表信息。
select * from information_schema.tables where table_schema=‘databasename查看某種具體表的信息
select * from information_schema.tables where table_name =‘table_name查看數據庫的所有用戶信息
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查看某個具體用戶的權限
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)查看數據庫的最大連接數
mysql show variables like %max_connections%
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql查看數據庫當前連接數，并發數。
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 : 代表當前此時此刻線程緩存中有多少空閑線程。
Threads_connected : 代表當前已建立連接的數量，因為一個連接就需要一個線程，所以也可以看成當前被使用的線程數。
Threads_created : 代表從最近一次服務啟動，已創建線程的數量。
Threads_running : 代表當前激活的（非睡眠狀態）線程數。并不是代表正在使用的線程數，有時候連接已建立，但是連接處于 sleep 狀態，這里相對應的線程也是 sleep 狀態。
查看數據文件存放路徑
mysql show variables like %datadir%
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| datadir | /mysqldata/mysql/ |
+---------------+-------------------+
1 row in set (0.00 sec)
mysql讀到這里，這篇“Mysql 怎么查詢數據庫連接狀態及連接信息”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注丸趣 TV 行業資訊頻道。
正文完
