Automated MySQL backup using cron

2Guns

The basic syntax for the mysqldump command is as follows:

mysqldump -u username -ppassword databasename > backup_file
Example;

mysqldump -u root -p12345 sitefuse > /home/backupfile.sql
If you just want a simple method of backing up your database every so often (say once a day) and don't mind have the backup file being overwritten every time a new backup is made, then you could use a simple cronjob, such as;

0 0 * * 0 /usr/bin/mysqldump -u root -p12345 sitefuse > /home/backupfile.sql
Whenever you're calling a command, use the full path. If you're using mysqldump, then use /usr/bin/mysqldump, or whatever the path is. It can save a lot of trouble in the future. :)

The job above would backup your database at 12 midnight every Monday morning and it will overwrite the existing back file every time a new backup is made.

If you don't want the backup file(s) to be overwritten every time a new one is made, the procedure is a bit more complicated. For me, the job would look something like;
0 0 * * 0 /usr/bin/mysqldump -u root -p12345 sitefuse > /home/backup_$(date +\%m\%d\%Y).sql
This cronjob will, again, backup your database at 12 midnight every Monday morning. However, this job will write every new backup to a different file, named based on the current date. The file output by this cronjob would, depending on the date, look something like:

backup_01162005.sql

Additionally, you can compress your backups directly even without hitting your or your server's HDD. Simply add gzip to your cronjob;

0 0 * * 0 /usr/bin/mysqldump -u root -p12345 sitefuse | gzip > /home/backup_$(date +\%m\%d\%Y).sql.gz

2Guns

PS: if you get an error similiar to the one below;


Make sure you typed all in one line.. no line breaks..