Mysql查询数据库状态及信息

蔚落 2023-09-24 11:18 89阅读 0赞

使用MySQL时，需要了解当前数据库的情况，例如当前的数据库大小、字符集、用户等等。下面总结了一些查看数据库相关信息的命令

1：查看显示所有数据库

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql]

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information\_schema |

| INVOICE |

| mysql |

| performance\_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

mysql>

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 1]

2：查看当前使用的数据库

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 2]

mysql> select database();

+------------+

| database() |

+------------+

| INVOICE |

+------------+

1 row in set (0.00 sec)

mysql>

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 3]

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息\_05][Mysql_Mysql_05]

3：查看数据库使用端口

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 4]

mysql> show variables like 'port';

+---------------+-------+

| Variable\_name | Value |

+---------------+-------+

| port | 3306 |

+---------------+-------+

1 row in set (0.00 sec)

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 5]

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息\_08][Mysql_Mysql_08]

4：查看当前数据库大小

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

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 6]

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查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 7]

查看数据所占的空间大小

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 8]

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查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 9]

查看索引所占的空间大小

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 10]

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查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 11]

5：查看数据库编码

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 12]

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)

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 13]

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息\_17][Mysql_Mysql_17]

character\_set\_client 为客户端编码方式；

character\_set\_connection 为建立连接使用的编码；

character\_set\_database 为数据库的编码；

character\_set\_results 为结果集的编码；

character\_set\_server 为数据库服务器的编码；

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

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 14]

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)

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 15]

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息\_20][Mysql_Mysql_20]

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

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 16]

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查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 17]

6：查看数据库的表信息

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 18]

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)

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 19]

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

select \* from information\_schema.tables where table\_schema='databasename';

查看某种具体表的信息

select \* from information\_schema.tables where table\_name='table\_name'

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

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 20]

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查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 21]

8: 查看某个具体用户的权限

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 22]

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查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 23]

9： 查看数据库的最大连接数

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 24]

mysql> show variables like '%max\_connections%';

+-----------------+-------+

| Variable\_name | Value |

+-----------------+-------+

| max\_connections | 151 |

+-----------------+-------+

1 row in set (0.00 sec)

mysql>

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 25]

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

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 26]

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)

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 27]

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

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

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

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

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息\_33][Mysql_Mysql_33]

11：查看数据文件存放路径

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 28]

mysql> show variables like '%datadir%';

+---------------+-------------------+

| Variable\_name | Value |

+---------------+-------------------+

| datadir | /mysqldata/mysql/ |

+---------------+-------------------+

1 row in set (0.00 sec)

mysql>

![Mysql查询数据库状态及信息\_Mysql查询数据库状态及信息][Mysql_Mysql 29]

[Mysql_Mysql]: https://img-blog.csdnimg.cn/img_convert/4d1f0711ca045534f2790ccee1887f88.gif
[Mysql_Mysql 1]: https://img-blog.csdnimg.cn/img_convert/af87447df3f703d7271244cf21ce2784.gif
[Mysql_Mysql 2]: https://img-blog.csdnimg.cn/img_convert/3054a070861142a8c241c81208fe21b9.gif
[Mysql_Mysql 3]: https://img-blog.csdnimg.cn/img_convert/36b129e1e0b2665acd366163ad0dc35d.gif
[Mysql_Mysql_05]: https://img-blog.csdnimg.cn/img_convert/2e3bf2e0c9e617ae2174310fb1d3c86b.png
[Mysql_Mysql 4]: https://img-blog.csdnimg.cn/img_convert/6c1c7701fb37a7ede40b17bcabe3caf9.gif
[Mysql_Mysql 5]: https://img-blog.csdnimg.cn/img_convert/23377b6b76cc0f3c69c78cc93a5fe7b7.gif
[Mysql_Mysql_08]: https://img-blog.csdnimg.cn/img_convert/6edb3fa362597e4d8097671de0691df1.png
[Mysql_Mysql 6]: https://img-blog.csdnimg.cn/img_convert/650bf41f1285e53683a415560e6ddc25.gif
[Mysql_Mysql 7]: https://img-blog.csdnimg.cn/img_convert/8512694a49d039c714f95d924e5bf827.gif
[Mysql_Mysql 8]: https://img-blog.csdnimg.cn/img_convert/979470154040bb51cc2237dd535498b4.gif
[Mysql_Mysql 9]: https://img-blog.csdnimg.cn/img_convert/030023bbf7532179f8e56a25f02cd068.gif
[Mysql_Mysql 10]: https://img-blog.csdnimg.cn/img_convert/977b2e43c4062f2f9701a713721cf59c.gif
[Mysql_Mysql 11]: https://img-blog.csdnimg.cn/img_convert/d4c5c33c71c2aa6ce28a69a7c35a4ba8.gif
[Mysql_Mysql 12]: https://img-blog.csdnimg.cn/img_convert/6e9bd105c57735db1a5fe139d740a5e5.gif
[Mysql_Mysql 13]: https://img-blog.csdnimg.cn/img_convert/4f190f6145b4e41569a8c62593e7510f.gif
[Mysql_Mysql_17]: https://img-blog.csdnimg.cn/img_convert/0b82903ba6d27037679e8fb1200c6d66.png
[Mysql_Mysql 14]: https://img-blog.csdnimg.cn/img_convert/4c401de91313c7675a2644a0354a323c.gif
[Mysql_Mysql 15]: https://img-blog.csdnimg.cn/img_convert/9bc874b42eb643a4fe02d24cce189d8a.gif
[Mysql_Mysql_20]: https://img-blog.csdnimg.cn/img_convert/9981adcc1645807d180da4b4e655b7a4.png
[Mysql_Mysql 16]: https://img-blog.csdnimg.cn/img_convert/419d54f55a65da76796daffd26d504e5.gif
[Mysql_Mysql 17]: https://img-blog.csdnimg.cn/img_convert/8999f4ec6232f1a207489c638ef3c584.gif
[Mysql_Mysql 18]: https://img-blog.csdnimg.cn/img_convert/ebbf29a8afee506f5378b0bb1cef8c49.gif
[Mysql_Mysql 19]: https://img-blog.csdnimg.cn/img_convert/60cdc396a3692d3d23c821488fc6cbed.gif
[Mysql_Mysql 20]: https://img-blog.csdnimg.cn/img_convert/cef684aa8fc51f3f197f58afa3ddb0c3.gif
[Mysql_Mysql 21]: https://img-blog.csdnimg.cn/img_convert/344c885918c6984e797e85cedd716e4c.gif
[Mysql_Mysql 22]: https://img-blog.csdnimg.cn/img_convert/1f498cb339a6016dac1b3b6918f1010d.gif
[Mysql_Mysql 23]: https://img-blog.csdnimg.cn/img_convert/c3ec9e0a67c0932be66616f388116c77.gif
[Mysql_Mysql 24]: https://img-blog.csdnimg.cn/img_convert/21ad6fc41f65f88ae3cbabf1c9ecd945.gif
[Mysql_Mysql 25]: https://img-blog.csdnimg.cn/img_convert/e5c98954cb0215682f79a65ea0ab811a.gif
[Mysql_Mysql 26]: https://img-blog.csdnimg.cn/img_convert/53aa4cc4314f46fc618056e6c9d4ff59.gif
[Mysql_Mysql 27]: https://img-blog.csdnimg.cn/img_convert/7ef9ed1f496fae833c2dd8081efe79a9.gif
[Mysql_Mysql_33]: https://img-blog.csdnimg.cn/img_convert/a7c41f6e7b561d579d43756d175f3ccf.png
[Mysql_Mysql 28]: https://img-blog.csdnimg.cn/img_convert/9e31aab718d56c350dd54a3cfb3a5988.gif
[Mysql_Mysql 29]: https://img-blog.csdnimg.cn/img_convert/c538ca9df681d2bff85ae32419bb7f60.gif