MySQL Backup with Event Scheduler

Question

MySQL Backup with Event Scheduler

Navigation

#1 by (4 votes)

5

I would like some tips on how to back up a mysql database with Event Scheduler.

I just got in my searches, find event to back individual tables. I would like to be able to backup the whole database, can anyone help me?

(this code is just a test I'm doing on localhost)

SET @sql_text = 
   CONCAT (
   "SELECT * FROM ?i dont know? INTO OUTFILE 'C:\Users\Eiglimar\Desktop\backup_mysql"
   , DATE_FORMAT( NOW(), '%Y%m%d')
   , "BonInterne.csv'"
);

PREPARE s1 FROM @sql_text;

delimiter |

CREATE EVENT BackUpCSV
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR
DO
  BEGIN
    EXECUTE s1;
  END |

delimiter ;

DROP PREPARE s1;

mysql backup scheduler

asked by anonymous 05.01.2015 / 14:07

1 answer

Error in the XML signature of NFs-e Belo Horizonte How to consume a JSON url without using jQuery

score 4 · Accepted Answer

To back up all tables I recommend the mysqldump command. Example:

mysqldump --defaults-file=/home/bk/arquivo.cnf schema > /home/bk/backup_mysql.sql

Where arquivo.cnf contains the login information:

[mysqldump]
user=meu_user
password=minha_senha

You can schedule jobs from Cron (or at Task Scheduler if you are using Windows) to do this on a recurring basis.

The scheduled events approach in MySQL, as you have noted, is recommended for backups of individual tables.

Although it is possible to query the tables of a schema through INFORMATION_SCHEMA , and thus create a generic backup logic, this approach would certainly be more costly and difficult to maintain.

Source: Learning MySQL - Automating Backups .