Backup MySQL Databases to Amazon S3 (Shell Script)

Backup MySQL databases to amazon AWS s3 using shell script; Through this tutorial, we will learn how to take backup MySQL databases to amazon AWS s3 using shell script.

Backup MySQL Databases to Amazon S3 (Shell Script)

Follow the following steps to take backup MySQL database to amazon aws s3 using shell script:

Step 1 – Install AWS CLI
Step 2 – Create S3 Bucket
Step 3 – Shell Script to Backup MySQL database to S3
Step 4 – Execute Backup Script
Step 5 – Schedule Backup Script

Step 1 – Install AWS CLI

Just use the following tutorial to learn how to install aws cli on unix/linux system:

How to Install AWS CLI on Linux

Step 2 – Create S3 Bucket

Then log in to your AWS account using CLI and execute the following command to create an s3 bucket:

aws s3api create-bucket --bucket s3-bucket-name --region us-east-1

Step 3 – Shell Script to Backup MySQL database to S3

Then use the following shell script to a file like db-backup.sh. This script uses mysqldump command to create databases backups. And use gzip command to archive backup files and finally use aws command to upload backup files to Amazon S3 bucket.

Create a file like /backup/scripts/s3-backup-mysql.sh in edit your favorite text editor. Then add the below content:

#!/usr/bin/env bash
 
#########################################################################
#########################################################################
###
####       Author: tutsmake
#####      Website: https://tutsmake.net
####
#########################################################################
#########################################################################
 
# Set the folder name formate with date (2022-05-28)
DATE_FORMAT=$(date +"%Y-%m-%d")
 
# MySQL server credentials
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="user"
MYSQL_PASSWORD="password"
 
# Path to local backup directory
LOCAL_BACKUP_DIR="/backup/dbbackup"
 
# Set s3 bucket name and directory path
S3_BUCKET_NAME="s3-bucket-name"
S3_BUCKET_PATH="backups/db-backup"
 
# Number of days to store local backup files
BACKUP_RETAIN_DAYS=30 
 
# Use a single database or space separated database's names
DATABASES="DB1 DB2 DB3"
 
##### Do not change below this line
 
mkdir -p ${LOCAL_BACKUP_DIR}/${DATE_FORMAT}
 
LOCAL_DIR=${LOCAL_BACKUP_DIR}/${DATE_FORMAT}
REMOTE_DIR=s3://${S3_BUCKET_NAME}/${S3_BUCKET_PATH}
 
for db in $DATABASES; do
   mysqldump \
        -h ${MYSQL_HOST} \
        -P ${MYSQL_PORT} \
        -u ${MYSQL_USER} \
        -p${MYSQL_PASSWORD} \
        --single-transaction ${db} | gzip -9 > ${LOCAL_DIR}/${db}-${DATE_FORMAT}.sql.gz
 
        aws s3 cp ${LOCAL_DIR}/${db}-${DATE_FORMAT}.sql.gz ${REMOTE_DIR}/${DATE_FORMAT}/
done
 
DBDELDATE=`date +"${DATE_FORMAT}" --date="${BACKUP_RETAIN_DAYS} days ago"`
 
if [ ! -z ${LOCAL_BACKUP_DIR} ]; then
 cd ${LOCAL_BACKUP_DIR}
 if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
 rm -rf ${DBDELDATE}
 
 fi
fi
 
## Script ends here

Step 4 – Execute Backup Script

And execute the following command on command line to backup script:

chmod +x s3-backup-mysql.sh

Then run the backup script.

./s3-backup-mysql.sh

Step 5 – Schedule Backup Script

Then use the following command to schedule the shell script using crontab to run on a daily basis.

crontab -e

Add the below settings to end of the file:

# Run daily @ 2am
0 2 * * * /backup/scripts/s3-backup-mysql.sh > /dev/null 2>&1

Save the file and close it.

Conclusion

Through this tutorial, we have learned how to take backup MySQL databases to amazon AWS s3 using shell script.

Backup MySQL Databases to Amazon S3 (Shell Script)