20.4. 存储子程序和触发程序的二进制日志功能 - MySQL 中文手册

返回介绍

20.4. 存储子程序和触发程序的二进制日志功能

发布于 2019-06-13 字数 7288 浏览 897 评论 0

,这一节介绍MySQL 5.1如何考虑二进制日志功能来处理存储子程序(程序和函数) 。这一节也适用于触发程序。

二进制日志包含修改数据库内容的SQL语句的信息。这个信息以描述修改的事件的形式保存起来。

二进制日志有两个重要目的:

·复制的基础是主服务器发送包含在二进制日志里的事件到从服务器,从服务器执行这些事件来造成与对主服务器造成的同样的数据改变,请参阅6.2节,“复制概述”。

·特定的数据恢复操作许要使用二进制日志。备份的文件被恢复之后,备份后纪录的二进制日志里的事件被重新执行。这些事件把数据库带从备份点的日子带到当前。请参阅5.9.2.2节,“使用备份恢复”。

MySQL中,以存储子程序的二进制日志功能引发了很多问题,这些在下面讨论中列出,作为参考信息。

除了要另外注意的之外,这些谈论假设你已经通过用–log-bin选项启动服务器允许了二进制日志功能。(如果二进制日志功能不被允许,复制将不可能,为数据恢复的二进制日志也不存在。)请参阅5.11.3节,“二进制日志”。

对存储子程序语句的二进制日志功能的特征在下面列表中描述。一些条目指出你应该注意到的问题。但是在一些情况下,有你可以更改的妇五七设置或你可以用来处理它们的工作区。

·CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,和ALTER FUNCTION 语句被写进二进制日志,CALL, DROP PROCEDURE, 和DROP FUNCTION 也一样。

尽管如此,对复制有一个安全暗示:要创建一个子程序,用户必须有CREATE ROUTINE权限,但有这个权限的用户不能写一个子程序在从服务器上执行任何操作。因为在从服务器上的SQL线程用完全权限来运行。例如,如果主服务器和从服务器分别有服务器ID值1和2,在主服务器上的用户可能创建并调用如下一个程序:

mysql> delimiter //
mysql> CREATE PROCEDURE mysp ()
    -> BEGIN
    ->   IF @@server_id=2 THEN DROP DATABASE accounting; END IF;
    -> END;
    -> //
mysql> delimiter ;
mysql> CALL mysp();

CREATE PROCEDURE和CALL语句将被写进二进制日志,所以从服务器将执行它们。因为从SQL线程有完全权限,它将移除accounting数据库。

要使允许二进制日志功能的服务器避免这个危险,MySQL 5.1已经要求存储程序和函数的创建者除了通常需要的CREATE ROUTINE的权限外,还必须有SUPER 权限。类似地,要使用ALTER PROCEDURE或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_routine_creators variable)

你可能不想强制要求子程序创建者必须有SUPER权限。例如,你系统上所有有CREATE ROUTINE权限的用户可能是有经验的应用程序开发者。要禁止掉对SUPER权限的要求,设置log_bin_trust_routine_creators 全局系统变量为1。默认地,这个变量值为0,但你可以象这样改变这样:

mysql> SET GLOBAL log_bin_trust_routine_creators = 1;

你也可以在启动服务器之时用–log-bin-trust-routine-creators选项来设置允许这个变量。

如果二进制日志功能不被允许,log_bin_trust_routine_creators 没有被用上,子程序创建需要SUPER权限。

·一个执行更新的非确定子程序是不可重复的,它能有两个不如意的影响:

o它会使得从服务器不同于主服务器。

-恢复的数据与原始数据不同。

要解决这些问题,MySQL强制做下面要求:在主服务器上,除非子程序被声明为确定性的或者不更改数据,否则创建或者替换子程序将被拒绝。这意味着当你创建一个子程序的时候,你必须要么声明它是确定性的,要么它不改变数据。两套子程序特征在这里适用:

-DETERMINISTIC和NOT DETERMINISTIC指出一个子程序是否对给定的输入总是产生同样的结果。如果没有给定任一特征,默认是NOT DETERMINISTIC,所以你必须明确指定DETERMINISTIC来声明一个子程序是确定性的。

使用NOW() 函数(或它的同义)或者RAND() 函数不是必要地使也一个子程序非确定性。对NOW()而言,二进制日志包括时间戳并正确复制。RAND()只要在一个子程序内被调用一次也可以正确复制。(你可以认为子程序执行时间戳和随机数种子作为毫无疑问地输入,它们在主服务器和从服务器上是一样的。)

– CONTAINS SQL, NO SQL, READS SQL DATA, 和 MODIFIES SQL数据提供子程序是读还是写数据的信息。无论NO SQL 还是READS SQL DATA i都指出,子程序没有改变数据,但你必须明白地指明这些中的一个,因为如果任何这些特征没有被给出,默认的特征是CONTAINS SQL。

默认地,要一个CREATE PROCEDURE 或 CREATE FUNCTION 语句被接受,DETERMINISTIC 或 NO SQL与READS SQL DATA 中的一个必须明白地指定,否则会产生如下错误:

ERROR 1418 (HY000): This routine 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_routine_creators
variable)

如果设置log_bin_trust_routine_creators 为1, 移除对子程序必须是确定的或不修改数据的要求。

注意,子程序本性的评估是基于创建者的“诚实度” :MySQL不检查声明为确定性的子程序是否不含产生非确定性结果的语句。

· 如果子程序返回无错,CALL语句被写进二进制日志,否则就不写。当一个子程序修改数据失败了,你会得到这样的警告:

·ERROR 1417 (HY000): A routine failed and has neither NO SQL nor
·READS SQL DATA in its declaration and binary logging is enabled; if
·non-transactional tables were updated, the binary log will miss their
·changes

这个记日志行为潜在地导致问题.如果一个子程序部分地修改一个非交互表(比如一个MyISAM表able)并且返回一个错误,二进制日志将反映这些变化。要防止这种情况,你应该在子程序中使用交互表并且在交互动作内修改表。

在一个子程序内,如果你在INSERT, DELETE, 或者UPDATE里使用IGNORE关键词来忽略错误,可能发生一个部分更新,但没有错误产生。这样的语句被记录日志,且正常复制。

·如果一个存储函数在一个如SELECT这样不修改数据的语句内被调用,即使函数本身更改数据,函数的执行也将不被写进二进制日志里。这个记录日志的行为潜在地导致问题。假设函数myfunc()如下定义:

·CREATE FUNCTION myfunc () RETURNS INT
·BEGIN
·  INSERT INTO t (i) VALUES(1);
·  RETURN 0;
·END;

按照上面定义,下面的语句修改表t,因为myfunc()修改表t, 但是语句不被写进二进制日志,因为它是一个SELECT语句:

SELECT myfunc();

对这个问题的工作区将调用在做更新的语句里做更新的函数。注意,虽然DO语句有时为了其估算表达式的副效应而被执行,DO在这里不是一个工作区,因为它不被写进二进制日志。

·在一个子程序内执行的语句不被写进二进制日志。假如你发布下列语句:

·CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
·CALL mysp;

对于这个例子来说,CREATEPROCEDURE 和CALL语句出现在二进制日志里,但INSERT语句并未出现。

·在从服务器上,当决定复制哪个来自主服务器的事件时,下列限制被应用:–replicate-*-table规则不适用于CALL语句或子程序内的语句:在这些情况下,总是返回“复制!”

触发程序类似于存储函数,所以前述的评论也适用于触发程序,除了下列情况: CREATE TRIGGER没有可选的DETERMINISTIC特征,所以触发程序被假定为总是确定性的。然而,这个假设在一些情况下是非法的。比如,UUID()函数是非确定性的(不能复制)。你应该小心在触发程序中使用这个函数。

触发程序目前不能更新表,但是在将来会支持。因为这个原因,如果你没有SUPER权限且log_bin_trust_routine_creators 被设为0,得到的错误信息类似于存储子程序与CREATE TRIGGER产生的错误信息。

在本节中叙述的问题来自发生在SQL语句级别的二进制日志记录的事实。未来发行的MySQL期望能实现行级的二进制日志记录,记录发生在更细致的级别并且指出哪个改变作为执行SQL的结果对单个记录而做。

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

目前还没有任何评论,快来抢沙发吧!