一文搞懂 MySQL 日志
前言
MySQL 的日志记录了运行的各种信息，是 MySQL 事务、性能、数据容灾、异常排查等的基础。本文将介绍 MySQL 一些关键日志的作用和原理。
MySQL InnoDB 引擎重要的三个日志：
| 日志 | 说明 |
|---|---|
| redo log | 重做日志，保证事务的持久性 |
| undo log | 回滚日志，来保证事务的原子性 |
| binlog | MySQL 的主从复制中同步数据 |
一、binlog
1. 简介
概述
binlog记录DDL 和 DML语句，但不包括SELECT、SHOW 等语句，简单说只要发上了表结构变化或表数据更新，都会产生binlog日志。
特点
undo log是二进制逻辑日志，记录内容是语句的原始逻辑，属于Server层，和引擎无关。只在事务提交时才写入，适用于数据备份和主从复制。
作用
- 灾难时的数据恢复；
- MySQL 的主从复制。
所在位置
通常默认的MySQL数据目录为/var/lib/mysql。
2. 记录格式
| 日志格式 | 记录内容 |
|---|---|
| Statement | 记录进行数据修改 SQL 语句。 |
| Row | 记录每一行的数据变更，占用较多空间。（默认） |
| Mixed | 前两者混合，判断是否可能引起数据不一致：可能则用Row 否则用Statement |
3. 写入机制
事务执行过程中，先把日志写到binlog cache。
事务提交的时候，再把binlog cache写到binlog文件中。
binlog cache是为了保证一个事务的所有操作能够不被拆开，一次性写入bin log。
binlog cache大小受binlog_cache_size参数控制。
binlog cache写入策略受sync_binlog参数控制。
4. 日志操作命令
4.1 查看启动情况
show variables like'%log_bin%';
4.2 日志查看
命令
日志是二进制存储的，无法直接读取，需要通过mysqlbinlog命令查看。
语法
mysqlbinlog [参数选项] logfilename
选项含义
-d：指定数据库名称，只列出指定的数据库相关操作。；-o：忽略掉日志中的前n行命令；-v：将行事件（数据变更）重构为SQL语句；-w：将行事件（数据变更）重构为SQL语句，并输出注样信息；
4.3 日志删除
对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：
| 指令 | 含义 |
|---|---|
| reset master | 删除全部日志 |
| purge master logs to 'binlog.xxx' | 删除xxx编号之前的日志 |
| purge master logs before 'yyyy-mm-dd hh:mm:ss' | 删除引号时间之前产生的日志 |
| show variables like '%binlog_expire_logs_seconds%'; | 配置日志过期时间，到期自动删除 |
二、redo log
1. 简介
概述
redo log，重做日志，记录的是事务提交时数据页的物理修改。
特点
物理日志，InnoDB存储引擎独有的，保证数据的持久性与完整性。记录内容是“在某个数据页上做了什么修改”，在事务过程中是不断写入。
大小是固定的，前面的内容会被覆盖。
2. 写入机制
-
当客户端提交数据修改时，会先去
Buffer Pool获取数据，若没有则查询出来放入Buffer Pool； -
生成
redo log放入Redolog Buffer，记录数据页的物理变化，此时redo log的状态是prepare； -
事务提交后，将
Redolog Buffer中的redo log刷新到磁盘持久化存储，此时redo log的状态是commit。
这样即使Buffer Pool中的脏页刷新到磁盘时出错，恢复时也可以通过redo log日志进行重新刷新。
脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
WAL：先写日志，再写磁盘的思想，叫做
WAL(Write Ahead Logging)。
3. 对比 binlog
| 对比维度 | redo log | bin log |
|---|---|---|
| 日志类型 | 物理日志 | 二进制逻辑日志 |
| 写入时机 | 事务过程中是不断写入 | 只在事务提交时才写入 |
| 位置 | InnoDB 磁盘中 | MySQL 的 Server 层 |
| 空间 | 固定空间，超出则覆盖 | 追加写入，可生成多份文件 |
4. 两阶段提交
了解了上面的binlog和redo log以后，你会发现， MySQL在执行更新操作的过程中，一次事务的完成均会记录着两个文件，区别见上面的对比表格。那么问题来了，两个文件到底是哪个先存？以及写入的时机有什么不同？
回答这两个问题之前，需要先考虑另外一个问题，这两个文件能否各存各的，会出问题吗？
答案是：不可以，会出现两个文件中数据不一致的问题，可能导致主从数据库数据不一致。
根据redo log的特点，在事务过程中是不断写入，而binlog只在事务提交时才写入，如果我们对某条数据执行了age 更改为 18的操作，此时原 age 为 17，redo log已经写入了数据，而undolog还没写入之前数据库崩溃了。
紧接着数据库重启后进行恢复，主数据库根据redo log恢复数据为age = 18，而从数据库根据binlog日志进行同步age = 17，这时就出现了不一致问题。
接着我们回答一下开始的两个问题，为了避免上述问题的产生，InnoDB存储引擎使用两阶段提交方案：
-
生成
redo log放入Redolog Buffer，记录数据页的物理变化，此时redo log的状态是prepare； -
事务提交后，并且，
binlog写入成功后，再将Redolog Buffer中的redo log刷新到磁盘持久化存储，此时redo log的状态commit； -
进行数据恢复时，若
redo log的状态是prepare，则有两种情况：binlog为空则进行数据回滚；binlog不为空，代表事务已commit，进行数据恢复，这个一般发生在binlog写入成功，但是redo log更改状态失败时。
三、undo log
1. 简介
概述
undo log，回滚日志，事务执行时，用于记录数据被修改前的信息，在异常发生时，会对已经执行的操作进行回滚。
作用
- 异常回滚，保证事务的原子性；
- 版本链用于
MVCC机制中；
特点
undo log是逻辑日志，可以认为：
- 当
delete一条数据时，它会插入一条对应的insert记录； - 当
update一条记录时，它会插入一条对象相反的记录。
当执行回滚时，就可以读取其中的记录进行操作。
分类
- 新增时 ： 指在
insert中产生的日志。这样的记录只对事务本身可见，对其他事务不可见，故可以在事务提交后直接删除。 - 修改时：
update或delete中产生的日志。该日志可能要作用于MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log版本链，使用后删除。
2. 版本链
不同事务或者相同事务对同一条记录进行修改，会使该记录的undo log生成一条记录版本的链表，链表头部是最新的旧记录，链表尾部是最早的旧记录。
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 表示最后一次插入或修改该行的事务 ID |
| DB_ROLL_PTR | 回滚指针，指向该行的 undo log，若该行未被更新，则为空 |
上述事务能够看到的版本链上的哪条历史数据，是由MVCC的ReadView来决定。
四、错误日志
最重要的日志之一，记录了当mysqld.log启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息，当数据库出现故障无法使用时，建议先看此日志。
日志默认打开，默认存放目录/var/log/，默认文件名mysqld.log。
如果找不到，可执行show variables like '%log_error%'查看。
五、查询日志
该日志记录了客户端所有的操作语句，默认关闭，开启需做以下配置：
- 修改
/etc/my.cnf文件； - 设置
general_log = 1，1 表示开启，0 表示关闭； - 设置日志的文件名，
general_log_file = mysql_query.log，未指定默认为host_name.log。
六、慢查询日志
该日志记录了所有执行时间超过参数long_query_time，且所记录数不小于min_examined_row_limit的所有 SQL 语句。默认关闭，开启需以下配置（根据所需）：
- 修改
/etc/my.cnf文件； - 设置
show_query_log = 1，1 表示开启，0 表示关闭； - 设置
long_query_time = 2，未指定默认为 10 秒； - 设置
long_show_admin_statements = 1，开启记录执行慢的管理语句； - 设置
long_queries_not_using_indexes = 1，开启记录执行较慢且未使用索引的语句；
参考
[1] B 站. 黑马邓老师. MySQL数据库入门到精通.
