Automated MySQL backup using cron
| Important: The following is a text only archive! For full features; Go to Automated MySQL backup using cron |
posted by 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
posted by 2Guns
PS: if you get an error similiar to the one below;
Make sure you typed all in one line.. no line breaks..