MySQL查看数据库相关信息

客官°小女子只卖身不卖艺 2023-09-24 11:21 145阅读 0赞

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

1：查看显示所有数据库

mysql> show databases;

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

| Database |

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

| information\_schema |

| INVOICE |

| mysql |

| performance\_schema |

| test |

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

5 rows in set (0.00 sec)

mysql>

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

mysql> select database();

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

| database() |

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

| INVOICE |

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

1 row in set (0.00 sec)

mysql>

![MySQL查看数据库相关信息\_MySQL查看数据库相关信息][MySQL_MySQL]

3：查看数据库使用端口

mysql> show variables like 'port';

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

| Variable\_name | Value |

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

| port | 3306 |

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

1 row in set (0.00 sec)

![MySQL查看数据库相关信息\_MySQL查看数据库相关信息\_02][MySQL_MySQL_02]

4：查看当前数据库大小

例如，我要查看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>

5：查看数据库编码

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查看数据库相关信息\_03][MySQL_MySQL_03]

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)

![MySQL查看数据库相关信息\_MySQL查看数据库相关信息\_04][MySQL_MySQL_04]

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>

6：查看数据库的表信息

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'

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

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>

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

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)

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

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

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

| Variable\_name | Value |

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

| max\_connections | 151 |

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

1 row in set (0.00 sec)

mysql>

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

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查看数据库相关信息\_MySQL查看数据库相关信息\_05][MySQL_MySQL_05]

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

mysql> show variables like '%datadir%';

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

| Variable\_name | Value |

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

| datadir | /mysqldata/mysql/ |

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

1 row in set (0.00 sec)

mysql>

[MySQL_MySQL]: https://img-blog.csdnimg.cn/img_convert/1f47970bc066a66b8c0b6ad0d24c5530.png
[MySQL_MySQL_02]: https://img-blog.csdnimg.cn/img_convert/155343d07648d12b0d3ea600bccd24aa.png
[MySQL_MySQL_03]: https://img-blog.csdnimg.cn/img_convert/b3c3d44895f69989e4effc57ce4ffbe2.png
[MySQL_MySQL_04]: https://img-blog.csdnimg.cn/img_convert/5198ed6625551571e04aea262d0698d6.png
[MySQL_MySQL_05]: https://img-blog.csdnimg.cn/img_convert/7e4ced4131ded95f6b8647ad22240363.png