MySQL binlog底层主从同步原理

MySQL中的 binlog

记录mysql的数据更新或者潜在更新（delete from table where id=x）

 

 

mysql主从同步延迟原理
要了解MySQL数据库主从同步延迟原理，我们先从MySQL的数据库主从复制原理说起：

 

主从复制就是依靠binlog

Slave 端，里面有两个线程，一个是IO线程，另一个是SQL线程；IO线程负责从Master上读取信息然后返回，（slave什么时候读取，master会有一个事件通知slave ）
slave收到通知后使用IO Thread主动去master读取binlog日志,然后异步写入relay日志(中转日志),然后使 SQL Thread完成对`relay日志 的解析然后入库操作,完成同步

 

 

 

Mysql 中的binlog底层原理分析

记录mysql 数据更新或潜在更新（delete from table where id =x）

主从复制就是依靠binlog

Master-slave

binlog日志里面内容格式有三种
Statement 基于sql语句 insert （Master执行完增删改操作后都会记录binlog日志）

Row 基于行模式。Update table set value=x; 10000条 记录10000条变更的数据

Mixed 混合模式

 

解码binglog 二进制文件
mysqlbinglog --base64-output=decode-row -v mysqlbin.00001

show variables like ‘%log%’

 

 

Binlog模式分三种Row、Statement、Mixed
Row模式存储的是数据修改后的结果，binlog中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。对于update mytable set col1=’abc’ where col2=’c’在row模式下可能产生大量的数据，因为语句虽然是一条，但实际影响的数据记录却可能很多。而对于alter table、drop table、create table等信息在Row模式下则不会产生大量的log条目，因为它还是记录的语句，而不是单行数据的变化情况。

优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题
缺点：row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，会产生大量的日志内容。

 

Statemnet模式每一条会修改数据的sql都会记录到 master的binlog中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。

优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要在Master上锁执行的语句的细节，以及执行语句的上下文的信息。
缺点：由于只记录语句，所以，在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。

Mixed模式则是前两种的混合，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。选择性的使用面向行数据变化的Row方式记录，主要是面对一些未决语句（nondeterministic），考虑到安全问题，避免主从库之间数据出现不一致，比如语句面向多行插入，其中又有auto-increment的字段，数据库存储引擎不同，可能带来插入顺序