MySQL8 中文参考（二十一）（1）

7.4.4.1 二进制日志格式

服务器使用几种日志记录格式来记录二进制日志中的信息：

• MySQL 中的复制功能最初基于从源到副本的 SQL 语句传播。这被称为基于语句的日志记录。您可以通过使用--binlog-format=STATEMENT启动服务器来使用此格式。
  
• 在基于行的日志记录（默认情况下）中，源将事件写入二进制日志，指示单个表行受到的影响。您可以通过使用--binlog-format=ROW启动服务器来使用基于行的日志记录。
  
• 还有第三个选项：混合日志记录。使用混合日志记录，默认情况下使用基于语句的日志记录，但在某些情况下自动切换到基于行的日志记录，如下所述。您可以通过使用选项--binlog-format=MIXED显式启动 MySQL 来使用混合日志记录。
  

日志记录格式也可以由正在使用的存储引擎设置或限制。这有助于消除在源和副本之间复制某些语句时使用不同存储引擎时出现的问题。

使用基于语句的复制时，可能会出现复制非确定性语句的问题。在决定给定语句是否适合基于语句的复制时，MySQL  确定是否可以保证该语句可以使用基于语句的日志记录进行复制。如果 MySQL  无法做出此保证，则将该语句标记为潜在不可靠，并发出警告，语句可能不安全以语句格式记录。

您可以通过使用 MySQL 的基于行的复制来避免这些问题。

7.4.4.2 设置二进制日志格式

您可以通过使用--binlog-format=*type*启动 MySQL 服务器来明确选择二进制日志记录格式。*type*支持的值为：

• STATEMENT导致记录以语句为基础。
  
• ROW导致记录以行为基础。这是默认设置。
  
• MIXED导致记录使用混合格式。
  

设置二进制日志记录格式不会激活服务器的二进制日志记录。该设置仅在服务器启用二进制日志记录时生效，当log_bin系统变量设置为ON时为此情况。从 MySQL 8.0 开始，默认情况下启用二进制日志记录，只有在启动时指定--skip-log-bin或--disable-log-bin选项时才会禁用。

记录格式也可以在运行时切换，尽管请注意，在本节后面讨论的一些情况下，您无法这样做。设置全局值binlog_format系统变量以指定更改后连接的客户端的格式：

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

通过设置binlog_format的会话值，个别客户端可以控制其自己语句的记录格式：

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

更改全局binlog_format值需要具有足够权限来设置全局系统变量。更改会话binlog_format值需要具有足够权限来设置受限会话系统变量。参见 Section 7.1.9.1, “System Variable Privileges”。

有几个原因可能导致客户端希望按会话设置二进制日志记录：

• 对数据库进行许多小更改的会话可能希望使用基于行的记录。
  
• 执行更新匹配WHERE子句中许多行的会话可能希望使用基于语句的记录，因为记录少量语句比记录许多行更有效率。
  
• 一些语句在源端执行时需要大量时间，但只会修改少量行。因此，使用基于行的记录可能是有益的。
  

在运行时无法切换复制格式的情况有例外：

• 无法从存储函数或触发器内部更改复制格式。
  
• 如果启用了NDB存储引擎。
  
• 如果会话中有打开的临时表，则无法为该会话更改复制格式（SET @@SESSION.binlog_format）。
  
• 如果任何复制通道有打开的临时表，则无法全局更改复制格式（SET @@GLOBAL.binlog_format 或 SET @@PERSIST.binlog_format）。
  
• 如果任何复制通道应用程序线程当前正在运行，则无法全局更改复制格式（SET @@GLOBAL.binlog_format 或 SET @@PERSIST.binlog_format）。
  

在这些情况下尝试切换复制格式（或尝试设置当前复制格式）会导致错误。但是，您可以随时使用 PERSIST_ONLY（SET @@PERSIST_ONLY.binlog_format）更改复制格式，因为此操作不会修改运行时全局系统变量值，并且仅在服务器重新启动后生效。

不建议在存在任何临时表时在运行时切换复制格式，因为仅在使用基于语句的复制时才记录临时表，而在基于行的复制和混合复制中，它们不会被记录。

当复制正在进行时切换复制格式也可能会导致问题。每个 MySQL 服务器都可以设置自己的二进制日志格式（无论 binlog_format 是以全局还是会话范围设置）。这意味着在复制源服务器上更改日志格式不会导致副本更改其日志格式以匹配。在使用 STATEMENT 模式时，binlog_format 系统变量不会被复制。在使用 MIXED 或 ROW 日志模式时，它会被复制，但副本会忽略它。

副本无法将以 ROW 日志格式接收的二进制日志条目转换为 STATEMENT 格式以在其自己的二进制日志中使用。因此，如果源使用 ROW 或 MIXED 格式，则副本必须使用 ROW 或 MIXED 格式。在复制仍在进行的情况下，将源上的二进制日志格式从 STATEMENT 更改为 ROW 或 MIXED 到具有 STATEMENT 格式的副本可能会导致复制失败，出现错误，例如执行行事件时出错：‘无法执行语句：由于语句处于行格式且 BINLOG_FORMAT = STATEMENT，因此无法写入二进制日志。’ 当源仍在使用 MIXED 或 ROW 格式时，将副本上的二进制日志格式更改为 STATEMENT 格式也会导致相同类型的复制失败。要安全更改格式，必须停止复制，并确保在源和副本上都进行相同的更改。

如果您正在使用 InnoDB 表，并且事务隔离级别为 READ COMMITTED 或 READ UNCOMMITTED，则只能使用基于行的日志记录。可以可能将日志格式更改为 STATEMENT，但在运行时这样做会非常快速地导致错误，因为 InnoDB 无法再执行插入操作。

将二进制日志格式设置为 ROW 后，许多更改将以基于行的格式写入二进制日志。然而，仍然有一些更改使用基于语句的格式。例如，所有 DDL（数据定义语言）语句，如 CREATE TABLE, ALTER TABLE, 或 DROP TABLE。

当使用基于行的二进制日志记录时，binlog_row_event_max_size 系统变量及其对应的启动选项 --binlog-row-event-max-size 设置了行事件的最大大小的软限制。默认值为 8192 字节，该值只能在服务器启动时更改。在可能的情况下，存储在二进制日志中的行被分组为大小不超过此设置值的事件。如果事件无法分割，则最大大小可能会超过。

--binlog-row-event-max-size 选项适用于能够进行基于行的复制的服务器。行以不超过此选项值的字节大小的块存储在二进制日志中。该值必须是 256 的倍数。默认值为 8192。

警告

当为复制使用基于语句的日志记录时，如果语句设计为数据修改是非确定性的，即由查询优化器决定，则源和副本上的数据可能会变得不同。一般来说，即使在复制之外，这也不是一个好的做法。有关此问题的详细解释，请参见 Section B.3.7, “MySQL 中的已知问题”。

7.4.4.3 混合二进制日志格式

当在MIXED日志格式下运行时，服务器在以下情况下会自动从基于语句的日志切换到基于行的日志记录：

• 当函数包含UUID()时。
  
• 当更新一个或多个具有AUTO_INCREMENT列的表并调用触发器或存储函数时。与所有其他不安全语句一样，如果binlog_format = STATEMENT，则会生成警告。
  更多信息，请参见第 19.5.1.1 节，“复制和 AUTO_INCREMENT”。
  
• 当视图的主体需要基于行的复制时，创建视图的语句也会使用它。例如，当创建视图的语句使用UUID()函数时。
  
• 当涉及对可加载函数的调用时。
  
• 当使用FOUND_ROWS()或ROW_COUNT()时。（Bug #12092, Bug #30244）
  
• 当使用USER()、CURRENT_USER()或CURRENT_USER时。（Bug #28086）
  
• 当涉及的表之一是mysql数据库中的日志表时。
  
• 当使用LOAD_FILE()函数时。（Bug #39701）
  
• 当语句涉及一个或多个系统变量时。（Bug #31168）异常。 下列系统变量在会话范围（仅限）中使用时不会导致日志格式切换：

• auto_increment_increment
  
• auto_increment_offset
  
• character_set_client
  
• character_set_connection
  
• character_set_database
  
• character_set_server
  
• collation_connection
  
• collation_database
  
• collation_server
  
• foreign_key_checks
  
• identity
  
• last_insert_id
  
• lc_time_names
  
• pseudo_thread_id
  
• sql_auto_is_null
  
• time_zone
  
• timestamp
  
• unique_checks
  

• 有关确定系统变量范围的信息，请参见 Section 7.1.9, “Using System Variables”。
  有关复制如何处理sql_mode的信息，请参见 Section 19.5.1.39, “Replication and Variables”。
  

在早期版本中，当使用混合二进制日志格式时，如果一条语句被记录为行，并且执行该语句的会话有任何临时表，那么所有后续语句都被视为不安全，并以行为基础的格式记录，直到该会话中使用的所有临时表都被删除。从  MySQL 8.0 开始，对临时表的操作不会以混合二进制日��格式记录，并且会话中临时表的存在不会影响每条语句使用的日志模式。

注意

如果尝试使用基于语句的日志记录执行应该使用基于行的日志记录的语句，则会生成警告。警告会显示在客户端（在SHOW WARNINGS的输出中）和通过mysqld错误日志。每次执行这样的语句时，都会向SHOW WARNINGS表中添加一个警告。但是，为了防止日志淹没，每个客户端会话中生成警告的第一条语句才会写入错误日志。

除了上述决定外，各个引擎还可以确定在更新表中的信息时使用的日志格式。各个引擎的日志记录能力可以定义如下：

• 如果一个引擎支持基于行的日志记录，那么该引擎被称为支持行日志记录。
  
• 如果一个引擎支持基于语句的日志记录，那么该引擎被称为支持语句日志记录。
  

给定的存储引擎可以支持任一或两种日志记录格式。以下表格列出了每个引擎支持的格式。

语句是否记录以及使用的记录模式是根据语句类型（安全、不安全或二进制注入）、二进制日志格式（STATEMENT、ROW或MIXED）以及存储引擎的记录能力（支持语句、支持行、两者都支持或两者都不支持）来确定的。（二进制注入指的是必须使用ROW格式记录的更改的记录。）

语句可能会被记录，有或没有警告；失败的语句不会被记录，但会在日志中生成错误。这在以下决策表中显示。类型，binlog_format，SLC和RLC列概述了条件，错误/警告和记录为列代表相应的操作。SLC代表“支持语句记录”，RLC代表“支持行记录”。

当决定产生警告时，会产生一个标准的 MySQL 警告（可以使用 SHOW WARNINGS 查看）。这些信息也会被写入到 mysqld 错误日志中。为了防止日志被淹没，每个客户端连接的每个错误实例只记录一个错误。日志消息包括尝试执行的 SQL 语句。

如果一个复制实例设置了 log_error_verbosity 以显示警告，那么复制实例会将消息打印到错误日志中，以提供有关其状态的信息，例如开始作业的二进制日志和中继日志坐标，切换到另一个中继日志时，重新连接后的情况，不适合基于语句的日志记录的语句等等。

7.4.4.4 更改mysql数据库表的日志格式

mysql数据库中授权表的内容可以直接（例如，使用INSERT或DELETE）或间接（例如，使用GRANT或CREATE USER）进行修改。影响mysql数据库表的语句将根据以下规则写入二进制日志：

• 直接更改mysql数据库表中数据的数据操作语句将根据binlog_format系统变量的设置进行记录。这包括诸如INSERT、UPDATE、DELETE、REPLACE、DO、LOAD DATA、SELECT和TRUNCATE TABLE等语句。
  
• 间接更改mysql数据库的语句将作为语句记录，不受binlog_format值的影响。这包括诸如GRANT、REVOKE、SET PASSWORD、RENAME USER、CREATE（除CREATE TABLE ... SELECT之外的所有形式）、ALTER（所有形式）和DROP（所有形式）等语句。
  

CREATE TABLE ... SELECT是数据定义和数据操作的组合。CREATE TABLE部分使用语句格式记录，而SELECT部分根据binlog_format的值进行记录。

7.4.4.5 二进制日志事务压缩

从 MySQL 8.0.20 开始，您可以在 MySQL 服务器实例上启用二进制日志事务压缩。启用二进制日志事务压缩后，事务负载将使用 zstd 算法进行压缩，然后作为单个事件（Transaction_payload_event）写入服务器的二进制日志文件。

在将压缩的事务负载发送到副本、其他组复制组成员或客户端（如mysqlbinlog）时，压缩的事务负载保持压缩状态。接收线程不会对其进行解压缩，并且仍以压缩状态写入中继日志。因此，二进制日志事务压缩在事务发起者和接收者（以及它们的备份）上节省了存储空间，并在服务器实例之间发送事务时节省了网络带宽。

当需要检查其中包含的各个事件时，压缩的事务负载将被解压缩。例如，Transaction_payload_event由应用程序线程解压缩，以便在接收端应用其中包含的事件。在恢复期间，通过mysqlbinlog重放事务时，以及通过SHOW BINLOG EVENTS和SHOW RELAYLOG EVENTS语句进行解压缩。

你可以使用binlog_transaction_compression系统变量在 MySQL 服务器实例上启用二进制日志事务压缩，该变量默认为OFF。您还可以使用binlog_transaction_compression_level_zstd系统变量设置用于压缩的  zstd 算法的级别。该值确定了压缩的努力程度，从 1（最低努力）到  22（最高努力）。随着压缩级别的增加，压缩比例增加，从而减少了事务负载所需的存储空间和网络带宽。然而，数据压缩所需的努力也增加，消耗了源服务器上的时间、CPU  和内存资源。压缩努力的增加与压缩比例的增加之间没有线性关系。

注意

NDB 8.0.31 之前：可以在 NDB Cluster  中启用二进制日志事务压缩，但仅在使用–binlog-transaction-compression  选项（可能还包括–binlog-transaction-compression-level-zstd）启动服务器时；在运行时更改binlog_transaction_compression和binlog_transaction_compression_level_zstd系统变量的值对NDB表的日志记录没有影响。

NDB 8.0.31 及更高版本：您可以在运行时使用该版本引入的ndb_log_transaction_compression系统变量启用对使用NDB存储引擎的表的压缩事务的二进制日志记录，并使用ndb_log_transaction_compression_level_zstd控制压缩级别。在命令行或my.cnf文件中使用--binlog-transaction-compression启动mysqld会自动启用ndb_log_transaction_compression，并忽略--ndb-log-transaction-compression选项的任何设置；要仅为NDB存储引擎禁用二进制日志事务压缩，需在启动mysqld后在客户端会话中设置ndb_log_transaction_compression=OFF。

以下类型的事件不包括在二进制日志事务压缩中，因此始终以未压缩形式写入二进制日志：

• 与事务的 GTID 相关的事件（包括匿名 GTID 事件）。
  
• 其他类型的控制事件，例如视图更改事件和心跳事件。
  
• 事故事件和包含它们的任何事务的全部内容。
  
• 非事务事件和包含它们的任何事务的全部内容。涉及非事务性和事务性存储引擎混合的事务不会对其有效负载进行压缩。
  
• 使用基于语句的二进制日志记录的事件。二进制日志事务压缩仅适用于基于行的二进制日志格式。
  

可以在包含压缩事务的二进制日志文件上使用二进制日志加密。

MySQL8 中文参考（二十一）（2）https://developer.aliyun.com/article/1566171