Shell Scripting — Database Centric: MySQL Backup & Restore

#!/usr/bin/env bash
# mysql_backup.sh — Production backup with compression, verification, rotation

set -euo pipefail

DB_CONF="/etc/myapp/mysql.conf"
DB_NAME="myapp"
BACKUP_DIR="/backups/mysql"
KEEP_DAYS=7
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"
LOG="/var/log/mysql_backup.log"

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "${LOG}"; }
die() { log "ERROR: $*"; exit 1; }

mkdir -p "${BACKUP_DIR}"
log "Starting backup: ${DB_NAME}"

# ── Verify connection before starting ─────────────────────
mysql --defaults-file="${DB_CONF}" -e "SELECT 1" &>/dev/null \
  || die "Cannot connect to MySQL"

# ── Run the dump ──────────────────────────────────────────
mysqldump \
  --defaults-file="${DB_CONF}" \
  --single-transaction \     # consistent snapshot without lock
  --routines \               # include stored procedures
  --triggers \               # include triggers
  --add-drop-database \      # DROP DATABASE IF EXISTS before CREATE
  --databases "${DB_NAME}" \
  | gzip -9 > "${BACKUP_FILE}"

SIZE=$(du -sh "${BACKUP_FILE}" | cut -f1)
log "  ✔ Backup: ${BACKUP_FILE} (${SIZE})"

# ── Verify backup integrity ───────────────────────────────
if ! gzip -t "${BACKUP_FILE}" 2>/dev/null; then
  die "Backup file is corrupt!"
fi
log "  ✔ Integrity verified"

# ── Verify backup contains expected tables ────────────────
TABLE_COUNT=$(zcat "${BACKUP_FILE}" | grep -c '^CREATE TABLE' || true)
[[ "${TABLE_COUNT}" -gt 0 ]] || die "No tables found in backup!"
log "  ✔ Tables found: ${TABLE_COUNT}"

# ── Prune old backups ─────────────────────────────────────
find "${BACKUP_DIR}" -name "${DB_NAME}_*.sql.gz" \
  -mtime +"${KEEP_DAYS}" -delete
log "  ✔ Old backups pruned (keep ${KEEP_DAYS} days)"
log "Backup complete"

# ── Dump all databases separately ────────────────────────
mysql --defaults-file="${DB_CONF}" -BNs \
  -e "SHOW DATABASES" | \
  grep -vE '^(information_schema|performance_schema|sys|mysql)$' | \
while read -r db; do
  mysqldump --defaults-file="${DB_CONF}" \
    --single-transaction "${db}" \
    | gzip > "/backups/${db}_$(date +%Y%m%d).sql.gz"
  echo "  ✔ ${db}"
done

# ── Dump specific tables only ─────────────────────────────
mysqldump --defaults-file="${DB_CONF}" \
  myapp users orders products | gzip > core_tables.sql.gz

# ── Dump schema only (no data) ────────────────────────────
mysqldump --defaults-file="${DB_CONF}" \
  --no-data myapp > schema_only.sql

# ── Dump data only (no schema) ────────────────────────────
mysqldump --defaults-file="${DB_CONF}" \
  --no-create-info myapp > data_only.sql

# ── Dump with WHERE filter (partial table export) ─────────
mysqldump --defaults-file="${DB_CONF}" \
  --where="created_at >= '2026-01-01'" \
  myapp orders > orders_2026.sql

#!/usr/bin/env bash
# mysql_restore.sh — Restore with verification

set -euo pipefail

BACKUP_FILE="${1:?Usage: mysql_restore.sh BACKUP_FILE [database]}"
TARGET_DB="${2:-}"
DB_CONF="/etc/myapp/mysql.conf"

[[ -f "${BACKUP_FILE}" ]] || { echo "File not found: ${BACKUP_FILE}"; exit 1; }

echo "Restoring from: ${BACKUP_FILE}"
echo "Size: $(du -sh "${BACKUP_FILE}" | cut -f1)"

# Verify backup before restoring
gzip -t "${BACKUP_FILE}" || { echo "ERROR: Corrupt backup"; exit 1; }
echo "  ✔ Integrity verified"

# Preview what databases are in the backup
echo "  Tables in backup:"
zcat "${BACKUP_FILE}" | grep '^CREATE TABLE' | awk '{print "   ", $3}' | head -20

# Confirm before restoring
read -rp "  Restore to ${TARGET_DB:-default}? [yes/no]: " CONFIRM
[[ "${CONFIRM}" == "yes" ]] || { echo "Aborted"; exit 0; }

# Execute restore
if [[ -n "${TARGET_DB}" ]]; then
  zcat "${BACKUP_FILE}" | mysql --defaults-file="${DB_CONF}" "${TARGET_DB}"
else
  zcat "${BACKUP_FILE}" | mysql --defaults-file="${DB_CONF}"
fi

echo "  ✔ Restore complete"

# Verify restore by counting tables
RESTORED_TABLES=$(mysql --defaults-file="${DB_CONF}" -BNs \
  -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='${TARGET_DB:-myapp}'")
echo "  ✔ Tables restored: ${RESTORED_TABLES}"