How to schedule a cron job to backup a MySql database every week?

Question

What is a command line I can use to back up a MySql database every single week into a file name with the date (so that it doesn't collide with previous backups)?

Also, is this a reasonable backup strategy? My database is relatively small (a complete export is only 3.2 megs right now). The churn rate is relatively low. I need to be able to get the complete DB back if something goes wrong. And it would be extra cool if there's a way that I could see the changes that occur across a time span.

Answer 1

Weekly backup with cron and mysqldump:

Credit goes to @timdev answer (+1) for providing the command line.

The cron syntax goes like this :

* * * * * = [minute] [hour] [day of month] [month] [day of week]

However since you wanted weekly backups, you can specify the days on which to run the backup in the third start above (day of month), to simulate the weekly interval.

0 4 1,8,15,21,28 * * DATE=`date +%y-%m-%d`; mysqldump -u <dbuser> -p<dbpasswd> <dbname> | gzip > dbname-$DATE.sql.gz

This will run it at 4:00 AM on 1st, 8th, 15th, 21st, and 28th of each month.

Answer 2

You could try something like this:

0 4 1 * * DATE=`date +%y-%m-%d`; mysqldump -u <dbuser> -p<dbpasswd> <dbname> | gzip > dbname-$DATE.sql.gz

Which will dump your database (and gzip it) on the first of each month at 4:00 AM.

Answer 3

You could try MySQL Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service.

This way, if something goes wrong with your primary database (the master), the slave can serve as a ready-to-use replacement.