网站的建设方式有哪些,网站备案 几年,网络营销策划是什么意思,windows2008做网站开心一刻 image 产品还没测试直接投入生产时#xff0c;这尼玛... 背景问题 再讲 binlog 之前#xff0c;我们先来回顾下主流关系型数据库的默认隔离级别#xff0c;是默认隔离级别#xff0c;不是事务有哪几种隔离级别#xff0c;别会错题意了 1、Oracle、SQL Server 的默… 开心一刻 image 产品还没测试直接投入生产时这尼玛... 背景问题 再讲 binlog 之前我们先来回顾下主流关系型数据库的默认隔离级别是默认隔离级别不是事务有哪几种隔离级别别会错题意了 1、Oracle、SQL Server 的默认隔离级别是什么MySQL 的呢 2、为什么 MySQL 的默认隔离级别是 RR 这个问题其实不太严谨我们知道UMySQL 5.5 才将 InnoDB 代替 MyISAM 成为 MySQL 默认的存储引擎而事务才有隔离级别一说MyISAM 本就不支持事务那么这个问题在 MySQL 5.5 之前根本就不成立。 严谨点来说应该是为什么 MySQL 5.5 及之后版本的事务默认隔离级别是 RR或者是为什么 InnoDB 的事务默认隔离级别是 RR 对于问题1我相信大家都能回答的上来OracleSqlServer 的默认隔离级别是 都已提交( Read Commited简称 RC) 而 MySQL 的默认隔离级别是 可重复读( Repeatable Read 简称 RR) 但是对于问题2相信有很多小伙伴就会支支吾吾了呃...这个...昂昂昂昂昂太久了我记忆都不太好了... 调皮的小伙伴可能就开始岔开话题了你讲 binlog 就讲 binlog 啦扯什么默认隔离级别难道 MySQL 的默认隔离级别还与 binlog 有关 想知道呀 那得加钱 image 具体它俩是不是有关楼主也不知道我们一起往下看 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 的格式只有 STATEMENT5.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.xSTATEMENT 一直坚挺在 binlog 的格式中只是从 5.7.7 开始它退居幕后头把交椅给了 ROW binglog 与我们开发中的代码日志是不一样的它包含两类文件 索引文件文件名.index记录了哪些日志文件正在被使用内容如下 image 日志文件文件名.00000* image 记录了对 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 bebase64-encoded BINLOG statements: never disables it andworks only for binlogs without row-based events;decode-rows decodes row events into commented SQLstatements if the --verbose option is also given; autoprints base64 only when necessary (i.e., for row-basedevents and format description events); always printsbase64 whenever possible. always is deprecated, will beremoved in a future version, and should not be used in aproduction system. --base64-output with no nameargument is equivalent to --base64-outputalways and isalso deprecated. If no --base64-output[name] option isgiven at all, the default is auto.--character-sets-dirnameDirectory for character set files.-d, --databasename 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-binDisable binary log. This is useful, if you enabled--to-last-log and are sending the output to the sameMySQL server. This way you could avoid an endless loop.You would also like to use it when restoring after acrash to avoid duplication of the statements you alreadyhave. NOTE: you will need a SUPER privilege to use thisoption.-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, --hostname Get the binlog from server.-l, --local-loadnamePrepare local temporary files for LOAD DATA INFILE in thespecified 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, inorder of preference, my.cnf, $MYSQL_TCP_PORT,/etc/services, built-in default (3306).--protocolname The protocol to use for connection (tcp, socket, pipe,memory).-R, --read-from-remote-serverRead binary logs from a MySQL server.-r, --result-filenameDirect output to a given file.--server-id# Extract only binlog entries created by the server havingthe given id.--set-charsetname Add SET NAMES character_set to the output.--shared-memory-base-namenameBase name of shared memory.-s, --short-form Just show regular queries: no extra info and no row-basedevents. This is for testing only, and should not be usedin production systems. If you want to suppressbase64-output, consider using --base64-outputneverinstead.-S, --socketname The socket file to use for connection.--start-datetimenameStart reading the binlog at first event having a datetimeequal or posterior to the argument; the argument must bea date and time in the local time zone, in any formataccepted by the MySQL server for DATETIME and TIMESTAMPtypes, for example: 2004-12-25 11:25:56 (you shouldprobably use quotes for your shell to set it properly).-j, --start-position#Start reading the binlog at position N. Applies to thefirst binlog passed on the command line.--stop-datetimenameStop reading the binlog at first event having a datetimeequal or posterior to the argument; the argument must bea date and time in the local time zone, in any formataccepted by the MySQL server for DATETIME and TIMESTAMPtypes, for example: 2004-12-25 11:25:56 (you shouldprobably use quotes for your shell to set it properly).--stop-position# Stop reading the binlog at position N. Applies to thelast binlog passed on the command line.-t, --to-last-log Requires -R. Will not stop at the end of the requestedbinlog but rather continue printing until the end of thelast binlog of the MySQL server. If you send the outputto the same MySQL server, that may lead to an endlessloop.-u, --username Connect to the remote server as username.-v, --verbose Reconstruct SQL statements out of row events. -v -v addscomments 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-namevalue)
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 18432View Code这些参数不做细讲有兴趣的可自行去查阅我们重点来关注日志文件的内容执行 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 从 2885 倒忙 3929 接下来我们看下日志文件中是怎么记录的执行 mysqlbinlog.exe -- start-position2885 --stop-position3929 ../data/mysql-bin.000002 可以看到表结构变更操作以明文形式的 SQL 记录在日志文件中与 STATEMENT 一样但表数据变更的操作却是以一坨一坨的密文形式记录在日志文件中不便于我们阅读 庆幸的是mysqlbinlog 提供参数 - v 或 - vv 来解密查看执行 mysqlbinlog.exe -- base64-outputdecode-rows -v --start-position2885 --stop-position3929 ../data/mysql-bin.000002 INSERT 没什么好注意的每一列都插入对应的值 insert into tbl_row values(qq,23),(ww,24);对应### INSERT INTO my_project.tbl_row
### SET
### 1qq
### 223
### INSERT INTO my_project.tbl_row
### SET
### 1ww
### 224View CodeUPDATE 就有需要注意的了虽然我们修改列只有一列条件列也只有一列但是日志中记录的却是修改列是全部列条件列也是全部列并且列值是具体的值而没有 NOW()、UUID() 这样的函数 update tbl_row set age 18 where name qq;对应### UPDATE my_project.tbl_row
### WHERE
### 1qq
### 223
### SET
### 1qq
### 218View Code表没有明确的指定主键满足更新条件的记录也只有一条大家可以去试试明确指定主键且满足更新条件的记录有多条的情况看看 binlog 日志是怎么记录的 DELETE 与 UPDATE 一样虽说条件列只有一个但日志中记录的确实全部列 delete from tbl_row where name ww;对应### DELETE FROM my_project.tbl_row
### WHERE
### 1ww
### 224View Code相较 STATEMENT显得更复杂内容会多很多 具体 ROW 有什么优点我们往下看 MIXED 字面意思混合那它混合谁 还能混合谁智能混合 STATEMENT 和 ROW 大多数情况下是以 STATEMENT 格式记录 binlog 日志因为 MySQL 默认隔离级别是 RR而又很少有人去修改默认隔离级别当隔离级别为 RC 模式的时候则修改为 ROW 模式记录 有些特殊场景也是以 ROW 格式来记录的就不区分 RR 和 RC 了摘自 关于binary log那些事——认真码了好长一篇 image 当然还有一个 NOW() 说白了就是只有具体的值才最可靠其他依赖于上下文、环境的函数、系统变量都不可靠因为它们会因上下文、环境而变化 这个就不去展示具体的日志内容了有兴趣的小伙伴自行去跑结果 优缺点总结 三种格式都已介绍完毕相比之下相信大家对它们各自的特点、优缺点已经有一定的了解了 基于 binlog 的用途之一主从复制三个用途主从复制、数据恢复、审计 楼主给大家总结下它们的优缺点 image MIXED 的愿景是好的结合 STATEMENT 和 ROW 两者的优点产生一个完美的格式但事与愿违它还是会有一些问题 相比于准确性而言性能优先级会低一些随着技术的发展硬件性能已不再是不可接受的瓶颈所以推荐使用 ROW 格式 MySQL 的 binlog 与其默认隔离级别 RR 的关系 从上面 binlog 格式的内容来看似乎与默认隔离级别 RR 没有半毛钱关系先莫急慢慢往下看 RCSTATEMENT 下各版 MySQL 执行表数据修改操作 表引擎是 InnoDB隔离级别是 RCbinlog_formatSTATEMENT的统一前提下我们分别看下 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 STATEMENTMySQL5.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_formatSTATEMENT 时 UPDATE t1 SET a 11 where b 2 ; 的事务未提交则 UPDATE t1 SET b 2 where b 1 ; 的事务会被阻塞那么从库复制的时候数据是没问题的 所以综合前面的来看从 MySQL5.0 开始InnoDB 在 RC 级别binlog_formatSTATEMENT 诗 主从复制是没有 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_formatSTATEMENT 下使用系统函数NOW()、UUID()等时还是会导致主从数据不一致 总结 1、binlog 三个格式 目前主流的 MySQLbinlog 格式有 3 种STATEMENT、ROW、MIXED从数据准确性考虑推荐使用 ROW 格式 2、binlog 默认格式 MySQL 5.1.5 之前只支持 STATEMENT 格式的 binlog5.1.5 开始支持 binlog_formatROWMySQL 5.7.7 之前binlog 的默认格式都是 STATEMENT在 5.7.7 及更高版本中binlog_format的默认值才是 ROW 3、主从复制 bugInnoDB 引擎 MySQL 5.1.30及之后InnoDB 下开启 RC 隔离级别的话是不能启用 binlog_formatSTATEMENT的 RC、RR 隔离级别binlog_formatMIXED主从复制仍会有数据不一致的问题受系统函数影响 RR 隔离级别binlog_formatSTATEMENT主从复制仍会有数据不一致的问题受系统函数影响 binlog_formatROW不管是 RC 隔离级别还是 RR 隔离级别主从复制仍不会有数据不一致的问题 4、MySQL 为什么默认隔离级别是 RR 为了规避 MySQL5.0 以前版本的主从复制问题然后一直被沿用了下来而已 5、引擎选择问题 MySQL 5.6 及之后InnoDB 做了大量的优化性能并不比MyISAM低说没特别的理由基本可以放弃 MyISAM 了 © 著作权归作者所有,转载或内容合作请联系作者 喜欢的朋友记得点赞、收藏、关注哦
