mysql binlog 以及数据回滚

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。

Table_map    |   table_id: 226(test.t)
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 信息，那么可以不使用该参数，使用或者不使用都可以恢复成功