23.7 存储的程序二进制日志记录

二进制日志包含有关修改数据库内容的 SQL 语句的信息。该信息以描述修改的“事件”的形式存储。 (二进制日志事件与计划的事件存储对象不同.)二进制日志有两个重要目的：

对于复制，二进制日志在源复制服务器上用作要发送到副本服务器的语句的记录。源将其二进制日志中包含的事件发送到其副本，副本将执行这些事件以对源进行相同的数据更改。参见第 16.2 节“复制实现”。
某些数据恢复操作需要使用二进制日志。还原备份文件后，将重新执行在执行备份后记录的二进制日志中的事件。这些事件使数据库从备份开始就保持最新状态。参见第 7.3.2 节“使用备份进行恢复”。

但是，如果日志记录发生在语句级别，则与存储程序(存储过程和函数，触发器和事件)有关的某些二进制日志记录问题：

在某些情况下，一条语句可能会影响源和副本上不同的行集。
在副本上执行的复制语句由副本 SQL 线程处理，该线程具有完全特权。一个过程可能遵循源服务器和副本服务器上的不同执行路径，因此用户可以编写一个包含危险语句的例程，该例程仅在具有完全特权的线程对其进行处理的副本上执行。
如果修改数据的存储程序是不确定的，则它是不可重复的。这可能导致源和副本上的数据不同，或导致还原的数据与原始数据不同。

本节介绍 MySQL 如何处理存储程序的二进制日志。它说明了实现在使用存储程序时所处的当前条件，以及如何避免记录问题。它还提供有关这些情况的原因的其他信息。

通常，此处描述的问题是在 SQL 语句级别发生二进制日志记录(基于语句的二进制日志记录)时产生的。如果使用基于行的二进制日志记录，则该日志包含由于执行 SQL 语句而对单个行所做的更改。执行例程或触发器时，将记录行更改，而不记录进行更改的语句。对于存储过程，这意味着不记录CALL语句。对于存储的函数，将记录在函数内进行的行更改，而不记录函数调用。对于触发器，将记录触发器进行的行更改。在副本端，仅看到行更改，而不看到存储的程序调用。

混合格式二进制日志记录(binlog_format=MIXED)使用基于语句的二进制日志记录，但仅保证基于行的二进制日志记录会导致正确结果的情况除外。对于混合格式，当存储的函数，存储过程，触发器，事件或准备好的语句包含对于基于语句的二进制日志记录不安全的任何内容时，整个语句将被标记为不安全并以行格式记录。用于创建和删除过程，函数，触发器和事件的语句始终是安全的，并以语句格式记录。有关基于行，混合和基于语句的日志记录以及如何确定安全和不安全语句的更多信息，请参见第 16.2.1 节“复制格式”。

除非另有说明，否则此处的 Comments 均假定服务器上启用了二进制日志记录(请参见第 5.4.4 节“二进制日志”)。如果未启用二进制日志，则无法进行复制，二进制日志也不可用于数据恢复。

在 MySQL 中使用存储函数的条件可以总结如下。这些条件不适用于存储过程或事件计划程序事件，并且除非启用了二进制日志记录，否则它们将不适用。

要创建或更改存储的功能，除了通常需要的CREATE ROUTINE或ALTER ROUTINE特权外，您还必须具有SUPER特权。 (取决于函数定义中的DEFINER值，无论是否启用了二进制日志记录，都可能需要SUPER。)
创建存储函数时，必须声明它是确定性的或未修改数据。否则，对于数据恢复或复制可能是不安全的。

默认情况下，要接受CREATE FUNCTION语句，必须显式指定DETERMINISTIC，NO SQL或READS SQL DATA中的至少一个。否则会发生错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

此函数是确定性的(并且不会修改数据)，因此很安全：

CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;

此函数使用UUID()，它不是确定性的，因此该函数也不是确定性的，也不安全：

CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
  RETURN UUID();
END;

此函数会修改数据，因此可能不安全：

CREATE FUNCTION f3(p_id INT)
RETURNS INT
BEGIN
  UPDATE t SET modtime = NOW() WHERE id = p_id;
  RETURN ROW_COUNT();
END;

对功能性质的评估基于创建者的“诚实”。 MySQL 不会检查声明为DETERMINISTIC的函数是否没有产生不确定结果的语句。

尝试执行存储的函数时，如果设置了binlog_format=STATEMENT，则必须在函数定义中指定DETERMINISTIC关键字。如果不是这种情况，除非指定了log_bin_trust_function_creators=1来覆盖此检查，否则将生成错误并且函数不会运行(请参见下文)。对于递归函数调用，仅在最外部的调用上需要DETERMINISTIC关键字。如果使用的是基于行的日志记录或混合二进制日志记录，则即使该函数未使用DETERMINISTIC关键字定义，该语句也会被接受并复制。
由于 MySQL 在创建时不会检查函数是否确实具有确定性，因此使用DETERMINISTIC关键字调用存储的函数可能会执行对基于语句的日志记录不安全的操作，或调用包含不安全语句的函数或过程。如果在设置binlog_format=STATEMENT时发生这种情况，则会发出警告消息。如果使用基于行或混合的二进制日志记录，则不会发出警告，并且该语句将以基于行的格式复制。
要放松函数创建的上述条件(您必须具有SUPER特权并且必须声明函数为确定性或不修改数据)，请将全局log_bin_trust_function_creators系统变量设置为 1.默认情况下，此变量的值为 0 ，但您可以这样更改：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

您也可以在服务器启动时设置此变量。

如果未启用二进制日志记录，则log_bin_trust_function_creators不适用。除非如上所述，函数定义中的DEFINER值要求，否则函数创建不需要SUPER。

有关可能对复制不安全(从而导致使用它们的存储函数也不安全)的内置函数的信息，请参见第 16.4.1 节“复制功能和问题”。

触发器与存储的函数相似，因此有关函数的前述说明也适用于触发器，但以下情况除外：CREATE TRIGGER没有可选的DETERMINISTIC特性，因此假定触发器始终是确定性的。但是，此假设在某些情况下可能无效。例如，UUID()函数是不确定的(并且不会复制)。在触发器中使用此类功能时要小心。

触发器可以更新 table，因此如果您没有必需的特权，则与CREATE TRIGGER相似的错误消息会通过CREATE TRIGGER出现。在副本端，副本使用触发器DEFINER属性来确定哪个用户被认为是触发器的创建者。

本节的其余部分提供有关日志记录实现及其含义的更多详细信息。除非您对存储常规使用中当前与日志记录相关的条件的原理背景感兴趣，否则无需阅读本手册。该讨论仅适用于基于语句的日志记录，而不适用于基于行的日志记录，但以下各项除外：CREATE和DROP语句被记录为语句，而不管记录方式如何。

服务器将CREATE EVENT，CREATE PROCEDURE，CREATE FUNCTION，ALTER EVENT，ALTER PROCEDURE，ALTER FUNCTION，DROP EVENT，DROP PROCEDURE和DROP FUNCTION语句写入二进制日志。
如果函数更改数据并且发生在本来不会记录的语句中，则存储的函数调用将记录为SELECT语句。这样可以防止由于未记录的语句中使用存储的函数而导致的数据更改不重复。例如，SELECT语句未写入二进制日志，但是SELECT可能会调用进行更改的存储函数。为了解决这个问题，当给定函数进行更改时，将SELECT func_name()语句写入二进制日志。假设在源服务器上执行以下语句：

CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
  IF (a < 3) THEN
    INSERT INTO t2 VALUES (a);
  END IF;
  RETURN 0;
END;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);

SELECT f1(a) FROM t1;

SELECT语句执行时，函数f1()被调用三次。这些调用中有两个会插入一行，MySQL 会为每个日志记录一个SELECT语句。也就是说，MySQL 将以下语句写入二进制日志：

SELECT f1(1);
SELECT f1(2);

当函数调用会导致错误的存储过程时，服务器还会为存储的函数调用记录SELECT语句。在这种情况下，服务器将SELECT语句以及预期的错误代码写入日志。在副本上，如果发生相同的错误，则这是预期的结果，复制将 continue。否则，复制将停止。

记录存储的函数调用而不是记录函数执行的语句具有复制的安全隐患，这由两个因素引起：
函数有可能遵循源服务器和副本服务器上的不同执行路径。
- 在副本服务器上执行的语句由具有完全特权的副本 SQL 线程处理。

含义是，尽管用户必须具有CREATE ROUTINE特权才能创建函数，但用户可以编写包含危险语句的函数，该函数仅在具有完全特权的线程对其进行处理的副本上执行。例如，如果源服务器和副本服务器的服务器 ID 值分别为 1 和 2，则源服务器上的用户可以创建并调用不安全函数unsafe_func()，如下所示：

mysql> delimiter //
mysql> CREATE FUNCTION unsafe_func () RETURNS INT
    -> BEGIN
    ->   IF @@server_id=2 THEN dangerous_statement; END IF;
    ->   RETURN 1;
    -> END;
    -> //
mysql> delimiter ;
mysql> INSERT INTO t VALUES(unsafe_func());

CREATE FUNCTION和INSERT语句被写入二进制日志，因此副本将执行它们。由于副本 SQL 线程具有完全特权，因此它将执行危险语句。因此，函数调用对源和副本具有不同的影响，并且不是复制安全的。

为了防止启用了二进制日志记录的服务器面临这种危险，除了通常需要的CREATE ROUTINE特权之外，存储函数创建者还必须具有SUPER特权。同样，要使用ALTER FUNCTION，除了ALTER ROUTINE特权外，还必须具有SUPER特权。没有SUPER特权，将发生错误：

ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

如果您不想让函数创建者拥有SUPER特权(例如，如果您系统上所有具有CREATE ROUTINE特权的用户都是经验丰富的应用程序开发人员)，请将全局log_bin_trust_function_creators系统变量设置为 1.您也可以将此变量设置为服务器启动。如果未启用二进制日志记录，则log_bin_trust_function_creators不适用。除非如上所述，函数定义中的DEFINER值要求，否则函数创建不需要SUPER。

如果执行更新的功能不确定，则该功能不可重复。这会产生两个不良影响：
它将使副本与源不同。
- 恢复的数据将与原始数据不同。

为了解决这些问题，MySQL 强制执行以下要求：在源服务器上，除非您声明功能是确定性的或不修改数据的，否则将拒绝创建和更改功能。两组功能 Feature 在这里适用：

DETERMINISTIC和NOT DETERMINISTIC特性指示给定 Importing 的功能是否始终产生相同的结果。如果没有给出任何 Feature，则默认值为NOT DETERMINISTIC。要声明函数是确定性的，必须显式指定DETERMINISTIC。
- CONTAINS SQL，NO SQL，READS SQL DATA和MODIFIES SQL DATA特性提供有关该函数是否读取或写入数据的信息。 NO SQL或READS SQL DATAtable 示函数不会更改数据，但您必须明确指定其中之一，因为如果未提供任何 Feature，则默认值为CONTAINS SQL。

默认情况下，要接受CREATE FUNCTION语句，必须显式指定DETERMINISTIC，NO SQL或READS SQL DATA中的至少一个。否则会发生错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)

如果将log_bin_trust_function_creators设置为 1，则将取消确定功能或不修改数据的要求。

存储过程调用记录在语句级别而不是CALL级别。也就是说，服务器不记录CALL语句，而是记录实际执行的过程中的那些语句。结果，在副本服务器上将观察到源服务器上发生的相同更改。这样可以防止由于在不同计算机上具有不同执行路径的过程而导致的问题。

通常，使用与以独立方式执行的语句相同的规则，将在存储过程内执行的语句写入二进制日志。在记录过程语句时要特别注意，因为过程内的语句执行与非过程上下文中的执行并不完全相同：

要记录的语句可能包含对本地过程变量的引用。这些变量在存储过程上下文之外不存在，因此引用该变量的语句不能原样记录。取而代之的是，出于日志记录目的，对此构造替换了对局部变量的每个引用：

NAME_CONST(var_name, var_value)

var_name 是局部变量名称， var_value 是一个常数，指示变量在记录语句时所具有的值。 NAME_CONST()的值为 var_value ，“名称”为 var_name *。因此，如果直接调用此函数，则会得到如下结果：

mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+

NAME_CONST()使已记录的独立语句可以在副本上执行，其效果与在存储过程中在源上执行的原始语句相同。

当源列 table 达式引用局部变量时，使用NAME_CONST()可能会导致创建 table...选择语句出现问题。将这些引用转换为NAME_CONST()table 达式可能会导致源服务器和副本服务器上的列名不同，或者名称太长而不能成为合法的列标识符。一种解决方法是为引用局部变量的列提供别名。当myvar的值为 1 时，请考虑以下语句：

CREATE TABLE t1 SELECT myvar;

它将被重写如下：

CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);

为确保源 table 和副本 table 具有相同的列名，请编写如下语句：

CREATE TABLE t1 SELECT myvar AS myvar;

重写后的语句变为：

CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;

要记录的语句可能包含对用户定义变量的引用。为了解决这个问题，MySQL 在二进制日志中写入了SET语句，以确保该变量存在于副本数据库中，并且其值与源变量相同。例如，如果一条语句引用变量@my_var，则该语句将在二进制日志中的以下语句之前，其中* value *是源上@my_var的值：

SET @my_var = value;

过程调用可以在已提交或回退的事务中发生。要考虑事务上下文，以便正确复制过程执行的事务方面。也就是说，服务器将这些语句记录在实际执行和修改数据的过程中，并根据需要记录BEGIN，COMMIT和ROLLBACK语句。例如，如果某个过程仅更新事务 table 并在回滚的事务中执行，则不会记录这些更新。如果该过程发生在提交的事务中，则BEGIN和COMMIT语句将与更新一起记录。对于在回滚事务中执行的过程，使用与以独立方式执行的语句相同的规则记录其语句：
不会记录对事务 table 的更新。
- 记录对非事务处理 table 的更新，因为回滚不会取消它们。
- 记录事务和非事务 table 混合的更新，并用BEGIN和ROLLBACK包围，以便副本将进行与源上相同的更改和回滚。
如果从存储函数内部调用存储过程，则不会在语句级别将存储过程调用写入二进制日志。在这种情况下，唯一记录的是调用该函数的语句(如果它在记录的语句内发生)或DO语句(如果它在未记录的语句内发生)。因此，即使过程本身是安全的，也应谨慎使用调用过程的存储函数。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35