专栏首页开发技术MySQL 日志之 binlog 格式 → 关于 MySQL 默认隔离级别的探讨

MySQL 日志之 binlog 格式 → 关于 MySQL 默认隔离级别的探讨

开心一刻

产品还没测试直接投入生产时,这尼玛...

背景问题

  在讲 binlog 之前,我们先来回顾下主流关系型数据库的默认隔离级别,是默认隔离级别,不是事务有哪几种隔离级别,别会错题意了

  1、Oracle、SQL Server 的默认隔离级别是什么,MySQL 的呢 ?

  2、为什么 MySQL 的默认隔离级别是 RR ?

    这个问题其实不太严谨,我们知道 MySQL 5.5 才将 InnoDB 代替 MyISAM 成为 MySQL 默认的存储引擎,而事务才有隔离级别一说,MyISAM 本就不支持事务,那么这个问题在 MySQL 5.5 之前根本就不成立。

    严谨点来说,应该是:为什么 MySQL 5.5 及之后版本的事务默认隔离级别是 RR,或者是:为什么 InnoDB 的事务默认隔离级别是 RR

  对于问题1,我相信大家都能回答的上来,Oracle,SqlServer 的默认隔离级别是 读已提交(Read Commited,简称 RC) ,而 MySQL 的默认隔离级别是 可重复读(Repeatable Read,简称 RR)

  但是对于问题2,相信有很多小伙伴就会支支吾吾了:呃...,这个...,昂昂昂昂昂,太久了我记忆都不太好了...

  调皮的小伙伴可能就开始岔开话题了:你讲 binlog 就讲 binlog 啦,扯什么默认隔离级别,难道 MySQL 的默认隔离级别还与 binlog 有关 ?

  想知道呀? 那得加钱

  具体它俩是不是有关,楼主也不知道,我们一起往下看

binlog 格式

  binlog 全称:binary log,即二进制日志,有时候也称归档日志,记录了对 MySQL 数据库执行了更改的所有操作,包括表结构变更(CREATE、ALTER、DROP TABLE…)、表数据修改(INSERT、UPDATE、DELETE...),但不包括 SELECT 和 SHOW 这类操作,因为这类操作对数据本身并没有修改;若更改操作并未导致数据库变化,那么该操作也会写入 binlog,例如

create table tbl_t1(name varchar(32));
insert into tbl_t1 values('zhangsan');
update tbl_t1 set name = 'lisi' where name = '123';
show master status\G;
show binlog events in 'mysql-bin.000002'\G;

  此时的:update tbl_t1 set name = 'lisi' where name = '123'; 并未引起数据库的变化,但还是被记录到了 binlog 中

  binlog 的格式有三种:STATEMENT、ROW、MIXED,一开始只有 STATEMENT,后面慢慢衍生出了 ROW、MIXED

  MySQL 5.1.5 之前 binlog 的格式只有 STATEMENT,5.1.5 开始支持 ROW 格式的 binlog,从 5.1.8 版本开始,MySQL 开始支持 MIXED 格式的 binlog

  MySQL 5.7.7 之前,binlog 的默认格式都是 STATEMENT,在 5.7.7 及更高版本中,binlog_format 的默认值才是 ROW

  三种格式的 binlog 各长什么样,它们有什么区别,各有什么优劣,我们往下看

  STATEMENT

    从 MySQL 第一个版本,到目前最新的 8.0.x,STATEMENT 一直坚挺在 binlog 的格式中,只是从 5.7.7 开始,它退居幕后,头把交椅给了 ROW

    binglog 与我们开发中的代码日志是不一样的,它包含两类文件

      索引文件:文件名.index,记录了哪些日志文件正在被使用,内容如下

      日志文件:文件名.00000*

        记录了对 MySQL 数据库执行了更改的所有操作

    因为 binlog 的日志文件是二进制文件,不能用文本编辑器直接打开,需要用特定的工具来打开,MySQL 提供了 mysqlbinlog 来帮助我们查看日志文件内容

    mysqlbinlog 可选参数很多, mysqlbinlog.exe --help

mysqlbinlog.exe Ver 3.3 for Win64 at x86
Copyright (c) 2001, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.

Usage: mysqlbinlog.exe [options] log-files
  -?, --help          Display this help and exit.
  --base64-output[=name]
                      Determine when the output statements should be
                      base64-encoded BINLOG statements: 'never' disables it and
                      works only for binlogs without row-based events;
                      'decode-rows' decodes row events into commented SQL
                      statements if the --verbose option is also given; 'auto'
                      prints base64 only when necessary (i.e., for row-based
                      events and format description events); 'always' prints
                      base64 whenever possible. 'always' is deprecated, will be
                      removed in a future version, and should not be used in a
                      production system.  --base64-output with no 'name'
                      argument is equivalent to --base64-output=always and is
                      also deprecated.  If no --base64-output[=name] option is
                      given at all, the default is 'auto'.
  --character-sets-dir=name
                      Directory for character set files.
  -d, --database=name List entries for just this database (local log only).
  --debug-check       Check memory and open file usage at exit .
  --debug-info        Print some debug info at exit.
  -D, --disable-log-bin
                      Disable binary log. This is useful, if you enabled
                      --to-last-log and are sending the output to the same
                      MySQL server. This way you could avoid an endless loop.
                      You would also like to use it when restoring after a
                      crash to avoid duplication of the statements you already
                      have. NOTE: you will need a SUPER privilege to use this
                      option.
  -F, --force-if-open Force if binlog was not closed properly.
                      (Defaults to on; use --skip-force-if-open to disable.)
  -f, --force-read    Force reading unknown binlog events.
  -H, --hexdump       Augment output with hexadecimal and ASCII event dump.
  -h, --host=name     Get the binlog from server.
  -l, --local-load=name
                      Prepare local temporary files for LOAD DATA INFILE in the
                      specified directory.
  -o, --offset=#      Skip the first N entries.
  -p, --password[=name]
                      Password to connect to remote server.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -R, --read-from-remote-server
                      Read binary logs from a MySQL server.
  -r, --result-file=name
                      Direct output to a given file.
  --server-id=#       Extract only binlog entries created by the server having
                      the given id.
  --set-charset=name  Add 'SET NAMES character_set' to the output.
  --shared-memory-base-name=name
                      Base name of shared memory.
  -s, --short-form    Just show regular queries: no extra info and no row-based
                      events. This is for testing only, and should not be used
                      in production systems. If you want to suppress
                      base64-output, consider using --base64-output=never
                      instead.
  -S, --socket=name   The socket file to use for connection.
  --start-datetime=name
                      Start reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  -j, --start-position=#
                      Start reading the binlog at position N. Applies to the
                      first binlog passed on the command line.
  --stop-datetime=name
                      Stop reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  --stop-position=#   Stop reading the binlog at position N. Applies to the
                      last binlog passed on the command line.
  -t, --to-last-log   Requires -R. Will not stop at the end of the requested
                      binlog but rather continue printing until the end of the
                      last binlog of the MySQL server. If you send the output
                      to the same MySQL server, that may lead to an endless
                      loop.
  -u, --user=name     Connect to the remote server as username.
  -v, --verbose       Reconstruct SQL statements out of row events. -v -v adds
                      comments on column data types.
  -V, --version       Print version and exit.
  --open-files-limit=#
                      Used to reserve file descriptors for use by this program.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
base64-output                     (No default value)
character-sets-dir                (No default value)
database                          (No default value)
debug-check                       FALSE
debug-info                        FALSE
disable-log-bin                   FALSE
force-if-open                     TRUE
force-read                        FALSE
hexdump                           FALSE
host                              (No default value)
local-load                        (No default value)
offset                            0
port                              3307
read-from-remote-server           FALSE
server-id                         0
set-charset                       (No default value)
shared-memory-base-name           (No default value)
short-form                        FALSE
socket                            E:/soft/mysql5.5.8/tmp/mysql.sock
start-datetime                    (No default value)
start-position                    4
stop-datetime                     (No default value)
stop-position                     18446744073709551615
to-last-log                       FALSE
user                              (No default value)
open-files-limit                  18432

    这些参数不做细讲,有兴趣的可自行去查阅,我们重点来关注日志文件的内容,执行 mysqlbinlog.exe ../data/mysql-bin.000004

    可以看到,对数据库执行了更改的操作

insert tbl_t1 values ('aaa'),('bbb');
update tbl_t1 set name = 'a1' where name = 'aaa';
delete from tbl_t1 where name = 'bbb';

    都是以明文形式的 SQL 记录在日志文件中,至于优缺点,我们看完另外两种格式之后再来比较

  ROW

    MySQL 5.7.7 及之后版本,binlog 的默认格式是 ROW,我们基于 5.7.30 版本,来看下 ROW 格式 binlog 内容是怎样的

    先产生数据库更改操作

    更改操作有

create table tbl_row(
    name varchar(32),
    age int
);
insert into tbl_row values('qq',23),('ww',24);
update tbl_row set age = 18 where name = 'aa';
update tbl_row set age = 18 where name = 'qq';
delete from tbl_row where name = 'aa';
delete from tbl_row where name = 'ww';

    master 当前正在写入的 binlog 文件: mysql-bin.000002 , position 从 28853929

    接下来我们看下日志文件中是怎么记录的,执行 mysqlbinlog.exe --start-position=2885 --stop-position=3929 ../data/mysql-bin.000002

    可以看到,表结构变更操作以明文形式的 SQL 记录在日志文件中(与 STATEMENT 一样),但表数据变更的操作却是以一坨一坨的密文形式记录在日志文件中,不便于我们阅读

    庆幸的是,mysqlbinlog 提供参数 -v 或 -vv 来解密查看,执行 mysqlbinlog.exe --base64-output=decode-rows -v --start-position=2885 --stop-position=3929 ../data/mysql-bin.000002

    INSERT 没什么好注意的,每一列都插入对应的值

insert into tbl_row values('qq',23),('ww',24);

对应

### INSERT INTO `my_project`.`tbl_row`
### SET
###   @1='qq'
###   @2=23
### INSERT INTO `my_project`.`tbl_row`
### SET
###   @1='ww'
###   @2=24

    UPDATE 就有需要注意的了,虽然我们修改列只有一列,条件列也只有一列,但是日志中记录的却是:修改列是全部列,条件列也是全部列,并且列值是具体的值,而没有 NOW()、UUID() 这样的函数

update tbl_row set age = 18 where name = 'qq';

对应

### UPDATE `my_project`.`tbl_row`
### WHERE
###   @1='qq'
###   @2=23
### SET
###   @1='qq'
###   @2=18

      表没有明确的指定主键,满足更新条件的记录也只有一条,大家可以去试试:明确指定主键且满足更新条件的记录有多条的情况,看看 binlog 日志是怎么记录的

    DELETE 与 UPDATE 一样,虽说条件列只有一个,但日志中记录的确实全部列

delete from tbl_row where name = 'ww';

对应

### DELETE FROM `my_project`.`tbl_row`
### WHERE
###   @1='ww'
###   @2=24

    相较 STATEMENT,显得更复杂,内容会多很多, 具体 ROW 有什么优点,我们往下看

  MIXED

    字面意思:混合,那它混合谁? 还能混合谁?只能混合 STATEMENT 和 ROW

    大多数情况下,是以 STATEMENT 格式记录 binlog 日志(因为 MySQL 默认隔离级别是 RR,而又很少有人去修改默认隔离级别),当隔离级别为 RC 模式的时候,则修改为 ROW 模式记录

    有些特殊场景,也是以 ROW 格式来记录的,就不区分 RR 和 RC 了(摘自:关于binary log那些事——认真码了好长一篇

    当然还有一个 NOW() ,说白了就是,只有具体的值才最可靠,其他依赖于上下文、环境的函数、系统变量都不可靠,因为它们会因上下文、环境而变化

    这个就不去展示具体的日志内容了,有兴趣的小伙伴自行去跑结果

  优缺点总结

    三种格式都已介绍完毕,相比之下,相信大家对它们各自的特点、优缺点已经有一定的了解了

    基于 binlog 的用途之一:主从复制(三个用途:主从复制、数据恢复、审计), 楼主给大家总结下它们的优缺点

    MIXED 的愿景是好的:结合 STATEMENT 和 ROW 两者的优点,产生一个完美的格式,但事与愿违,它还是会有一些问题

    相比于准确性而言,性能优先级会低一些(随着技术的发展,硬件性能已不再是不可接受的瓶颈),所以推荐使用 ROW 格式

MySQL 的 binlog 与其默认隔离级别 RR 的关系

  从上面 binlog 格式的内容来看,似乎与默认隔离级别 RR 没有半毛钱关系,先莫急,慢慢往下看

  RC,STATEMENT 下,各版 MySQL 执行表数据修改操作

    表引擎是 InnoDB,隔离级别是 RC,binlog_format=STATEMENT的统一前提下,我们分别看下 MySQl5.0.96、MySQL5.1.30、MySQL5.5.8、MySQL5.7.30 执行表数据更改操作的情况

    MySQl5.0.96 可以正常执行

    MySQL5.1.30 执行报错,提示

ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

    MySQL5.5.8、MySQL5.7.30 执行报错,都提示

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

    也就是说,MySQL5.1.30及之后,RC 隔离级别的 InnoDB 对 binlog_format 是有限制的,不能是 STATEMENT,否则表数据无法进行修改

MySQL 4.x 系列,由于官方不提供下载了,没法做测试,有 4.x 版本(或者5.1.21之前的5.1.x版本)的可以私信下我哦,不胜感激!

  不同 session 的操作记录在 binlog 中的记录顺序

    我们用两个 session 来执行更新操作,看下不同 session 的操作记录在 binlog 中的记录顺序有什么决定

    可以看到 update tbl_rr_test set age = 20 where id = 1; 先执行,后 commit, update tbl_rr_test set age = 21 where id = 2; 后执行,先 commit,日志中记录的是:先commit的记录在前面,后commit的记录在后面,与执行时间点无关;就单个 session 来说,好理解,执行顺序就是记录顺序;多个 session 之间的话,先 commit 的先记录

    主库对数据库的更改是按执行时间的先后顺序进行的,而 binlog 却是按 commit 的先后顺序记录的,理论上来说就会出现 MySQL Bug23051 中的示例问题

  默认隔离级别 RR 与 binlog 的关系

    我们来看看 MySQL Bug23051,里面有说到,MySQL 5.1 的早期版本,隔离级别是 RC、binlog 格式是STATEMENT时,InnoDB 的主从复制是有 bug 的(5.1.21 中修复),而 5.0.x 是没问题的,我们在 5.0.96 上跑下 Bug23051 中的例子

    可以看到,5.0.96 下的 InnoDB,在 RC 级别,binlog_format=STATEMENT 时, UPDATE t1 SET a=11 where b=2; 的事务未提交,则 UPDATE t1 SET b=2 where b=1; 的事务会被阻塞,那么从库复制的时候,数据是没问题的

    所以,综合前面的来看,从 MySQL5.0 开始,InnoDB 在 RC 级别,binlog_format=STATEMENT 时 主从复制是没有 bug 的(5.0没问题,5.1.21之前的5.1.x有问题,但官方不提供下载了,5.1.21及之后的版本不支持 RC 隔离级别下设置 binlog 为 STATEMENT)

    那么 binlog 与 默认级别 RR 的关系就清楚了,就是烟哥在【原创】互联网项目中mysql应该选什么事务隔离级别中说的这段话:

那Mysql在5.0这个版本以前,binlog只支持STATEMENT这种格式!而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的,因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别!

    也就是说,在 MySQL5.0之前,将 RR 作为默认隔离级别,是为了规避大部分主从复制的bug(具体什么bug,可详看 Bug23051 中的案例,或者【原创】互联网项目中mysql应该选什么事务隔离级别中的案例),然后一直被沿用了下来而已;为什么不是规避全部的主从复制 bug,因为在 RR 隔离级别、binlog_format=STATEMENT 下,使用系统函数(NOW()、UUID()等)时,还是会导致主从数据不一致

总结

  1、binlog 三个格式

    目前主流的 MySQL,binlog 格式有 3 种:STATEMENT、ROW、MIXED,从数据准确性考虑,推荐使用 ROW 格式

  2、binlog 默认格式

    MySQL 5.1.5 之前只支持 STATEMENT 格式的 binlog,5.1.5 开始支持 binlog_format=ROW,MySQL 5.7.7 之前,binlog 的默认格式都是 STATEMENT,在 5.7.7 及更高版本中,binlog_format的默认值才是 ROW

  3、主从复制 bug(InnoDB 引擎)

    MySQL 5.1.30及之后,InnoDB 下,开启 RC 隔离级别的话是不能启用 binlog_format=STATEMENT的

    RC、RR 隔离级别,binlog_format=MIXED,主从复制仍会有数据不一致的问题(受系统函数影响)

    RR 隔离级别,binlog_format=STATEMENT,主从复制仍会有数据不一致的问题(受系统函数影响)

    binlog_format=ROW,不管是 RC 隔离级别,还是 RR 隔离级别,主从复制仍不会有数据不一致的问题

  4、MySQL 为什么默认隔离级别是 RR

    为了规避 MySQL5.0 以前版本的主从复制问题,然后一直被沿用了下来而已

  5、引擎选择问题

    MySQL 5.6 及之后,InnoDB 做了大量的优化,性能并不比MyISAM低,说没特别的理由,基本可以放弃 MyISAM 了

参考

【原创】互联网项目中mysql应该选什么事务隔离级别

【原创】研发应该懂的binlog知识(上)

关于binary log那些事——认真码了好长一篇

mysql查看binlog日志

MySQL · 特性分析 · InnoDB对binlog_format的限制

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 详述MySQL事务及ACID特性的实现原理

    事务是 MySQL 等关系型数据库区别于 NoSQL 的重要方面,是保证数据一致性的重要手段。

    数据和云
  • 深入学习MySQL事务:ACID特性的实现原理

    事务是MySQL等关系型数据库区别于NoSQL的重要方面,是保证数据一致性的重要手段。本文将首先介绍MySQL事务相关的基础概念,然后介绍事务的ACID特性,并...

    Bug开发工程师
  • 深入学习MySQL事务:ACID特性的实现原理

    事务是MySQL等关系型数据库区别于NoSQL的重要方面,是保证数据一致性的重要手段。本文将首先介绍MySQL事务相关的基础概念,然后介绍事务的ACID特性,并...

    lyb-geek
  • 深入学习MySQL事务:ACID特性的实现原理

    来源:https://www.cnblogs.com/kismetv/p/10331633.html

    搜云库
  • mysql事务的实现原理

    此篇文章算是对mysql事务的一个总结,在了解这些之前我们先对mysql在执行的过程中 有一个整体的认识,如下图

    程序员小饭
  • MySQL系列一:掌握MySQL底层原理从学习事务开始

    面试时候,经常会被问到什么是事务、事务的特征、事务的隔离级别这些八股文问题,凭死记硬背通常也可回答的七七八八。但是面试官一旦换个角度问这些问题,有时候可能就语塞...

    IT大咖说
  • 探究 Canal EventParser 的设计与实现奥妙

    首先我们先从官方文档来看 EventParser 的整体设计,其架构设计图如下所示:

    丁威
  • Mysql为何使用可重复读(Repeatable read)为默认隔离级别?

    群里有小伙伴面试时,碰到面试官提了个很刁钻的问题:Mysql为何使用可重复读(Repeatable read)为默认隔离级别???

    Java宝典
  • MySQL这些题目你都会吗?

    1、MySQL偶尔会出现OOM(内存溢出)现象,导致MySQl服务重启,以下哪种方式能有效缓解OOM的情况发生( )

    MySQL轻松学
  • MYSQL 必考面试题10道(答案解释)

    MySQL偶尔会出现OOM(内存溢出)现象,导致MySQl服务重启,以下哪种方式能有效缓解OOM的情况发生()

    MySQL轻松学
  • MYSQL 必考面试题10道(多选)

    MySQL偶尔会出现OOM(内存溢出)现象,导致MySQl服务重启,以下哪种方式能有效缓解OOM的情况发生()

    MySQL轻松学
  • MySQL中binlog的三种格式

    在MySQL中,我们经常需要打开binlog来观察用户对某一个数据库的操作,binlog中记载着对用户数据库所做的所有修改类操作,例如delete,up...

    AsiaYe
  • MySQL学习笔记(五)Binlog解读

    我们在使用和维护MySQL时,一定经常听到binlog这个概念。binlog在主从复制,数据恢复等场景都有着重要作用。本篇文章主要介绍binlog的概念,功能及...

    scarlett学习手册
  • MySQL组复制(MGR)全解析 Part 9 MGR的要求和限制

    http://www.zhaibibei.cn/mysql/replication/tutorial10/

    bsbforever
  • MySQL5.7应当注意的参数

    简介: 本篇文章主要介绍 MySQL 初始化应当注意的参数,对于不同环境间实例迁移,这些参数同样应当注意。

    MySQL技术
  • MySQL的存储引擎与日志说明

      类型:ext2 3 4 ,xfs 数据。 不管使用什么文件系统,数据内容不会变化,不同的是,存储空间、大小、速度。

    惨绿少年
  • MySQL是如何实现事务的ACID

    最近在面试,有被问到,MySQL的InnoDB引擎是如何实现事务的,又或者说是如何实现ACID这几个特性的,当时没有答好,所以自己总结出来,记录一下。

    纪莫
  • MySQL高可用之主从复制

    最近公司业务量有点大,服务器I/O访问频率过高,之前单节点MySQL有点扛不住压力了,于是我找老板又搞了一台服务器,准备上MySQL的主从复制和读写分离,做多库...

    行百里er
  • Mysql binlog 之阿里canal 1、What is Canal?2、工作原理3、Canal使用场景代码集成方式:

    canal [kə'næl],中文翻译为 水道/管道/沟渠/运河,主要用途是用于 MySQL 数据库增量日志数据的订阅、消费和解析,是阿里巴巴开发并开源的,采用...

    用户5927264

扫码关注云+社区

领取腾讯云代金券