MySQL Snapshot Script

mysqlsnapshot.sh

#!/bin/bash

DBNAME=mydb
SNAPSHOT_DAYS=2
ARCHIVE_DAYS=0
COMPRESS_INPLACE=1

mysqldump='docker exec mysql_mysql_1 mysqldump'
gzip=/bin/gzip

function error() {
  echo $1 >> $logfile.tmp
  cat $logfile.tmp >> $logfile
  cat $logfile.tmp
  rm $logfile.tmp
  exit 1
}

cd /root/mysql-snapshots || { echo "Snapshots directory does not exist."; exit 1; }
logfile=$(readlink -f mysqlsnapshot.log)

name=$(date +%b-%d-%H)
aname=$(date +%b-%d)
year=$(date +%Y)
snapshotFile=snapshots/$year/$DBNAME-$name.sql.gz
archiveFile=archive/$DBNAME-$year-$aname.sql.gz
[ -d snapshots ] || { mkdir snapshots || error "Could not create directory 'snapshots'"; }
[ -d snapshots/$year ] || { mkdir snapshots/$year || error "Could not create directory 'snapshots/$year'"; }
[ -d archive ] || { mkdir archive || error "Could not create directory 'archive'"; }

echo "Starting dump at $(date)" > $logfile.tmp
if [[ $COMPRESS_INPLACE -ne 1 ]]; then
  $mysqldump --single-transaction --quick --routines --events --databases $DBNAME 2>> $logfile.tmp \
    > $DBNAME-latest.sql.new
else
  $mysqldump --single-transaction --quick --routines --events --databases $DBNAME 2>> $logfile.tmp \
    | $gzip --rsyncable > $DBNAME-latest.sql.gz.new
fi
error=$?

if [ $error -eq 0 ]; then
  set -e
  if [[ $COMPRESS_INPLACE -ne 1 ]]; then
    mv $DBNAME-latest.sql.new $DBNAME-latest.sql
    echo "Compressing dump file..." >> $logfile.tmp
    $gzip --rsyncable --force $DBNAME-latest.sql || error "Could not gzip dump file: $?"
  else
    mv $DBNAME-latest.sql.gz.new $DBNAME-latest.sql.gz
  fi
  rm -f $snapshotFile
  if ! cp -alv $DBNAME-latest.sql.gz $snapshotFile >> $logfile.tmp; then
    echo "WARNING: Could not snapshot hardlink to latest dump: $?" >> $logfile.tmp
    mv $DBNAME-latest.sql.gz $snapshotFile
  fi
  if [ $ARCHIVE_DAYS -gt 0 ] && [ $(($(date +%d | sed 's/^0*//') % 10)) == 0 ] && [ ! -f $archiveFile ]; then
    echo "Archiving to $archiveFile" >> $logfile.tmp
    if ! cp -alv $snapshotFile $archiveFile >> $logfile.tmp; then
      echo "WARNING: Could not create archive hardlink to latest dump: $?" >> $logfile.tmp
    fi
  fi
  echo -e "Completed at $(date)\n\n" >> $logfile.tmp

  ## Cleanup old snapshots
  find snapshots -type f -mtime +$SNAPSHOT_DAYS -delete
  find archive -type f -mtime +$ARCHIVE_DAYS -exec rm {} \;
  find . -type d -empty | xargs rmdir

  cat $logfile.tmp >> $logfile
  rm $logfile.tmp
else
  error "Error creating MySQL dump for $year/$name: $error"
fi