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

2023-12-03 15:08
文章标签 mysql log binlog database buffer 你家 redo 管管

本文主要是介绍MySQL binlog、redo log，请管管你家buffer，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

以前聊过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过程浅析

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

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

图片

往期文章回顾：

  1. 设计模式

  2. 招聘

  3. 思考

  4. 存储

  5. 算法系列

  6. 读书笔记

  7. 小工具

  8. 架构

  9. 网络

  10. Go语言

这篇关于MySQL binlog、redo log，请管管你家buffer的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/449878。

相关文章

Mysql数据库聚簇索引与非聚簇索引举例详解

Mysql数据库聚簇索引与非聚簇索引举例详解

《Mysql数据库聚簇索引与非聚簇索引举例详解》在MySQL中聚簇索引和非聚簇索引是两种常见的索引结构,它们的主要区别在于数据的存储方式和索引的组织方式,：本文主要介绍Mysql数据库聚簇索引与非... 目录前言一、核心概念与本质区别二、聚簇索引（Clustered Index）1. 实现原理（以 Inno
阅读更多...
sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符

sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符

《sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符》：本文主要介绍sqlserver、mysql、oracle、pgsql、sqlite五大... 目录一、转义符1.1 oracle1.2 sqlserver1.3 PostgreSQL1.4 SQLi
阅读更多...
MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2