How to create a daily dump in MySQL?


Problem :

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



Solution :

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

Mysql Tutorials

Mysql Howto..

After I authenticate a user in API how should I make sure they don't have access for too long

How to retrieve all records after one with an ID in symfony?

How can you make a remote MySQL connection faster?

Google maps markers not showing in explorer or chrome

Use Left Join for show 3rd Week data wrong result

How to display the list of categories which contain items in mysql [closed]

During Mysql Excel upload using PHP how to validate mobile numbers column? [closed]

How to return a specific variable from PHP to Android

php - mysql how to get duplicate data from each UPDATE.?

How to combine(Full Join) two unrelated tables in Mysql

How to order the rows with respect to multiple columns in mysql

How to link the foreign key field to a primary key field from another table using php and mysql?

how i can test the connectionstring in Mysql

Slow MySQL IN query — how to convert to JOINs?

MySQL: How do I speed up a “Count()” query with a “JOIN” and “order_by”?

How to Upload images to mysql database Using PHP/Flex

How to transfer wordpress mysql md5 user passwords field into another mysql table

how to get the table of missing rows in mysql

How to clear mysql screen console in windows?

How to return a list of results in MySQL, WHERE only return one

MySQL: how to store height/weight information?

How to do a foreach type query in mysql, comparing each row with several rows in another table?

How to select all articles with a tag and all article's tags with one MySQL query?

how to map resultset from mysql to pojo using hibernate?

How to know if MySql's conditional INSERT worked?

How to manage mysql databases created in Azure?

How to join those two tables in MySQL

How to estimate MYSQL database size for known column range

How to update sno ascending order in mysql?

How to increase the MySQL variable count