使用事件计划程序自动执行备份 [英] Automating Backups using Event scheduler

问题描述

我正在尝试使用MySQL中的事件计划程序创建自动备份.

I'm trying to create an automating backup using Event scheduler in MySQL.

这是我的剧本:

CREATE DEFINER=`root`@`localhost` EVENT `Backup` 
ON SCHEDULE EVERY 1 WEEK 
STARTS '2013-06-14 18:19:02' ON COMPLETION NOT PRESERVE ENABLE 
DO 
SET @sql_text = CONCAT ( "SELECT * FROM BonInterne INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "BonInterne.csv'" ); 
PREPARE s1 FROM @sql_text; 
EXECUTE s1; 
DROP PREPARE s1;

当我执行它时，在PREPARE s1 FROM @sql_text行中出现错误:

When I execute it I get an error in the line PREPARE s1 FROM @sql_text:

您的SQL语法有错误；检查手册 对应于您的MySQL服务器版本以使用正确的语法 在第1行的"NULL"附近

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

我的脚本有什么问题?

推荐答案

必须在BEGIN和END之间放置多个语句.另外，您还必须更改定界符，否则MySQL认为事件创建语句以第一个;完成.最后是DEALLOCATE PREPARE ...，而不是DROP PREPARE....

Multiple statements have to be put between BEGIN and END. Also you have to change the delimiter, or else MySQL thinks that the event creation statement is finished with the first ;. And at last, it's DEALLOCATE PREPARE ..., not DROP PREPARE....

DELIMITER $$
CREATE DEFINER=`root`@`localhost` EVENT `Backup` 
ON SCHEDULE EVERY 1 WEEK 
STARTS '2013-06-14 18:19:02' ON COMPLETION NOT PRESERVE ENABLE 
DO 
BEGIN
SET @sql_text = CONCAT("SELECT * FROM BonInterne INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "BonInterne.csv'" ); 
PREPARE s1 FROM @sql_text; 
EXECUTE s1; 
DEALLOCATE PREPARE s1;
END $$
DELIMITER ;

这篇关于使用事件计划程序自动执行备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！