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.
