Tag Archives: MySQL

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 🙂

#!/bin/bash

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
done
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

Backup and restore a MySQL database

For this task I normally use the mysqldump program bundled with the mysql database package. It is a niffty little program that lets you backup multiple databases, a selection of tables or just one database to a SQL file. Here is the syntax:

Backup one database:

mysqldump --user=username --password=password --databases dbname > filename.sql

This will backup alla tables of the database dbname to the file filename.sql

Backup multiple databases:

mysqldump --user=username --password=password --databases dbname1 dbname2 dbname3 > filename.sql

This will dump database dbname1, dbname2 and dbname3 to the file filename.sql

Backup all databases:

mysqldump --user=username --password=password -A > filename.sql

The -A option tells the program to dump all databases

Backup tables:

mysqldump --user=username --password=password --databases dbname --tables tablename1 tablename2 > filename.sql

This will backup tables tablename1 and tablename2 from database dbname to file filename.sql

Restore a database:

mysql --user=username --password=password dbname < filename.sql

Yes, when restoring a dump we use the normal mysql program and not the mysqldump program. This will restore the database/tables in the file filename.sql to the dbname database