In this post, we will go through with the bash script which will upload the MySQL backup dump to AWS S3 bucket. With the reference of this idea, you can apply the same logic in other Cloud Computing services also.
PreRequirements in AWS
We expect the AWS Engineer will fulfill these pre-reqs.
- AWS S3 bucket (Create AWS S3 bucket for MySQL backup files)
- Either attache AWS IAM Role to EC2 or generate API Key for IAM user (service account)
Bash Script: MySQL backup
Create a bash script file and paste the given below contents in it. We are using the vi editor here.
vi mysqlbackup.sh
What this script does?
- It takes the MySQL backup dump and upload in the AWS S3 bucket.
- It notify about the backup via e-mail.
- It removes the older backup files from the Operating System as per defined retention days.
#!/bin/bash
# mysql backup script
# Author: Sharad Chhetri
# Website: https://sharadchhetri.com
# 4-Sept-2013
#
# Provide the MySQL user which can do the backup.
MYSQLUSER=mysqlbackup
# Provide MySQL user password
PASSWORD=123456
# Provide AWS S3 bucket
s3_bucket_name=YourS3BucketName
# Provide MYSQL Server IP address or FQDN, For eg. 127.0.0.1 or localhost
MYSQLHOST=localhost
# In this file, add the list of Database name.
DB_LIST_FILEPATH='~/mysqlbackup/dblist'
# Define database backup path in Operating System
BACKUPPATH=/opt/database-backup/$db_name-`date +%F-%H%M%S`
# Define number of days to retain MySQL backup file in the System
backup_retention_days=30
#You can give multiple email id in MAILTO variable by using comma (,) for eg. MAILTO=abc@example.com,xyz@example.com
MAILTO=alert@example.com
for db_name in `cat $DB_LIST_FILEPATH`
do
mysqldump -h$MYSQLHOST -u$MYSQLUSER -p$PASSWORD $db_name > $BACKUPPATH
### compressing the file
gzip $BACKUPPATH
sleep 2
# Upload the backup file in AWS S3 bucket. You must configure either AWS API keys or attach the S3 IAM role to EC2
aws s3 cp $BACKUPPATH.gz s3://$s3_bucket_name --region us-east-1
FILESIZE=$( du -sh $BACKUPPATH.gz )
if [ ! -f $BACKUPPATH.gz ]; then
echo "$BACKUPPATH.gz File not found!, Database Name: $db_name" | mail -s "$db_name backup failed" $MAILTO
else
echo "$BACKUPPATH.gz File found,Database Name: $db_name, Actual size after compression is $FILESIZE " | mail -s "DBNAME database backup is done" $MAILTO
fi
done
# Find and remove last 30 Days backup file.
find /opt/database-backup/* -mtime +$backup_retention_days -exec rm {} \;
Once you set your script in the file and change all the required variables, now it is time to change the permission of file in Linux system.
sudo chmod +x mysqlbackup.sh
Use case
You can use this script either for one time execution or for regular in use. In regular use of this MySQL backup script, in Linux system you can setup this in Cronjob. This will schedule the MySQL backup in regular interval.