Script for macOS to create daily, weekly and monthly mysqldumps and delete old ones

Readme.md

MySQL Backup for macOS

This shell script creates daily backups of all MySQL databases on macOS. Currently the script uses mysql@5.7, installed by homebrew.

• Daily backups are kept for a week.
• Weekly backups are kept for a month.
• Monthly backups are kept for a year.

Setup

1. Make mysqldump_cron.sh executable:

   ~: sudo chmod +x mysqldump_cron.sh

2. Create a symlink for mysqldump_cron.sh that is placed in /usr/local/bin and that is named mysqlbackup

   ~: ln -s /Users/USERNAME/PATH/TO/SCRIPT/mysqldump_cron.sh /usr/local/bin/mysqlbackup

3. Edit your Mac's crontab and have it execute mysqlbackup hourly. A backup will only be created once a day.

   env EDITOR=nano crontab -e

   add the line

   0 * * * * /usr/local/bin/mysqlbackup >> /Users/PATH/TO/LOGFILE/.shell-scripts/mysqlbackup.log 2>&1

mysqldump_cron.sh

#!/bin/bash
PATH=/usr/local/bin:/usr/local/sbin:~/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/opt/mysql@5.7/bin

# original source Lyman Lai
# http://002.yaha.me/item/22728a58-c967-46d5-93eb-2649d684a9aa/
# edited by C. Carstens 2019-08-26


STORE_FOLDER="$HOME/.mysql-backups"


TODAY=$(date +"%Y-%m-%d")
DAILY_DELETE_NAME="daily-"`date -j -v-7d +"%Y-%m-%d"`
WEEKLY_DELETE_NAME="weekly-"`date -j -v-5w +"%Y-%m-%d"`
MONTHLY_DELETE_NAME="monthly-"`date -j -v-12m +"%Y-%m-%d"`

databases=($(mysql -Bse "show databases" | grep -i -v "_schema" | grep -i -v "sys" | grep -i -v "mysql"))


if [ -f "$STORE_FOLDER/all/daily-$TODAY.sql.gz" ]
then
  echo [$(date +'%Y-%m-%d %H:%M')]" Ran today already, aborting."
  exit
fi

function do_backups() {
  # Get db name or "all"

  backup_db=$1

  # run dump
  if [ "$backup_db" == "all" ]; then
    BACKUP_PATH=$STORE_FOLDER/all
    [[ ! -d "$BACKUP_PATH" ]] && mkdir -p "$BACKUP_PATH"
  	echo "   Creating $BACKUP_PATH/daily-$TODAY.sql.gz"
    mysqldump --all-databases | gzip -9 > $BACKUP_PATH/daily-$TODAY.sql.gz
  else
    BACKUP_PATH=$STORE_FOLDER/$db
    [[ ! -d "$BACKUP_PATH" ]] && mkdir -p "$BACKUP_PATH"
  	echo "   Creating $BACKUP_PATH/daily-$TODAY.sql.gz"
    mysqldump $db | gzip -9 > $BACKUP_PATH/daily-$TODAY.sql.gz
  fi

  # delete old backups
  if [ -f "$BACKUP_PATH/$DAILY_DELETE_NAME.sql.gz" ]; then
  	echo "   Deleting $BACKUP_PATH/$DAILY_DELETE_NAME.sql.gz"
  	rm -rf $BACKUP_PATH/$DAILY_DELETE_NAME.sql.gz
  fi
  if [ -f "$BACKUP_PATH/$WEEKLY_DELETE_NAME.sql.gz" ]; then
	echo "   Deleting $BACKUP_PATH/$WEEKLY_DELETE_NAME.sql.gz"
    rm -rf $BACKUP_PATH/$WEEKLY_DELETE_NAME.sql.gz
  fi
  if [ -f "$BACKUP_PATH/$MONTHLY_DELETE_NAME.sql.gz" ]; then
	echo "   Deleting $BACKUP_PATH/$MONTHLY_DELETE_NAME.sql.gz"
    rm -rf $BACKUP_PATH/$MONTHLY_DELETE_NAME.sql.gz
  fi

  # make weekly
  if [ `date +%u` -eq 7 ];then
    cp $BACKUP_PATH/daily-$TODAY.sql.gz $BACKUP_PATH/weekly-$TODAY.sql.gz
  fi

  # make monthly
  if [ `date +%d` -eq 25 ];then
    cp $BACKUP_PATH/daily-$TODAY.sql.gz $BACKUP_PATH/monthly-$TODAY.sql.gz
  fi

}

echo "*** MySQL Backups"
echo
echo "To be deleted if present:"
echo "   $DAILY_DELETE_NAME"
echo "   $WEEKLY_DELETE_NAME"
echo "   $MONTHLY_DELETE_NAME"
echo

# Entire backup
echo "Starting complete MySQL backup..."
do_backups all

# Individual db backups
for db in "${databases[@]}"; do
  echo "Starting $db MySQL backup..."
  do_backups $db
done