【阿里最新数据库面试题】MySQL主从一致性，kafka实现负载均衡的原理

【阿里最新数据库面试题】MySQL主从一致性，kafka实现负载均衡的原理

from ttt -c /comment/

where a >= 4

and t_modified <= ‘2018-11-10’

limit 1;

当binlog_format=statement时，binlog里面记录的就是SQL语句的原文。你可以用

• 查看binlog内容，上面是 ROW 格式，下面是 statement 格式。

mysql> show binlog events in ‘binlog.000034’;

±--------------±-----±-----

《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享

----------±----------±------------±-------------------------------------------------------------------------------------------+

| 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

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 @2=4 @3=1541779200 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的行，不会有主备删除不同行的问题。

为何binlog有mixed格式？

================================================================================

因为有些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时，是这么做的：

1. 用mysqlbinlog解析出日志

2. 然后把里面的statement语句直接拷贝出来执行

这样有风险。因为有些语句的执行结果依赖于上下文命令，直接执行很可能错误。

使用binlog恢复数据的正确做法：

1. 用 mysqlbinlog工具解析出来

2. 然后把解析结果整个发给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去执行。