Easiest way to backup your mysql databases to Amazon S3

Do all databases at once.

Automatic MySQL Database Backups

 

To get started, create a folder db_backup. For this tutorial I’ve placed db_backupin my home folder. In db_backup create a directory named backups and a text file named backup.bin with the following code replacing the DB and S3 variables at the top with your own information

#!/bin/sh
 
DATE=`date +%y-%m-%d`#current date
DIR=`dirname "$0"`/ #current directory
 
#MySQL vars
DB_USER=root
DB_PASS=password
DB_HOST=localhost
 
#Backup directory vars
BACKUP_DIR=${DIR}backups/
 
#S3 vars
S3=0 #Set to 1 to back db up to S3
S3_KEY=Your S3 Key
S3_SEC_KEY=Your S3 Secret Key
S3_BUCKET=db_backups
 
#Get list of dbs
LIST=`mysql -u${DB_USER} -p${DB_PASS} -h${DB_HOST} INFORMATION_SCHEMA -e "SELECT SCHEMA_NAME FROM SCHEMATA WHERE SCHEMA_NAME !='information_schema';"`
 
#Loop through list ignoring result table name
for each in $LIST; do
 if [ "$each" != "SCHEMA_NAME" ]; then
 mysqldump -u${DB_USER} -p${DB_PASS} -h${DB_HOST} --opt --single-transaction $each > ${BACKUP_DIR}${DATE}_${each}.sql
 fi
done
 
#Zip and remove SQL dumps
zip -qj ${BACKUP_DIR}dbBackup_${DATE} ${BACKUP_DIR}${DATE}_*.sql
rm ${BACKUP_DIR}${DATE}_*.sql
 
#Back up to S3
if [ ${S3} != 0 ]; then
 php5 -f "${DIR}S3/backup.php" "${S3_KEY}" "${S3_SEC_KEY}" "${S3_BUCKET}" "${BACKUP_DIR}dbBackup_${DATE}.zip"
fi

We’ll be executing this script so we’ll need to set the backup.bin file to be executable and while we’re at it give the script a test run. If you wish to use S3 you’ll also need to add the S3 directory from the tutorial download below.

 

Leave a Reply