- 查看binlog内容,上面是 ROW 格式,下面是 statement 格式。
mysql> show binlog events in ‘binlog.000034’;
±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+
| binlog.000034 | 786 | Anonymous_Gtid | 1 | 865 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| binlog.000034 | 865 | Query | 1 | 977 | BEGIN |
| binlog.000034 | 977 | Query | 1 | 1151 | use common_mistakes
; delete from ttt where a >= 4 and t_modified <= ‘2018-11-10’ limit 1 |
| binlog.000034 | 1151 | Query | 1 | 1264 | COMMIT |
±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
-
BEGIN和最后的COMMIT匹配,表示中间是个事务
-
第三行就是真实执行的语句。在真实执行的delete命令之前,还有一个use命令。这条命令是MySQL根据当前要操作的表所在的数据库而自行添加的。这么做,可以保证日志传到备库去执行时,不论当前工作线程在哪个库,都能够正确更新到common_mistakes库的ttt表。
-
后面的delete 语句,就是SQL原语句
-
最后一行是一个COMMIT写着xid。
当前binlog设置的是statement格式,并且语句中有limit,该命令可能是unsafe的。因为delete 带limit,可能出现主备数据不一致。比如上面这个例子:
-
若delete使用的是索引a,则会根据索引a找到第一个满足条件的行,即删除的是a=4这一行
-
但若使用的是索引t_modified,则删除的就是
t_modified='2018-11-09'
,即a=5这行
由于statement格式下,记录到binlog里的是原语句,可能发生:在主库执行该SQL时,用的是索引a;而在备库执行该SQL时,却使用索引t_modified。因此,这样写是有风险的。
若把binlog改为ROW 格式,是不是就没这问题了?
- row格式binlog
mysql> show binlog events in ‘binlog.000034’;
±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+
| binlog.000034 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| binlog.000034 | 235 | Query | 1 | 329 | BEGIN |
| binlog.000034 | 329 | Table_map | 1 | 392 | table_id: 102 (common_mistakes.ttt) |
| binlog.000034 | 392 | Delete_rows | 1 | 440 | table_id: 102 flags: STMT_END_F |
| binlog.000034 | 440 | Xid | 1 | 471 | COMMIT /* xid=71 */
BEGIN和COMMIT是一样的。但row格式的binlog里没有原SQL语句,而两个event:
- Table_map event
说明接下来要操作的表是test库的表t
- Delete_rows event
定义删除的行为
其实还需要借助mysqlbinlog工具,用下面的命令解析和查看binlog内容。因为图5中的信息显示,这个事务的binlog是从8900这个位置开始的,所以可以用start-position参数来指定从这个位置的日志开始解析。
/usr/local/Cellar/mysql/8.0.21_1/bin/mysqlbinlog
-vv /usr/local/var/mysql/binlog.000034
–start-position=156;
row格式binlog 示例的详细信息
BEGIN
/!/;
at 329
#210606 14:11:44 server id 1 end_log_pos 392 CRC32 0xe8d79800 Table_map: common_mistakes
.ttt
mapped to number 102
at 392
#210606 14:11:44 server id 1 end_log_pos 440 CRC32 0x8b3b43d1 Delete_rows: table id 102 flags: STMT_END_F
BINLOG ’
IGe8YBMBAAAAPwAAAIgBAAAAAGYAAAAAAAEAD2NvbW1vbl9taXN0YWtlcwADdHR0AAMDAxEBAAIB
AQAAmNfo
IGe8YCABAAAAMAAAALgBAAAAAGYAAAAAAAEAAgAD/wAEAAAABAAAAFvlrwDRQzuL
'/!/;
DELETE FROM common_mistakes
.ttt
WHERE
@1=4 /* INT meta=0 nullable=0 is_null=0 */
@2=4 /* INT meta=0 nullable=1 is_null=0 */
@3=1541779200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
at 440
#210606 14:11:44 server id 1 end_log_pos 471 CRC32 0x8e0dab1d Xid = 71
COMMIT/!/;
- server id 1
该事务在server_id=1
的库上执行
- 每个event都有CRC32值,因为binlog_checksum是CRC32
- Table_map event
显示了接下来要打开的表,map到数字226。现在我们这条SQL语句只操作了一张表,若操作多表呢?每个表都有一个对应的Table_map event、都会map到一个单独的数字,用于区分对不同表的操作。
-
-vv参数是为了把内容都解析出来,所以从结果里面可以看到各个字段的值(比如,@1=4、 @2=4这些值)
-
binlog_row_image
默认配置是FULL,因此Delete_event里面,包含了删掉的行的所有字段的值。若把binlog_row_image设置为MINIMAL,则只会记录必要的信息。在该例,就只会记录id=4。
- Xid event
表示事务被正确提交了。
可见,当binlog_format=row
,binlog记录了真实删除行的主键id,这样binlog传到备库时,就肯定会删除id=4的行,不会有主备删除不同行的问题。
================================================================================
因为有些statement格式的binlog可能会导致主备不一致,所以要使用row格式。
但row很占空间(不然怎么叫肉呢?)。比如你用一个delete语句删掉10万行:
-
statement就是一个SQL语句被记录到binlog,占用几十个字节
-
row就要把这10万条记录都写到binlog。不仅占用巨大空间,写binlog也要耗费I/O资源,影响执行速度。
所以,MySQL采取折中方案-mixed:MySQL自己会判断该SQL是否可能引起主备不一致:
-
有可能,用row
-
不可能,用statement
即mixed既可以利用statment格式的优点,又能避免数据不一致。
比如我们的例子,设为mixed后:
-
就会记录为row
-
若执行的语句没有limit 1,就记录为statement
现在越来越多的场景要求把MySQL的binlog格式设成row。这么做的理由有很多,比如恢复数据。
=======================================================================
-
即使执行delete,row格式binlog也会保存被删掉的行的整行信息。所以,若你在执行完一条delete后,发现删错数据了,可以直接把binlog中记录的delete转insert,把错删数据插回。
-
若执行错insert呢?row下,insert的binlog里会记录所有字段信息,可以用来定位被插入的那行。再直接把insert转delete 即可。
-
若执行update,binlog会记录修改前整行的数据和修改后的整行数据。所以只需把这个event前后两行信息对调,在DB里执行
虽然mixed格式的binlog现在已经用得不多了,但这里还是要再借用一下mixed格式来说明一个问题,来看一下这条SQL语句:
insert into ttt values(10,10, now());
若binlog设为mixed,MySQL会把它记录为啥格式呢?
MySQL采用statement格式。若该binlog过了1min才传给备库,那主备数据不就不一致了?
再用mysqlbinlog查看:
binlog在记录event时,多记了一条命令:SET TIMESTAMP。它用 SET TIMESTAMP命令约定了接下来的now()函数的返回时间。
因此,无论该binlog多久后被备库执行或用于恢复该库的备份,该insert插入的行,值都固定。即通过SET TIMESTAMP,MySQL保证了主备数据一致性。
有人重放binlog时,是这么做的:
-
用mysqlbinlog解析出日志
-
然后把里面的statement语句直接拷贝出来执行
这样有风险。因为有些语句的执行结果依赖于上下文命令,直接执行很可能错误。
使用binlog恢复数据的正确做法:
-
用 mysqlbinlog工具解析出来
-
然后把解析结果整个发给MySQL执行。类似下面的命令:
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u u s e r − p user -p user−ppwd;
将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析出来,放到MySQL去执行。
=====================================================================
binlog确保了在备库执行相同的binlog,可以得到与主库相同的状态。
最后
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长,自己不成体系的自学效果低效漫长且无助。
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Android开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!
如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
链图片转存中…(img-L6QNcQy4-1715177716499)]
[外链图片转存中…(img-ux75WsfN-1715177716500)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Android开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!
如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!