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

发布时间：2023-07-12 15:18:18 所属栏目：MySql教程来源：

导读：为大家详细介绍“Mysql怎么查询数据库连接状态及连接信息”，内容详细，步骤清晰，细节处理妥当，希望这篇“Mysql怎么查询数据库连接状态及连接信息”文章能帮助大家解决疑惑，下面跟着小编的思

为大家详细介绍“Mysql怎么查询数据库连接状态及连接信息”，内容详细，步骤清晰，细节处理妥当，希望这篇“Mysql怎么查询数据库连接状态及连接信息”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。

查看显示所有数据库

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>

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!