Backup Heroku MySQL to local

backup-database.sh

#!/usr/bin/env bash
function join_by { local IFS="$1"; shift; echo "$*"; }
# Check for commands used by this script
command -v pv >/dev/null 2>&1 || { echo >&2 "I require pv but it's not installed.  Aborting."; exit 1; }
command -v mysqldump >/dev/null 2>&1 || { echo >&2 "I require mysqldump but it's not installed.  Aborting."; exit 1; }
command -v gzip >/dev/null 2>&1 || { echo >&2 "I require gzip but it's not installed.  Aborting."; exit 1; }

stages=$(git remote | sed  's/origin//g' | tr '\n' ' ' | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')
stage_req=$1

availible_stages=$(IFS=, ; echo "${stages[*]}")

if [ "$1" == "" ]; then
    echo "Please give the stage to backup: (${availible_stages})"
    exit 1
fi
if [ "$1" == "-h" ]; then
    echo "This script is used to backup the database before deploy!"
    exit 2
fi

if [[ " ${stages[@]} " =~ " $stage_req " ]]; then
  database_url=`heroku config -r ${stage_req} | grep DATABASE_URL | sed 's/DATABASE_URL://g' | sed 's/ //g'`
  database=`echo ${database_url} | sed -nE 's/.*\/{1}(.+)\?.*/\1/p'`
  dbhost=`echo ${database_url} | sed -nE 's/.*\@(.+)\/{1}.*/\1/p' | sed -nE 's/(.*)\/.*/\1/p'`
  dbuser=`echo ${database_url} | sed -nE 's/.*\/\/(.+)\:.*/\1/p'`
  dbpasswd=`echo ${database_url} | sed -nE 's/.*\:(.+)\@.*/\1/p'`
else
  # is not correct
  echo "Please use a stage! (${availible_stages})"
  echo "${stage_req} is not a known stage!"
  exit 3
fi

echo "Backing up ${database} database"

backup_date=`date +%Y_%m_%d_%H_%M`
backup_dir=$HOME/dumps
echo "Location: $backup_dir"
mkdir -p $backup_dir

mysqldump -h ${dbhost} -u ${dbuser} --password=${dbpasswd} --compress --skip-lock-tables --port=3306 --single-transaction --routines --triggers --databases ${database} | pv | gzip -c > ${backup_dir}/${backup_date}_${database}.sql.gz

if [ $? -ne 0 ]; then
  echo "Error executing backup for $stage ! Exiting!"
  exit 4
fi
arr=(staging acceptance)
if [[ " ${arr[@]} " =~ " $stage " ]]; then
  echo "Cleaning up older backups. Keeping latest 2."
  ls -tp ${backup_dir}/*_${database}.sql.gz | tail -n +3 | while IFS= read -r f; do rm "$f"; done
else
  echo "Cleaning up older backups. Keeping latest 4."
  ls -tp ${backup_dir}/*_${database}.sql.gz | tail -n +5 | while IFS= read -r f; do rm "$f"; done
fi

file_size=`du -h ${backup_dir}/${backup_date}_${database}.sql.gz`
echo "Done with backup! Size: $file_size"
exit 0