How to create a MySql Job to create a database backup and a maintenace plan?

Question

How can I create a MySql Job that runs daily to generate a database backup and stores in on the server?

Also How can I create a second Job that does a maintenance on the database to keep it running without problem?

Thanks

Read up on `mysqldump` and `mysqlcheck`. Create a script and schedule it. — MrCleanX, Jun 03 '13 at 17:47

score 0 · Answer 1 · answered Jun 03 '13 at 17:51

0

one possible way is run the following as daily cron.

mysqldump -u <db_user> -p <db_password> <db_name> -h <db_host_if_any> > /home/backups/backup_<timestamp>.sql

gzip it and store it(just a mechanism to reduce size)

gzip /home/backup/backup_<timestamp>.sql

answered Jun 03 '13 at 17:51

itz2k13

I am looking for an automatic solution, something I can set as a MySql Job to back up the database correctly and also make does a maintenance on the table. – Mike Jun 03 '13 at 18:01
[mysqldump](http://dev.mysql.com/doc/refman/5.0/en/using-mysqldump.html) is the standard. Run it from a cron job and it is automatic. – MrCleanX Jun 03 '13 at 18:08
What is a cron job? sorry I am new to this. – Mike Jun 03 '13 at 18:15
cron is a periodic task scheduler. please follow the link on how to set up a cron task : http://www.thesitewizard.com/general/set-cron-job.shtml – itz2k13 Jun 03 '13 at 18:21
Thank you, My server is Windows 2008 R2 and not lunix. does corn still apply? – Mike Jun 03 '13 at 19:04
yes.. windows has similar mechanism to schedule a task to run periodically. please see here: http://technet.microsoft.com/en-us/library/cc748993.aspx – itz2k13 Jun 03 '13 at 19:19

1 Answers1