24 | MySQL主备原理分析（吐血到键盘上才整理出来的笔记）

一、话不多说正文开始

1.1.主备复制需要哪几个线程？每个线程作用是什么？架构图是什么样子的？

• mysql主从复制需要三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）

• binlog dump线程：主库中有数据更新时，根据设置的binlog格式，将更新的事件类型写入到主库的binlog文件中，并创建log dump线程通知slave有数据更新。当I/O线程请求日志内容时，将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。

  I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中。

       SQL线程：该线程检测到relay log有更新后，会读取并在本地做回放操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。

• 

1.2.binlog的三种格式优缺点和使用建议是什么？

• 当binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文。你可以用show binlog events in 'mysql-bin.000001';#查看指定binlog文件的内容。

  问题）当执行某些操作时容易造成主备数据不一致，比如：delete from t where a>=4 and t_modified <='2021-01-01' limit 1;[如果a和t_modified两个字段都有索引，容易导致主备执行过程中数据不一致]

• 当binlog_format=row 时，binlog里面记录的就是SQL语句的Event信息。注意：此处需要借助mysql/bin/mysqlbinlog来查看

  优点）会记录删除过程中的主键id，所以备库执行过程中不会产生数据不一致现象。如果再删除过程中想要记录所有的行信息，需要开启binlog_row_image 的默认配置是 FUL

binlog_row_image 的默认配置是 FULL，因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录删除行 id=4 这个信息

• 当binlog_format=mixed 时，避免了statement的缺点，扬长了row的优点。但是，线上数据库都会设置为row格式，对于恢复数据的好处是显而易见的。【PS：赶紧看看自己的线上数据库去～】

  使用mixed时一个有意思的问题）mysql> insert into t values(10,10, now()); MySQL会记录成什么格式？statement格式。因为mysql在解析成binlog时候会插入一行set timestamp=xxxx（now()的时间）

1.3.MySQL双M和MS架构简单区别？双M如何避免循环复制问题？

答案1）双 M 结构和 M-S 结构，其实区别只是多了一条线，即：节点 A 和 节点B 之间总是互为主备关系

 

答案2）通过binlog中的server id来保证。

从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；

传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；

再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

二、课后题

2.1.双M情况下，MySQL 通过判断 server id 的方式，断掉死循环。但是，这个机制其实并不完备，在某些场景下，还是有可能出现死循环。举例说明。

• 在一个主库更新事务后，用命令 set global server_id=x 修改了 server_id。等日志再传回来的时候，发现 server_id 跟自己的 server_id 不同，就只能执行了

• 有三个节点的时候，如图 7 所示，trx1 是在节点 B 执行的，因此 binlog 上的 server_id 就是 B，binlog 传给节点 A，然后 A 和 A’搭建了双 M 结构，就会出现循环复制。

   

  

       🤔️）什么情况下会出现这种三节点情况？这种三节点复制的场景，做数据库迁移的时候会出现。如果出现了循环复制，可以在 A 或者 A’上，执行如下命令：

       stop slave；CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);start slave;

       这样这个节点收到日志后就不会再执行。过一段时间后，再执行下面的命令把这个值改回来。

三、评论区

3.1.主从复制命令【了解即可】

1）change master to master_host='IP地址',master_user='用户名',master_password='密码',master_log_file='mysql_bin.000011【binlog名称】',master_log_pos=11137524【复制的起始位置】

2）—stop-datetime 可以指定从某个时间点开始进行主从备份

3.2.如果没有设置主键id，在row模式下不会记录 innodb隐藏的主键id字段

截图如下：

 

3.3.binlog中是否使用上change buffer？

binlog里面的内容用的是主键索引上的，主键索引确实用不上change buffer，但是普通索引更新可以用上。