MySQL8.0二进制日志格式详解及官方文档参考

服务器使用几种日志记录格式在二进制日志中记录信息&＃xff1a;

MySQL中的复制功能最初是基于SQL语句从主服务器到从服务器的传播。这称为基于语句的日志记录。您可以通过使用--binlog-format&＃61;STATEMENT启动服务器来使这种格式被使用 。

在基于行的日志记录(默认)中&＃xff0c;主服务器将事件写入二进制日志&＃xff0c;以指示各个表行受到的影响。您可以通过以开头使服务器使用基于行的日志记录 --binlog-format&＃61;ROW。

还有第三个选项&＃xff1a;混合日志记录。对于混合日志记录&＃xff0c;默认情况下使用基于语句的日志记录&＃xff0c;但是在某些情况下&＃xff0c;日志记录模式会自动切换为基于行&＃xff0c;如下所述。通过使用option 启动mysqld&＃xff0c; 可以使MySQL显式使用混合日志记录--binlog-format&＃61;MIXED。

日志记录格式还可以通过使用的存储引擎来设置或限制。这有助于消除在使用不同存储引擎的主服务器和从服务器之间复制某些语句时出现的问题。

使用基于语句的复制时&＃xff0c;复制不确定性语句可能会出现问题。在确定给定语句对于基于语句的复制是否安全时&＃xff0c;MySQL确定是否可以保证可以使用基于语句的日志记录来复制该语句。如果MySQL无法做出保证&＃xff0c;它将标记该语句为潜在不可靠的对象&＃xff0c;并发出警告&＃xff0c;以语句格式登录该语句可能并不安全。

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

设置二进制日志格式

您可以通过使用启动MySQL服务器来显式选择二进制日志记录格式 。支持的值为&＃xff1a; --binlog-format&＃61;typetype

STATEMENT 使日志记录基于语句。

ROW使日志记录基于行。这是默认值。

MIXED 使日志记录使用混合格式。

日志记录格式也可以在运行时进行切换&＃xff0c;尽管请注意&＃xff0c;在许多情况下您无法执行此操作&＃xff0c;如本节后面所述。设置binlog_format 系统变量的全局值&＃xff0c;以指定更改后连接的客户端的格式&＃xff1a;

mysql> SET GLOBAL binlog_format &＃61; &＃39;STATEMENT&＃39;;

mysql> SET GLOBAL binlog_format &＃61; &＃39;ROW&＃39;;

mysql> SET GLOBAL binlog_format &＃61; &＃39;MIXED&＃39;;

单个客户端可以通过将会话值设置为来控制其自己的语句的日志记录格式 binlog_format&＃xff1a;

mysql> SET SESSION binlog_format &＃61; &＃39;STATEMENT&＃39;;

mysql> SET SESSION binlog_format &＃61; &＃39;ROW&＃39;;

mysql> SET SESSION binlog_format &＃61; &＃39;MIXED&＃39;;

更改全局 binlog_format值需要足够的特权来设置全局系统变量。更改会话binlog_format值需要足够的特权来设置受限制的会话系统变量。请参见第5.1.9.1节“系统变量特权”。

客户端可能要基于每个会话设置二进制日志记录的原因有几个&＃xff1a;

对数据库进行许多细微更改的会话可能要使用基于行的日志记录。

执行与WHERE子句中的许多行匹配的更新的会话 可能要使用基于语句的日志记录&＃xff0c;因为记录几条语句比记录多行会更有效。

有些语句在主服务器上需要大量执行时间&＃xff0c;但导致仅修改了几行。因此&＃xff0c;使用基于行的日志记录来复制它们可能是有益的。

当您无法在运行时切换复制格式时&＃xff0c;会有一些例外情况&＃xff1a;

无法从存储的函数或触发器中更改复制格式。

如果NDB启用了存储引擎。

如果会话具有打开的临时表&＃xff0c;则不能为该会话(SET &＃64;&＃64;SESSION.binlog_format)更改复制格式。

如果任何复制通道都有打开的临时表&＃xff0c;则不能全局(SET &＃64;&＃64;GLOBAL.binlog_format或SET &＃64;&＃64;PERSIST.binlog_format)更改复制格式。

如果当前正在运行任何复制通道应用程序线程&＃xff0c;则不能全局(SET &＃64;&＃64;GLOBAL.binlog_format或 SET &＃64;&＃64;PERSIST.binlog_format)更改复制格式。

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

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

在复制进行过程中切换复制格式也会导致问题。每个MySQL Server可以设置自己的并且只能设置自己的二进制日志记录格式(无论binlog_format是在全局范围还是在会话范围内设置&＃xff0c;均为true )。这意味着更改复制主服务器上的日志记录格式不会导致从属服务器更改其日志记录格式以匹配。使用 STATEMENT模式时&＃xff0c; binlog_format不会复制系统变量。使用MIXED或 ROW记录模式时&＃xff0c;它会被复制&＃xff0c;但被从属设备忽略。

复制从站无法将以ROW日志记录格式 接收的二进制日志条目STATEMENT转换为在其自己的二进制日志中使用的格式。因此&＃xff0c;如果主服务器需要&＃xff0c;则从服务器必须使用ROW或 MIXED格式化。将主服务器上的二进制日志记录格式从更改 STATEMENT为ROW或 MIXED正在进行复制到从属服务器时&＃xff0c;其STATEMENT格式会导致复制失败&＃xff0c;并显示诸如以下错误&＃xff1a;执行行事件出错&＃xff1a;&＃39;无法执行语句&＃xff1a;由于语句位于行中&＃xff0c;因此无法写入二进制日志格式&＃xff0c;BINLOG_FORMAT &＃61; STATEMENT。将从站上的二进制日志记录格式更改为STATEMENT当主服务器仍在使用时格式化MIXED或 ROW格式化也会导致相同类型的复制失败。为了安全地更改格式&＃xff0c;必须停止复制并确保在主服务器和从服务器上都进行了相同的更改。

如果使用InnoDB表&＃xff0c;并且事务隔离级别为READ COMMITTED或READ UNCOMMITTED&＃xff0c;则只能使用基于行的日志记录。这是 可能的更改日志格式 STATEMENT&＃xff0c;但在运行时引线这样做非常迅速地错误&＃xff0c;因为InnoDB不能再进行插入。

将二进制日志格式设置ROW为时&＃xff0c;使用基于行的格式将许多更改写入二进制日志。但是&＃xff0c;某些更改仍然使用基于语句的格式。实例包括所有DDL(数据定义语言)语句&＃xff0c;例如CREATE TABLE&＃xff0c; ALTER TABLE&＃xff0c;或 DROP TABLE。

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

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

警告

当使用基于语句的日志记录进行复制时&＃xff0c;如果以一种不确定的方式修改数据的方式设计一条语句&＃xff0c;则主服务器和从服务器上的数据可能会变得不同 。也就是说&＃xff0c;它由查询优化器决定。通常&＃xff0c;即使在复制之外&＃xff0c;这也不是一个好习惯。有关此问题的详细说明&＃xff0c;请参见 第B.4.7节“ MySQL中的已知问题”。

混合二进制记录格式

当以MIXED日志记录格式运行时&＃xff0c;服务器在以下情况下自动从基于语句的记录切换为基于行的记录&＃xff1a;

当函数包含时 UUID()。

当一个或多个带有 AUTO_INCREMENT列的表被更新并且触发器或存储的函数被调用时。像所有其他不安全的语句一样&＃xff0c;如果会生成警告 binlog_format &＃61; STATEMENT。

有关更多信息&＃xff0c;请参见 第17.5.1.1节“复制和AUTO_INCREMENT”。

当视图主体需要基于行的复制时&＃xff0c;创建视图的语句也将使用它。例如&＃xff0c;当创建视图的语句使用该UUID()函数时&＃xff0c;就会发生这种情况 。

当涉及到UDF的调用时。

使用FOUND_ROWS()或时 ROW_COUNT()。(缺陷&＃xff03;12092&＃xff0c;缺陷&＃xff03;30244)

使用USER()&＃xff0c; CURRENT_USER()或时 CURRENT_USER。(错误&＃xff03;28086)

当涉及的表之一是mysql数据库中的日志表时 。

使用该LOAD_FILE()功能时。(缺陷&＃xff03;39701)

当一条语句引用一个或多个系统变量时。错误&＃xff03;31168)

例外。 以下系统变量(仅与会话范围一起使用)不会导致切换日志记录格式&＃xff1a;

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

有关确定系统变量范围的信息&＃xff0c;请参见 第5.1.9节“使用系统变量”。

有关复制如何处理的信息 sql_mode&＃xff0c;请参见 第17.5.1.38节“复制和变量”。

在较早的版本中&＃xff0c;当使用混合二进制日志记录格式时&＃xff0c;如果按行记录一条语句&＃xff0c;并且执行该语句的会话具有任何临时表&＃xff0c;则所有后续语句均被视为不安全&＃xff0c;并以基于行的格式记录&＃xff0c;直到所有临时表该会话正在使用中的已删除。从MySQL 8.0开始&＃xff0c;对临时表的操作不会以混合二进制日志格式记录&＃xff0c;并且会话中临时表的存在不会影响每个语句所使用的日志模式。

注意

如果尝试使用基于语句的日志记录执行语句&＃xff0c;则应使用基于行的日志记录来生成警告。该警告在客户端(在的输出中SHOW WARNINGS)和mysqld错误日志中均显示。SHOW WARNINGS 每次执行这样的语句时&＃xff0c;都会向表中添加一条警告。但是&＃xff0c;只有为每个客户端会话生成警告的第一条语句才被写入错误日志&＃xff0c;以防止日志泛滥。

除了上述决定外&＃xff0c;各个引擎还可以确定更新表中信息时使用的日志记录格式。单个引擎的日志记录功能可以定义如下&＃xff1a;

如果引擎支持基于行的日志记录&＃xff0c;则称该引擎具有行记录功能。

如果引擎支持基于语句的日志记录&＃xff0c;则称该引擎具有语句记录能力。

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

储存引擎

支持行记录

支持语句记录

ARCHIVE

是

是

BLACKHOLE

是

是

CSV

是

是

EXAMPLE

是

没有

FEDERATED

是

是

HEAP

是

是

InnoDB

是

当事务隔离级别为REPEATABLE READ或 时为“是” SERIALIZABLE&＃xff1b;没有其他。

MyISAM

是

是

MERGE

是

是

NDB

是

没有

更改MySQL数据库表的日志记录格式

mysql 数据库中 授权表的内容可以直接(例如&＃xff0c;用INSERT或 DELETE)修改&＃xff0c;也可以 间接地(例如用GRANT或 CREATE USER)修改。mysql使用以下规则将影响数据库表的语句写入二进制日志&＃xff1a;

mysql根据binlog_format系统变量的设置&＃xff0c;直接记录直接 更改数据库表中 数据的数据操作语句 。这适用于语句&＃xff0c;如 INSERT&＃xff0c; UPDATE&＃xff0c; DELETE&＃xff0c; REPLACE&＃xff0c; DO&＃xff0c;LOAD DATA&＃xff0c;SELECT&＃xff0c;和 TRUNCATE TABLE。

mysql不管的值如何&＃xff0c;都 将间接更改数据库的语句记录为语句binlog_format。这涉及语句&＃xff0c;例如 GRANT&＃xff0c; REVOKE&＃xff0c; SET PASSWORD&＃xff0c; RENAME USER&＃xff0c; CREATE(所有形式的除外 CREATE TABLE ... SELECT)&＃xff0c; ALTER(所有形式的)&＃xff0c;和DROP(各种形式)。

CREATE TABLE ... SELECT是数据定义和数据处理的组合。该CREATE TABLE 部分是利用语句格式记录&＃xff0c;且 SELECT部分是根据的值记录binlog_format。