Backup MySQL Database to Amazon S3

README.md

Backup MySQL Database automatically to Amazon S3

Amazon S3 services is a wonderful fast and good service to backup your databases. This script dumps currently only one, database and uploads the file to a s3 bucket.

0. Register and prepare AWS S3

1. Make your self an Amazon AWS account if you dont already have one. 2.Create a new bucket for your backups (https://console.aws.amazon.com/s3/home).
2. (optional) Maybe you want also create a lifecycle policy for example to delete all backups older than 30 days or so. Just edit the properties of the bucket.
3. Create a new user in the IAM an give him the policy AmazonS3FullAccess.
4. Remember to download the access key and the secret key, you will need them soon.

1. Install s3cmd

This are the instructions for Ubuntu, for other distributions they may vary.

# Install s3cmd
sudo apt-get -y install python-setuptools
wget http://netix.dl.sourceforge.net/project/s3tools/s3cmd/1.6.1/s3cmd-1.6.1.tar.gz
tar xvfz s3cmd-1.6.1.tar.gz
cd s3cmd-1.6.1
sudo python setup.py install
# Configure s3cm (have your access keys ready!)
s3cmd --configure

2. Add this script

Upload a copy of s3backup.sh (make sure you edit the configurations in the first lines), make it executable and test it

# Add the executable bit
chmod +x s3backup.sh
# Run the script to make sure it's all working flawless
./s3backup.sh

3. Set up a automatic backup every night

Edit the crontab (if you first start crontab it will ask you to choose an editor, just use nano it is the best)

crontab -e
# Add the following lines:
# Run the database backup script at 3am every night
0 3 * * * bash /var/www/s3backup.sh >/dev/null 2>&1

4. Now go sleep without being scared of data loss. ;)

If you have found an error or have an idea to improve the script feel free to comment or make a fork. And please please give me a star.

s3backup.sh

#!/bin/bash
# Shell script to backup MySql database
# Copyright Kevin B. (reflic) (c) 2016
# Source: https://gist.github.com/Reflic/8168a6ffb8c59afdb8118546e21fde0f
# Dependencies: s3cmd = v1.6.*

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

# CONFIG - Only edit the below lines to setup the script
# ===============================

MyUSER="root"           # USERNAME
MyPASS="telefon145#"       # PASSWORD
MyHOST="localhost"      # Hostname
MyDATABASE="infosys" # Database

S3Bucket="infosys-backup" # S3 Bucket
echo -e "Backing up database \e[1;32m$MyDATABASE\e[00m to \e[0;35m$S3Bucket\e[00m bucket."

# DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING
# ===============================

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="backup"

# Main directory where backup will be stored
MBD="$DEST"

# Get hostname
HOST="$(hostname)"

# Get data in yyyy-mm-dd_hh:mm:ss format
NOW="$(date +"%Y-%m-%d_%T")"

# File to store current backup file
FILE=""

[ ! -d $MBD ] && mkdir -p $MBD || :

 FILE="$MBD/$NOW-$HOST-$MyDATABASE.sql.gz"
 $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $MyDATABASE | $GZIP -9 > $FILE

# copy mysql backup directory to S3
echo "Started uploading to S3"
s3cmd put $FILE s3://$S3Bucket/

#delete uploaded file
rm $FILE
echo "Deleted backup file."

echo -e  "\e[1;36mAll done. :) \e[00m"