第3章　复制格式详解

通过前面的两章，我们了解了复制技术的使用场景及其基本原理与实现，知道复制是通过二进制日志记录在主从库之间的流转来实现的。第1章对二进制日志的记录格式做了简要的介绍，本章将详细阐述复制格式。

3.1　复制格式概述

复制功能之所以能够正常工作，是因为写入二进制日志的事件是从主库读取，然后在从库上回放的。根据事件的类型，事件以不同的格式被记录在二进制日志中。复制格式由系统变量binlog_format控制（主要针对DML语句生效）。根据主库中记录的二进制日志格式以及系统变量binlog_format的不同值，在配置使用时可以将复制划分为如下几种格式（在第1章中有所提及，详见1.4节“复制格式”）：

• 使用statement格式的二进制日志时，主库会将SQL语句文本写入二进制日志。在从库上执行SQL语句，然后将主库的数据变更应用到从库中，这称为基于statement（语句）的复制，简称为SBR。

• 使用row格式的二进制日志时，主库会将产生的事件（一组事件）写入二进制日志，以事件来表示数据的变更。将这些表示数据变更的事件复制到从库，然后在从库中应用这些事件，把主库数据同步到从库，这称为基于row（行）的复制，简称为RBR。

• 还可以使用statement和row的混合（mixed）格式的二进制日志，具体为statement还是row格式，由记录的内容决定。默认使用的是statement格式，根据语句以及使用的存储引擎，在特殊情况下会自动切换到row格式。这种使用mixed格式二进制日志的复制，简称为MBR。

在MySQL 5.7.7之前，默认的二进制日志采用statement格式。在MySQL 5.7.7及更高的版本中，默认的二进制日志变更为row格式。MySQL NDB Cluster 7.5中默认的二进制日志为mixed格式。要注意，MySQL NDB Cluster的复制始终使用基于row的格式，NDB存储引擎与基于statement的复制不兼容。

通过系统变量binlog_format来控制二进制日志的格式时，可以在会话（session）或全局（global）级别动态修改其值。在会话级别修改时，修改的值只对当前会话生效，会话断开即失效，而且修改的值对其他会话不可见；在全局级别修改时，修改的值对修改之后新建立的所有客户端连接生效，对之前已建立的客户端连接不生效（包括执行全局级别修改操作的连接本身）。动态修改的值在数据库进程重启后会丢失，如果要对这个值进行持久化，就需要在配置文件中进行设置。

注意：在某些情况下不能动态修改二进制日志格式，否则容易导致复制失败。例如，事务内不允许修改会话级别的二进制日志格式。

要修改系统变量binlog_format在全局级别和会话级别的值，用户必须拥有SUPER权限。通常，对于大部分会话而言，修改系统变量的值不需要用户具有SUPER权限，但在某些会话中修改它们可能会在会话之外产生影响（例如，系统变量binlog_format、sql_log_bin和sql_log_off），因此用户需要拥有SUPER权限。

基于statement和基于row的复制各自有不同的问题和限制。有关它们的优缺点对比详见3.2节。

使用基于statement的复制，可能会遇到复制存储过程或触发器的问题（在主从数据库上各自执行这些语句会导致主从库的数据不一致），可以通过使用基于row的复制来避免这些问题。更多信息详见3.2节。

3.2　复制格式明细

3.2.1　基于statement和基于row的复制的优缺点

每一种二进制日志格式都有优点和缺点。对于大多数用户而言，混合复制是兼具数据完整性和较高性能的最佳选择。但是，执行某些任务时，需要根据实际情况来选择使用statement或者row二进制日志格式，本节对不同复制格式的优缺点进行对比，供读者在决策时参考。

1. 基于statement的复制的优点

• 技术成熟。

• 写入日志文件的数据较少。当更新或删除操作涉及多行时，可以大大减少存储空间，在利用二进制日志备份与恢复数据时也可以快速完成。

• 日志文件中包含所有的数据变更的原始语句，可用于数据库审计。

2. 基于statement的复制的缺点

• 一些执行结果不确定的DML语句，不能使用基于statement的复制，否则可能会造成主从库的数据不一致。

• UDF（用户自定义函数，即用户创建的函数）和存储过程执行的结果也不确定，因为具体的返回值受传入的参数值的影响。

• 在DML语句中，使用不带ORDER BY的LIMIT子句时，由于在主从库之间执行的排序结果可能不同，所以执行结果是不确定的（如果使用混合复制，会自动使用row格式记录执行DML语句后对数据所做的变更，而不是记录DML语句本身）。

• 使用statement格式的日志时，一些内置的函数无法正确复制，如下：

• LOAD_FILE()

• UUID()、UUID_SHORT()

• USER()

• FOUND_ROWS()

• SYSDATE()（主库和从库都使用--sysdate-is-now选项启动时适用）

• GET_LOCK()

• IS_FREE_LOCK()

• IS_USED_LOCK()

• MASTER_POS_WAIT()

• RAND()

• RELEASE_LOCK()

• SLEEP()

• VERSION()

注意：无法正确复制不一定就不允许执行，在READ-COMMITTED（读提交）隔离级别下，某些函数不允许执行，而在REPEATABLE-READ（可重复读）隔离级别下却允许执行，但不一定保证能够正确复制。凡是在执行语句时产生了警告信息的，都需要留意。

• INSERT INTO ... SELECT语句在基于statement的复制中需要的行级锁比基于row的复制多。

• 未使用索引的UPDATE语句需要进行表扫描，基于statement的复制可能比基于row的复制锁定的行数更多。

• 对于复杂语句，必须在主从库之间先评估数据的一致性（DML语句），基于row的复制则不存在这个风险，因为主库的二进制日志只记录发生数据变更的行，而从库执行这些二进制日志时也只会执行发生数据变更的行，而不是执行实际的复杂语句本身。

提示：

使用基于statement的复制时：

• 从MySQL 5.7开始，类似NOW()的函数（这里指的是一些从系统变量timestamp获取时间值的函数）可以正确地在主从库之间进行复制（对于这些时间函数，在MySQL 5.6及其之前的版本在REPEATABLE-READ隔离级别下也是允许执行的，但在READ-COMMITTED隔离级别下基本不允许执行）。因为二进制日志中每个Query_ log_event（一个事件类型）都会记录时间戳（例如，SET TIMESTAMP=1555828207/!/;），所以对于使用时间戳的一些函数，可以在二进制日志中直接记录SQL语句文本，而且可以确保主从库的一致性。

• 如果碰到无法正确复制的语句，在REPEATABLE-READ隔离级别下将发出警告信息，并正常执行语句，但在READ-COMMITTED和READ-UNCOMMITTED（读未提交）隔离级别下不允许执行。例如警告信息：“Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.”，可以使用SHOW WARNINGS语句查看。

3. 基于row的复制的优点

• 可以正确复制所有数据的变更，这是最安全的复制格式。

注意：会更新MySQL系统库数据的GRANT、REVOKE、TRIGGER、PROCEDURE、VIEW等操作，都是使用statement格式复制到从库的。而CREATE TABLE ... SELECT之类的语句的复制，会被拆分为两个步骤：建表操作使用statement格式的日志记录；涉及数据插入操作时，会使用row格式的日志记录。但GTID复制模式不允许执行CREATE TABLE ... SELECT语句，因为两步操作会导致产生两个不同的GTID（在GTID机制下，二进制日志中的每一个操作都会生成一个单独的GTID）。从逻辑上来说，这显然是不合理的，所以启用GTID之后，GTID的使用限制中有不允许执行该语句这一条。

• 对于以下类型的语句，从库需要的行锁更少，实现了更高的并发性：

• INSERT INTO ... SELECT

• 使用了AUTO_INCREMENT（自增字段）的INSERT语句（这里指的是INSERT语句在对定义了自增字段的表执行插入数据时，不指定自增字段名和自增字段值，让其自动分配）。

• 在UPDATE或DELETE语句中，WHERE条件字段未使用索引时，可能导致全表扫描，但大多数被扫描的行实际上都不会被修改，只有满足WHERE条件值的行才会真正被修改。采用statement格式的二进制日志中记录的是原始SQL语句，这时如果该语句无法使用索引，则会扫描并锁定全表的所有数据；而如果采用row格式，则二进制日志中记录的是逐行数据变更，从库在回放这些二进制日志时也逐行回放，不会锁住所有行。这得益于从MySQL 5.6开始引入的一个新特性：在row格式下，如果表存在主键或唯一索引，那么可以通过特殊的优化算法找到能够唯一标志行的主键值或唯一索引值，从而避免对不需要修改的行加锁。查找算法由系统变量slave_rows_search_algorithms进行设置。关于该特性，可参考高鹏的“复制”专栏，登录简书网站搜索“第24节：从库数据查找和参数slave_ rows_search_algorithms”。

提示：综上所述，对于任何INSERT、UPDATE或DELETE语句，从库需要的行锁可能都会更少。

4. 基于row的复制的缺点

• 生成更多的二进制日志数据，因为基于row的复制会将每行数据的变更都写入二进制日志。利用二进制日志进行备份和恢复的时间也会更长。此外，二进制日志的文件锁也会因为需要更长的时间来写入数据而被持有更久的时间，这可能会影响数据库的并发能力。可以使用系统变量binlog_row_image = minimal来减少二进制日志的写入量。

• 如果要生成大字段的BLOB值，使用基于row的复制比使用基于statement的复制耗费的时间更长，因为前者记录了BLOB字段的具体值，而不是生成数据的语句。

• 无法直接看到从库中执行的语句，但是可以使用mysqlbinlog工具的--base64-output= decode-rows和--verbose选项进行查看，或者在主库中启用系统变量binlog_rows_ query_log_events，它会在二进制日志中写入一个Rows_query_log_event类型的事件来记录原始的语句文本，可以使用mysqlbinlog工具的-vv选项来查看。

• 对于使用MyISAM存储引擎的表，当INSERT语句操作多行数据，在从库中重放该INSERT语句时，可能需要更多的表级锁，即在基于row的复制中，MyISAM引擎的并发性能会受到很大影响。

3.2.2　使用row格式的二进制日志进行复制

所使用的二进制日志格式不同，在二进制日志文件中记录的日志量及其写入时间也各不相同。在实际使用场景中，需要根据应用程序和环境来选择。

在基于row的复制中，不会复制临时表，临时表只能被创建临时表的线程访问，因此没有必要记录到二进制日志中。但如果使用基于statement的复制，则二进制日志中会记录对临时表的操作语句，而实际上把它们记录到二进制日志中也没有什么用处。

注意：从MySQL 5.7.25开始，MySQL会跟踪创建每个临时表时生效的二进制日志格式。如果是statement格式，则当客户端会话连接断开时，会记录DROP TEMPORARY TABLE IF EXISTS语句；如果是row格式，则不会记录该语句。在之前的版本中，无论二进制日志被设置为何种格式，当客户端连接断开时都会在二进制日志中记录DROP TEMPORARY TABLE IF EXISTS语句，以确保主从库都会删除该临时表。

在基于row的复制中，当修改的行数较多时，可能会将行数据的变更拆分到多个事件中，因此在主库中的非事务表修改多行，在从库中进行重放时会更频繁地持有表级锁。如果是不同的表，可能一定程度上能增加并发性；如果是相同的表，则不能增加并发性。

由于基于row的复制是将行数据的变更都记录到二进制日志中，因此日志量可能会迅速增加，而且在从库中进行重放时可能需要更长的时间，所以当应用程序访问从库时，应用开发人员需要清楚访问从库可能出现数据延迟的情况。

通过mysqlbinlog工具解析二进制日志，可以看到其中使用了BINLOG语句（这里指的是二进制日志文件中用于记录Base64编码的BINLOG语句）来显示row格式的行数据变更信息。此语句将事件内容显示为不容易读懂的Base64编码字符串，可以结合使用mysqlbinlog工具的--base64-output=decode-rows和--verbose选项，将这个字符串解析为更适合人阅读的格式，以便更容易使用二进制日志来恢复误删除的数据或从故障中恢复。

当系统变量slave_exec_mode设置为IDEMPOTENT时，通常仅对MySQL NDB Cluster复制有用（在NDB存储引擎中，该系统变量的默认值为IDEMPOTENT，使用其他引擎时，其默认值为STRICT）。如果slave_exec_mode设置为IDEMPOTENT，当找不到行记录或者主键冲突时，会自动跳过发生错误的事件，这就意味着从库上最终并没有应用这些发生错误的事件数据，主从库之间的数据一致性会被破坏。

不能在查询语句中使用Server ID来过滤复制内容（这里指的是在DML语句的WHERE条件值中使用@@server_id系统变量），因为在row格式的二进制日志中会将其转换为具体的值记录下来，而不是记录@@server_id变量字符串。

如果需要使用Server ID来过滤复制内容，可以在从库中配置复制时，使用CHANGE MASTER TO语句的IGNORE_SERVER_IDS选项指定需要过滤的Server ID（对于row格式和statement格式的二进制日志的复制过滤都支持此方法）。不建议在DML语句中使用包含WHERE @@server_id <> id_value的子句来过滤复制内容，例如，WHERE @@server_id <> 1，因为对于row格式的二进制日志，这样的语句不能正常进行复制过滤。如果确实需要在语句中使用系统变量server_id过滤语句，请使用statement格式的二进制日志。

关于数据库级别的复制选项，在row格式和statement格式的二进制日志中，--replicate-do-db、--replicate-ignore-db和--replicate-rewrite-db选项的效果差别很大。通常情况下，不建议使用数据库级的复制选项，而应该用表级复制选项，例如，--replicate-do-table和--replicate-ignore-table。

使用row格式的二进制日志时，如果从库在更新非事务性表时停止了复制线程，则从库中可能发生数据不一致（因为非事务表数据无法回滚）。因此，建议在使用基于row的复制时，所有表都使用事务存储引擎（例如InnoDB）。另外，无论使用何种格式的复制和存储引擎，建议在关闭从库MySQL进程之前，使用STOP SLAVE或STOP SLAVE SQL_THREAD先停止复制线程，因为这样能避免复制时出现的一些问题。

3.3　如何确定与记录复制中的安全和不安全语句

MySQL复制中语句是否“安全”是指是否可以使用基于statement的格式（这里指的是在二进制日志文件中实际记录的内容为statement格式，不是指设置系统变量binlog_format = statement）正确复制语句，如果能正确复制，则认为语句是安全的，否则就认为是不安全的。

• 某些执行结果不确定的函数被视为不安全（详见下文）。

• 使用浮点数的函数（执行结果与硬件相关）的语句被认为不安全。

根据语句是否被认为是安全的，以及二进制日志的格式（即系统变量binlog_format的当前值），对语句有不同的处理方式。

• 使用row格式的日志时，对安全和不安全语句的处理没有区别。

• 使用mixed格式的日志时，被视为不安全的语句在记录到二进制日志时会自动转换为row格式，被视为安全的语句在记录到二进制日志时会使用statement格式。

• 使用statement格式的日志时，对标记为不安全的语句会生成警告，甚至拒绝执行，被标记安全的语句则被正常记录。

每个被标记为不安全的语句，MySQL都会生成一个警告。在早期版本中，如果在主库上执行大量不安全的语句（这里指的是会触发警告的语句），可能会导致错误日志文件过大。为了防止这种情况的发生，MySQL 5.5.27及其之后的5.5发行版、MySQL 5.6.7及其之后的5.6发行版、MySQL 5.7及其以上发行版提供了一种警告抑制机制：在任意50秒的时间段内，当ER_BINLOG_UNSAFE_STATEMENT发出超过50次警告时，就会启用警告抑制。

当某种警告达到50次时，在最后S秒内重复N次的最后一个警告将被写入错误日志中，即一旦触发警告抑制机制，则对于50秒内超过50次的警告，只将最后一次记录到错误日志中。如果警告持续保持该频率，则警告抑制持续有效，一旦警告频率低于此阈值，则所有的警告信息将都正常记录到错误日志中。警告抑制不会影响确定语句的安全性，也不会影响向客户端发送警告信息，MySQL客户端仍然会收到所有的警告信息。

在statement格式的日志中，包含某些函数的语句被认为是不安全的，因为在主从数据库中执行的结果可能不相同，在READ-UNCOMMITTED和READ-COMMITTED隔离级别下不允许执行，REPEATABLE-READ或SERIALIZABLE（串行）隔离级别允许执行，但是会收到警告信息。这些函数包括：FOUND_ROWS()、GET_LOCK()、IS_FREE_LOCK()、IS_ USED_LOCK()、LOAD_FILE()、MASTER_POS_WAIT()、PASSWORD()、RAND()、RELEASE_LOCK()、ROW_COUNT()、SESSION_USER()、SLEEP()、SYSDATE()、SYSTEM_ USER()、USER()、UUID()、UUID_SHORT()。

以下一些函数虽然执行结果也不确定，但是它们被视为安全的（实际上这些函数中除了使用系统变量timestamp获取时间戳的时间函数之外，其他大多数函数在主从数据库中的执行结果并不一致，在READ-UNCOMMITTED和READ-COMMITTED隔离级别下这些函数都不允许执行，在REPEATABLE-READ或SERIALIZABLE隔离级别下允许执行，但不会收到警告信息，使用这些函数时需要留意主从数据的一致性）。这些函数包括：CONNECTION_ID()、CURDATE()、CURRENT_DATE()、CURRENT_TIME()、CURRENT_ TIMESTAMP()、CURTIME()、LAST_INSERT_ID()、LOCALTIME()、LOCALTIMESTAMP()、NOW()、UNIX_TIMESTAMP()、UTC_DATE()、UTC_TIME()、UTC_TIMESTAMP()。

执行语句中如果有对系统变量的引用，使用statement格式的日志时，将无法正确复制大多数的系统变量。

对于UDF，由于无法控制UDF的作用，因此必须假设在UDF中执行的语句是不安全的。

Fulltext plugin（全文索引插件）在不同的MySQL Server上的行为可能不同，在不同的语句中执行结果也可能不相同，因此，跟Fulltext plugin相关的所有语句都被视为不安全。

使用触发器或存储程序UPDATE一个具有AUTO_INCREMENT字段的表时，被认为不安全，因为更新行的顺序在主从数据库中可能不相同。另外，如果一个表具有复合主键，且复合主键包含一个AUTO_INCREMENT字段，而该字段又不是这个复合主键的第一字段时，那么对该表的INSERT操作也被认为不安全。

INSERT INTO ... ON DUPLICATE KEY UPDATE语句在具有多个唯一约束（主键 + 唯一索引 = 多个唯一约束）的表中执行时，被认为不安全。因为它对存储引擎检查索引键的顺序很敏感，MySQL Server更新行的选择依赖于唯一索引的检查顺序，而该顺序是不确定的，所以在基于statement的复制中，该语句也会被标记为不安全。

使用LIMIT子句执行UPDATE操作时，未使用ORDER BY来指定检索行的顺序的语句，被视为不安全。

主从库之间的系统日志表的内容可能不相同，当访问或引用日志表时，可能返回不同的结果。

在同一个事务中，混合事务引擎与非事务引擎的读/写操作被认为不安全。

在事务中，对内部使用的一些记录表的所有读/写操作都被认为是不安全的。

LOAD DATA被视为不安全，当binlog_format = mixed时，语句将以row格式记录。要注意：当binlog_format = statement时（而且是REPEATABLE-READ或者SERIALIZABLE隔离级别），LOAD DATA不会生成警告，这一点与其他不安全的语句不同。

如果在主库上并行提交的两个XA（一种分布式事务的协议名称）事务在从库上按相反的顺序执行，那么基于statement的复制可能会发生不安全的锁依赖关系，这可能导致从库发生死锁，进而导致复制失败。

• 当设置binlog_format = statement时，XA事务中的DML语句被标记为不安全，并生成警告。

• 当设置binlog_format = mixed或binlog_format = row时，XA事务中的DML语句将使用row格式的日志记录，不存在该问题。

提示：

• 更多关于二进制日志的内容，可参考第26章“二进制日志文件的基本组成”。

• 更多关于隔离级别的内容，可参考《千金良方：MySQL性能优化金字塔法则》的第19章“事务概念基础”。