存储程序的二进制日志
二进制日志包含修改数据库内容的SQL语句信息。这些信息以描述修改的“事件”的形式存储。二进制日志有两个重要的目的。
.对于复制，主复制服务器使用二进制日志记录要发送到从服务器的语句。主服务器将包含在其二进制日志中的事件发送给从服务器，从服务器 执行这些事件以进行与主服务器相同的数据更改。
.某些数据恢复操作需要使用二进制日志。恢复备份文件后，将重新执行二进制日志中记录的备份后的事件。这些事件使数据库从备份点更新到 最新状态。
但是，如果日志记录发生在语句级别，存储程序（存储过程和函数、触发器和事件）会出现某些二进制日志记录问题：
.在某些情况下，一条语句可能会影响主从端的不同行集。
.在从服务器上执行的复制语句由具有完全特权的从SQL线程处理。一个过程可能在主服务器和从服务器上遵循不同的执行路径，因此用户可以编 写包含危险语句的例程，该例程仅在由具有完全特权的线程处理的从服务器上执行
.如果修改数据的存储程序是不确定的，则它是不可重复的。这可能导致主服务器和从服务器上的数据不同，或者导致恢复的数据与原始数据不 同。
本节描述MySQL如何处理存储程序的二进制日志记录。它陈述了实现对存储程序使用的当前条件，以及您可以做些什么来避免日志记录问题。它 还提供了关于这些条件的原因的额外信息。
一般来说，当SQL语句级别发生二进制日志记录时，就会出现这里描述的问题。如果使用基于行的二进制日志，则日志包含由于执行SQL语句而对 单个行所做的更改。当例程或触发器执行时，记录行更改，而不是进行更改的语句。对于存储过程，这意味着不会记录CALL语句。对于存储函数 ，在函数内所做的行更改将被记录，而不是函数调用。对于触发器，触发器所做的行更改将被记录。在从端，只会看到行上的变化，而不会看到 对存储程序的调用。
除非另有说明，否则这里的注释假定您通过使用——log-bin选项启动服务器，从而启用了二进制日志记录。如果未启用二进制日志，则无法进 行复制，也无法使用二进制日志进行数据恢复。
在MySQL中使用存储函数的条件可以总结如下。这些条件不适用于存储过程或Event Scheduler事件，除非启用了二进制日志记录，否则这些条件 不适用。
.要创建或修改存储函数，除了具有create权限外，还必须具有SUPER权限通常需要的ROUTINE或ALTER ROUTINE权限。(根据函数定义中的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性的函数是否没有产生不确定性结果的语句。
.虽然可以在不指定deterministic的情况下创建确定性存储函数，但不能使用基于语句的二进制日志记录执行该函数。要执行这样的函数，必须 使用基于行或混合二进制日志记录。另外，如果在函数定义中显式指定DETERMINISTIC，则可以使用任何类型的日志记录，包括基于语句的日志 记录。
.要放宽上述创建函数的条件（必须具有SUPER权限，并且必须声明函数是确定的或不能修改数据），请将全局 log_bin_trust_function_creators系统变量设置为1。默认情况下，这个变量的值为0，但你可以这样修改它：
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
您也可以在启动服务器时使用–log-bin-trust-function-creators=1选项来设置这个变量。
.有关可能对复制不安全的内置函数（从而导致使用它们的存储函数也不安全）的信息，请参阅第16.4.1节，“复制功能和问题”。
触发器类似于存储函数，因此前面关于函数的注释也适用于触发器，但有以下例外：CREATE TRIGGER没有可选的DETERMINISTIC特征，因此假定 触发器始终是确定性的。然而，这种假设在某些情况下可能是无效的。例如，UUID（）函数是不确定的（并且不复制）。在触发器中使用这类函 数时要小心。
触发器可以更新表，因此，如果您没有所需的权限，则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语句连同预期的错误代码 一起写入日志。在从属服务器上，如果出现相同的错误，这就是预期的结果，复制将继续进行。否则，复制停止。
.记录存储函数调用，而不是记录函数执行的语句，对复制有安全隐患，这源于两个因素：
.一个函数可能在主服务器和从服务器上遵循不同的执行路径。
.在从属线程上执行的语句由具有完全权限的从属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=1选项来 设置此变量。如果没有启用二进制日志记录，log_bin_trust_function_creators将不适用。创建函数时不需要SUPER，除非如前所述，函数定义 中的DEFINER值需要它。
.如果执行更新的函数是不确定的，则它是不可重复的。这可能会产生两种不良影响：
.它将使从服务器与主服务器不同。
.恢复后的数据将与原始数据不同。
为了处理这些问题，MySQL强制执行以下要求：在主服务器上，除非您声明函数是确定的或不修改数据，否则拒绝创建和更改函数。这里适用两 组函数特征：
.DETERMINISTIC和NOT DETERMINISTIC特征表明对于给定的输入，函数是否总是产生相同的结果。如果两个特征都没有给出，默认值是NOT DETERMINISTIC。要声明函数是确定性的，必须显式指定deterministic。
.CONTAINS SQL、NO SQL、READS SQL DATA和modified SQL DATA特性提供了函数是否读取或写入数据的信息。NO SQL或READS SQL DATA表示函 数不更改数据，但您必须显式指定其中一个，因为如果没有给定特征，默认值是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的“name”。因此，如果你直接调用这个函数，你会得到这样的结果：
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+
1 row in set (0.00 sec)
NAME_CONST（）允许在从机上执行已记录的独立语句，其效果与存储过程中在主机上执行的原始语句相同。
使用NAME_CONST（）可能会导致CREATE TABLE…SELECT语句当源列表达式引用局部变量时，。将这些引用转换为NAME_CONST（）表达式可能导致 主服务器和从服务器上的列名不同，或者列名太长而不能作为合法的列标识符。一种解决方法是为引用局部变量的列提供别名。考虑当myvar的 值为1时的这个语句：
CREATE TABLE t1 SELECT myvar;
它将被重写如下：
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
为了确保主表和从表具有相同的列名，编写这样的语句：
CREATE TABLE t1 SELECT myvar AS myvar;
改写后的语句变成：
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
.要记录的语句可能包含对用户定义变量的引用。为了解决这个问题，MySQL在二进制日志中写入一个SET语句，以确保从服务器上的变量与主服 务器上的变量值相同。例如，如果有一条语句引用了变量@my_var，那么在二进制日志中，这条语句的前面会有以下语句，其中value是@my_var 在master上的值：
SET @my_var = value;
.过程调用可以发生在已提交或回滚的事务中。考虑了事务上下文，以便正确地复制过程执行的事务方面。也就是说，服务器在实际执行和修改 数据的过程中记录这些语句，并在必要时记录BEGIN、COMMIT和ROLLBACK语句。例如，如果过程只更新事务表，并且在回滚的事务中执行，则不 会记录这些更新。如果该过程发生在已提交的事务中，则BEGIN和COMMIT语句将随更新一起记录。对于在回滚事务中执行的过程，其语句使用与 以独立方式执行语句相同的规则记录：
.对事务表的更新不记录日志。
.对非事务性表的更新要记录日志，因为回滚不会取消它们。
.对于事务表和非事务表的混合更新都被记录在BEGIN和ROLLBACK中，这样从服务器就可以和主服务器一样进行相同的更改和回滚。
.如果从存储函数中调用存储过程，则不会在语句级别将存储过程调用写入二进制日志。在这种情况下，唯一记录的是调用函数的语句（如果它 发生在记录日志的语句中）或DO语句（如果它发生在没有记录日志的语句中）。因此，在使用调用过程的存储函数时应该谨慎，即使过程本身是 安全的。