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。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
内核启动时减少log的方式

内核启动时减少log的方式

内核引导选项 内核引导选项大体上可以分为两类：一类与设备无关、另一类与设备有关。与设备有关的引导选项多如牛毛，需要你自己阅读内核中的相应驱动程序源码以获取其能够接受的引导选项。比如，如果你想知道可以向 AHA1542 SCSI 驱动程序传递哪些引导选项，那么就查看 drivers/scsi/aha1542.c 文件，一般在前面 100 行注释里就可以找到所接受的引导选项说明。大多数选项是通过"_
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
MySQL-CRUD入门1

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
阅读更多...
Java 连接Sql sever 2008

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2