How to create a daily dump in MySQL?

Question

I want to make a daily dump of all the databases in MySQL using

Event Scheduler

, by now I have this query to create the event:

DELIMITER $$
CREATE EVENT `DailyBackup`
ON SCHEDULE EVERY 1 DAY STARTS '2015-11-09 00:00:01'
ON COMPLETION NOT PRESERVE ENABLE
DO 
BEGIN
mysqldump -user=MYUSER -password=MYPASS all-databases > CONCAT('C:\Users\User\Documents\dumps\Dump',DATE_FORMAT(NOW(),%Y %m %d)).sql
END $$
DELIMITER ;

The problem is that MySQL seems to not recognize the command 'mysqldump' and shows me an error like this: Syntax error: missing 'colon'. I am not an expert in SQL and I've tried to find the solution, but I couldn't, hope someone can help me with this.

Edit:

Help to make this statement a cron task

bro, you are already connected and in the system, no need to login again, and more importantly, That is a command line tool. That would be more of a cron strategy. What goes inside BEGIN END are sql statements. — Drew, Sep 11 '15 at 23:22
@Drew I don't know what a cron strategy is, can you help me to do this please? — Andres Valencia, Sep 11 '15 at 23:29
google on cron and crontab. Question was just re-tagged as such — Drew, Sep 11 '15 at 23:35

Brian DeMilia · Accepted Answer · 2015-09-12T00:10:36.063

For Windows, create a .bat file with the needed command, and then create a scheduled task that runs that .bat file according to a schedule.

Create a .bat file in this fashion, replacing your username, password, and database name as appropriate:

mysqldump --opt --host=localhost --user=root --password=yourpassword dbname > C:\some_folder\some_file.sql

Then go to the start menu, control panel, administrative tools, task scheduler. Hit action > create task. Go to the actions tab, hit new, browse to the .bat file and add it to the task. Then go to the triggers tab, hit new, and define your daily schedule. Refer to http://windows.microsoft.com/en-US/windows/schedule-task

You might want to use a tool like 7zip to compress your backups all in the same command (7zip can be invoked from the command line). An example with 7zip installed would look like:

mysqldump --opt --host=localhost --user=root --password=yourpassword dbname | 7z a -si C:\some_folder\some_file.7z

I use this to include the date and time in the filename:

set _my_datetime=%date:~-4%_%date:~4,2%_%date:~7,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%_%time:~9,2%_
set _my_datetime=%_my_datetime: =_%
set _my_datetime=%_my_datetime::=%
set _my_datetime=%_my_datetime:/=_%
set _my_datetime=%_my_datetime:.=_%
echo %_my_datetime%
mysqldump --opt --host=localhost --user=root --password=yourpassword dbname | 7z a -si C:\some_folder\backup_with_datetime_%_my_datetime%_dbname.7z

Thank you so much, I was already creating the bat file and the task was easy, I just changed the way the date is set to this one: set "dateStr=%date:~6,4%-%date:~0,2%-%date:~3,2%", it is in YYYYMMDD format. — Andres Valencia, Sep 12 '15 at 01:36

Paul Vidal · Answer 2 · 2015-09-11T23:49:28.033

1

@Drew means to use a cronjob. to add a cronjon just start the crontab using this command:

crontab -e

the add a new entry at the end like this:

0 0 * * * mysqldump -u username -ppassword databasename > /path/to/file.sql

this will perform a database dump every day at 00:00

edited Sep 11 '15 at 23:49

answered Sep 11 '15 at 23:39

Paul Vidal

403
2
6

@Paul Vidal I have been looking and it seems that cron is part of Linux/Unix, but I'm using Windows, so I think it is more like Schedule Task doesn't it? – Andres Valencia Sep 11 '15 at 23:43

score 1 · Answer 3 · answered Sep 11 '15 at 23:51

1

yes program the scheduler to run something like this:

C:/path/to/mysqldump.exe -u username -ppassword databasename > /path/to/file.sql

answered Sep 11 '15 at 23:51

Paul Vidal

403
2
6

How to create a daily dump in MySQL?

3 Answers3