1 Redo Log

1.1 简介

redo log 是属于引擎层的日志，称为重做日志。

持久化

当MySQL服务器意外崩溃或者宕机后，保证已经提交的事务持久化到磁盘中。它能保证对于已经COMMIT的事务产生的数据变更，即使是系统宕机崩溃也可以通过它来进行数据重做，达到数据的持久性，一旦事务成功提交后，不会因为异常、宕机而造成数据错误或丢失。

1.2 日志落地流程

先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝，产生脏数据
生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
默认在事务提交后将redo log buffer中的内容刷新到redo log file，对redo log file采用追加写的方式
定期将内存中修改的数据刷新到磁盘中（这里说的是那些还没及时被后台线程刷盘的脏数据）

1.2.1 日志写入

InnoDB 的 redo log 是固定大小的。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移。循环写
check point 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

1.2.2 落盘策略

当redo log空间满了之后又会从头开始以循环的方式进行覆盖式的写入。MySQL 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置，各参数含义如下：

0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，开启一个后台线程，每1s刷新一次到磁盘中 ;
1 ：表示每次事务提交时都将 redo log 直接持久化到磁盘，真正保证数据的持久性；
2：表示每次事务提交时都只是把 redo log 写到 page cache，具体的刷盘时机不确定。

除了上面几种机制外，还有其它两种情况会把redo log buffer中的日志刷到磁盘。

定时处理：有线程会定时(每隔 1 秒)把redo log buffer中的数据刷盘。
根据空间处理：redo log buffer 占用到了一定程度( innodb_log_buffer_size 设置的值一半)占，这个时候也会把redo log buffer中的数据刷盘。

2 Bin Log

2.1 简介

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。binlog 不会记录不修改数据的语句，比如Select或者Show

2.2 刷盘策略

事务执行过程中，先把日志写到 binlog cache（系统给每个线程分配一个binlog cache，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果大事务占用内存超过了这个参数规定的大小，就要暂存到磁盘。）
事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache

【Tips】

图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

参数sync_binlog配置如下：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

2.3 数据格式

案例分析

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

执行同一个SQL语句，观察不同格式的binlog记录的数据内容，具体的执行SQL如下

mysql> delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;

2.3.1 Row

server id 1，表示这个事务是在 server_id=1 的这个库上执行的。
Table_map event 显示了接下来要打开的表，map 到数字 226。现在我们这条 SQL 语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字，用于区分对不同表的操作。
Delete_rows event，用于定义删除的行为。binlog_row_image 的默认配置是 FULL，因此 Delete_event 里面，包含了删掉的行的所有字段的值。
最后的 Xid event，用于表示事务被正确地提交了。

应用场景：数据恢复

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据

我们就分别从 delete、insert 和 update 这三种 SQL 语句的角度，来看看数据恢复的问题。

即使我执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。
如果你是执行错了 insert 语句呢？那就更直接了。row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。
如果执行的是 update 语句的话，binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

其实，由 delete、insert 或者 update 语句导致的数据操作错误，需要恢复到操作之前状态的情况，也时有发生。MariaDB 的Flashback工具就是基于上面介绍的原理来回滚数据的。

2.3.2 Statement

第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’；
第二行是一个 BEGIN，跟第四行的 commit 对应，表示中间是一个事务；
第三行就是真实执行的语句了。可以看到，在真实执行的 delete 命令之前，还有一个“use ‘test’”命令。这条命令不是我们主动执行的，而是 MySQL 根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 test 库的表 t。use 'test’命令之后的 delete 语句，就是我们输入的 SQL 原文了。
最后一行是一个 COMMIT。你可以看到里面写着 xid=61。（XID负责关联redolog与binlog）

缺陷：主从不一致

因为 delete 带 limit，很可能会出现主备数据不一致的情况。比如上面这个例子：

如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；
如果使用的是索引 t_modified，那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。

由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的。但是row格式记录的是具体删除的数据，所以不会出现statement格式的binlog的主备不一致的情况。

2.3.3 Mixed

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

2.4 binlog/redolog异同

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

3 Undo Log

3.1 简介

undo log 主要用于保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚。比如一条 INSERT 语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时，undo log 也是 MVCC (多版本并发控制) 实现的关键。

4 Error Log

4.1 简介

错误日志（Error Log）是 MySQL 中最常用的一种日志，主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。

4.2 管理错误日志

4.2.1 启动/设置错误日志

在 MySQL 数据库中，默认开启错误日志功能。一般情况下，错误日志存储在 MySQL 数据库的数据文件夹下，通常名称为hostname.err。其中，hostname 表示 MySQL 服务器的主机名。

在 MySQL 配置文件中，错误日志所记录的信息可以通过 log-error 和 log-warnings 来定义

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

4.2.2 查看错误日志

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------------------------------------+
| Variable_name | Value                                                          |
+---------------+----------------------------------------------------------------+
| log_error     | C:\ProgramData\MySQL\MySQL Server 5.7\Data\LAPTOP-UHQ6V8KP.err |
+---------------+----------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

4.2.3 删除错误日志

在 MySQL 中，可以使用 mysqladmin 命令来开启新的错误日志，以保证 MySQL 服务器上的硬盘空间。mysqladmin 命令的语法如下：

mysqladmin -uroot -p flush-logs

执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为 filename.err-old。

5 General Log

5.1 简介

开启 general log 将所有到达MySQL Server的SQL语句记录下来。一般不会开启该功能，因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general log以供排障使用。相关参数一共有3：general_log、log_output、general_log_file.

show variables like 'general_log';  -- 查看日志是否开启
set global general_log=on; -- 开启日志功能

show variables like 'general_log_file';  -- 看看日志文件保存位置
set global general_log_file='tmp/general.lg'; -- 设置日志文件保存位置

show variables like 'log_output';  -- 看看日志输出类型  table或file
set global log_output='table'; -- 设置输出类型为 table
set global log_output='file';   -- 设置输出类型为file

6 Relay Log

6.1 简介

relay-log中继日志是连接master和slave的核心。binlog日志的结构由索引文件和binlog文件组成，其中binlog文件又包含通用头、提交头和事件体3个部分组成。relay-log的结构和binlog非常相似，只不过他多了一个master.info和relay-log.info的文件。

master.info记录了上一次读取到master同步过来的binlog的位置，以及连接master和启动复制必须的所有信息。
relay-log.info记录了文件复制的进度，下一个事件从什么位置开始，由sql线程负责更新。

6.2 主从复制

Master收到客户端请求语句，在语句结束之前向二进制日志写入一条记录，可能包含多个事件。
此时，一个Slave连接到Master，Master的dump线程从binlog读取日志并发送到Slave的IO线程
IO线程从master.info读取到上一次写入的最后的位置。
IO线程写入日志到relay-log中继日志，如果超过指定的relay-log大小，写入轮换事件，创建一个新的relay-log。
更新master.info的最后位置
SQL线程从relay-log.info读取进上一次读取的位置
SQL线程读取日志事件
在数据库中执行sql
更新relay-log.info的最后位置
Slave记录自己的binlog日志

7 Slow Query Log

7.1 简介

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

参数说明

slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录

7.2 管理日志

7.2.1 查看参数

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

7.2.2 开启慢查询

mysql> set global slow_query_log='ON';

7.2.3 设置慢查询文件位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

7.2.4 设置慢查询阈值

mysql> set global long_query_time=1;

附录

1 两阶段提交

1.1 简介

mysql> update T set c=c+1 where ID=2;

执行器和 InnoDB 引擎在执行上面这个简单的 update 语句时的内部流程如下：

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

1.2 为什么必须是两阶段提交

仍然用前面的 update 语句来做例子。

先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候恢复出来的这一行 c 的值就是 1，与原库的值不同。

如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

1.3 异常崩溃

时刻A 写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
时刻B 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。

1.4 binlog完整性判断

一个事务的 binlog 是有完整格式的：

statement 格式的 binlog，最后会有 COMMIT；
row 格式的 binlog，最后会有一个 XID event。

redolog、binlog 它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

2 组提交

组提交的出现主要就是为了降低磁盘的IOPS (每秒读写次数)。如下图所示，是三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160。

(日志逻辑序列号（log sequence number，LSN）的概念。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。)

trx1 是第一个到达的，会被选为这组的 leader；
等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；这时候 trx2 和 trx3 就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

【MySQL】日志

1 Redo Log

1.1 简介

1.2 日志落地流程

1.2.1 日志写入

1.2.2 落盘策略

2 Bin Log

2.1 简介

2.2 刷盘策略

2.3 数据格式

2.3.1 Row

2.3.2 Statement

2.3.3 Mixed

2.4 binlog/redolog异同

3 Undo Log

3.1 简介

4 Error Log

4.1 简介

4.2 管理错误日志

4.2.1 启动/设置错误日志

4.2.2 查看错误日志

4.2.3 删除错误日志

5 General Log

5.1 简介

6 Relay Log

6.1 简介

6.2 主从复制

7 Slow Query Log

7.1 简介

7.2 管理日志

7.2.1 查看参数

7.2.2 开启慢查询

7.2.3 设置慢查询文件位置

7.2.4 设置慢查询阈值

附录

1 两阶段提交

1.1 简介

1.2 为什么必须是两阶段提交

1.3 异常崩溃

1.4 binlog完整性判断

2 组提交