MySQL binlog、redo log,请管管你家buffer

823 阅读10分钟

小知识,大挑战!本文正在参与“程序员必备小知识”创作活动。

以前聊过binlog和redo log,没有涉及binlog buffer和redo log buffer,主要是因为在核心脉络的理解上,buffer容易产生干扰。但buffer很重要,所以我们来看一下log和buffer之间的关系。

binlog简介

binlog用于复制,在主从复制中,从库利用主库上的binlog进行重播,实现主从同步。

三种格式

binlog可以设置三种格式:

STATEMENT

STATEMENT格式记录的是日志的逻辑SQL语句。

  • 优点:数据量小

  • 缺点:为了语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同的结果;某些语句和函数如UUID, LOAD DATA INFILE等在复制过程可能导致数据不一致甚至出错

ROW

在ROW格式下,二进制日志记录的不再是简单的SQL语句了,而是记录表的行更改情况。

  • 优点:日志内容会非常清楚的记录下每一行数据修改的细节

  • 缺点:可能会产生大量的日志,如update全表,则每一条更改都需要记录

MIXED

MIXED格式下,MySQL默认采用STATEMENT格式进行二进制日志文件的记录,但在一些情况下会使用ROW格式,可能的情况有:

1)表的存储引擎为NDB,这时对于表的DML操作都会以ROW格式记录。

2)用UUID()、USER()、CUR-RENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数。

3)使用了INSERT DELAY语句。

4)使用了用户定义函数(UDF)。

5)使用了临时表(temporary table)。

查看

日志格式

我们可用如下命令设置和查看binlog的格式。可按照会话级别设置,也可按照全局级别设置。

mysql> set @@session.binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.binlog_format;
+-------------------------+
| @@session.binlog_format |
+-------------------------+
| STATEMENT               |
+-------------------------+
1 row in set (0.00 sec)

mysql> set global binlog_format='ROW';

文件位置

mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.01 sec)

mysql> system ls -lh /usr/local/var/mysql/
total 447360
-rw-r-----    1 bytedance  admin   5.9K  9 20 12:55 binlog.000001
-rw-r-----    1 bytedance  admin    16B  8 13 21:12 binlog.index

binlog.index为二进制索引文件,binlog.000001为二进制日志文件

文件内容

binlog的文件内容为二进制,不能直接查看,须通过MySQL提供的工具mysqlbinlog。

查看STATEMENT格式的命令:

mysqlbinlog --start-position=5910 /usr/local/var/mysql/binlog.000001

查看ROW格式命令:

mysqlbinlog -vv --start-position=3353 --stop-position=4478 /usr/local/var/mysql/binlog.000001

样例如下,可以看到具体的语句。

~ mysqlbinlog -vv --start-position=3353 --stop-position=4478 /usr/local/var/mysql/binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#210813 21:12:05 server id 1  end_log_pos 125 CRC32 0xc29576e3  Start: binlog v 4, server v 8.0.23 created 210813 21:12:05 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
pW8WYQ8BAAAAeQAAAH0AAAABAAQAOC4wLjIzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAClbxZhEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigB43aVwg==
'/*!*/;
# at 3353
#210912 12:00:11 server id 1  end_log_pos 4478 CRC32 0x110c4a8c   Query thread_id=21  exec_time=0 error_code=0  Xid = 134
use `testdb`/*!*/;
SET TIMESTAMP=1631419211/*!*/;
SET @@session.pseudo_thread_id=21/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `trace_sp_info2` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sp_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '服务id',
  `sp_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务名称',
  `type` tinyint DEFAULT '0' COMMENT '服务',
  `type_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务类型名称',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 0未激活 1激活 2失效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
  `update_by` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_spid` (`sp_id`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='服务'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

redo log简介

redo log可用来做数据库crash recovery,是数据库保障数据安全的重要功能之一。它记录的关于每个页(Page)的更改的物理情况,一般默认包括2个日志文件,也可通过命令进行配置。

格式

redo log的日志文件,每个页面大小512字节,可通过参数调节。文件中的前四个页面,主要用于管理日志内容及整个数据库状态。在2KB(4*512字节)内容之后,就是正常的用来存储日志内容的部分。

格式如下图所示,图中的数值是指每一项在页面中的偏移位置。

图片

蓝色为4个header页面,黄色为普通页面。在普通页面中中,都会有12个字节用来存储页面头信息,这些信息主要用于管理这个页面本身的数据存储方式。

LOG_BLOCK_HDR_NO:4字节,一个与LSN有关系的块号。

LOG_BLOCK_HDR_DATA_LEN:2字节,表示当前页面中存储的日志长度,这个值一般都等于512-12(12位页面头的大小),因为日志在相连的块中是连续存储的,中间不会存在空闲空间,所以如果这个长度不为500,表示此时日志已经扫描完成(Crash Recovery的工作)。

LOG_BLOCK_FIRST_REC_GROUP:2字节,表示在当前块中是不是有一个MTR的开始位置。因为一个MTR所产生的日志量有可能是超过一个块大小的,那么如果一个MTR跨多个块时,这个值就表示了这个MTR的开始位置究竟是在哪一个块中。如果为0,则表示当前块的日志都属于同一个MTR;而如果其值大于0并且小于上面LOG_BLOCK_HDR_DATA_LEN所表示的值,则说明当前块中的日志是属于两个MTR的,后面MTR的开始位置就是LOG_BLOCK_FIRST_REC_GROUP所表示的位置。

LOG_BLOCK_CHECKPOINT_NO:4字节,存储的是检查点的序号。

这里简单说明一下LSN,LSN全名叫Log Sequence Number,用来精确记录日志位置信息,且是连续增长的。

上面所讲述的就是日志文件的组织结构,只有前面2KB是日志头,后面所有的都是一个个连续的、用来存储MTR产生的日志页面。

MTR

MTR也叫Mini-transaction,被称作“物理事务”,用于保证物理操作的完整性。比如在底层页面插入一条记录,如果只修改页头信息而没有修改页尾信息,那对这个页面来说是不完整的,这就需要用事务进行保证。

MTR有开始与提交阶段,保证物理事务一致性。其作用部位如下图所示:

图片

对页面page进行修改时,MTR会生成redo log record,当MTR提交的时候,会将redo log record拷贝到redo log buffer。而且MTR提交的时候,会给每个log record生成一个lsn,此lsn确定了其在log file中的位置。所以在redo log普通页面中的数据,都会有对应的lsn。

缓存

上面聊完binlog和redo log的基础知识,现在看一下和binlog cache、redo log cache的关系。

write与fsync

把buffer里的数据写到磁盘需要几步?主要分两步,write和fsync。

write:指把buffer中日志写入到文件系统的 page cache,这个操作并没有把数据持久化到磁盘,所以速度比较快,但断电丢失。

fsync:真正将数据持久化到磁盘,不会丢失。一般情况下,我们认为 fsync 才占磁盘的 IOPS。

当然对于buffer中的数据,mysql异常重启就会丢失。

图片

所以write和fsync的时机,控制了binlog和redo log的持久化。

binlog刷盘

binlog 的写入逻辑比较简单:事务执行过程中,先把日志写到 binlog cache,事务提交时,再把 binlog cache 里的完整事务写到 binlog 文件中。

binlog 是不能被拆开的,因此不论这个事务多大,也要确保一次性写入。

参数 sync_binlog 控制binlog的 write 与 fsync 时机:

  1. sync_binlog=0 的时候,表示每次提交事务都只 write,不 fsync。如果主机发生异常重启,会丢失未fsync的 binlog 日志。

  2. sync_binlog=1 的时候,表示每次提交事务都会执行 fsync;

  3. sync_binlog=N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才fsync。如果主机发生异常重启,会丢失最近N个事务的 binlog 日志。

redo log刷盘

事务在执行过程中,生成的 redo log 是要先写到 redo log buffer 的。

innodb_flush_log_at_trx_commit 参数控制redo log的write与fsync:

  1. 设置为 0 的时候,表示每次事务提交时都只是把 redo log 留在 redo log buffer 中,MySQL异常重启丢失数据;

  2. 设置为 1 的时候,表示每次事务提交时都将 redo log 直接持久化到磁盘;

  3. 设置为 2 的时候,表示每次事务提交时都只是把 redo log 写到 page cache,主机发生异常重启,丢失数据;

当然,对于binlog和redo log刷盘时机,除了参数外还受其它配置控制。如InnoDB 有一个后台线程,每隔 1 秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的 page cache,然后调用 fsync 持久化到磁盘。

但这些内容会影响我们理解,所以本次主要理解提交过程中的刷盘时机。

流程

以前画过更新一行数据的流程,如下图所示,里面没有涉及binlog cache和redo log cache,这次给增加上。在介绍两阶段提交的时候说过,时序上 redo log 先 prepare, 再写binlog,最后再把 redo log commit。

图片

在sync_binlog 和innodb_flush_log_at_trx_commit 都为1的情况下,更新流程如下图所示:

图片

对redo log的两个阶段prepare和commit需要说明一下:

  • 在prepare阶段执行write和fsync操作,redo log完成了持久化

  • 在commit阶段只执行write操作,这是因为redo log在prepare阶段已经持久化,只是状态未变更为commit。如果发生异常,根据binlog的写入情况,是能够实现数据恢复的。大家如果忘记这部分内容,可以重看一下InnoDB redo、undo、binlog,是如何合作的

所以大家能够发现,sync_binlog 和innodb_flush_log_at_trx_commit控制的就是write和fsync操作,哪些在提交过程中执行。

从流程图上看,双1情况,即sync_binlog 和innodb_flush_log_at_trx_commit都为1,MySQL持久化是最及时的。这意味一个事务完整提交前,需要等待两次刷盘,一次是 redo log(prepare 阶段),一次是 binlog,相应刷盘QPS也会增高。

对于这两个配置如何选择,看大家业务具体情况。

总结

最近事情比较多,写这篇文章用了差不多一个星期,完成之后还是挺开心的。对于自己以前一些模糊的知识点也梳理清晰了。希望这篇文章能够帮助到大家。

资料

  1. MySQL探秘(三):InnoDB的内存结构和特性

  2. MySQL共享表空间概念

  3. linux 同步IO: sync、fsync与fdatasync

  4. Mysql Binlog三种格式详细介绍

  5. MySQL binlog格式解析

  6. MySQL redo log 格式解析

  7. redo log日志文件

  8. mysqlbinlog基于某个偏移量进行数据的恢复(重做),--start-position,--stop-position的使用方法

  9. mysqlbinlog 技巧

  10. MySQL redo log及recover过程浅析

最后

大家如果喜欢我的文章,可以关注我的公众号(程序员麻辣烫)

我的个人博客为:shidawuhen.github.io/

往期文章回顾:

  1. 设计模式

  2. 招聘

  3. 思考

  4. 存储

  5. 算法系列

  6. 读书笔记

  7. 小工具

  8. 架构

  9. 网络

  10. Go语言