• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 存储程序二进制记录

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

    • 对于复制,二进制日志在主复制服务器上用作要发送到从属服务器的语句的记录。主服务器将其二进制日志中包含的事件发送到其从属服务器,从属服务器执行这些事件以对主服务器进行相同的数据更改。请参见“复制实现的详细信息”。
    • 某些数据恢复操作需要使用二进制日志。还原备份文件后,将重新执行在执行备份后记录的二进制日志中的事件。这些事件使数据库从备份开始就保持最新状态。请参见“示例备份和恢复策略”。

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

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

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

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

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

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

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

    • 要创建或更改存储的功能,除了通常需要的or 特权外,还必须具有SET_USER_IDor SUPER特权。(取决于函数定义中的值,或者取决于是否启用了二进制日志记录,可能是必需的。请参见“ CREATE PROCEDURE和CREATE FUNCTION语句”。)CREATE ROUTINEALTER ROUTINEDEFINERSET_USER_IDSUPER
    • 创建存储函数时,必须声明它是确定性的或未修改数据。否则,对于数据恢复或复制可能是不安全的。

      默认情况下,一个CREATE FUNCTION语句被接受,至少一个DETERMINISTICNO SQLREADS 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则不适用。SUPER除非如上所述,DEFINER函数定义中的值是必需的,否则函数创建不需要。

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

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

    触发器可以更新表,因此CREATE TRIGGER如果您没有所需的特权,则会发生类似于存储功能的错误消息。在从属方,从属使用触发器DEFINER属性来确定哪个用户被认为是触发器的创建者。

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

    • 服务器写入CREATE EVENTCREATE PROCEDURECREATE FUNCTIONALTER EVENTALTER PROCEDUREALTER FUNCTIONDROP EVENTDROP PROCEDURE,和DROP FUNCTION语句二进制日志。
    • SELECT如果函数更改数据并且发生在本来不会记录的语句中,则将存储的函数调用记录为一条语句。这样可以防止由于未记录的语句中使用存储的函数而导致的数据更改不重复。例如,SELECT语句未写入二进制日志,但是a 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 FUNCTIONINSERT语句被写入二进制日志,以便从机将执行它们。由于从属SQL线程具有完全特权,因此它将执行危险的语句。因此,函数调用对主服务器和从服务器具有不同的影响,并且不是复制安全的。

      为了防止启用了二进制日志记录的服务器面临这种危险SUPER,除了CREATE ROUTINE所需的通常特权之外,存储函数创建者还必须具有特权。同样,要使用ALTER FUNCTIONSUPER除了特权外,您还必须具有ALTER ROUTINE特权。没有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则不适用。SUPER除非如上所述,DEFINER函数定义中的值是必需的,否则函数创建不需要。

    • 如果执行更新的功能不确定,则该功能不可重复。这会产生两个不良影响:

      • 它将使奴隶不同于主人。
      • 恢复的数据将与原始数据不同。

      为了解决这些问题,MySQL强制执行以下要求:在主服务器上,除非您声明该函数具有确定性或不修改数据,否则将拒绝创建和更改该函数。两组功能特征在这里适用:

      • DETERMINISTICNOT DETERMINISTIC特性指示的功能是否总是产生对于给定的输入相同的结果。默认值是NOT DETERMINISTIC如果两个特性均未给出。要声明一个函数是确定性的,必须DETERMINISTIC明确指定。
      • CONTAINS SQLNO SQLREADS SQL DATA,和MODIFIES SQL DATA特征提供有关该功能是否读取或写入数据信息。无论是NO SQLREADS SQL DATA指示功能不会改变的数据,但由于默认情况下是必须指定的这些明确的一个CONTAINS SQL,如果没有特性给予。

      默认情况下,一个CREATE FUNCTION语句被接受,至少一个DETERMINISTICNO SQLREADS 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()使已记录的独立语句可以在从属服务器上执行,其效果与在存储过程中在主服务器上执行的原始语句相同。

        当源列表达式引用局部变量时,使用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,则该语句将在二进制日志中的以下语句之前,其中master value的值是@my_var

        SET @my_var = value;
        
      • 过程调用可以在已提交或回退的事务中发生。要考虑事务上下文,以便正确复制过程执行的事务方面。也就是说,服务器日志的过程中这些语句实际执行和修改数据,并记录BEGINCOMMITROLLBACK语句是必要的。例如,如果过程仅更新事务表并在回滚的事务中执行,则不会记录这些更新。如果该过程发生在已提交的事务中,BEGIN并且COMMIT语句与更新一起记录。对于在回滚事务中执行的过程,使用与以独立方式执行的语句相同的规则记录其语句:

        • 不会记录对事务表的更新。
        • 记录对非事务处理表的更新,因为回滚不会取消它们。
        • 事务表和非事务表混合的更新记录在并用括起来BEGINROLLBACK这样从属服务器将进行与主服务器相同的更改和回滚。
    • 如果从存储函数内部调用存储过程,则不会在语句级别将存储过程调用写入二进制日志。在那种情况下,唯一记录的是调用该函数的语句(如果它在记录的DO语句内发生)或一条语句(如果它在未记录的语句内发生)。因此,即使过程本身是安全的,也应谨慎使用调用过程的存储函数。