MySql S3 Backup

mysql-s3-backup

#!/bin/sh

# Make sure to:
# 1) Name this file `backup.sh` and place it in /srv/www
# 2) Run sudo apt-get install awscli to install the AWSCLI
# 3) Run aws configure (enter s3-authorized IAM user and specify region)
# 4) Fill in DB host + name
# 5) Create S3 bucket for the backups and fill it in below (set a lifecycle rule to expire files older than X days in the bucket)
# 6) Run chmod +x backup.sh
# 7) Test it out via ./backup.sh
# 8) Set up a daily backup at midnight via `crontab -e`:
#    0 0 * * * /srv/www/backup.sh > /srv/www/backup.log

# DB host (secondary preferred as to avoid impacting primary performance)
HOST=localhost

# DB name
DBNAME=my-db

# S3 bucket name
BUCKET=bucketname

# Linux user account
USER=www

# Current time
TIME=`date +%d-%m-%Y-%T`

# Backup directory
DEST=/srv/$USER/tmp

# Tar file of backup directory
TAR=$DEST/../$TIME.tar

# Create backup dir (-p to avoid warning if already exists)
mkdir -p $DEST

# Log
echo "Backing up $HOST/$DBNAME to s3://$BUCKET/ on $TIME";

# Dump from mysql host into backup directory
mysqldump -u root -p$mysqlpass -h $HOST -d $DBNAME -o $DEST

mysqldump -u root -p$mysqlpass --force --opt --databases "$db" | gzip -c > "$tmpfile"


# Create tar of backup directory
tar cvf $TAR -C $DEST .

# Upload tar to s3
aws s3 cp $TAR s3://$BUCKET/

# Remove tar file locally
rm -f $TAR
rm -rf $DEST


# Upload
echo -e "  uploading..."
s3cmd put "$tmpfile" "$object"

# Delete
rm -f "$tmpfile"
  
# All done
echo "Backup available at https://s3.amazonaws.com/$BUCKET/$TIME.tar"

# Restore from Backup
# Download the .tar backup to the server from the S3 bucket via wget or curl:

wget -O backup.tar https://s3.amazonaws.com/my-bucket/xx-xx-xxxx-xx:xx:xx.tar
# Alternatively, use the awscli to download it securely.
# Then, extract the tar archive:

tar xvf backup.tar
mysql -u root -p$pass DB_NAME < ./