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
3 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
3 Comment authors
AlyYaroslav NakonechnikovAlexandre Karpov Recent comment authors
newest oldest most voted
Aly
Guest
Aly

Hi Yong Mook, Thanks so much for such an excellent script. Giving full credit to you, I took the great script and made it to work with shared hosting, by first installing a Python virtual environment. This allowed the installation of AWS CLI without restriction of the shared hosting root Python folder protections. Without this setup AWS could not be installed in shared hosting. o Added multi database support o Clean up of local backups after X days o Amazon S3 file tagging (using S3API) after their upload. File tagging helps control S3 lifecyle retention policy. S3 then takes care… Read more »

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.