A shell script to backup MYSQL database and upload it to Amazon S3.

1. mysqldump + gzip + aws

Dump the database with mysqldump and gzip it into a folder, later uses the aws command to upload the file to Amazon S3

#!/bin/bash
################################################################
##
##   MySQL Database To Amazon S3
##   Written By: YONG MOOK KIM
################################################################
NOW=$(date +"%Y-%m-%d")
BACKUP_DIR="/home/mkyong/backup"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="YOUR_DB_USER"
MYSQL_PASSWORD="YOUR_DB_PASSWORD"
DATABASE_NAME="YOUR_DB_NAME"
AMAZON_S3_BUCKET="s3://mkyong/backup/mysql/"
AMAZON_S3_BIN="/home/mkyong/.local/bin/aws"
FOLDERS_TO_BACKUP=("/home/mkyong/bk1" "/home/mkyong/bk2")
#################################################################
mkdir -p ${BACKUP_DIR}
backup_mysql(){
         mysqldump -h ${MYSQL_HOST} \
           -P ${MYSQL_PORT} \
           -u ${MYSQL_USER} \
           -p${MYSQL_PASSWORD} ${DATABASE_NAME} | gzip > ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}.sql.gz
# backup any folders?
backup_files(){
        tar -cvzf ${BACKUP_DIR}/backup-files-${NOW}.tar.gz ${FOLDERS_TO_BACKUP[@]}
upload_s3(){
        ${AMAZON_S3_BIN} s3 cp ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}.sql.gz ${AMAZON_S3_BUCKET}
backup_mysql
upload_s3

2. How to run?

Assign execute permission to the shell script, and run it directly.

$ chmod +x backup-script.sh
# run it
$ ./backup-script.sh

3. Run it daily

3.1 cron schedule to run the script daily.

$ crontab -e
# Daily, 7pm
0 19 * * * /path.to/backup-script.sh > /dev/null 2>&1