相关的文档10bet官方网站 本手册下载 本手册节选

MySQL 8.0参考手册/存储对象/存储程序二进制日志

25.7存储的程序二进制日志

二进制日志包含修改数据库内容的SQL语句信息。此信息存储为事件这就是修改的内容。(二进制日志事件与存储对象中的预定事件不同。)二进制日志有两个重要的用途:

  • 对于复制,二进制日志在源复制服务器上用作要发送到复制服务器的语句的记录。源将二进制日志中包含的事件发送到副本,副本执行这些事件,以做出与源上相同的数据更改。看到第17.2节,“复制实现”

  • 某些数据恢复操作需要使用二进制日志。恢复备份文件后,将重新执行在备份后记录的二进制日志中的事件。这些事件使数据库从备份点更新。看到第7.3.2节,“使用备份进行恢复”

然而,如果日志记录发生在语句级别,则存储程序(存储过程和函数、触发器和事件)会出现某些二进制日志记录问题:

  • 在某些情况下,一条语句可能会影响源和副本上的不同行集。

  • 在副本上执行的复制语句由副本的应用程序线程处理。除非你实现了复制权限检查,这在MySQL 8.0.18中是可用的(参见第17.3.3节,“复制权限检查”)时,应用程序线程具有完全特权。在这种情况下,过程可能在源服务器和副本服务器上遵循不同的执行路径,因此用户可以编写一个包含危险语句的例程,只在副本上执行。

  • 如果一个修改数据的存储程序是不确定的,它是不可重复的。这可能导致源和副本上的数据不同,或者导致恢复数据与原始数据不同。

本节描述MySQL如何处理存储程序的二进制日志。它说明了该实现对存储程序使用的当前条件,以及您可以做些什么来避免日志问题。它还提供了关于这些情况的原因的额外信息。

除非另有说明,这里的注释假定在服务器上启用了二进制日志记录(参见第5.4.4节,“二进制日志”)。如果未启用二进制日志,则无法进行复制,也无法使用二进制日志进行数据恢复。从MySQL 8.0开始,二进制日志默认是启用的,只有当您指定——skip-log-bin——disable-log-bin在启动时选项。

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

混合格式二进制日志(binlog_format =混合)使用基于语句的二进制日志记录,除非只有基于行的二进制日志记录才能保证得到正确的结果。使用混合格式,当存储函数、存储过程、触发器、事件或准备好的语句包含任何对于基于语句的二进制日志记录不安全的内容时,整个语句将被标记为不安全的,并以行格式登录。用于创建和删除过程、函数、触发器和事件的语句总是安全的,并且以语句格式记录。有关基于行、混合和基于语句的日志记录以及如何确定安全和不安全语句的更多信息,请参见第17.2.1节,“复制格式”

MySQL中存储函数的使用条件总结如下。这些条件不适用于存储过程或事件调度器事件,除非启用二进制日志记录,否则它们不适用。

  • 要创建或更改存储函数,必须具有SET_USER_ID特权(或已弃用的超级特权),除了创建程序改变日常通常需要的特权。(取决于定义者函数定义中的值,SET_USER_ID超级无论是否启用二进制日志记录,都可能需要。看到第13.1.17节,“CREATE PROCEDURE and CREATE FUNCTION语句”)。

  • 在创建存储函数时,必须声明它是确定性的,或者声明它不修改数据。否则,数据恢复或复制可能不安全。

    缺省情况下,对于a创建函数声明被接受,至少有一个确定的没有SQL,或读取SQL数据必须显式指定。否则将出现错误:

    错误1418 (HY000):该函数在其声明中没有DETERMINISTIC, NO SQL,或读取SQL DATA,并且启用了二进制日志记录(你可能想使用不安全的log_bin_trust_function_creators变量)

    这个函数是确定性的(不修改数据),所以它是安全的:

    CREATE FUNCTION f1(i INT)返回INT确定读取SQL数据开始返回i;结束;

    这个函数使用UUID (),这是不确定的,所以函数也是不确定的,不安全:

    CREATE FUNCTION () RETURN CHAR(36) CHARACTER SET utf8 BEGIN RETURN UUID();结束;

    这个函数修改数据,所以可能不安全:

    CREATE FUNCTION f3(p_id INT) return INT BEGIN UPDATE t SET modtime = NOW() WHERE id = p_id;返回ROW_COUNT ();结束;

    函数性质的评估是基于诚实的创造者。MySQL不检查函数声明确定的不包含产生不确定结果的语句。

  • 当您试图执行一个存储的函数时,如果binlog_format =声明设置,确定的关键字必须在函数定义中指定。如果不是这种情况,则会生成一个错误,函数不会运行,除非log_bin_trust_function_creators = 1指定为覆盖此检查(参见下面)。对于递归函数调用确定的关键字仅在最外面的呼叫上需要。如果使用基于行或混合二进制日志记录,则接受并复制语句,即使函数在定义时没有使用确定的关键字。

  • 由于MySQL在创建时不检查函数是否真的是确定性的,因此使用确定的关键字可能执行对基于语句的日志记录不安全的操作,或者调用包含不安全语句的函数或过程。如果这种情况发生在binlog_format =声明,则发出警告消息。如果使用基于行或混合二进制日志记录,则不会发出警告,并且以基于行的格式复制语句。

  • 要放宽函数创建的上述条件(您必须有超级权限和函数必须声明为确定性或不修改数据),设置全局log_bin_trust_function_creators系统变量为1。默认情况下,这个变量的值是0,但是你可以这样改变它:

    mysql> SET GLOBAL log_bin_trust_function_creators = 1;

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

    如果没有启用二进制日志记录,log_bin_trust_function_creators不适用。超级不是函数创建所必需的,除非,如前所述,定义者函数定义中的值需要它。

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

触发器类似于存储函数,因此前面关于函数的说明同样适用于触发器,但有以下例外:创建触发器没有可选的确定的特征,所以触发器被认为总是决定性的。然而,这种假设在某些情况下可能是无效的。例如,UUID ()函数是不确定的(并且不复制)。在触发器中使用这样的函数时要小心。

触发器可以更新表,因此与存储函数的错误消息类似创建触发器如果您没有所需的特权。在副本端,副本使用触发器定义者属性来确定哪个用户被认为是触发器的创建者。

本节的其余部分将提供关于日志记录实现及其含义的更多细节。您不需要阅读它,除非您对当前存储例程使用中与日志相关的条件的背景知识感兴趣。本讨论仅适用于基于语句的日志记录,而不适用于基于行的日志记录,除了第一项:创建而且下降不管日志记录模式是什么,语句都作为语句记录。

  • 服务器写创建事件创建过程创建函数改变事件改变的过程改变函数删除事件下降过程,删除函数语句到二进制日志。

  • 存储的函数调用被记录为选择语句,如果函数更改了数据,并且发生在一条不会被记录的语句中。这可以防止由于在非日志语句中使用存储函数而导致的数据更改的不可复制。例如,选择语句不写入二进制日志,但a选择可能会调用进行更改的存储函数。为了解决这个问题,a选择func_name()语句将在给定函数进行更改时写入二进制日志。假设在源服务器上执行以下语句:

    CREATE FUNCTION f1(a INT) return INT BEGIN IF (a < 3) THEN INSERT INTO t2 VALUES (a);如果;返回0;结束;CREATE TABLE t1 (INT);INSERT INTO t1 VALUES (1),(2),(3);SELECT f1(a) FROM t1;

    选择语句执行,函数f1 ()调用三次。其中两个调用插入一行,MySQL记录一个选择对每一项进行陈述。也就是说,MySQL将如下语句写入二进制日志:

    选择f1 (1);选择f1 (2);

    服务器还记录了a选择当存储函数调用导致错误的存储过程时,存储函数调用的语句。在这种情况下,服务器写入选择语句与预期的错误代码一起发送到日志。在副本上,如果发生相同的错误,这是预期的结果,复制继续。否则,复制停止。

  • 记录存储的函数调用而不是函数执行的语句对复制有安全影响,这源于两个因素:

    • 函数可以在源服务器和副本服务器上遵循不同的执行路径。

    • 在副本上执行的语句由副本的应用程序线程处理。除非你实现了复制权限检查,这在MySQL 8.0.18中是可用的(参见第17.3.3节,“复制权限检查”)时,应用程序线程具有完全特权。

    这意味着,尽管用户必须具有创建程序创建函数时,用户可以编写一个包含危险语句的函数,该语句只能在副本上执行,该副本由具有完全权限的线程处理。例如,如果源服务器和副本服务器的服务器ID值分别为1和2,则源服务器上的用户可以创建和调用不安全的函数unsafe_func ()如下:

    mysql> delimiter // mysql> CREATE FUNCTION unsafe_func () RETURNS INT -> BEGIN -> IF @@server_id=2 THENdangerous_statement;如果;- >返回1;- >结束;// mysql>分隔符;INSERT INTO t VALUES(unsafe_func());

    创建函数而且插入语句被写入二进制日志,因此副本执行它们。因为副本的应用程序线程拥有完全的特权,所以它会执行危险的语句。因此,函数调用对源和副本有不同的影响,并且不是复制安全的。

    为防止启用二进制日志记录的服务器出现这种危险,存储函数创建者必须具有超级特权,除了平常创建程序必要的特权。同样,使用改变函数,你必须有超级特权除了改变日常特权。没有超级特权,错误发生:

    错误1419 (HY000):你没有超级权限和二进制日志被启用(你可能想使用不安全的log_bin_trust_function_creators变量)

    如果您不想要求函数创建者具有超级特权(例如,如果所有用户使用创建程序您系统上的特权都是有经验的应用程序开发人员),设置为全局log_bin_trust_function_creators系统变量为1。您还可以在服务器启动时设置此变量。如果没有启用二进制日志记录,log_bin_trust_function_creators不适用。超级不是函数创建所必需的,除非,如前所述,定义者函数定义中的值需要它。

  • 无论为函数创建者选择什么权限,建议在可用的情况下使用复制权限检查(来自MySQL 8.0.18)。可以设置复制权限检查,以确保仅为复制通道授权预期的和相关的操作。有关此操作的说明,请参见第17.3.3节,“复制权限检查”

  • 如果执行更新的函数是不确定的,那么它是不可重复的。这可能会产生两种不良影响:

    • 它导致副本与源不同。

    • 恢复的数据与原始数据不匹配。

    为了解决这些问题,MySQL实施了以下要求:在源服务器上,函数的创建和修改将被拒绝,除非你声明该函数是确定的或不修改数据。这里适用两组函数特征:

    • 确定的而且不确定性特征表示函数对于给定的输入是否总是产生相同的结果。默认值是不确定性如果两个特征都没有给出。要声明一个函数是确定性的,必须指定确定的明确。

    • 包含SQL没有SQL读取SQL数据,修改SQL数据特征提供关于函数是否读取或写入数据的信息。要么没有SQL读取SQL数据指示函数不更改数据,但必须显式指定其中一个,因为默认值为包含SQL如果没有给出特征。

    缺省情况下,对于a创建函数声明被接受,至少有一个确定的没有SQL,或读取SQL数据必须显式指定。否则将出现错误:

    错误1418 (HY000):该函数在其声明中没有DETERMINISTIC, NO SQL,或读取SQL DATA,并且启用了二进制日志记录(你可能想使用不安全的log_bin_trust_function_creators变量)

    如果你设置log_bin_trust_function_creators对于1,函数是确定性的或不修改数据的要求被删除。

  • 存储过程调用在语句级别而不是在调用的水平。也就是说,服务器不记录调用语句时,它将在实际执行的过程中记录这些语句。因此,在源服务器上发生的相同更改也会在副本上发生。这可以防止因过程在不同机器上具有不同的执行路径而导致的问题。

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

    • 要记录的语句可能包含对本地过程变量的引用。这些变量不存在于存储过程上下文之外,因此不能按字面意义记录引用此类变量的语句。相反,为了记录日志,对局部变量的每次引用都会被这个构造函数替换:

      NAME_CONST (var_namevar_value

      var_name是局部变量名,和var_value是一个常量,指示记录语句时变量的值。NAME_CONST ()值为var_value和一个的名字var_name.因此,如果你直接调用这个函数,你会得到这样的结果:

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

      NAME_CONST ()启用在副本上执行记录的独立语句,其效果与在存储过程中的源上执行的原始语句相同。

      的使用NAME_CONST ()会导致什么问题创建表……选择当源列表达式引用局部变量时。将这些引用转换为NAME_CONST ()表达式可能导致源服务器和副本服务器上的列名不同,或者名称太长而不能成为合法的列标识符。一种解决方法是为引用局部变量的列提供别名。在以下情况下考虑这句话myvar#值为1:

      创建myvar表

      重写如下:

      SELECT NAME_CONST(myvar, 1);

      为了确保源表和副本表有相同的列名,这样写语句:

      创建myvar表

      重写后的语句变成:

      CREATE TABLE 1 SELECT NAME_CONST(myvar, 1)
    • 要记录的语句可能包含对用户定义变量的引用。为了处理这个问题,MySQL写了一个语句写入二进制日志,以确保该变量在副本上的值与源上的值相同。例如,如果一个语句引用了一个变量@my_var,该语句在二进制日志的前面加上下面的语句,其中价值的值@my_var来源:

      设置@my_var =价值
    • 过程调用可以在已提交或回滚的事务中发生。考虑事务上下文,以便正确地复制过程执行的事务方面。也就是说,服务器在实际执行和修改数据的过程中记录这些语句,并记录日志开始提交,回滚声明是必要的。例如,如果一个过程只更新事务表,并且在回滚的事务中执行,则不会记录这些更新。如果过程发生在已提交的事务中,开始而且提交语句与更新一起记录。对于在回滚事务中执行的过程,它的语句使用与独立执行语句相同的规则记录:

      • 对事务性表的更新不会被记录。

      • 对非事务性表的更新会被记录下来,因为回滚不会取消它们。

      • 对事务性和非事务性表混合的更新记录在开始而且回滚这样副本就可以进行与源文件相同的更改和回滚。

  • 存储过程调用是如果从存储函数中调用过程,则在语句级别写入二进制日志。在这种情况下,唯一记录的是调用函数的语句(如果它发生在记录日志的语句中)或语句(如果它发生在没有记录的语句中)。因此,在使用调用过程的存储函数时应该小心谨慎,即使过程本身在其他方面是安全的。