Automatically Backup MySQL Databases on Linux Server

We can automatically backup MySQL database on linux server by writing script and run cron. The cron command can run the script every x day at time y. The script use mysqldump in back up the database

The sample procedure is as follows

1. create script mysqldump.bsh which has it content as

#!/bin/bash mysqldump –user=”Database Username” –password=”Database Password” –all “Database Name” | gzip > “Full Path Where You Want Backup Saved”-`date +%Y%m%d`.gz

2. create file cron.txt

Assume that we want to backup the database at 6 am everyday

0 6 * * * /path/to/your/mysql_dump.bsh

There must be an empty line before the end of the file

3. run command "crontab cron.txt" to start the jobs specified in cron.txt

4. run command "crontab -l" to see the list of jobs currently scheduled and run on the system


References

http://www.bloggingpro.com/archives/2004/05/28/backing-it-up-or-lose-it/

http://en.wikipedia.org/wiki/Crontab

http://www.adminschoice.com/docs/crontab.htm