How to make auto backup of MySQL databases & rotate 3 days?


What is the best way to make automated daily backup of MySQL databases and have these backups rotated on a 3 days interval basis?

To be exact, here is what I need to do:

1- Backup Database db_X at 1AM EST into db_X_02_07_13.sql with being todays date.

2- Next day Backup Database db_X at 1AM EST into db_X_02_08_13.sql and Delete the db_X_02_05_13.sql
so that we are keeping total of 3 backups of each DB

I assume the best way to do this is via Crontabs. But just not clear about the exact command for above 2 Steps process.


I have a similar bash script on a linux box via cron. I have subdirectories for day 1,2,3 in the backup directory, and when the script runs it rsync’s the files from 2 to 3 subdirectories, then rsync’s from 1 to 2. The script then cleans out directory 1 and runs mysqldump saving to 1. This gives me 3 days worth of backups, current, yesterday, and day-before yesterday copies.

Hi Doug,

So what is the actual command that you are using on the Cron?
And how do you direct the MySQL dump output to Dir1 vs Dir2 etc.?