Mysql Backup with Event Scheduler

Question

I would like some tip on how to backup a mysql database with Event Scheduler.

I only got in my searches, find Event to back up tables individually.
I wish I could back up the whole bank, someone can help me?

(this code is just a test I’m running 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;

Answer 1

To backup all tables recommend the command mysqldump. Example:

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

Where arquivo.cnf contains the access information:

[mysqldump]
user=meu_user
password=minha_senha 

You can schedule the Cron (or in the Task Scheduler if you are using Windows) to do this recurring.

The scheduled events approach in Mysql, as you yourself noted, is recommended for individual table backups.

Although it is possible to consult which are the tables of a schema through the INFORMATION_SCHEMA, and with this create a generic backup logic, surely this approach would be more costly and difficult to maintain.

---

Source: Learning Mysql - Automating Backups.