MySQL - 文件

在一定程度上，本文仅仅是铺路（挖坑）。

MySQL 数据库正常运行依赖于各种文件，分为数据库文件和存储引擎文件,具体有以下几类：

• 参数文件

• pid 文件

• 日志文件

• 表结构定义文件

• 存储引擎文件

参数文件

MySQL 实例启动首先会去读取一个参数配置文件，用来初始化数据库参数和环境，通过命令 mysql--help | grep my.cnf 寻找即可。参数文件参数配置是以键值对的方式存储的吗，可以通过 SHOW VARIABLES
 查看数据库中所有参数。

MySQL 参数可以分为两类：动态参数和静态参数。动态参数意味着可以在 MySQL 实例运行期间更改生效，静态参数意味着更改这些参数在整个实例周期都不会生效。

pid 文件

MySQL 实例启动时会将自己的进程 ID 写入到 pid 文件中。

日志文件

MySQL 数据库最常见的日志有以下几种：

• 错误日志文件（error log）

• 慢查询日志（slow query log）

• 查询日志（log）

• 二进制日志（bin log）

错误日志

错误日志记录了 MySQL 实例从启动、运行到关闭整个生命周期信息，该日志除了错误日志还记录了一些警告或正确的信息，可以通过命令 SHOW VARIABLES LIKE
'log_error'
 定位文件。

mysql> SHOW VARIABLES LIKE 'log_error';


Variable_name: log_error
        Value: /var/log/mysqld.log

慢查询日志

慢查询日志可以定位可能存在问题的 SQL 语句，从而进行 SQL 层面优化。通过 long_query_time
 参数可以配置慢查询阈值，默认值是 10，单位秒。

默认情况 MySQL 是不会开启慢查询日志的，可以通过下面命令查询慢查询日志是否启用、慢查询日志路径和开启慢查询日志。

SHOW VARIABLES LIKE like 'slow_query_log%';


set global slow_query_log=ON;

还有一个与之相关的参数——log_queries_not_using_indexes
，用于记录未使用所有的 SQL 语句。

SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

用户可以通过 mysqldumpslow
 命令分析慢查询日志,帮助信息如下：

-s, 是表示按照何种方式排序
    c: 访问计数
    l: 锁定时间
    r: 返回记录
    t: 查询时间
    al:平均锁定时间
    ar:平均返回记录数
    at:平均查询时间


-t, 是top n的意思,即为返回前面多少条的数据;


-g, 后边可以写一个正则匹配模式,大小写不敏感的;

MySQL 5.1 版本支持 Table 类型慢查询日志，表为 mysql.slow_log，参数 log_output 指定慢查询输出的格式，默认为 FILE，修改为 TABLE 则变更为 TABLE 类型慢查询日志。

SHOW CREATE TABLE mysql.slow_log;


-- slow_log 建表语句
CREATE TABLE `slow_log` (
    `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) 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(11) NOT NULL,
    `rows_examined` int(11) NOT NULL,
    `db` varchar(512) NOT NULL,
    `last_insert_id` int(11) NOT NULL,
    `insert_id` int(11) NOT NULL,
    `server_id` int(10) unsigned NOT NULL,
    `sql_text` mediumblob NOT NULL,
    `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'


-- 设置慢查询日志类型为 TABLE
SET GLOBAL log_output='TABLE';

二进制日志

二进制日志（bin log）记录了对 MySQL 数据库执行的（可能产生）更改操作，二进制日志还包括了执行数据库更改操作的时间等其他额外信息，二进制日志主要包含以下几种作用：

• 恢复（recovery）
  ：某些数据的恢复需要二进制日志，例如，在一个数据库全备份文件恢复后，用户可以通过二进制日志进行 point-in-time 恢复。

• 复制（replication）
  ：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（Slave）与一台 MySQL 数据库（master）进行实时同步。

• 审计（audit）
  ：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

默认情况下二进制日志不会开启，可以通过配置参数 log-bin [=name] 可以启动二进制日志，此外还有一些参数配置如下：

• max_binlog_size

• binlog_cache_size

• sync_binlog

• binlog-do-db

• binlog-ignore-db

• log-slave-update

• binlog_format

max_binlog_size
 参数指定单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件。当使用事务的表存储引擎时，所有未提交的二进制日志会被记录到一个缓存中，等该事务提交时直接将缓存中的二进制日志写入二进制日志文件中，该缓冲的大小由 binlog_cache_size
 参数决定，默认 32K。sync_binlog=[ N ] 表示二进制日志每写缓冲 N 次同步到磁盘，如果 N 为 1 则意味着同步写磁盘，不会使用缓冲。 binlog-do-db
 和 binlog-ignore-db
 表示需要写入和忽略写入哪些库的日志，默认为空，表示需要同步所有库的日志到二进制日志。如果当前数据库是复制中的 Slave，它将不会从 Master 取得并执行的二进制日志写入自己的二进制日志文件中去，如果需要写入，设置 log-slave-update
 参数即可。 binlog_format
 影响记录二进制日志的格式，可选值有 STATEMENT、ROW 和 MIXED。STATEMENT 格式下二进制日志记录的是 SQL 语句，ROW 格式下二进制日志记录行记录的变更，和 STATEMENT 格式相比日志文件更大，MIXED 格式是 STATEMENT 格式和 ROW 格式混合记录。通常我们将参数 binlog_format 参数设置为 ROW 格式，这可以为恢复和复制带来更好的可靠性，但对磁盘空间消耗更大，在复制的时候，网络开销也将增大。

如其名二进制日志的文件格式为二进制，不可直接阅读，要查看二进制日志需要使用 MySQL 提供的 mysqlbinlog 工具。

mysqlbinlog [options] log_file ...

通过 --help 可以获取很多参数，其中要注意的，转储 bin log 貌似需要是 Linux 系统环境，Windows 系统下解析不出 SQL，可能是 base64 解码的问题（雾）。

查询日志

查询日志记录了所有对 MySQL 数据库请求信息，无论正确与否。

表结构定义文件

前文提过，MySQL 数据库是插件式存储引擎设计，但不论何种存储引擎，每个表都有一个 frm 后缀名的文件，该文件是表结构定义文件，与存储引擎无关。

存储引擎文件

不同的存储引擎有各自独有的文件，以主流的 InnoDB 存储引擎来说，这些文件包括表空间文件、重做日志文件。

表空间文件

InnoDB 存储引擎采用表空间存放设计，在默认配置下会有一个初始化大小为 10MB，名为 ibdata1 的文件，该文件就是默认的表空间文件，用户可以通过参数 innodb_data_file_path
 对其进行设置，用户可以通过多个文件组成一个表空间，同时制定文件的属性，如下：

[mysqld]
innodb_data_file_path= db/ibdata1:2000M;/dr2/ibdata2:2000M:autoextend

上例将 db/ibdata1 和 /dr2/ibdata2 两个文件组成一个表空间。若这两个文件位于不同的磁盘上，磁盘的负载可能会被平均，因此提高数据库的整体性能。其中，文件 ibdata1 的大小为 200MB，文件 ibdata2 大小为 2000MB，如果用完了这 2000MB，该文件可以自动增长（autoextend）。

可以通过设置参数 innodb_file_per_table
 参数可以将每个基于 InnoDB 存储引擎的表单独产生一个表空间，文件名为表名.ibd，这样不用将所有数据都存放于默认的表空间中。需要注意的是，这些表单独的表空间仅仅存储该表的数据、索引和插入缓冲等信息，其余信息仍然存放在默认的表空间中。InnoDB 存储引擎对于文件的存储方式如下：

与 InnoDB 不同，MyISAM 存储引擎表结构、数据和索引是分开存储的，表结构依然是 frm 文件，数据文件和索引文件分别存储在 .MYD 和 .MYI 后缀的文件中。

重做日志

redo log，也称重做日志，InnoDB 通过 redo log 保持数据的完整性（后面的文章会详细说）。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0、ib_logfile1。为了得到更高的可靠性，你可以设置多个镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上。日志组中每个重做日志文件的大小一致，并以循环方式使用。InnoDB 存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再切换到重做日志文件1中，它们由以下参数控制：

• innodb_log_file_size
  ：指定重做日志文件的大小。

• innodb_log_files_in_group
  ：指定了日志文件组中重做日志文件的数量，默认为 2。

• innodb_mirrored_log_groups
  ：指定了日志镜像文件组的数量，默认为 1，代表只有一个日志文件组，没有镜像。

• innodb_log_group_home_dir
  ：指定了日志文件组所在路径，默认在数据库路径下。

重做日志文件大小对 InnoDB 存储引擎影响很大，如果设置得太大在恢复数据耗时较长，如果设置太小可能导致一个事务需要多次切换重做日志文件，而且重做日志太小还会导致频繁发生 async checkpoint，导致性能抖动。重做日志有一个 capacity 变量，该值代表了最后的检查点不能超过的阈值，如果超过则必须将缓冲池（innodb buffer pool）中刷新列表（flush list）中的部分脏页刷回磁盘。

回滚日志

与 redo log 对应的还有一个 undo log,也称回滚日志。undo log 存储的是逻辑 SQL，即恢复到事务开启前状态的操作过程。undo log 的第一个作用便是回滚，另外还有一个作用是实现多版本并发控制（简称：MVCC，后文详细说明），实现事务的原子性。

公众号文章同步github。

本文地址：github.com/lazecoding/Note/blob/main/note/articles/mysql/文件.md