🖥️ Server 层日志

错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。查看日志位置：

1

show variables like '%log_error%';

查询日志

查询日志中记录了客户端的所有操作语句。（binlog 不包含查询数据的 SQL 语句）

默认情况下，查询日志是未开启的。

如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

1
2
3
4
5
6
7

# 该选项用来开启查询日志
# 可选值 0 关闭  1 开启
general_log=1

# 设置日志的文件名 
# 如果没有指定，默认的文件名为 host_name.log
general_log_file=mysql_query.log

开启了查询日志之后，/var/lib/mysql/ 目录下就会出现 mysql_query.log 文件。

所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。

慢查询日志

慢查询日志记录了：

• 执行时间超过 long_query_time
• 扫描记录数大于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。

如果需要开启慢查询日志，需要在 MySQL 的配置文件 /etc/my.cnf 中配置如下参数：

1
2
3
4
5

# 慢查询日志
slow_query_log=1

# 执行时间参数
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。

1
2
3
4
5

# 记录执行较慢的管理语句
log_slow_admin_statements=1

# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes= 1

🔢 bin log

二进制日志（bin log）是记录了所有的数据库表结构变更（DDL）和表数据修改（DML）的日志。

作用：

• 数据归档
• 灾难时数据恢复
• MySQL 主从复制

1

show variables like '%log_bin%';

• log_bin_basename

  • 当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文件名需要再该basename的基础上加上编号(编号从000001开始)。

• log_bin_index

  • binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。

binlog 格式

MySQL 服务器中提供了多种格式来记录二进制日志：

配置二进制日志的格式，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。

• 📃 statement

  • 基于 SQL 语句的日志记录，记录的是 SQL 语句，对数据进行修改的 SQL 都会记录在日志文件中。

• 📕 row （默认）

  • 基于行的日志记录，记录的是每一行的数据变更。

• 🖇️ mixed

  • 混合了 statememt 和 row 两种格式，默认采用statement，在某些特殊情况下会自动切换为 row 进行记录。

1

show variables like '%binlog_format%';

查看 bin log

日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看。

1
2
3
4
5
6
7

mysqlbinlog [参数] logfilename

参数选项：
  -d  指定数据库名称，只列出指定的数据库相关操作
  -o  忽略掉日志中的前 n 行命令
  -v  将行事件(数据变更)重构为SQL语句
  -vv 将行事件(数据变更)重构为SQL语句，并输出注释信息

清理 bin log

• reset master

  • 删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始

• purge master logs to 'binlog.000005'

  • 删除 000005 之前的所有日志

• purge master logs before 'yyyy-mm-dd hh24:mi:ss'

  • 删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志

可以在 mysql 的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

1

show variables like '%binlog_expire_logs_seconds%';

💽 InooDB 存储引擎日志

⏱️ redo log 重做日志

MySQL 更新数据操作的都是 Buffer Pool 内存中的缓存页，被修改过的脏页不会立即被刷新到磁盘中（随机磁盘 IO 性能低），为了防止断电等情况造成内存中的数据丢失，利用 redo log 记录数据页的更新操作，后续在合适的时间再修改磁盘中的数据。（WAL 技术）

redo log 是物理日志，记录了数据页做了什么修改。例如：对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新。

redo log 记录了事务完成后的数据状态，当事务提交时，只要将 redo log 持久化到硬盘即可（顺序磁盘 IO），不需要将 Buffer Pool 中的脏页持久化到硬盘（随机磁盘 IO）。

保证了事务 ACID 特性中的持久性，当数据库发生故障时，可以利用 redo log 恢复数据。

🔃 redo log 循环写

redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的。当随着服务的运行，Buffer Pool 中的脏页刷新到了磁盘中，redo log 中的记录就没有用了，可以擦除旧记录。

所以 redo log 采用了循环写的方式，数据写到末尾又会回到文件的开头，新数据可以覆盖无用的旧数据。

刷盘

产生的 redo log 并不是直接写入磁盘的，会先写到 redo log buffer 中，后续再持久化到磁盘。

redo log buffer 默认大小为 innodb_log_Buffer_size = 16MB，增加它的大小，可以让 MySQL 处理大事务不必写入到磁盘，进而提升 IO 性能。

redo log buffer 刷盘时机：

• InnoDB 的后台线程，每隔 1s，会将 redo log buffer 持久化到磁盘

• redo log buffer 中记录的写入量大于设置空间的一半时

• MySQL 正常关闭时

• 每次事务提交时，将 redo log buffer 中 redo log 持久化到磁盘 innodb_flush_log_at_trx_commit 控制

两阶段提交

redo log 和 bin log 持久化到硬盘，是两个独立的操作，可能会出现半成功的状态，导致两份日志之间的逻辑不一致。

为了避免这个问题，保证两个日志的一致性，使用了”两阶段提交“来解决。把单个事务的提交拆分成了两个阶段：

• Prepare 准备阶段

• Commit 提交阶段

将 redo log 写入拆成了两个步骤：prepare 和 commit，中间再穿插写入 bin log。

redo log prepare -> bin log -> redo log commit

当 bin log 成功持久化到磁盘，就被认为事务已经执行成功（即使 redo log 是 prepare 也没有关系）。

🔙 undo log 回滚日志

undo log 是一种用于撤销回滚的日志。InnoDB 更新记录前，会将操作前的数据记录的 undo 中，当事务回滚时，利用 undo log 进行回滚。

undo log 主要用于：

• 🎗️ 实现事务回滚，保障事务 ACID 中的原子性

• 🎗️ undo log 与 ReadView 配合，实现 MVCC

在 InnoDB 存储引擎对记录进行修改时，会把回滚需要的信息全部记录到 undo 中，如果发生回滚，就会读取 undo log，然后执行与之相反的操作。

• ➕ 插入数据，记录这条数据的主键，回滚时根据主键把该条记录删除

• ➖ 删除数据，记录这条数据的所有内容，回滚时将这些内容组成的记录插入表中

• 🖋️ 更新数据，

  • 更新了主键，会记录两条 undo log，一条删除的，一条更新的
  • 没有更新主键，记录这条数据更新列的旧值，回滚时将数据更新为旧值

版本链

一条记录的每次更新操作产生的 undo log 格式都会有：

• trx_id 事务 id

  记录了该记录是由哪一个事务修改的。

• roll_pointer 回滚指针

  回滚指针可以将 undo log 串连成一个链表，这个链表称为版本链。