Automating Backups using Event scheduler

Question

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

Here is my script :

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;

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

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

what is the problem with my script ?

Answer 1

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 ;