I recently setup a system to manage our companies IP addresses. This was an open source PHP application that ran in Apache and used MySQL for its database. Since I typically don’t have time to manually run a backup (and why would I want to do something the hard way anyhow?) I decided it was time to automate the task.
The first issue was what to name the backup files. I wanted at least a few weeks history worth of backups in case something bad was done in the database… Obviously, I could not have 30 files with the same file name in my backup directory. So I wrote a small shell script to generate a filename with a date / time embedded in it.
The first line of the script changed to the backup directory and runs the MySQL dump action. The output is piped to a file. The filename being the variable: $(date +%Y_%m_%d_%T_MYDB.sql)
If you type the command:

root@localhost# date +%Y_%m_%d_%T

You will get something like this:

2012_03_28_09:46:03

I just added the “_MYDB.sql” to the end of the string produced by the date command.

The second part of my script checks for the age of old files. I do a “find” on all the files in the directory, specifying that they start with a “20” to make sure I am only looking at dated .sql backup files. The “-mtime +30” option to the find command only returns file that are older than 30 days. Finally, I pass this result off to be executed as an argument to “rm –f” forcing the removal of the old files.
The script is show in its entirety below.

#!/bin/bash
cd /var/db_backups
/usr/bin/mysqldump --opt -u USERNAME -pPASSWORD MYDB > $(date +%Y_%m_%d_%T_MYDB.sql)
find /var/db_backups -name "20*" -mtime +30 -exec rm -f {} \;

Finally, to automate, you will want to put this script in a cron folder, add it as a job, or point to it from another automatic script.
Enjoy!