How to create a daily dump in MySQL?
I want to make a daily dump of all the databases in MySQL using
, 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.
Help to make this statement a cron task
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
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]