万字长文带你了解MySQL中的各种文件

柔情只为你懂 2022-10-10 01:47 13阅读 0赞

> 参考文献《MySQL技术内幕 InnoDB存储引擎 第二版》

MySQL中有各类文件，这些文件主要有：

*  **参数文件** ：告知MySQL实例启动时在哪里可以找到数据库文件，并指定初始化参数等。
 *  **日志文件** ：用来记录MySQL实例对某些条件做出响应时写入的文件，例如错误日志、二进制日志、慢查询日志等。
 *  **Socket文件** ：当用UNIX域套接字方式进行连接时需要的文件。
 *  **pid文件** ： MySQL实例等进程ID文件。
 *  **MySQL表结构文件**：用来存放MySQL表结构定义文件。
 *  **存储引擎文件**：MySQL表的存储引擎有多种，每个存储引擎都有自己的文件来存储各种数据。

先对MySQL文件的分类有个大体的印象，接下来我们一一来学习下各种文件。

# 1 参数文件 #

MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数。

## 1.1 参数文件里的参数 ##

数据库参数可以看成一个key-value对。例如`innodb_buffer_pool_size = 1G` 就是设置了InnoDB表的buffer pool大小为1G。

## 1.2 参数类型 ##

参数类型分为两类：

*  **动态参数**。MySQL实例运行中可以进行更改。
 *  **静态参数**。MySQL实例的整个生命周期内都不能修改。

可以通过Set命令修改global（全局）或session（当前会话）动态参数的参数值。

# 2 日志文件 #

日志文件记录了影响MySQL数据库的各种类型活动。

## 2.1 错误日志 ##

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA在遇到问题时首先该查看错误日志。可以通过`show variables like 'log_error'`来定位文件。

## 2.2 慢查询日志 ##

**long\_query\_time**

慢查询日志可以帮助我们定位存在问题的SQL语句，从而进行SQL层面的优化。MySQL中参数文件的`long_query_time`是慢查询的一个阈值，例如`long_query_time=10`时，超过10秒的SQL语句都是慢查询，并记录在慢查询日志文件中。  
值得注意两点：

*  MySQL会记录超过`long_query_time`的SQL语句到慢查询日志中，而小于等于`long_query_time`的SQL语句不会被记录下。
 *  MySQL5.1开始，`long_query_time`开始以微秒记录SQL语句的运行时间，这样可以更精确地记录SQL供分析。其实一条SQL运行0.5秒和0.05秒是非常不同的，前者可能进行了全表扫描，后者可能走了索引。

**long\_queries\_not\_using\_indexes**

MySQL5.6.5版本开始新增了一个`long_queries_not_using_indexes`参数，用来表示每分钟记录到慢查询日志且未使用索引的SQL语句次数。

**mysqldumpslow**

DBA可以通过`mysqldumpslow`命令来更好地分析慢查询日志文件，具体的用法就不一一列举咯，感兴趣的同学可以查看MySQL文档进行学习。

**慢查询日志表**

MySQL 5.1开始后可以将慢查询日志记录到一张表中，使得用户的查询更加方便。此表在mysql这个schema下，表的DDL语句如下：

create table slow_log
    (
        start_time     timestamp(6) default CURRENT_TIMESTAMP(6) not null on update CURRENT_TIMESTAMP(6),
        user_host      mediumtext                                not null,
        query_time     time(6)                                   not null,
        lock_time      time(6)                                   not null,
        rows_sent      int                                       not null,
        rows_examined  int                                       not null,
        db             varchar(512)                              not null,
        last_insert_id int                                       not null,
        insert_id      int                                       not null,
        server_id      int unsigned                              not null,
        sql_text       mediumblob                                not null,
        thread_id      bigint unsigned                           not null
    )
        comment 'Slow log' charset = utf8;

## 2.3 查询日志 ##

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log。同样，MySQL5.1开始，可以将查询日志记录到mysql 这个schema下的general\_log表中。

## 2.4 二进制日志 ##

二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括select和show这类操作，因为这类操作对数据本身没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作也可能会写入二进制日志中。例如update语句中的affected rows为0，虽然对数据没有啥改变，但也会被写入到二进制日志中。

如果用户想要记录select和show操作，那只能使用查询日志，而不是二进制日志。此外，二进制日志还包括了执行数据库更改操作的时间等额外信息，总而言之，二进制日志主要有以下作用：

*  恢复（recovery）：某些数据的恢复需要使用二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
 *  复杂（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（slave或standby）与一台MySQL数据库（master或primary）进行实时同步。
 *  审计（audit）：用户可以通过二进制文件中的信息来进行审计，判断是否有对数据库进行注入的攻击。

接下来，我们一起来看下二进制目录在哪。首先执行命令：`show variables like 'datadir';` 查看数据库所在的目录：  
![在这里插入图片描述][20210626115142744.png]

[20210626115142744.png]: /images/20221005/3e6d4e43177041049c444d8fc4398d0e.png