MySQL 日志

MySQL 常见日志有以下七种：

• binlog 日志
• redo log
• undo log
• slow_query_log 慢查询日志
• general query log 普通查询日志
• relay_log 中继日志
• 错误日志

---

binlog 是 Server 层的日志，主要用于归档，在主备同步、备份、恢复数据时发挥作用，常见格式有 row，mixed，statement 三种。一般通过配置 sync_binlog = 1 开启 binlog 持久化

binlog 保存逻辑格式日志，可以简单理解为执行 SQL 的语句，它包含了执行 SQL 的反向信息：delete 对应 insert（反之亦然）、update 对应上一个版本 update 等等。一般可以通过 mysqlbinlog 解析 binlog，实现基于时间点的恢复

binlog 日志记录并没有直接写入磁盘，而是先写入缓存，后续根据配置判断是否执行 fsync。binlog 日志会一直追加

fsync：同步内存中所有已修改的文件数据到储存设备

binlog 相关配置如下：

// binlog 文件名
log_bin_basename='xxx'
// 过期删除时间
expire_logs_days=x

常见 binlog 常用命令有以下几种：

// 查询配置信息
show variables like '%log_bin%';
// 查询 binlog 文件格式
how variables like 'binlog_format';
// 设置 binlog 文件格式
set global binlog_format= ' ROW、STATEMENT、MIXED'
// 查询日志列表
show binary logs;
// 查询当前日志写入状态
show master status;
// 删除 binlog 日志
reset master;

三种格式日志的区别如下所示：

• ROW：仅保存记录被修改细节，不记录上下文相关信息。优点在于可以清晰的记录每行数据的修改细节，包含所有特殊情况如存储过程、函数、触发器的调用等，不需要记录上下文。缺点在于文件繁琐：如果数据被修改n次，就会记录 N 条日志，如果表结构被修改，那每条数据都会记录一条日志，文件中存在大量的干扰内容。
• STATEMENT：记录每一条会修改数据的 sql。优点在于只记录执行语句的细节和上下文环境，避免记录每一行的变化，减少 binlog 日志量，节省 IO，提高性能。缺点在于必须记录上下文信息，以保证所有语句能在 slave 得到和 master 一样的结果
• MIXED：上述两者的混合，根据 sql 语句取舍选择合适格式的日志

MySQL 默认采用 ROW 格式日志，新版的 MySQL 对 ROW 做了优化，并不是所有的操作都记录 ROW 日志，如改表结构等就采用 STATEMENT 格式记录。

binlog 实现 MySQL 主从复制全过程：

1. master 节点将数据修改记录到 binlog 日志
2. slave 节点通过 IO 线程连接上 master，并请求指定位置之后的 binlog 日志
3. master 读取 binlog 日志返回给 IO 线程，返回信息附带 binlog 日志文件路径和名称
4. slave 将收到的日志添加到 relaylog 文件的末端，并记录 binlog 文件路径和名称，方便下一次接着同步
5. salve SQL 进程检测到 relaylog 新增了内容，解析内容，同步相应操作

---

redo log 是 InnoDB 特有的日志，主要用于实现事务的持久性，保证 crash-safe（异常安全）。

InnoDB 为了提高数据库的性能，首先将数据保存在内存而不是磁盘，后续空闲时从内存同步到磁盘。这里就涉及到同步前服务异常终止的情况，为了有办法恢复内存中未同步的数据，InnoDB 引入 redo log，每次先将数据库操作记录在 redo log，记录某一页做了哪些修改，记录完毕后再同步到内存。后续即使服务出问题，重启也可以通过日志恢复挂机时内存中还未同步到磁盘的数据。这种先写日志，后写磁盘的技术也叫 WAL（write ahead logging）技术。

redo log 大小是固定的，采用循环写入的方式记录：分别用两个指针指向同步的位置和写入的位置，此时同步指针到写入指针之间就是待同步的数据。当写入指针追上同步指针时，表示待同步的数据已经占满了，此时同步一部分数据即可，这里有点类似快慢指针的思想。我们可以通过将配置 innodb_flush_log_at_trx_commit 设置为1，打开 redo log 持久化

下面我简单描述 InnoDB 一次 update 操作全过程：

1. 执行器调用 InnoDB 接口查询数据，InnoDB 找到数据，从磁盘同步到内存，返回执行器
2. 执行器执行更新操作，通过 InnoDB 写入新数据
3. InnoDB 拿到新数据，执行更新同时将操作记录到 redo log 中，并更改 redo log 状态为 prepare，告知执行器在合适的时候提交事务
4. 执行器生成这个操作的 binlog，执行相应的刷盘操作（将 binlog 同步到磁盘）
5. 执行器提交事务，将 redo log 的状态改为 commit 状态，至此更新完毕

在一次更新操作中，redo log 被拆分为两个步骤：prepare 和 commit，也就是两阶段提交。如果不采用两阶段提交，可能会产生以下问题：

• 先写 redo log 后写 binlog，假设在写入 redo log 后 MySQL 异常重启，此时 binlog 没有写入。重启后由于 redo log 已写入，数据库数据没问题，但通过 binlog 同步后会发现少一步操作
• 先写 binlog 后写 redo log.，binlog 写入后 MySQL 异常重启，此时 redo log 没有写入。重启后由于 redo log 没有成功写入，认为事务无效，此时拿 binlog 去同步后会发现多一步操作

此时采用两阶段提交就可以通过判断 redo log 的状态判断事务是否有效，是否执行回滚操作。具体判断过程逻辑如下：

• redo log 不完整，事务无效，回滚
• redo log 完整，binlog 不完整，事务无效，回滚
• redo log 完整，binlog 完整，判断 redo log 标识，commit 表示已提交，提交事务
• redo log 完整，binlog 完整，redo log 标识 prepare，判断 binlog 是否完整，完整就提交、否则回滚

判断 binlog 日志是否完整有以下方法：

• row 格式：根据 XID event 标识
• statement 格式：根据 COMMIT 标识

XID 标识：redo log 和 binlog 结构中共同的数据字段，

---

undo log 也是 InnoDB 特有的日志，主要用于保证数据的原子性和 MVCC

undo log 和 redo log 不同，它是逻辑日志。当 delete 一条记录时，undo log 会记录一条 insert 记录，当 update 记录时，记录一条相反的 update 记录，当事务因为某些原因执行失败后，可以根据 undo log 回滚

在多版本并发访问中，当读取的某一行数据被其它事务锁定时，可以通过 undo log 分析上一个版本的数据信息，从而提供旧版本信息，实现非锁定一致性读取

undo log 本身也会产生 redo log，因为它还要保证自身的持久性，否则服务异常重启后，重启前正在执行的事务没法保证原子性

undo log 是采用段（segment）的方式来记录的，每个 undo 操作占用一个 undo log segment

事务提交后 undo log 不能立即删除，比如在 RR 隔离级别下，事务每次读取最新数据，如果该事务不结束，该版本就不能删除，也就是说 undo log 不能删除。这里采用先将 undo log 放入待清理的队列，通过 purge 线程判断是否存在其它事务正在使用 undo log 段中最新事务版本之前的信息，如果没有就删除该段，否则不能删除。

---

慢查询日志记录没有使用索引和执行时间超过参数 long_query_time 的SQL语句，主要用于帮助我们发现性能瓶颈。

常用慢查询日志操作如下：

// 开启慢查询日志
set global slow_query_log = 1;
// 查看慢查询日志是否已开启
show variables like "%slow_query_log%";
// 设置慢查询超时时间(单位秒)
set global long_query_time = 'xxx';
// 获取慢查询设置的时间
show variables like "long_query_time";
// 设置慢查询日志名称，默认名称：hostname-slow.log
set global log-slow-queries = 'xxx';
// 获取慢查询日志目录
show variables like "%slow%";
// 查看慢查询日志
cat 'xxx';

---

普通查询日志记录服务端收到的每一条命令，无论语法是否正确。由于所有命令都记录，开销巨大，MySQL 默认关闭

常用普通日志操作如下：

// 打开普通日志
set global general_log = on;
// 关闭普通日志
set global general_log = off;
// 设置通过表记录，表名默认gengera_log，表存储引擎默认CSV
set global log_output = 'table'; 
// 设置通过文件记录
set global log_output = 'file';
// 设置文件路径
set global general_log_file = 'xxx';

---

从服务器 IO 线程将主服务器的 binlog 日志读取过来记录到从服务器本地文件末尾，这里本地文件就是中继日志 relay_log。从服务器 SQL 线程通过中继日志同步数据，保证从服务器和主服务器数据一致。

中继日志常用配置有：

// 日志目录及名称
relay_log
// 记录主库 binlog 恢复位置和从库 relay_log 位置
relay_log_info_file
// 中继日志索引的位置和名称
relay_log_index
// 最大值，如果为0表示默认 1G，否则按配置来
max_relay_log_size
// 是否自动清空
relay_log_purge
// 如果从库挂机，可能中继文件不全，是否删除旧中继文件，重新从主库拉取，默认关闭
relay_log_recovery
// 写中继日志时，是否先写入系统缓冲区。默认关闭，打开更安全，但相应的 IO 操作较多，关闭时根据操作系统调度写入中继日志
sync_relay_log

---

错误日志记录 MySQL 启动、停止过程中信息和运行过程中遇到的错误情况，默认开启，文件名称 hostname.err，其中 hostname 表示主机名。

错误日志相关配置如下：

[mysqld]
// 定义是否启用错误日志功能和错误日志的存储位置
log-err=xxx
// 定义是否将警告信息也记录到错误日志中
log-warnings=

错误日志并非只记录错误信息，MySQL 启动时部分初始化信息也记录在错误文件，一般通过如下命令查看错误文件：

// 查看错误文件目录及文件名
SHOW VARIABLES LIKE 'log_error';
// 刷新错误日志
mysqladmin -u root -p flush-logs