Hi there! Today i’ll explain how to create scheduled backup of AWS mysql data using snapshot script.
The first step is to create an IAM user with permissions to do what our backup script requires. Create one in the IAM section of AWS console and in the Inline Policies area give it the following policy:
{
"Statement": [
{
"Effect": "Allow",
"Action": [
"ec2:CreateSnapshot",
"ec2:CreateTags",
"ec2:DeleteSnapshot",
"ec2:DescribeSnapshots",
"ec2:DescribeTags"
],
"Resource": [
"*"
]
}
]
}
- Be sure to save the IAM user credentials (AWS access key id and AWS secret access key)
The next step is to create the script that will lock the mysql db at night (do it on slave instance to make sure your app will keep running during this process ) , create a snapshot of the volume and release the lock.
we’ll do it using two scripts :
1) lockTables.sh
#!/bin/bash
mysql -u root --password='passwordd' -e "flush tables with read lock; flush logs;"
if [ $? -gt 0 ];then
echo "mysql freeze failed" 1>&2
exit 1
else
echo "mysql freeze succeeded" 1>&2
mysql -u root -e "stop slave;"
sh /usr/local/bin/createSnapshot.sh ##2nd script location
mysql -u root -e "start slave;"
mysql -u root --password='passwordd' -e "unlock tables;"
if [ $? -gt 0 ];then
echo "mysql unfreeze failed" 1>&2
exit 1
else
echo "mysql unfreeze success" 1>&2
fi
fi
2) createSnapshot.sh
#!/bin/bash
# For more details about this script
# www.devops-engineer.com
# Constants
ec2_bin="/opt/aws/bin"
access_key="HereAccessKeyFromTheFirstStep"
secret_key="HereSecretKeyFromTheFirstStep"
instance_id=`wget -q -O- http://169.254.169.254/latest/meta-data/instance-id`
export EC2_HOME=/opt/aws/apitools/ec2
export JAVA_HOME=/usr/lib/jvm/jre
# Dates
datecheck_7d=`date +%Y-%m-%d --date '7 days ago'`
datecheck_s_7d=`date --date="$datecheck_7d" +%s`
# Get the region
EC2_AVAIL_ZONE=`curl -s http://169.254.169.254/latest/meta-data/placement/availability-zone`
EC2_REGION="`echo \"$EC2_AVAIL_ZONE\" | sed -e 's:\([0-9][0-9]*\)[a-z]*\$:\\1:'`"
# Get all volume info and copy to temp file
$ec2_bin/ec2-describe-volumes --region $EC2_REGION -O $access_key -W $secret_key --filter "attachment.instance-id=$instance_id" > /tmp/volumeInfo.txt 2>&1
# Get all snapshot info
$ec2_bin/ec2-describe-snapshots --region $EC2_REGION -O $access_key -W $secret_key | grep "$instance_id" > /tmp/snapInfo.txt 2>&1
# Loop to remove any snapshots older than 7 days
# Load text file lines into a bash array
IFS=$'\n'
lines_array=($(cat /tmp/snapInfo.txt))
for snap in ${lines_array[@]}
do
snapshot_name=$(printf %s {lines_array[$snap]} | awk '{print $2}')
datecheck_old=$(cat /tmp/snapInfo.txt | grep "$snapshot_name" | awk '{print $5}' | awk -F "T" '{printf "%s", $1}')
datecheck_s_old=$(date --date="$datecheck_old" +%s)
if (( $datecheck_s_old <= $datecheck_s_7d ));
then
echo "deleting snapshot $snapshot_name ..."
$ec2_bin/ec2-delete-snapshot --region $EC2_REGION -O $access_key -W $secret_key $snapshot_name
else
echo "not deleting snapshot $snapshot_name ..."
fi
done
# Create snapshot
volume="HereVolumeID"
description="Daily Backup- $(hostname)_$(date +%Y-%m-%d)_[$instance_id]_[$volume]"
echo "Creating Snapshot for the volume: $volume with description: $description"
$ec2_bin/ec2-create-snapshot --region $EC2_REGION -O $access_key -W $secret_key -d $description $volume
*Change the bold parts with your personal settings
The last step is to make sure this scripts will run every night
crontab -e :
### /MYSQLDATA/ SNAPSHOT EVREY NIGHT AT 00:00 ### 00 00 * * * /usr/local/bin/lockTables.sh > /tmp/lockTail.log 2>&1
That’s it! 
Now you can sleep tight knowing your data is safe 7 days back.
Thank you!! Bookmarked!@