Tuesday 9 April 2019

Mysql DB backup with lock table system

#!/usr/bin/env bash

WAITFORLOCK=/root/waitlock

WAITFORSNAPSHOT=/root/waitforsnapshot

LOCKTABLERUN=/root/locktables.pid

function locktable {
(
    echo "FLUSH TABLES WITH READ LOCK;" && \
    sleep 5 && \
    touch ${WAITFORSNAPSHOT} && \
    rm -f ${WAITFORLOCK} && \
    while [ -e ${WAITFORSNAPSHOT} ]; do sleep 1; done && \
    echo "SHOW MASTER STATUS;" && \
    echo "UNLOCK TABLES;" && \
    echo "\quit" \
) | mysql --defaults-file=/root/.my.cnf

rm -f ${LOCKTABLERUN}
}

function prefreeze {

if [ -e ${WAITFORLOCK} ]; then
 echo Previous backup failed, waitforlock file still present && exit 1
fi

if [ -e ${WAITFORSNAPSHOT} ]; then
 echo Previous backup failed, WAITFORSNAPSHOT file still present && exit 1
fi

if [ -e ${LOCKTABLERUN} ]; then
 ps -p `cat ${LOCKTABLERUN}` > /dev/null 2>&1;
 if [ $? -eq 0 ]; then
  echo Panic, locktables script still running && exit 1
 else
  rm -f ${LOCKTABLERUN}
 fi
fi

touch ${WAITFORLOCK}

locktable &

LOCKTABLEPID=$!
echo ${LOCKTABLEPID} > ${LOCKTABLERUN}

while [ -e ${WAITFORLOCK} ]; do
 ps -p ${LOCKTABLEPID} > /dev/null 2>&1;
 if [ $? -eq 1 ]; then
  break
 fi
 sleep 1
done

if [ -e ${WAITFORLOCK} ]; then
 echo Tablelock script exited without removing waitforlock file, something went wrong
else
 echo Tables are locked
fi
}

prefreeze &&

server=$(hostname)
 if [[ "${server}" == *"product"* ]]; then
   server="db-product-slave-snapshot"
 elif [[ "${server}" == *"customer"* ]]; then
   server="db-customer-slave-snapshot"
 elif [[ "${server}" == *"finance"* ]]; then
   server="db-finance-slave-snapshot"
 else
   server=$(hostname)
 fi
instanceid=$(ec2metadata --instance-id)
for letter in /dev/xvdj xvdj /dev/sdj sdj; do
  volumeid=$(aws ec2 describe-volumes --filters Name=attachment.instance-id,Values=$instanceid Name=attachment.device,Values=$letter --query "Volumes[*].{ID:VolumeId}" --output text --region us-east-1)
  if [ -z "$volumeid" ]; then
  continue
  else
  break
  fi
done
snapid=$(aws ec2 create-snapshot --volume-id $volumeid --description ""$server"_$(date +%Y%m%d)" --output text --region us-east-1 | awk '{print $4}')
echo "Backup initiated with SnapshotID: "$snapid"" >> /var/log/ebs-snapshot.log

while :
do
 progress=$(aws ec2 describe-snapshots --snapshot-id $snapid --query "Snapshots[*].{Cond:State}" --output text --region us-east-1)
 if [[ "${progress}" == "pending" ]]; then
 sleep 5m
 continue
 else
 result=$(aws ec2 describe-snapshots --snapshot-id $snapid --query "Snapshots[*].{Cond:State}" --output text --region us-east-1)
 echo "Snap has been "$result" and Mysql has been started on $(date)" >> /var/log/ebs-snapshot.log
 echo "--" >> /var/log/ebs-snapshot.log
 break
 fi
done

rm ${WAITFORSNAPSHOT}

exit 0

No comments:

Post a Comment