mysql binlog 以及数据回滚

2022-08-2229

1、binlog=statement格式

当binlog=statement时,binlog记录的是SQL本身的语句

ues `test`;delete from t where a>=4 and u_time<='2021-11-15' limit 1

binlog设置为statement格式的时候,因为记录的是sql语句本身,并且语句带limit 1,这个命令可能是unsafe的。这里我来说明一下为啥:

如果delete使用的是索引a,根据索引a找到第一条数据删除,也就是删除a=4这一行;
如果使用索引u_time,那么找到就是u_time='2021-11-14’这一条,也就是a=5这一行
由于statement格式下,binlog记录的是sql原文,可能导致在主库执行的时候使用的是索引a,而在备库执行的时候用了索引u_time,因此,会出现主备不一致的情况

那么怎么解决主备不一致呢?MySQL的另一种格式row

2、binlog=row格式

如果我们把binlog设置为row格式的时候,binlog记录的不是sql原语句,而是替换成了两个event:Table_map和Delete_rows。

  1. Table_map    |   table_id: 226(test.t)
  2. Delete_rows  |   table_id: 226 flags: STMT_END_F

Table_map event,用于说明接下来要操作的表是test库的t表
Delete_rows event,用于定义删除的行为
通过对row格式的binlog看不出详细信息,需要进一步借助mysqlbinlog工具,用’mysqlbinlog -w data/master.0.000001 -start -position=8900’解析和查看binlog的内容,binlog可以看到这条语句是从8900事物开始,所以可以用start-position指定从哪个位置开始解析

最后可以查看到binlog使用row格式,binlog里面记录了真实删除记录的主键id,这样备库同步的时候一定会删除id=4的行,不会有主备同步不一致的问题

3、binlog=mixed格式

上面已经有了row格式,已经可以解决主备不一致的问题,为啥还会有mixed格式呢

statement格式记录sql原句,可能会导致主备不一致,所以出现了row格式
但是row格式也有一个缺点,就是很占空间,比如你delete语句删除1万行记录,statement格式会记录一个sql删除1万行就没了;但是使用row格式会把这1万要删除的记录都写到binlog中,这样会导致binlog占用了大量空间,同时写binlog也要耗费大量IO,影响mysql的整体速度
所以MySQL出了个mixed格式,它是前面两种格式的混合。意思是MySQL自己会判断这条SQL语句是否会引起主备不一致,是的话就会使用row,否则就用statement格式
也就是说上面delete语句加上了limit 1,MySQL认为会引起主备不一致,它就会使用row格式记录到binlog;如果delete 1万行记录,MySQL认为不会引起主备不一致,它就会使用statement格式记录到binlog。


显示所有binlog

show binary logs;


读取最新的binlog数据

show binlog events;


读取指定binlog中的数据

show binlog events in 'DESKTOP-PRRNA5O-bin.000071'


(如果binlog为row模式则可以闪回利用存储的row数据可以实现恢复,statement则为备份模式,需要有备份即恢复点,利用存储的sql语句进行重新执行sql语句恢复数据)


利用mysqlbinlog获取回滚sql --no-defaults 可以去掉乱码

mysqlbinlog --no-defaults -uroot -p123456 -hlocalhost -P3306 --start-position="1227" --stop-position="1546" --read-from-remote-server --skip-gtids=true DESKTOP-PRRNA5O-bin.000073 > D:\test.sql


--skip-gtids=xxx的作用为:mysqldump
是否使用--skip-gtids=true 参数,要根据情况来定;
第一种情况:
如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例,那么就要使用该参数。如果不带该参数的话,是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了,根据 GTID 特性,一个 GTID 信息在一个数据库只能执行一次,所以不会恢复成功。

# mysqlbinlog --skip-gtids=true  mysql-bin.000012 |mysql -uroot -p

 

或者

# mysqlbinlog --skip-gtids=true  mysql-bin.000012 > backup.sql
mysql -uroot -p < backup.sql

 

第二种情况:
如果是恢复到其他实例的数据库并且不包含源实例的 GTID 信息,那么可以不使用该参数,使用或者不使用都可以恢复成功


上一篇:Mysql优化 下一篇:Mysql数据库锁设计