mysql数据库巡检

最新推荐文章于 2025-06-25 16:31:46 发布

原创

最新推荐文章于 2025-06-25 16:31:46 发布 · 1.5k 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#经验分享

本文详细介绍了MySQL数据库巡检的二十四个关键步骤，包括查看数据库版本、端口、用户信息、字符集、安装目录、数据文件目录，以及深入到InnoDB引擎状态、进程状态、索引使用、慢查询、主从同步状态等重要指标的检查，帮助管理员全面了解数据库运行状况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 查看数据库版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.38-log |
+------------+
1 row in set (9.99 sec)

2. 数据库端口

mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 11110 |
+---------------+-------+
1 row in set (0.63 sec)

3. 查询数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |   |
| mdmdatafix         |
| mdmquery           |
| mysql              |
| percona            |
| performance_schema |    |
| sys                |
+--------------------+
9 rows in set (0.07 sec)

4. 数据库用户信息

mysql> use mysql;
Database changed
mysql> select user,host from user;
+-----------------+--------------+
| user            | host         |
+-----------------+--------------+
| mdmdatafixer    | 10%          |
| aiuap           | 10.%         |
+-----------------+--------------+

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'mdmdatafixer'@'10%';           |
| User: 'aiuap'@'10.%';                 |
+-----------------+--------------+

5. 数据库字符集

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.12 sec)

6. 数据库的安装目录

mysql> select @@basedir as basePath from dual ;
+-------------------+
| basePath          |
+-------------------+
| /usr/local/mysql/ |
+-------------------+
1 row in set (0.10 sec)

mysql> show variables like '%basedir%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| basedir       | /usr/local/mysql/ |
+---------------+-------------------+
1 row in set (6.20 sec)