backup-mysql-all-db

#!/usr/bin/env bash
[ -n "$BASH_VERSION" ] || exec bash "$0" "$@"
set -euo pipefail
LANG=C

# ------------------------- USER-ADJUSTABLE ----------------------------
FTP_HOST=""                    # If empty → read from /etc/ftp-backup.conf
FTP_USER=""
FTP_PASS=""
DAYS_TO_KEEP=30
BACKUP_PATH="/backup/$HOSTNAME/sql"
# ---------------------------------------------------------------------

############################################################################
# 1.  Load **one or several** FTP blocks from /etc/ftp-backup.conf
#    - Each block is the classic KEY=value format.
#    - Empty lines or # comments separate blocks.
############################################################################
FTP_CONF="/etc/ftp-backup.conf"
declare -a FTP_HOSTS FTP_USERS FTP_PASSWORDS

if [[ -f "$FTP_CONF" ]]; then
  cur_host="" cur_user="" cur_pass=""
  while IFS= read -r raw; do
    line="${raw%%#*}"          # strip comments
    line="$(xargs <<<"$line")" # trim
    [[ -z "$line" ]] && {                 # end-of-block ⇒ push if complete
      if [[ -n "$cur_host" && -n "$cur_user" && -n "$cur_pass" ]]; then
        FTP_HOSTS+=("$cur_host") FTP_USERS+=("$cur_user") FTP_PASSWORDS+=("$cur_pass")
      fi
      cur_host="" cur_user="" cur_pass=""
      continue
    }
    case "$line" in
      FTP_HOST=*) cur_host=${line#FTP_HOST=} ;;
      FTP_USER=*) cur_user=${line#FTP_USER=} ;;
      FTP_PASS=*) cur_pass=${line#FTP_PASS=} ;;
    esac
  done <"$FTP_CONF"
  # push last block if file didn’t end with a blank line
  if [[ -n "$cur_host" && -n "$cur_user" && -n "$cur_pass" ]]; then
    FTP_HOSTS+=("$cur_host") FTP_USERS+=("$cur_user") FTP_PASSWORDS+=("$cur_pass")
  fi
fi

# If inline single-server vars are set, append them to the arrays
if [[ -n "$FTP_HOST" && -n "$FTP_USER" && -n "$FTP_PASS" ]]; then
  FTP_HOSTS+=("$FTP_HOST") FTP_USERS+=("$FTP_USER") FTP_PASSWORDS+=("$FTP_PASS")
fi
############################################################################

CMD_NAME=$(basename "$0")
LOCK_DIR="/tmp/${CMD_NAME}.lock"
MY_CNF="/root/.my.cnf"
DAY=$(date +%y%m%d-%H-%M)

usage() {
  cat <<EOF
Usage: $CMD_NAME [--recover [BACKUP_DIR]]
         --recover   Interactive restore (default directory \$BACKUP_PATH)
EOF
  exit 1
}

############################################################################
# Interactive restore
############################################################################
recover_backup() {
  local RDIR=${1:-$BACKUP_PATH}
  [[ -d "$RDIR" ]] || { echo "Backup dir '$RDIR' not found" >&2; exit 1; }

  echo "Snapshots found in $RDIR:"
  mapfile -t DATES < <(ls -1 "$RDIR" | grep -oE '[0-9]{6}-[0-9]{2}-[0-9]{2}' | sort -u)
  [[ ${#DATES[@]} -gt 0 ]] || { echo "No snapshots." >&2; exit 1; }
  select SNAP in "${DATES[@]}"; do [[ -n "$SNAP" ]] && break; echo "Choose a number." ; done

  # ---- read snapshot master-status and build a CHANGE MASTER command ----
  MS_FILE="$RDIR/${SNAP}-master-status.txt"
  if [[ -f "$MS_FILE" ]]; then
    BINLOG=$(awk -F': *' '/File:/     {print $2; exit}' "$MS_FILE")
    POS=$(    awk -F': *' '/Position:/{print $2; exit}' "$MS_FILE")
    CHANGE_CMD="CHANGE MASTER TO MASTER_LOG_FILE='${BINLOG}', MASTER_LOG_POS=${POS};"
    echo
    echo "Master status of snapshot $SNAP"
    cat "$MS_FILE"
    echo
    echo "Suggested command:"
    echo "  $CHANGE_CMD"
    echo
  fi

  echo "User databases in the snapshot:"
  mapfile -t FILES < <(ls "$RDIR"/*"$SNAP"*.sql.gz 2>/dev/null)
  [[ ${#FILES[@]} -gt 0 ]] || { echo "No .sql.gz files for $SNAP" >&2; exit 1; }

  DBS=()
  for f in "${FILES[@]}"; do
    bn=$(basename "$f" .sql.gz)
    db=${bn#${SNAP}-}; db=${db%-${SNAP}}
    case "$db" in mysql|performance_schema|information_schema|sys) continue;; esac
    DBS+=("$db")
  done
  [[ ${#DBS[@]} -gt 0 ]] || { echo "Only system schemas present."; exit 0; }

  PS3="Select DB to restore (or 'all'): "
  select CHOICE in all "${DBS[@]}"; do [[ -n "$CHOICE" ]] && break; echo "Choose." ; done
  [[ $CHOICE == all ]] && TO_RESTORE=("${DBS[@]}") || TO_RESTORE=("$CHOICE")

  # --------------- replication handling on the local server -------------
  SLAVE_INFO=$(mysql --defaults-file="$MY_CNF" -ss -e "SHOW SLAVE STATUS\\G;" 2>/dev/null || true)
  if [[ -n "$SLAVE_INFO" && -n "${BINLOG:-}" && -n "${POS:-}" ]]; then
    echo "Local server is a slave – it will be stopped, re-pointed, and restarted."
    mysql --defaults-file="$MY_CNF" -e "STOP SLAVE;"
    mysql --defaults-file="$MY_CNF" -e "$CHANGE_CMD"
    SLAVE_WAS_MANAGED=1
  else
    SLAVE_WAS_MANAGED=0
  fi
  # ----------------------------------------------------------------------

  for DB in "${TO_RESTORE[@]}"; do
    DUMP_FILE=""
    for f in "${FILES[@]}"; do
      case "$(basename "$f")" in ${SNAP}-${DB}.sql.gz|${DB}-${SNAP}.sql.gz) DUMP_FILE="$f"; break;; esac
    done
    [[ -n "$DUMP_FILE" ]] || { echo "Dump for $DB not found"; continue; }

    echo "Restoring $DB ..."
    mysql --defaults-file="$MY_CNF" -e "DROP DATABASE IF EXISTS \`$DB\`; CREATE DATABASE \`$DB\`;"
    gunzip -c "$DUMP_FILE" | mysql --defaults-file="$MY_CNF" --force --database="$DB"
    echo "✔ $DB restored."
  done

  [[ $SLAVE_WAS_MANAGED -eq 1 ]] && mysql --defaults-file="$MY_CNF" -e "START SLAVE;"

  echo "Restore finished."
  exit 0
}

############################################################################
# Backup mode
############################################################################
case "${1:-}" in
  --recover) shift; recover_backup "${1:-}" ;;
  "") ;;                       # continue to backup mode
  *) usage ;;
esac

# --- NEW: track whether we stopped the slave ourselves ---
SLAVE_STOPPED_BY_US=0

# ----------------------------------------------------------------------
# Helper: safely stop slave after catching up
# ----------------------------------------------------------------------
stop_slave_safely() {
  local SLAVE_INFO
  SLAVE_INFO=$(mysql --defaults-file="$MY_CNF" -ss -e "SHOW SLAVE STATUS\\G;" 2>/dev/null || true)
  if [[ -n "$SLAVE_INFO" ]]; then
    until [[ "$(awk -F': ' '/Seconds_Behind_Master/{print $2}' <<<"$SLAVE_INFO" | tr -d '[:space:]')" == 0 ]]; do
      SECS=$(awk -F': ' '/Seconds_Behind_Master/{print $2}' <<<"$SLAVE_INFO" | tr -d '[:space:]')
      echo "Waiting for replication – $SECS s behind master..."
      sleep 5
      SLAVE_INFO=$(mysql --defaults-file="$MY_CNF" -ss -e "SHOW SLAVE STATUS\\G;" 2>/dev/null || true)
    done
    echo "Replication caught up. Stopping slave."
    mysql --defaults-file="$MY_CNF" -e "STOP SLAVE;"
    SLAVE_STOPPED_BY_US=1
    mysql --defaults-file="$MY_CNF" -e "SHOW MASTER STATUS\\G;" >"$BACKUP_PATH/${DAY}-master-status.txt"
    mysql --defaults-file="$MY_CNF" -e "SHOW SLAVE STATUS\\G;"  >"$BACKUP_PATH/${DAY}-slave-status.txt"
  fi
}

if ! mkdir "$LOCK_DIR" 2>/dev/null; then
  echo "Another instance is already running." >&2
  exit 1
fi

# --- NEW: ensure slave restarts and lock removed on ANY exit ---
trap '[[ $SLAVE_STOPPED_BY_US -eq 1 ]] && mysql --defaults-file="$MY_CNF" -e "START SLAVE;" ; rm -rf "$LOCK_DIR"' EXIT INT TERM

mkdir -p "$BACKUP_PATH"

# Show planned FTP uploads
if [[ ${#FTP_HOSTS[@]} -gt 0 ]]; then
  echo "Backup will be uploaded to: ${FTP_HOSTS[*]}"
else
  echo "No FTP destinations configured – snapshots stay local."
fi
echo

# Wait for replication if host is a slave and stop it safely
stop_slave_safely

# Always capture master status (in case server not a slave)
[[ -f "$BACKUP_PATH/${DAY}-master-status.txt" ]] || \
  mysql --defaults-file="$MY_CNF" -e "SHOW MASTER STATUS\\G;" >"$BACKUP_PATH/${DAY}-master-status.txt" || true

echo "Dumping all user databases to $BACKUP_PATH ($DAY)…"
sanitize() { tr '/`\\ ' '____' <<<"$1"; }

mysql --defaults-file="$MY_CNF" -Bse "SHOW DATABASES;" | while read -r DB; do
  case "$DB" in mysql|information_schema|performance_schema|sys) continue;; esac
  echo "  → $DB"
  nice -n19 ionice -c3 mysqldump --defaults-file="$MY_CNF" \
      --lock-tables --single-transaction --skip-extended-insert \
      --skip-routines --skip-triggers "$DB" \
    | nice -n19 ionice -c3 gzip >"$BACKUP_PATH/${DB}-$(sanitize "$DAY").sql.gz"
done
echo "Database dumps complete."

# --- still try to restart slave (redundant but harmless thanks to flag) ---
[[ $SLAVE_STOPPED_BY_US -eq 1 ]] && mysql --defaults-file="$MY_CNF" -e "START SLAVE;"

# Remove old snapshots
find "$BACKUP_PATH" -type f -ctime +$DAYS_TO_KEEP -exec nice -n19 ionice -c3 rm -f {} +

# Upload to every configured FTP server
for ((i=0; i<${#FTP_HOSTS[@]}; i++)); do
  HOST=${FTP_HOSTS[i]} USER=${FTP_USERS[i]} PASS=${FTP_PASSWORDS[i]}
  echo "Syncing to FTP $HOST…"
  trickle -u 8192 -d 8192 nice -n19 ionice -c3 lftp -c "
    set ftp:list-options -a
    set ssl:verify-certificate no
    open ftp://$USER:$PASS@$HOST
    lcd $BACKUP_PATH
    mkdir -p $HOSTNAME/sql
    cd $HOSTNAME/sql
    mirror --reverse --delete --verbose
  "
done

echo "Backup finished."