面试官问我Mysql binlog如何运作的

青旅半醒 2022-10-12 01:46 184阅读 0赞

![binlog][]

### 主节点binary log dump线程 ###

当从节点连接主节点时，主节点会创建一个 log dump 线程，用于发送 binlog 的内容。在读取 binlog 中的操作时，此线程会对主节点上的 binlog 加锁，当读取完成，在发送给从节点之前，锁会被释放。

### 从节点 I/O 线程 ###

当从节点上执行start slave命令之后，从节点会创建一个 I/O 线程用来连接主节点，请求主库中更新的 binlog。I/O 线程接收到主节点 binlog dump 进程发来的更新之后，保存在本地 relay-log（中继日志）中。

### 从节点 SQL 线程 ###

SQL 线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

### 主从复制模式 ###

MySQL 主从复制默认是异步的模式。MySQL 增删改操作会全部记录在 binlog 中，当 slave 节点连接 master 时，会主动从 master 处获取最新的 bin log 文件。并把 bin log 中的 sql relay。

### 异步模式（mysql async-mode） ###

这种模式下，主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地。

### 半同步模式(mysql semi-sync) ###

这种模式下主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长。

### 全同步模式 ###

全同步模式是指主节点和从节点全部执行了commit并确认才会向客户端返回成功。

### binlog格式分三种（row，statement，mixed） ###

**1.row**

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改，只记录要修改的数据，只有value，不会有sql多表关联的情况。  
  `优点：`在row模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和出发无法被正确复制问题。  
  `缺点：`在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

**2.statement**

每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行多相同的sql再执行。  
  `优点：`在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息。  
  `缺点：`在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last\_insert\_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

**3.Mixed**

从官方文档中看到，之前的 MySQL 一直都只有基于 statement 的复制模式，直到 5.1.5 版本的 MySQL 才开始支持 row 复制。从 5.0 开始，MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给 MySQL Replication 又带来了更大的新挑战。另外，看到官方文档说，从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

### 注意 ###

Read Committed隔离级别下使用statement格式的binlog会存在日志逻辑一致性问题，导致slave的结果和master不一致。

[binlog]: /images/20221005/d45cb30866e64efcbb25095a995a5f31.png