Shell Scripting — Database Centric: Database Backup Verification

#!/usr/bin/env bash
# verify_backup.sh — Restore backup to test DB and verify integrity

set -euo pipefail

BACKUP_FILE="${1:?Usage: verify_backup.sh BACKUP.sql.gz}"
TEST_DB="myapp_verify_$$"
DB_CONF="/etc/myapp/mysql.conf"
ERRORS=0

db() { mysql --defaults-file="${DB_CONF}" -BNs -D "${TEST_DB}" -e "$1"; }
cleanup() { mysql --defaults-file="${DB_CONF}" -e "DROP DATABASE IF EXISTS ${TEST_DB}"; }
trap cleanup EXIT

echo "=== Backup Verification: $(basename "${BACKUP_FILE}") ==="

# Step 1: Validate file integrity
echo "  Checking file integrity..."
gzip -t "${BACKUP_FILE}" || { echo "  FAIL: Corrupt gzip"; exit 1; }
echo "  PASS: File integrity OK ($(du -sh "${BACKUP_FILE}" | cut -f1))"

# Step 2: Check backup is not empty
TABLE_COUNT=$(zcat "${BACKUP_FILE}" | grep -c "^CREATE TABLE" || true)
(( TABLE_COUNT > 0 )) || { echo "  FAIL: No CREATE TABLE statements found"; exit 1; }
echo "  PASS: ${TABLE_COUNT} CREATE TABLE statements found"

# Step 3: Restore to test database
echo "  Restoring to ${TEST_DB}..."
mysql --defaults-file="${DB_CONF}" -e "CREATE DATABASE ${TEST_DB}"
zcat "${BACKUP_FILE}" | mysql --defaults-file="${DB_CONF}" "${TEST_DB}" 2>/dev/null
echo "  PASS: Restore completed"

# Step 4: Verify restored data
RESTORED_TABLES=$(db "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='${TEST_DB}'")
echo "  PASS: ${RESTORED_TABLES} tables restored"

USERS=$(db "SELECT COUNT(*) FROM users" 2>/dev/null || echo 0)
ORDERS=$(db "SELECT COUNT(*) FROM orders" 2>/dev/null || echo 0)
echo "  PASS: users=${USERS} orders=${ORDERS}"

# Step 5: Referential integrity spot check
ORPHANS=$(db "SELECT COUNT(*) FROM orders o LEFT JOIN users u ON u.id=o.user_id WHERE u.id IS NULL" 2>/dev/null || echo -1)
(( ORPHANS == 0 )) || { echo "  WARN: ${ORPHANS} orphaned orders"; (( ERRORS++ )); }

echo ""
echo "  Backup verified: ${ERRORS} warnings"
(( ERRORS == 0 )) && echo "  RESULT: VERIFIED OK" || echo "  RESULT: VERIFIED WITH WARNINGS"

#!/usr/bin/env bash
# backup_catalogue.sh — Track and report backup status

BACKUP_DIR="/backups/mysql"
CATALOGUE="${BACKUP_DIR}/catalogue.tsv"
KEEP_DAYS=30

# ── Register a new backup in catalogue ────────────────────
register_backup() {
  local file="$1" db_name="$2" tables="$3" rows="$4"
  local size ts checksum
  size=$(du -sb "${file}" | cut -f1)
  ts=$(date --iso-8601=seconds)
  checksum=$(sha256sum "${file}" | cut -d' ' -f1)
  printf '%s\t%s\t%s\t%s\t%d\t%d\t%s\n'     "${ts}" "$(basename "${file}")" "${db_name}" "${checksum}"     "${size}" "${tables}" "${rows}" >> "${CATALOGUE}"
}

# ── Report backup status ───────────────────────────────────
echo "=== Backup Catalogue Report ==="
printf "%-25s %-40s %8s %6s\n" "Timestamp" "File" "Size" "Tables"
echo "$(printf '%.0s─' {1..80})"
tail -20 "${CATALOGUE}" |   awk -F$'\t' '{printf "%-25s %-40s %8s %6s\n",$1,$2,int($5/1048576)"MB",$6}'

# ── Alert if no recent backup ──────────────────────────────
LATEST_BACKUP=$(find "${BACKUP_DIR}" -name "*.sql.gz" -mtime -1 | head -1)
if [[ -z "${LATEST_BACKUP}" ]]; then
  echo "CRITICAL: No backup in last 24 hours!" |     mail -s "BACKUP MISSING" ops@example.com
fi

# ── Prune old backups ─────────────────────────────────────
echo "Pruning backups older than ${KEEP_DAYS} days..."
find "${BACKUP_DIR}" -name "*.sql.gz" -mtime "+${KEEP_DAYS}" -delete -print |   awk '{print "  Deleted: "$0}