Linux script to backup MySQL to Amazon S3

mysql backup script

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

Note
Make sure the AWS CLI is installed properly

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

backup-script.sh

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

Terminal

$ chmod +x backup-script.sh

# run it
$ ./backup-script.sh

3. Run it daily

3.1 cron schedule to run the script daily.

Terminal

$ crontab -e


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

References

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar