Skip to content

Instantly share code, notes, and snippets.

Last active May 18, 2020 11:13
Show Gist options
  • Save Reflic/8168a6ffb8c59afdb8118546e21fde0f to your computer and use it in GitHub Desktop.
Save Reflic/8168a6ffb8c59afdb8118546e21fde0f to your computer and use it in GitHub Desktop.
Backup MySQL Database to Amazon S3

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 (
  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
tar xvfz s3cmd-1.6.1.tar.gz
cd s3cmd-1.6.1
sudo python install
# Configure s3cm (have your access keys ready!)
s3cmd --configure

2. Add this script

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

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

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/ >/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.

# Shell script to backup MySql database
# Copyright Kevin B. (reflic) (c) 2016
# Source:
# Dependencies: s3cmd = v1.6.*
# CONFIG - Only edit the below lines to setup the script
# ===============================
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."
# ===============================
# 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
# Main directory where backup will be stored
# Get hostname
# Get data in yyyy-mm-dd_hh:mm:ss format
NOW="$(date +"%Y-%m-%d_%T")"
# File to store current backup file
[ ! -d $MBD ] && mkdir -p $MBD || :
# 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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment