在复制中，二进制日志在源复制服务器上用作要发送到副本服务器的语句记录。源服务器将其二进制日志中包含的事件发送给其副本，副本执行这些事件，以实现与源服务器相同的数据更改。参见 第 19.2 节，“复制实现”。For replication, the binary log is used on source replication servers as a record of the statements to be sent to replica servers. The source sends the events contained in its binary log to its replicas, which execute those events to make the same data changes that were made on the source. See Section 19.2, “Replication Implementation”.

某些数据恢复操作需要使用二进制日志。在备份文件恢复后，备份创建后记录的二进制日志事件会被重新执行。这些事件使数据库从备份时点更新到最新状态。参见 第 9.3.2 节，“使用备份进行恢复”。Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 9.3.2, “Using Backups for Recovery”.

在某些情况下，一个语句可能会对源和副本上的不同行集合产生影响。In some cases, a statement might affect different sets of rows on source and replica.

在副本上执行的复制语句由副本的应用线程处理。除非您实施复制权限检查（参见 第 19.3.3 节，“复制权限检查”），否则应用线程拥有完整权限。在这种情况下，存储过程可能在源服务器和副本服务器上走不同的执行路径，因此用户可能编写一个仅在副本上执行的危险语句的例程。Replicated statements executed on a replica are processed by the replica's applier thread. Unless you implement replication privilege checks (see Section 19.3.3, “Replication Privilege Checks”), the applier thread has full privileges. In this situation, it is possible for a procedure to follow different execution paths on source and replica servers, so a user could write a routine containing a dangerous statement that executes only on the replica.

如果修改数据的存储程序是非确定性的，那么它是不可重复的。这可能导致源和副本上的数据不同，或者导致恢复的数据与原始数据不同。If a stored program that modifies data is nondeterministic, it is not repeatable. This can result in different data on source and replica, or cause restored data to differ from the original data.

要创建或修改存储函数，除了通常所需的 CREATE ROUTINE 或 ALTER ROUTINE 权限之外，还必须拥有 SET_ANY_DEFINER 权限。（根据函数定义中的 DEFINER 值，无论是否启用二进制日志，都可能需要 SET_ANY_DEFINER 权限。参见 第 15.1.17 节，“CREATE PROCEDURE 和 CREATE FUNCTION 语句”。）To create or alter a stored function, you must have the SET_ANY_DEFINER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required. (Depending on the DEFINER value in the function definition, SET_ANY_DEFINER might be required regardless of whether binary logging is enabled. See Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”.)

当您创建存储函数时，必须声明它是确定性的或不修改数据。否则，它可能对数据恢复或复制不安全。When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

默认情况下，对于 CREATE FUNCTION 语句，要被接受，必须显式指定 DETERMINISTIC、NO SQL 或 READS SQL DATA 中的至少一项。否则会发生错误：By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:

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)

此函数是确定性的（且不修改数据），因此是安全的：This function is deterministic (and does not modify data), so it is safe:

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

此函数使用 UUID()，它不是确定性的，因此该函数也不是确定性的，并且不安全：This function uses UUID(), which is not deterministic, so the function also is not deterministic and is not safe:

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

此函数修改数据，因此可能不安全：This function modifies data, so it may not be safe:

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

对函数性质的评估是基于创建者的“诚信”。MySQL 并不检查被声明为 DETERMINISTIC 的函数是否包含会产生非确定性结果的语句。Assessment of the nature of a function is based on the “honesty” of the creator. MySQL does not check that a function declared DETERMINISTIC is free of statements that produce nondeterministic results.

当您尝试执行存储函数时，如果设置了binlog_format=STATEMENT，则必须在函数定义中指定DETERMINISTIC关键字。如果没有指定，将会生成错误并且函数无法运行，除非指定log_bin_trust_function_creators=1来覆盖此检查（见下文）。对于递归函数调用，仅在最外层调用中需要DETERMINISTIC关键字。如果使用基于行或混合的二进制日志，即使函数定义时没有DETERMINISTIC关键字，该语句仍然会被接受并复制。When you attempt to execute a stored function, if binlog_format=STATEMENT is set, the DETERMINISTIC keyword must be specified in the function definition. If this is not the case, an error is generated and the function does not run, unless log_bin_trust_function_creators=1 is specified to override this check (see below). For recursive function calls, the DETERMINISTIC keyword is required on the outermost call only. If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the DETERMINISTIC keyword.

由于 MySQL 在创建时不会检查函数是否真的确定性，带有 DETERMINISTIC 关键字的存储函数调用可能执行对基于语句的日志记录不安全的操作，或调用包含不安全语句的函数或过程。如果在设置 binlog_format=STATEMENT 时发生这种情况，将发出警告信息。如果使用基于行或混合的二进制日志记录，则不会发出警告，并且该语句将以基于行的格式进行复制。Because MySQL does not check if a function really is deterministic at creation time, the invocation of a stored function with the DETERMINISTIC keyword might carry out an action that is unsafe for statement-based logging, or invoke a function or procedure containing unsafe statements. If this occurs when binlog_format=STATEMENT is set, a warning message is issued. If row-based or mixed binary logging is in use, no warning is issued, and the statement is replicated in row-based format.

要放宽有关函数创建的前述条件（即您必须拥有 SUPER 权限，并且函数必须声明为确定性或不修改数据），请将全局 log_bin_trust_function_creators 系统变量设置为 1。默认情况下，该变量的值为 0，但您可以这样更改：To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like this:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

您也可以在服务器启动时设置此变量。You can also set this variable at server startup.

如果未启用二进制日志，log_bin_trust_function_creators 不适用。除非如前所述，函数定义中的 DEFINER 值需要，否则创建函数不需要 SUPER 权限。If binary logging is not enabled, log_bin_trust_function_creators does not apply. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.

有关可能对复制不安全的内置函数（因此使用它们的存储函数也可能不安全）的信息，请参见 第 19.5.1 节，“复制功能和问题”。For information about built-in functions that may be unsafe for replication (and thus cause stored functions that use them to be unsafe as well), see Section 19.5.1, “Replication Features and Issues”.

服务器将 CREATE EVENT、CREATE PROCEDURE、CREATE FUNCTION、ALTER EVENT、ALTER PROCEDURE、ALTER FUNCTION、DROP EVENT、DROP PROCEDURE 以及 DROP FUNCTION 语句写入二进制日志。The server writes CREATE EVENT, CREATE PROCEDURE, CREATE FUNCTION, ALTER EVENT, ALTER PROCEDURE, ALTER FUNCTION, DROP EVENT, DROP PROCEDURE, and DROP FUNCTION statements to the binary log.

如果存储函数改变数据并且出现在本来不会被记录的语句中，则其调用会作为 SELECT 语句被记录。这可以防止由于在非记录语句中使用存储函数而导致的数据更改无法复制。例如，SELECT 语句不会写入二进制日志，但一个 SELECT 可能会调用修改数据的存储函数。为此，当给定函数进行更改时，会将 SELECT func_name() 语句写入二进制日志。假设在源服务器上执行以下语句：A stored function invocation is logged as a SELECT statement if the function changes data and occurs within a statement that would not otherwise be logged. This prevents nonreplication of data changes that result from use of stored functions in nonlogged statements. For example, SELECT statements are not written to the binary log, but a SELECT might invoke a stored function that makes changes. To handle this, a SELECT func_name() statement is written to the binary log when the given function makes a change. Suppose that the following statements are executed on the source server:

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 会将以下语句写入二进制日志：When the SELECT statement executes, the function f1() is invoked three times. Two of those invocations insert a row, and MySQL logs a SELECT statement for each of them. That is, MySQL writes the following statements to the binary log:

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

当存储函数调用一个导致错误的存储过程时，服务器还会记录该函数调用的 SELECT 语句。在这种情况下，服务器会将该 SELECT 语句及预期的错误代码写入日志。在副本上，如果发生相同的错误，这是预期结果，复制会继续。否则，复制会停止。The server also logs a SELECT statement for a stored function invocation when the function invokes a stored procedure that causes an error. In this case, the server writes the SELECT statement to the log along with the expected error code. On the replica, if the same error occurs, that is the expected result and replication continues. Otherwise, replication stops.

记录存储函数调用而不是函数执行的语句对复制存在安全隐患，这种隐患来自两个因素：Logging stored function invocations rather than the statements executed by a function has a security implication for replication, which arises from two factors:

其含义是，尽管用户必须拥有 CREATE ROUTINE 权限才能创建函数，但用户可以编写包含危险语句的函数，该语句仅在副本上执行，由拥有全部权限的线程处理。例如，如果源服务器和副本服务器的服务器 ID 分别为 1 和 2，则源服务器上的用户可以创建并调用不安全函数 unsafe_func()，如下所示：The implication is that although a user must have the CREATE ROUTINE privilege to create a function, the user can write a function containing a dangerous statement that executes only on the replica where it is processed by a thread that has full privileges. For example, if the source and replica servers have server ID values of 1 and 2, respectively, a user on the source server could create and invoke an unsafe function unsafe_func() as follows:

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 语句会被写入二进制日志，因此副本会执行它们。因为副本的应用线程拥有完整权限，所以它会执行危险的语句。因此，函数调用在源端和副本端的效果不同，并且不安全进行复制。The CREATE FUNCTION and INSERT statements are written to the binary log, so the replica executes them. Because the replica's applier thread has full privileges, it executes the dangerous statement. Thus, the function invocation has different effects on the source and replica and is not replication-safe.

为了防止在启用了二进制日志的服务器上出现这种危险，存储函数的创建者除了需要通常的 CREATE ROUTINE 权限外，还必须拥有 SUPER 权限。类似地，要使用 ALTER FUNCTION，除了需要 ALTER ROUTINE 权限外，还必须拥有 SUPER 权限。如果没有 SUPER 权限，将会发生错误：To guard against this danger for servers that have binary logging enabled, stored function creators must have the SUPER privilege, in addition to the usual CREATE ROUTINE privilege that is required. Similarly, to use ALTER FUNCTION, you must have the SUPER privilege in addition to the ALTER ROUTINE privilege. Without the SUPER privilege, an error occurs:

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 特权。If you do not want to require function creators to have the SUPER privilege (for example, if all users with the CREATE ROUTINE privilege on your system are experienced application developers), set the global log_bin_trust_function_creators system variable to 1. You can also set this variable at server startup. If binary logging is not enabled, log_bin_trust_function_creators does not apply. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.

建议无论您对函数创建者的权限作出何种选择，都使用复制权限检查。可以设置复制权限检查以确保只有预期的和相关的操作被授权用于复制通道。有关执行此操作的说明，请参见 第 19.3.3 节，“复制权限检查”。The use of replication privilege checks recommended regardless of any choices you make about privileges for function creators. Replication privilege checks can be set up to ensure that only expected and relevant operations are authorized for the replication channel. For instructions to do this, see Section 19.3.3, “Replication Privilege Checks”.

如果执行更新的函数是非确定性的，它是不可重复的。这可能有两个不良影响：If a function that performs updates is nondeterministic, it is not repeatable. This can have two undesirable effects:

为了解决这些问题，MySQL 强制以下要求：在源服务器上，除非您声明函数是确定性的或不修改数据，否则拒绝创建和修改函数。这里适用两组函数特性：To deal with these problems, MySQL enforces the following requirement: On a source server, creation and alteration of a function is refused unless you declare the function to be deterministic or to not modify data. Two sets of function characteristics apply here:

默认情况下，对于 CREATE FUNCTION 语句，要被接受，必须显式指定 DETERMINISTIC、NO SQL 或 READS SQL DATA 中的至少一项。否则会发生错误：By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:

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，则不再要求函数必须是确定性的或不修改数据。If you set log_bin_trust_function_creators to 1, the requirement that functions be deterministic or not modify data is dropped.

存储过程调用记录的是语句级别，而不是 CALL 级别。也就是说，服务器不记录 CALL 语句，而是记录过程内实际执行的那些语句。因此，源服务器上发生的相同更改也会在副本上发生。这可以防止由于过程在不同机器上有不同执行路径而可能导致的问题。Stored procedure calls are logged at the statement level rather than at the CALL level. That is, the server does not log the CALL statement, it logs those statements within the procedure that actually execute. As a result, the same changes that occur on the source server also occur on replicas. This prevents problems that could result from a procedure having different execution paths on different machines.

通常情况下，在存储过程中执行的语句会按照与单独执行语句时相同的规则写入二进制日志。在记录过程语句时需要特别注意，因为在存储过程中的语句执行与非过程上下文中的执行并不完全相同：In general, statements executed within a stored procedure are written to the binary log using the same rules that would apply were the statements to be executed in standalone fashion. Some special care is taken when logging procedure statements because statement execution within procedures is not quite the same as in nonprocedure context:

如果存储过程从存储函数内部调用，则该存储过程的调用 不会 在语句级别写入二进制日志。在这种情况下，唯一被记录的只是调用该函数的语句（如果它发生在被记录的语句中）或 DO 语句（如果它发生在未被记录的语句中）。因此，即使该存储过程本身是安全的，也应在使用调用过程的存储函数时谨慎。A stored procedure call is not written to the binary log at the statement level if the procedure is invoked from within a stored function. In that case, the only thing logged is the statement that invokes the function (if it occurs within a statement that is logged) or a DO statement (if it occurs within a statement that is not logged). For this reason, care should be exercised in the use of stored functions that invoke a procedure, even if the procedure is otherwise safe in itself.