深入理解MySQL中的bin log、redo log、undo log
bin log（二进制日志）
什么是bin log？
记录数据库执行的写入性操作信息，以二进制的形式保存在磁盘中。
由服务层产生，所有储存引擎都支持。
bin log属于逻辑日志。
bin log日志有三种格式：STATMENT、ROW、MIXED。MySQL5.7.7之后默认是ROW。
简单普及一下逻辑日志和物理日志：
- 逻辑日志：记录的sql语句。
- 物理日志：记录的数据页变更。
bin log的使用场景？
主从复制：在
master端开启bin log，然后master将bin log发送到每个slave端，slave端重放bin log从而达到主从数据一致。简单画个图来理解一下MySQL的主从复制流程：
- master在准备提交事务之前，将变更记录到bin log中。
- slave启动一个IO线程来读取bin log中的事件，并记录到自己的ready log（中继日志）中。
- 同时slave还会启动一个SQL线程，读取ready log中的事件在备库中执行，从而实现备库的数据更新。
数据恢复：通过使用
mysqlbinlog工具来恢复数据。增量备份
如何开启bin log？
# 查看是否开启bin log，这里可以看到没有开启
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)
# 确认已开启bin log
mysql> 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文件，添加以下配置：
# 开启bin log日志
log-bin=binlog
# 配置server-id
server-id=1
如何查看以及修改每个bin log文件大小最大值？
查看本机的bin log文件大小最大值，可以看到是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
如何使用bin log恢复数据？
# 查看当前bin log位置
mysql> show master status;
# 也可以刷新日志，方便后面筛选
mysql> flush logs;
# 查看二进制日志列表
mysql> show binary logs;
bin log恢复数据：
# 根据事件位置恢复（--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日志文件路径
bin log日志解码以及导出到服务器中：
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：如果bin log为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` = 2
mysql> INSERT * FROM `quick-test-data`;
+------+--------+
| id | name |
+------+--------+
| 1 | 小明 |
+------+--------+
# 查看bin log日志，确定从哪里开始恢复（数据比较多，我在此抽取一部分，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 */ |
+---------------+-------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
### 分析此处的bin log日志 ###
# 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 | 小花 |
+------+--------+
如何正确删除bin log日志?
reset master命令：虽然可以清空所有bin log文件，但是会导致从库异常，主从架构下无法使用。expire_logs_days变量：通过该变量可以指定自动删除日期，如果日志过多，在删除时会有IO过高问题，可能导致性能抖动。purge命令：推荐方法，可以快速删除指定bin log。# 删除bin log到指定的文件为止 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记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如bin log)要快很多。
在此，我画一个简单的流程图方便理解
还有一种比较特殊的情况，数据页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工作原理？
在此，简单画一张图来理解一下：
- 事务A提交之前，会备份之前的数据到对应的undo buffer，然后undo log保存之前的记录数据，然后再将最新的数据持久化到ibd文件。
- 此时事务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
- 本文标签： MySQL
- 本文链接： http://www.lzhpo.com/article/173
- 版权声明： 本文由lzhpo原创发布，转载请遵循《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权
热门推荐
相关文章
关于我
