Home › Blogs › Kevin's blog

Scripted MySQL Database Backups

Thu, 10/22/2009 - 12:26pm — Kevin

In a perfect world you would be able to rely on the promise of automated systems to keep recent backups of your data. In many environments you are promised this functionality but it can sometimes fail. I wrote the mysql database backup script for those of us that sleep better at night with extra insurance.


#!/bin/bash

DB_BACKUP="home/cyberswat/db_dumps"
DB_USER="root"
DB_PASSWD="passwd"
DB_NAME="my_valuable_database"

### End of user editable variables ###

TIMESTAMP=`date +%Y-%m-%d-%H-%M-%S`

rm -rf /$DB_BACKUP/09
mv /$DB_BACKUP/08 /$DB_BACKUP/09
mv /$DB_BACKUP/07 /$DB_BACKUP/08
mv /$DB_BACKUP/06 /$DB_BACKUP/07
mv /$DB_BACKUP/05 /$DB_BACKUP/06
mv /$DB_BACKUP/04 /$DB_BACKUP/05
mv /$DB_BACKUP/03 /$DB_BACKUP/04
mv /$DB_BACKUP/02 /$DB_BACKUP/03
mv /$DB_BACKUP/01 /$DB_BACKUP/02
mkdir /$DB_BACKUP/01

mysqldump -u $DB_USER -p$DB_PASSWD $DB_NAME > /$DB_BACKUP/01/mysql-$TIMESTAMP.sql
tar czf /$DB_BACKUP/01/mysql-$TIMESTAMP.tar.gz -C / $DB_BACKUP/01/mysql-$TIMESTAMP.sql
rm -f /$DB_BACKUP/01/mysql-$TIMESTAMP.sql
exit 0