深入理解MySQL中的binlog、redo log、undo log

binlog（二进制日志）

什么是binlog？

记录数据库执行的写入性操作信息，以二进制的形式保存在磁盘中。

由server层产生，所有储存引擎都支持。

binlog属于逻辑日志。

binlog日志有三种格式：STATMENT、ROW、MIXED。MySQL5.7.7之后默认是ROW。

简单理解一下逻辑日志和物理日志的概念：

• 逻辑日志：记录的sql语句。
• 物理日志：记录的数据页变更。

binlog的使用场景？

• 主从复制：在master端开启binlog，然后master将binlog发送到每个slave端，slave端重放binlog从而达到主从数据一致。

  简单画个图来理解一下MySQL的主从复制流程：

  1. master在准备提交事务之前，将变更记录到binlog中。
  2. slave启动一个IO线程来读取binlog中的事件，并记录到自己的ready log（中继日志）中。
  3. 同时slave还会启动一个SQL线程，读取ready log中的事件在备库中执行，从而实现备库的数据更新。

• 数据恢复：通过使用mysqlbinlog工具来恢复数据。

如何开启binlog？

# 查看是否开启binlog，这里可以看到没有开启mysql> show variables like '%log_bin%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin                         | OFF   || log_bin_basename                |       || log_bin_index                   |       || log_bin_trust_function_creators | OFF   || log_bin_use_v1_row_events       | OFF   || sql_log_bin                     | ON    |+---------------------------------+-------+6 rows in set (0.05 sec)# 查看当前MySQL版本mysql> select version();+-----------+| version() |+-----------+| 5.7.36    |+-----------+1 row in set (0.20 sec)# 确认已开启binlogmysql> show variables like '%log_bin%';+---------------------------------+-----------------------------+| Variable_name                   | Value                       |+---------------------------------+-----------------------------+| log_bin                         | ON                          || log_bin_basename                | /var/lib/mysql/binlog       || log_bin_index                   | /var/lib/mysql/binlog.index || log_bin_trust_function_creators | OFF                         || log_bin_use_v1_row_events       | OFF                         || sql_log_bin                     | ON                          |+---------------------------------+-----------------------------+6 rows in set (0.03 sec)

如果没有开启，编辑/etc/my.cnf文件，添加以下配置：

# 开启binlog日志log-bin=binlog# 配置server-idserver-id=1

如何查看以及修改每个binlog文件大小最大值？

查看本机的binlog文件大小最大值，可以看到是1073741824字节，也就是1G。

mysql> show variables like 'max_binlog_size';+-----------------+------------+| Variable_name   | Value      |+-----------------+------------+| max_binlog_size | 1073741824 |+-----------------+------------+1 row in set (0.00 sec)

添加或修改MySQL的my.cnf文件：

max_binlog_size=2G

如何使用binlog恢复数据？

# 查看当前binlog位置mysql> show master status;# 也可以刷新日志，方便后面筛选mysql> flush logs;# 查看二进制日志列表mysql> show binary logs;

binlog恢复数据：

# 根据事件位置恢复（--start-position是开始位置，--stop-position是结束位置）mysqlbinlog --start-position=16275 --stop-position=16566 --database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot -p123456# 根据指定时间恢复（--start-datetime是开始时间，--stop-datetime是结束时间）mysqlbinlog --start-datetime="2022-02-17 12:00:00" --stop-datetime="2022-02-17 18:00:00" --database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot -p123456# --database是指定只恢复just-test数据库，/var/lib/mysql/binlog.000004是binlog日志文件路径

binlog日志解码以及导出到服务器中：

mysqlbinlog -vvv --base64-output=decode-rows --start-position=154 --stop-position=2150 --database=just-test /var/lib/mysql/binlog.000001 > /just-test.sql | mysql -uroot -p123456 -f

-vvv：显示出执行的SQL以及binlog_rows_query_log_events参数。

--base64-output=decode-rows：如果binlog为row格式必须使用该选项对日志进行解析，恢复数据时不能加该选项。

简单演示一下如何恢复被删掉的数据：

# 快速创建一个极其简单的数据表来用于测试mysql> CREATE TABLE `quick-test-data` (  `id` bigint(20) DEFAULT NULL,  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;# 分别插入小明和小花两条数据mysql> INSERT INTO `quick-test-data` (`id`, `name`) VALUES (1, '小明');mysql> INSERT INTO `quick-test-data` (`id`, `name`) VALUES (2, '小花');mysql> INSERT * FROM `quick-test-data`;+------+--------+| id   | name   |+------+--------+|    1 | 小明   ||    2 | 小花   |+------+--------+# 删掉小花mysql> DELETE FROM `quick-test-data` WHERE `id` = 2mysql> INSERT * FROM `quick-test-data`;+------+--------+| id   | name   |+------+--------+|    1 | 小明   |+------+--------+# 查看binlog日志，确定从哪里开始恢复（数据比较多，我在此抽取一部分，binlog.000004是刷新几次之后的日志文件，根据自己实际情况来）mysql> show binlog events in 'binlog.000004';+---------------+-------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Log_name      | Pos   | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                     |+---------------+-------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| binlog.000004 | 15636 | Anonymous_Gtid |         1 |       15701 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     || binlog.000004 | 15701 | Query          |         1 |       15984 | use `just-test`; CREATE TABLE `just-test`.`quick-test-data`  (  `id` bigint NULL,  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci || binlog.000004 | 15984 | Anonymous_Gtid |         1 |       16049 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     || binlog.000004 | 16049 | Query          |         1 |       16126 | BEGIN                                                                                                                                                                                                                    || binlog.000004 | 16126 | Table_map      |         1 |       16192 | table_id: 116 (just-test.quick-test-data)                                                                                                                                                                                || binlog.000004 | 16192 | Write_rows     |         1 |       16244 | table_id: 116 flags: STMT_END_F                                                                                                                                                                                          || binlog.000004 | 16244 | Xid            |         1 |       16275 | COMMIT /* xid=1088 */                                                                                                                                                                                                    || binlog.000004 | 16275 | Anonymous_Gtid |         1 |       16340 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     || binlog.000004 | 16340 | Query          |         1 |       16417 | BEGIN                                                                                                                                                                                                                    || binlog.000004 | 16417 | Table_map      |         1 |       16483 | table_id: 116 (just-test.quick-test-data)                                                                                                                                                                                || binlog.000004 | 16483 | Write_rows     |         1 |       16535 | table_id: 116 flags: STMT_END_F                                                                                                                                                                                          || binlog.000004 | 16535 | Xid            |         1 |       16566 | COMMIT /* xid=1091 */                                                                                                                                                                                                    || binlog.000004 | 16566 | Anonymous_Gtid |         1 |       16631 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     || binlog.000004 | 16631 | Query          |         1 |       16708 | BEGIN                                                                                                                                                                                                                    || binlog.000004 | 16708 | Table_map      |         1 |       16774 | table_id: 116 (just-test.quick-test-data)                                                                                                                                                                                || binlog.000004 | 16774 | Delete_rows    |         1 |       16826 | table_id: 116 flags: STMT_END_F                                                                                                                                                                                          || binlog.000004 | 16826 | Xid            |         1 |       16857 | COMMIT /* xid=1094 */                                                                                                                                                                                                    |+---------------+-------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+### 分析此处的binlog日志 #### 15701~15984：建表quick-test-data# 15984~16275：插入第一条数据，即小明# 16275~16566：插入第二条数据，即小花# 16566~16857：删除小花## 所以，因为数据表和小明都是存在的，所以，我们的start-position应该是16275，stop-position应该是16566## 16566~16857这里是删除小花的，所以，这个部分不能被包括进来，不然恢复之后又被删除掉了# 使用mysqlbinlog工具恢复被删除的小花数据# 在Linux命令行执行（注意不是在mysql命令行）[root@lzhpo-light ~]# mysqlbinlog --start-position=16275 --stop-position=16566 --database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot -p123456# 恢复之后的数据表mysql> select * from `quick-test-data`;+------+--------+| id   | name   |+------+--------+|    1 | 小明   ||    2 | 小花   |+------+--------+

如何正确删除binlog日志?

• reset master命令：虽然可以清空所有binlog文件，但是会导致从库异常，主从架构下无法使用。

• expire_logs_days变量：通过该变量可以指定自动删除日期，如果日志过多，在删除时会有IO过高问题，可能导致性能抖动。

• purge命令：推荐方法，可以快速删除指定binlog。

  # 删除binlog到指定的文件为止mysql> PURGE MASTER LOGS TO 'binlog.000004'# 删除指定日期之前的文件mysql> PURGE MASTER LOGS BEFORE '2022-02-18 18:30:00'

redo log（重做日志）

什么是redo log？

简单一句话：redo log就是记录数据页的变更。

redo log由InnoDB的存储引擎层产生，是InnoDB 存储引擎特有的。

redo log属于物理日志，因为，它记录的是数据页的变更。

因为，这种改变记录不是说一定要全部保存下来，所以，redo log采用的是大小固定，循环写入的方式，当从开头开始写到结尾的时候，又会回到开头继续写日志。

redo log记录日志的方式（原理）？

emm…图丑，但能帮助理解就好。

InnoDB的redo log文件大小是固定的，假设我这里redo log大小为4G，并且我划分为4个部分，redo log就会从ib_logfile_0开始写ib_logfile_1、ib_logfile_2、ib_logfile_3，直到4个部分都写满为止，再重新回到第一个部分ib_logfile_0开始写。

write position：当前记录的LSN（Log Sequence Number，日志序列号）位置。一边写，一边顺时针移动（向前移动）。

check point：当前数据页更改记录刷盘之后所处的LSN（Log Sequence Number，日志序列号）位置。一边写，一边顺时针移动（向前移动）。

check point到write position部分就是待落盘的数据页更改记录。

write position到check point部分就是redo log空闲的部分，用来记录新的操作日志。

当write position追上check point的时候，会先推动check point顺时针移动（向前移动），等到有空闲的redo log位置的时候再记录新的操作日志。

redo log如何进行异常崩溃数据恢复的？

每当启动InnoDB的时候，不管上次是正常关闭还是异常关闭，都会进行恢复操作。

因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。

在此，我画一个简单的流程图方便理解

还有一种比较特殊的情况，数据页LSN也会大于日志LSN：

当宕机的时候，正在处理check point刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时，也会出现数据页LSN也会大于日志LSN的情况。
这种情况的话，数据页剩下的这点redo log日志将不会重做，会正常启动。

如何查看以及修改redo log的大小？

mysql> show variables like 'innodb_log%';+------------------------------------+----------+| Variable_name                      | Value    |+------------------------------------+----------+| innodb_log_buffer_size             | 16777216 || innodb_log_checksums               | ON       || innodb_log_compressed_pages        | ON       || innodb_log_file_size               | 50331648 || innodb_log_files_in_group          | 2        || innodb_log_group_home_dir          | ./       || innodb_log_spin_cpu_abs_lwm        | 80       || innodb_log_spin_cpu_pct_hwm        | 50       || innodb_log_wait_for_flush_spin_hwm | 400      || innodb_log_write_ahead_size        | 8192     || innodb_log_writer_threads          | ON       |+------------------------------------+----------+11 rows in set (0.00 sec)

innodb_log_file_size就是redo log文件大小，50331648字节，也就是48MB。

要修改的话，打开MySQL的my.cnf文件，添加或编辑配置，比如，我修改为1G：

innodb_log_file_size=1G

如何自定义在事务提交的时候将log buffer中的日志刷入log file中的时机？

可以通过配置innodb_flush_log_at_trx_commit参数来更改刷入时机。

查看当前MySQL的配置时机：

mysql> show variables like 'innodb_flush_log_at_trx_commit';+--------------------------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| innodb_flush_log_at_trx_commit | 1     |+--------------------------------+-------+1 row in set (0.00 sec)

参数值解释：

• 0：延迟写，延迟刷。
  事务提交的的时候不会将redo log buffer中的日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到redo log file中。
  这样子可能会导致，当系统崩溃，可能丢失1秒的数据。

• 1：实时写，实时刷。
  事务每次提交都会将redo log buffer中的日志写入os buffer并调用fsync()刷到redo log file中。
  这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

• 2：实时写，延迟刷。
  事务每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到redo log file。

undo log（回滚日志）

什么是undo log？

MySQL（存储引擎需要能支持事务）在修改记录之前（提交事务之前），会把原先记录的值先保存起来（也就是写入到undo log），然后再修改（提交事务），当出问题的时候MySQL可以利用undo log来回滚事务，即恢复原先的记录值。

undo log由InnoDB的存储引擎层产生，是InnoDB 存储引擎特有的（和redo log一样）。

undo log属于逻辑日志。

MySQL的事务四大特性：原子性、隔离性、持久性、一致性。其中，原子性的底层就是靠undo log实现的。

undo log的作用？

1.事务回滚

前面我们说到，当出问题需要回滚事务的时候，可以利用MySQL的undo log来回滚事务，以保证事务的一致性。

比如：执行一条delete语句:

DELETE FROM sys_log WHERE id = 1;

undo log就会记录一条与它相反的日志，即记录它的insert语句。

update语句也是同理，记录与它相反的update语句。

执行一条update语句：

# 假设，之前username是小花UPDATE sys_user SET username = '小明' WHERE id = 1;

undo log就会记录与它相反的update语句：

UPDATE sys_user SET username = '小花' WHERE id = 1;

2.多版本控制（MVCC）

MVCC全称即Multi-Version Concurrency Control。

在MySQL的InnoDB存储引擎中，就是用undo log来实现MVCC的。

举个例子，当我们读取的某一行被其它事务锁定的时候，InnoDB可以从undo log中分析出该记录历史版本的数据，从而让我们可以读取到当前事务操作之前的数据（也就是快照读）。

普及一下快照读和当前读：

• 快照读：读取的历史版本数据，不会加锁。
  普通的select就是快照读。

• 当前读：读取的是最新版本, 会对读取的记录加锁, 以保证其它事务无法对此记录进行变更，保证安全性。
  属于当前读的：update、delete、insert、select … for update、select … lock in share mode（共享读锁）等等。

undo log的工作机制？

在MySQL的InnoDB存储引擎中，undo log是采用分段（segment）的方式保存的，简单来说，就是一种命名为rollback segment的回滚段，每个回滚段中有1024个undo log segment。

在MySQL5.5之后，能支持128个回滚段，也就是能支持128*1024个undo log segment，在此之前是只支持1个回滚段，也就是1024个undo log segment。

undo log工作原理？

在此，简单画一张图来理解一下：

1. 事务A提交之前，会备份之前的数据到对应的undo buffer，然后undo log保存之前的记录数据，然后再将最新的数据持久化到ibd文件。
2. 此时事务B查询，直接读取undo buffer缓存，因为这时候事务A还没提交且它需要回滚事务，所以，这时候事务B是不读取磁盘的，是直接从undo buffer缓存中读取。

参考文章：
https://www.linuxe.cn/post-393.html
https://juejin.cn/post/6860252224930070536
https://blog.csdn.net/Weixiaohuai/article/details/117867353