本文小編為大家詳細(xì)介紹“MySQL怎么查詢數(shù)據(jù)庫連接狀態(tài)及連接信息”，內(nèi)容詳細(xì)，步驟清晰，細(xì)節(jié)處理妥當(dāng)，希望這篇“Mysql怎么查詢數(shù)據(jù)庫連接狀態(tài)及連接信息”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學(xué)習(xí)新知識(shí)吧。
創(chuàng)新互聯(lián)基于成都重慶香港及美國(guó)等地區(qū)分布式IDC機(jī)房數(shù)據(jù)中心構(gòu)建的電信大帶寬，聯(lián)通大帶寬，移動(dòng)大帶寬，多線BGP大帶寬租用,是為眾多客戶提供專業(yè)多線BGP機(jī)房報(bào)價(jià)，主機(jī)托管價(jià)格性價(jià)比高，為金融證券行業(yè)服務(wù)器托管，ai人工智能服務(wù)器托管提供bgp線路100M獨(dú)享，G口帶寬及機(jī)柜租用的專業(yè)成都idc公司。
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數(shù)據(jù)庫的大小，那么可以通過下面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 為數(shù)據(jù)庫的編碼；
character_set_results 為結(jié)果集的編碼；
character_set_server 為數(shù)據(jù)庫服務(wù)器的編碼；
只要保證以上采用的編碼方式一樣，就不會(huì)出現(xià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ù)庫的編碼
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語句查看某個(gè)數(shù)據(jù)庫的表信息。
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 : 代表當(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)。
mysql> show variables like '%datadir%'; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | datadir | /mysqldata/mysql/ | +---------------+-------------------+ 1 row in set (0.00 sec) mysql>
讀到這里，這篇“Mysql怎么查詢數(shù)據(jù)庫連接狀態(tài)及連接信息”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
