MySQL backup retention policy and deletion process

remove_outdated_sql_backups.sh

#!/usr/bin/env bash
# Author: Chris Brozdowski
# Date: 2025-09-22
#
# Remove outdated MySQL backups with the following retention policy, based
# on the date in the folder name (e.g., your-prefix-20250131):
# - Keep all backups from the last month
# - Keep one backup per month for the last year
# - Keep one backup per year for all previous years,
#   either Jan 1 or the very first
#
# Recommended as a monthly cron job. Depending on setup, may need sudo.

set -e # exit on error
set -u # treat unset variables as error
set -o pipefail # catch errors in pipes
shopt -s nullglob # null glob expands to empty list

# --- config ---
BACKUP_DIR="/YOUR/PATH/mysql-backup"  # parent directory
BACKUP_PREFIX="your-prefix"           # folder name prefix, e.g., "mydb-"
FIRST_BACKUP="20200224" # your first backup date

# --- time boundaries ---
# Precompute timestamp for 1 month ago
# (e.g., if today is 2025-09-22, this is 2025-08-22 00:00)
ts_1mo_ago=$(date -d "1 month ago 00:00" +%s) # timestamp of 1 month ago

# Precompute the first-of-month dates for the last 12 months (incl. current)
# e.g, in Sept 2025, keep Oct 1, 2024; Nov 1, 2024; ...; Sept 1, 2025
declare -A keep_month_first=()
for i in $(seq 0 11); do
  keep_month_first["$(date -d "$i months ago" +%Y%m01)"]=1
done

# --- function ---
keep_ymd() { # accepts YYYYMMDD, returns 0 (true) if keep, else 1 (false)
  local ymd=$1 # e.g., 20250131

  if ! [[ $ymd =~ ^[0-9]{8}$ ]]; then
    return 0
  fi

  local ts # timestamp of the date
  if ! ts=$(date -d "$ymd" +%s 2>/dev/null); then
    return 0 # invalid date
  fi

  # 1) keep all daily backups from the past month
  if (( ts >= ts_1mo_ago )); then
    return 0
  fi

  # 2) keep first of each month for the past year
  if [[ ${keep_month_first["$ymd"]:-0} -eq 1 ]]; then
    return 0
  fi

  # 3) keep first of every year and very first backup, always
  if [[ ${ymd:4:4} == "0101" || $ymd == $FIRST_BACKUP ]]; then
    return 0
  fi

  return 1
}

# --- main ---
CURRENT_DIR=$(pwd)
cd "$BACKUP_DIR" || exit 1

for dir in */; do
  [[ -d $dir ]] || continue # skip if not a directory

  if [[ ! $dir == "$BACKUP_PREFIX"* ]]; then
    continue # doesn't match prefix
  fi

  name=${dir%/}
  dpart=${name##*/}                # e.g., prefix-20250131
  ymd=${dpart#"$BACKUP_PREFIX"}    # -> 20250131

  if ! keep_ymd "$ymd"; then
    echo "Deleting $dir"
    rm -rf -- "$dir"
  fi
done

cd "$CURRENT_DIR" || exit 1