讲师中心 微信公众号

工具推荐

视频效率加速
剧云
剧云

中文短剧剧本制作平台

SoundView
SoundView

字幕擦除翻译、配音口型同步

立刻MV
立刻MV

低成本歌曲 MV、视频生成

Seko
Seko

AI短剧一键生成成片

关于binlog格式_MySQL

老伟姑娘_7274

老伟姑娘_7274

发布时间:2016-05-27 16:57:01

|

1800人浏览过

|

来源于php中文网

原创

写在前面的话

 

1、推荐用mixed,默认使用statement,基于上下文  set session/global binlog_format=mixed;

 

2、二进制日记录了数据库执行更改的操作,如Insert,Update,Delete等。不包括Select等不影响数据库记录的操作

 

3、MySQL记录的日志有三种模式:STATEMENT、ROW、MIXED

 

4、二进制主要的功能有:复制(Replication)和恢复(Recovery)

 

5、ROW与STATEMENT不同之处主要在于,服务器负载/一致性两个方面

 

复制的历史

 

mysql-3.2 开始支持基于命令的复制,也就是statement-based replication。

 

mysql-5.1 开始支持基于行的复制和混合复制,也就是row-based replication和mixed-based relication。

 

mysql-5.5 开始支持semi-synchronous的复制,也叫半同步复制,目的在于事务环境下保持主从一致。

 

mysql-5.6 开始支持并行复制,但是其并行只是基于schema的

 

mysql-5.7 开始支持基于组提交的并行复制

关于binlog格式_MySQL

 复制的基本过程如下:

 

(1)Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容

 

(2)Master接收到来自Slave的IO进程的请求后,通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息,返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置

 

(3)Slave的IO进程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中

 

(4)Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在自身执行

 

redolog 和 undolog

 

undolog 实现原子性。每当操作数据前,首先将数据备份到一个地方(这个存储数据备份的地方称为undolog)。然后再修改数据。如果出现了错误或者用户执行了rollback语句,可以利用undolog中的备份将数据恢复到事务开始之前的状态。

 

redolog 实现持久性。每当操作数据前,将数据真正更改时,先前相关操作写入重做日志。这样当断电,或者一些意外,导致后续任务无法完成时,系统恢复后,可以继续完成这些更改执行这些操作来恢复数据。 比如某一时刻数据库down机了,有两个事务,一个事务已经提交,另一个事务正在处理数据库重启的时候就要根据日志进行前滚及回退,把已提交事务的更改写到数据文件,未提交事务的更改恢复到事务开始前的状态。

 

Row based replication-RBR

 

日志中会记录成每一行数据被修改的形式,然后在slave端再对相同的数据进行回放,和其他大多数数据库系统的复制技能一样

 

优点:bin-log中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录那一条记录被修改了,修改成什么样了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节

 

1、任何情况都可以被复制,这对复制来说是最安全可靠的,不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题

 

2、多数情况下,从服务器上的表如果有主键的话,复制就会快了很多,执行 INSERT,UPDATE,DELETE 语句时锁更少,复制以下几种语句时的行锁更少

INSERT ... SELECT

包含 AUTO_INCREMENT 字段的 INSERT

 

没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句

 

缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容

 

比如有这样一条update语句:update product set owner_member_id = 100 where owner_member_id = 1 ,执行之后,日志中记录的不是这条update语句所对应额事件(MySQL以事件的形式来记录bin-log日志),而是这条语句所更新的每一条记录的变化情况,这样就记录成很多条记录被更新的很多个事件。

 

执行alter table之类的语句的时候,产生的日志量是惊人的。因为MySQL对于alter table之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动,实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中

 

binlog 大了很多,复杂的回滚时 binlog 中会包含大量的数据

 

执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,这会导致频繁发生 binlog 的并发写

 

UDF 产生的大 BLOB 值会导致复制变慢

 

注:采用 RBR 模式后,能处理很多原先出现的主键重复问题

 

Statement based replication-SBR

 

每一条会修改数据的sql都会记录到 master的bin-log中,而且记录每条语句在执行的时候的一些相关信息,也就是上下文信息

 

优点:不需要记录每一行数据的变化,减少bin-log日志量,节约IO,提高性能。主从版本可以不一样,从服务器版本可以比主服务器版本高

 

缺点:由于记录的执行语句,所以为了让这些语句在slave端也能正确执行,那么他还必须记录每条语句在执行的时候的一些相关信息,也就是上下文信息

 

另外就是,很多的新功能不断的加入,使MySQL得复制遇到了不小的挑战。目前已经发现的就有不少情况会造成MySQL的复制出现问题,主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现,比如:sleep() 函数在有些版本中就不能真确复制,在存储过程中使用了last_insert_id()函数,可能会使slave和master上得到不一致的id等等

 

1、不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候

 

2、调用具有不确定因素的 UDF 时复制也可能出问题,确定了的 UDF 也须要在从服务器上执行

文档库图标
文档库图标

文档库图标下载提供17张尺寸为128x128像素的PNG格式常用可爱图标免费下载。

下载

 

3、运用以下函数的语句也不能被复制:LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

 

4、INSERT ... SELECT 会产生比 RBR 更多的行级锁,复制须要执行全表扫描(WHERE 语句中没有运用到索引)的 UPDATE 时,须要比 RBR 请求更多的行级锁

 

6、对于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 语句会阻塞其他 INSERT 语句

 

7、对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响

 

8、存储函数(不是存储流程 )在被调用的同时也会执行一次 NOW() 函数,这个可以说是坏事也可能是好事

 

10、数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错

 

11、只支持REPEATABLE READ可重复读和以上的隔离级别

 

Mixed based replication-MBR

 

触发器(TRIGGER):

ROW

主上有,从上没有,复制正常,数据正常。

主上有,从上也有,复制正常,数据正常。

 

STATEMENT/MIXED

主上有,从上没有,复制正常,数据不正常,触发器里面的sql语句没有执行。

主上有,从上也有,复制正常,数据正常。

 

函数(FUNCTION):

ROW

主上有,从上没有,复制正常,数据正常。日志里记录的是UDF转换过的结果。

主上有,从上也有,复制正常,数据正常。

 

STATEMENT/MIXED

主上有,从上没有,复制报错。从不识别UDF函数。

主上有,从上也有,复制正常,数据正常。

 

存储过程(STORED PROCEDURES)

ROW

主上有,从上没有,复制正常,数据正常。记录的不是call sp,而是SP里面的sql。

主上有,从上也有,复制正常,数据正常。

 

STATEMENT/MIXED

主上有,从上没有,复制正常,数据正常。记录的不是call sp,而是SP里面的sql。

主上有,从上也有,复制正常,数据正常。

 

事件(event):

ROW

主上有,从上没有,复制正常,数据正常。记录的不是计划,而是EVENT里面的sql。

主上有,从上也有,复制正常,数据正常。(默认,DISABLE ON SLAVE),ALTER EVENT event_name ENABLE/DISABLE

 

STATEMENT/MIXED

主上有,从上没有,复制正常,数据正常。记录的不是计划,而是EVENT里面的sql。

主上有,从上也有,复制正常,数据正常。(默认,DISABLE ON SLAVE), ALTER EVENT event_name ENABLE/DISABLE

 

从5.1.8版本开始,MySQL提供了除Statement Level和Row Level之外的第三种复制模式:Mixed,实际上就是前两种模式的结合。在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。新版本中的Statment level还是和以前一样,仅仅记录执行的语句。而新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。

 

以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式

当DML语句更新一个NDB表时

 

当函数中包含 uuid(),user(),current_user(),found_rows(),row_count(),等不确定函数

 

2个及以上包含 AUTO_INCREMENT 字段的表被更新时

 

行任何 INSERT DELAYED 语句时

 

使用了用户定于的函数(UDF) 时

 

视图中必须要求运用 RBR 时,例如建立视图是运用了 UUID() 函数

 

使用了临时表(temporary table)

 

 新的基于行模式,用法是 binlog-row-image=minimal 设置该选项后,对于DML操作修改的数据,只有发生变化的字段才保存到binlog里面,(insert和delete还是全字段)。这提高了master和slave的复制吞吐量,减小了binlog的磁盘占用,网络资源和内存使用。

 

另外,针对系统库 mysql 里面的表发生变化时的处理准则如下:

 

1、如果是采用 INSERT,UPDATE,DELETE 直接操作表的情况,则日志格式根据 binlog_format 的设定而记录

 

2、如果是采用 GRANT,REVOKE,SET PASSWORD 等管理语句来做的话,那么无论如何 都采用 SBR 模式记录

 

3、blockhole引擎不支持row格式,ndb引擎不支持statement格式

热门AI工具

更多
WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

函子Zion

一款支持AI搭建、可视化编辑和多端发布的全栈无代码开发平台。

蛙蛙写作

一款AI论文写作工具,主要用于超级AI智能写作助手,适合需要提升相关任务效率的用户。

Loomy
Loomy Hot

一款AI工具,主要用于科大讯飞发布的桌面级 AI 助理,比 OpenClaw 更易用、更安全!,适合需要提升相关任务效率的用户。

Lovart
Lovart Hot

一款面向视觉设计创作的AI设计平台,可通过智能体和画布工作流辅助制作海报、Logo、网页、PPT及其他视觉内容。

音述AI
音述AI Hot

一款AI音频处理工具,主要用于音述AI是一个以“用声音述说故事”为核心的 AI 音乐创作与声音分享社区,适合需要提升相关任务效率的用户。

超级麦吉

一款通过数字员工协作完成调研、数据分析与文档制作的AI办公平台。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

0

2026.11.17

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

0

2026.11.17

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

0

2026.11.17

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

0

2026.11.17

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

0

2026.11.17

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

0

2026.11.17

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

180

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

120

2026.11.12

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

100

2026.11.12

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
DBeaver数据传输指南
DBeaver数据传输指南

共0课时 | 0人学习

Golangci-lint官方文档
Golangci-lint官方文档

共0课时 | 0人学习

Bootstrap Grid 栅格系统
Bootstrap Grid 栅格系统

共0课时 | 0人学习

关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn