MySQL binlog模式及主备的基本原理

MySQL binlog是二进制格式的日志文件，用于记录MySQL内部对数据库的修改操作，主要作用为数据库的主从复制及增量恢复

从 MySQL 5.1.12 开始，可以用以下三种模式来实现：

• 基于SQL语句的复制(statement-based replication, SBR)
• 基于行的复制(row-based replication, RBR)
• 混合模式复制(mixed-based replication, MBR)

相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED

MySQL 5.0版本前，因为binlog模式只有statement，为了避免主从复制不一致，只能使用RR隔离模式配合。MySQL 5.1+ 可以使用RC + Row进行binlog主从复制，在绝大多数情况无需加间隙锁，以提获得更好的并发性，且可保证主从复制一致性

Oracle及sqlserver默认使用RC隔离级别
大部分互联网公司使用RC + Row进行binlog主从复制

MySQL 主备的基本原理

此段文章修改自[MySQL 实战45讲——MySQL是怎么保证主备一致的？]，详细见原文

在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。

当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。

在状态 1 中，虽然节点 B 没有被直接访问，但是我依然建议你把节点 B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
3. 可以用 readonly 状态，来判断节点的角色。

readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限，故不影响主从同步

主备流程

接下来，我们再看看节点 A 到 B 这条线的内部流程。下图中画出的就是一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程图。

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

1. 在备库 B 上通过change master命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
2. 在备库 B 上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。
3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
5. sql_thread读取中转日志，解析出日志里的命令，并执行。

后来由于多线程复制方案的引入，sql_thread演化成为了多个线程

binlog 模式

1. STATAMENT格式：记录的是日志的逻辑SQL语句
2. ROW格式：记录表的行更改情况，包括EVENT TYPE，可保证主从一致
3. MIX格式：默认采用STATAMENT格式，下列情况自动判断并自动切换行和语句的策略
   • 表的存储引擎为NDB，此时对表的DML操作都会以ROW格式记录
   • 使用了UUID(),USER(),CURRENT_USER(),FOUND_ROWS(),ROW_count()等不确定函数
   • 使用了insert delay语句
   • 使用了用户定义函数（UDF）
   • 使用了临时表

MySQL 重要版本时间线

• MySQL 5.0前 只有statement模式，只能使用RR隔离级别以保证主从复制一致性
• MySQL 5.1 引入row及mixed模式
• MySQL 5.1.22 引入互斥量，替代auto_key_increment，保证主键唯一且自增长同时，大幅提升插入及主从复制效率
• MySQL 5.5 binlog半同步复制(semi-sync replication)
• MySQL 5.6 离散读
• MySQL 5.7 binlog增强半同步复制，基于多线程的复制技术
• MySQL 8.0 基于多线程的复制技术更新()

---

参考资料：

1. MySQL 实战 45 讲
2. MySQL第五弹
3. 《MySQL技术内幕 InnoDB存储引擎》
4. MySql Binlog statement row mixed 三种模式初探