当前位置 博文首页 > JavaEdge全是干货的技术号：【阿里最新数据库面试题】MySQL主从
为什么备库执行了binlog就可以跟主库保持一致？
上部分状态：客户端的读写都直接访问A，B是A的备库，只是将A的更新都同步过来，到本地执行。这样可以保持B和A的数据相同。
当需要切换时，就切成下部分状态：客户端读写访问的都是B，A是B的备库。
上部分状态，虽然节点B没有被直接访问，但推荐把B（备库）设成只读（readonly），考虑如下：
把备库设置成只读了，还能和主库保持同步更新吗？
用于同步更新的线程，拥有超级权限。 readonly设置对超级(super)权限用户无效。
A到B这条线的内部流程是什么样的？下图中画出的就是一个update语句在节点A执行，然后同步到节点B的完整流程图
主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。
备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的过程：
后来由于多线程复制方案的引入，sql_thread演化成为了多个线程。
为什么备库拿过去可以直接执行。
注意，下面这个语句包含注释，如果你用MySQL客户端来做这个实验的话，要记得加-c参数，否则客户端会自动去掉注释。
delete
from ttt -c /*comment*/
where a >= 4
and t_modified <= '2018-11-10'
limit 1;
当binlog_format=statement时，binlog里面记录的就是SQL语句的原文。你可以用
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)
当前binlog设置的是statement格式，并且语句中有limit，该命令可能是unsafe的。因为delete 带limit，可能出现主备数据不一致。比如上面这个例子：
t_modified='2018-11-09'，即a=5这行由于statement格式下，记录到binlog里的是原语句，可能发生：在主库执行该SQL时，用的是索引a；而在备库执行该SQL时，却使用索引t_modified。因此，这样写是有风险的。
若把binlog改为ROW 格式，是不是就没这问题了？
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：
其实还需要借助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的库上执行
可见，当binlog_format=row，binlog记录了真实删除行的主键id，这样binlog传到备库时，就肯定会删除id=4的行，不会有主备删除不同行的问题。
因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。
但row很占空间（不然怎么叫肉呢？）。比如你用一个delete语句删掉10万行：
所以，MySQL采取折中方案-mixed：MySQL自己会判断该SQL是否可能引起主备不一致：
即mixed既可以利用statment格式的优点，又能避免数据不一致。
比如我们的例子，设为mixed后：
现在越来越多的场景要求把MySQL的binlog格式设成row。这么做的理由有很多，比如恢复数据。
虽然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时，是这么做的：
这样有风险。因为有些语句的执行结果依赖于上下文命令，直接执行很可能错误。
使用binlog恢复数据的正确做法：
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析出来，放到MySQL去执行。
binlog确保了在备库执行相同的binlog，可以得到与主库相同的状态。
因此，可以认为正常情况下主备数据是一致的。即文章一开始的图中A、B两个节点的内容是一致的。不过那是M-S结构，实际生产上使用比较多的是双M结构：
区别只是多了一条线，即：节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。
但是，双M结构还有一个问题需要解决：业务逻辑在节点A上更新了一条语句，然后再把生成的binlog 发给节点B，节点B执行完这条更新语句后也会生成binlog。（推荐把参数log_slave_updates设置为on，表示备库执行relay log后生成binlog）。
那么，若节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了。这怎么解决？
我们已经知道，MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。因此，可以用下面的逻辑解决两个节点间的循环复制的问题：
按照这个逻辑，如果我们设置了双M结构，日志的执行流就会变成这样：
4. 从节点A更新的事务，binlog里面记的都是A的server id
5. 传到节点B执行一次以后，节点B生成的binlog 的server id也是A的server id
6. 再传回给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了
binlog在MySQL高可用方案很重要，是所有MySQL高可用方案，诸如多节点、半同步、MySQL group replication等的基础。
cs