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

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

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

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

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

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

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

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

默认情况下，要接受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的函数是否没有产生不确定结果的语句。

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

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

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

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

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

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

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。否则，复制将停止。

含义是，尽管用户必须具有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 在这里适用：

默认情况下，要接受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，则将取消确定功能或不修改数据的要求。

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

NAME_CONST(var_name, var_value)

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;

SET @my_var = value;
首页