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. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
2 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
Yaroslav NakonechnikovAlexandre Karpov Recent comment authors
newest oldest most voted
Yaroslav Nakonechnikov
Guest
Yaroslav Nakonechnikov

Good script!
but you wrote function to backup files and don’t execute it.

Alexandre Karpov
Guest
Alexandre Karpov

if anyone finds it useful, here’s a brain-dead python script which will keep your backups from accumulating:
https://github.com/alexakarpov/s3-logrotate

Yaroslav Nakonechnikov
Guest
Yaroslav Nakonechnikov

much better for this purpose use lifecycles. it has on almost any s3-compatible service.