A small MySQL backup script using mysqldump and creates one file per database

This is the script I use for all my Amazon RDS instances local backups (called from an EC2). It gives me one db backup file for every database in the RDS instance, and at the end it makes sure that I don’t have any files older then 30 days. I put it here now so I don’t loose it again 🙂

DATE=$(date +"%Y%m%d_%H%M")
DATABASES=`mysql -hsomedbname-someregion.rds.amazonaws.com -udbadmin -pmypassword -e "show databases;"`
for db in $DATABASES; do
        echo $db
        mysqldump --databases $db --single-transaction -hsomedbname-someregion.rds.amazonaws.com -udbadmin -pmypassword | gzip > /opt/backup/mysql.backup.$db.$DATE.sql.gz
find /opt/backup/* -mtime +30 -exec rm {} \;

I set this on a two hour cron to make sure that I have fresh backups of my production databases

0 */2 * * * /opt/scripts/mysql_db_backup.sh > /var/log/mysql_db_backup.log

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

This site uses Akismet to reduce spam. Learn how your comment data is processed.