217 lines
6.4 KiB
Bash
217 lines
6.4 KiB
Bash
#!/bin/bash
|
|
# Database Backup Script for Lottery Application
|
|
# This script creates a MySQL dump and transfers it to the backup VPS
|
|
#
|
|
# Usage:
|
|
# ./scripts/backup-database.sh [--keep-local] [--compress]
|
|
#
|
|
# Options:
|
|
# --keep-local Keep a local copy of the backup (default: delete after transfer)
|
|
# --compress Compress the backup before transfer (default: gzip)
|
|
#
|
|
# Prerequisites:
|
|
# 1. SSH key-based authentication to backup VPS (5.45.77.77)
|
|
# 2. Database password accessible via /run/secrets/honey-config.properties
|
|
# 3. Docker container 'honey-mysql' running
|
|
#
|
|
# Backup location on backup VPS: /raid/backup/acc_260182/
|
|
|
|
set -euo pipefail
|
|
|
|
# Configuration
|
|
BACKUP_VPS_HOST="5.45.77.77"
|
|
BACKUP_VPS_USER="acc_260182" # User account on backup VPS
|
|
BACKUP_VPS_PATH="/raid/backup/acc_260182"
|
|
MYSQL_CONTAINER="honey-mysql"
|
|
MYSQL_DATABASE="lottery_db"
|
|
SECRET_FILE="/run/secrets/honey-config.properties"
|
|
BACKUP_DIR="/opt/app/backups"
|
|
KEEP_LOCAL=false
|
|
COMPRESS=true
|
|
|
|
# Parse command line arguments
|
|
while [[ $# -gt 0 ]]; do
|
|
case $1 in
|
|
--keep-local)
|
|
KEEP_LOCAL=true
|
|
shift
|
|
;;
|
|
--no-compress)
|
|
COMPRESS=false
|
|
shift
|
|
;;
|
|
*)
|
|
echo "Unknown option: $1"
|
|
echo "Usage: $0 [--keep-local] [--no-compress]"
|
|
exit 1
|
|
;;
|
|
esac
|
|
done
|
|
|
|
# Colors for output
|
|
RED='\033[0;31m'
|
|
GREEN='\033[0;32m'
|
|
YELLOW='\033[1;33m'
|
|
NC='\033[0m' # No Color
|
|
|
|
# Logging function
|
|
log() {
|
|
echo -e "${GREEN}[$(date +'%Y-%m-%d %H:%M:%S')]${NC} $1"
|
|
}
|
|
|
|
error() {
|
|
echo -e "${RED}[ERROR]${NC} $1" >&2
|
|
}
|
|
|
|
warn() {
|
|
echo -e "${YELLOW}[WARN]${NC} $1"
|
|
}
|
|
|
|
# Check if running as root or with sudo
|
|
if [ "$EUID" -ne 0 ]; then
|
|
error "This script must be run as root (or with sudo)"
|
|
exit 1
|
|
fi
|
|
|
|
# Load database password
|
|
if [ ! -f "$SECRET_FILE" ]; then
|
|
error "Secret file not found at $SECRET_FILE"
|
|
exit 1
|
|
fi
|
|
|
|
DB_PASSWORD=$(grep "^SPRING_DATASOURCE_PASSWORD=" "$SECRET_FILE" | cut -d'=' -f2- | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')
|
|
|
|
if [ -z "$DB_PASSWORD" ]; then
|
|
error "SPRING_DATASOURCE_PASSWORD not found in secret file"
|
|
exit 1
|
|
fi
|
|
|
|
# Check if MySQL container is running
|
|
if ! docker ps --format '{{.Names}}' | grep -q "^${MYSQL_CONTAINER}$"; then
|
|
error "MySQL container '${MYSQL_CONTAINER}' is not running"
|
|
exit 1
|
|
fi
|
|
|
|
# Create backup directory if it doesn't exist
|
|
mkdir -p "$BACKUP_DIR"
|
|
|
|
# Generate backup filename with timestamp
|
|
TIMESTAMP=$(date +'%Y%m%d_%H%M%S')
|
|
BACKUP_FILENAME="lottery_db_backup_${TIMESTAMP}.sql"
|
|
BACKUP_PATH="${BACKUP_DIR}/${BACKUP_FILENAME}"
|
|
|
|
# If compression is enabled, add .gz extension
|
|
if [ "$COMPRESS" = true ]; then
|
|
BACKUP_FILENAME="${BACKUP_FILENAME}.gz"
|
|
BACKUP_PATH="${BACKUP_DIR}/${BACKUP_FILENAME}"
|
|
fi
|
|
|
|
log "Starting database backup..."
|
|
log "Database: ${MYSQL_DATABASE}"
|
|
log "Container: ${MYSQL_CONTAINER}"
|
|
log "Backup file: ${BACKUP_FILENAME}"
|
|
|
|
# Create MySQL dump
|
|
log "Creating MySQL dump..."
|
|
|
|
if [ "$COMPRESS" = true ]; then
|
|
# Dump and compress in one step (saves disk space)
|
|
if docker exec "${MYSQL_CONTAINER}" mysqldump \
|
|
-u root \
|
|
-p"${DB_PASSWORD}" \
|
|
--single-transaction \
|
|
--routines \
|
|
--triggers \
|
|
--events \
|
|
--quick \
|
|
--lock-tables=false \
|
|
"${MYSQL_DATABASE}" | gzip > "${BACKUP_PATH}"; then
|
|
log "✅ Database dump created and compressed: ${BACKUP_PATH}"
|
|
else
|
|
error "Failed to create database dump"
|
|
exit 1
|
|
fi
|
|
else
|
|
# Dump without compression
|
|
if docker exec "${MYSQL_CONTAINER}" mysqldump \
|
|
-u root \
|
|
-p"${DB_PASSWORD}" \
|
|
--single-transaction \
|
|
--routines \
|
|
--triggers \
|
|
--events \
|
|
--quick \
|
|
--lock-tables=false \
|
|
"${MYSQL_DATABASE}" > "${BACKUP_PATH}"; then
|
|
log "✅ Database dump created: ${BACKUP_PATH}"
|
|
else
|
|
error "Failed to create database dump"
|
|
exit 1
|
|
fi
|
|
fi
|
|
|
|
# Get backup file size
|
|
BACKUP_SIZE=$(du -h "${BACKUP_PATH}" | cut -f1)
|
|
log "Backup size: ${BACKUP_SIZE}"
|
|
|
|
# Transfer to backup VPS
|
|
log "Transferring backup to backup VPS (${BACKUP_VPS_HOST})..."
|
|
|
|
# Test SSH connection first
|
|
if ! ssh -o ConnectTimeout=10 -o BatchMode=yes "${BACKUP_VPS_USER}@${BACKUP_VPS_HOST}" "echo 'SSH connection successful'" > /dev/null 2>&1; then
|
|
error "Cannot connect to backup VPS via SSH"
|
|
error "Please ensure:"
|
|
error " 1. SSH key-based authentication is set up"
|
|
error " 2. Backup VPS is accessible from this server"
|
|
error " 3. User '${BACKUP_VPS_USER}' has access to ${BACKUP_VPS_PATH}"
|
|
|
|
if [ "$KEEP_LOCAL" = true ]; then
|
|
warn "Keeping local backup despite transfer failure: ${BACKUP_PATH}"
|
|
else
|
|
rm -f "${BACKUP_PATH}"
|
|
fi
|
|
exit 1
|
|
fi
|
|
|
|
# Create backup directory on remote VPS if it doesn't exist
|
|
ssh "${BACKUP_VPS_USER}@${BACKUP_VPS_HOST}" "mkdir -p ${BACKUP_VPS_PATH}"
|
|
|
|
# Transfer the backup file
|
|
if scp "${BACKUP_PATH}" "${BACKUP_VPS_USER}@${BACKUP_VPS_HOST}:${BACKUP_VPS_PATH}/"; then
|
|
log "✅ Backup transferred successfully to ${BACKUP_VPS_HOST}:${BACKUP_VPS_PATH}/${BACKUP_FILENAME}"
|
|
|
|
# Verify remote file exists
|
|
REMOTE_SIZE=$(ssh "${BACKUP_VPS_USER}@${BACKUP_VPS_HOST}" "du -h ${BACKUP_VPS_PATH}/${BACKUP_FILENAME} 2>/dev/null | cut -f1" || echo "0")
|
|
if [ "$REMOTE_SIZE" != "0" ]; then
|
|
log "✅ Remote backup verified (size: ${REMOTE_SIZE})"
|
|
else
|
|
warn "Could not verify remote backup file"
|
|
fi
|
|
else
|
|
error "Failed to transfer backup to backup VPS"
|
|
if [ "$KEEP_LOCAL" = true ]; then
|
|
warn "Keeping local backup despite transfer failure: ${BACKUP_PATH}"
|
|
else
|
|
rm -f "${BACKUP_PATH}"
|
|
fi
|
|
exit 1
|
|
fi
|
|
|
|
# Clean up local backup if not keeping it
|
|
if [ "$KEEP_LOCAL" = false ]; then
|
|
rm -f "${BACKUP_PATH}"
|
|
log "Local backup file removed (transferred successfully)"
|
|
fi
|
|
|
|
# Clean up old backups on remote VPS (keep last 10 days)
|
|
log "Cleaning up old backups on remote VPS (keeping last 10 days)..."
|
|
ssh "${BACKUP_VPS_USER}@${BACKUP_VPS_HOST}" "find ${BACKUP_VPS_PATH} -name 'lottery_db_backup_*.sql*' -type f -mtime +10 -delete" || warn "Failed to clean up old backups"
|
|
|
|
# Count remaining backups
|
|
BACKUP_COUNT=$(ssh "${BACKUP_VPS_USER}@${BACKUP_VPS_HOST}" "ls -1 ${BACKUP_VPS_PATH}/lottery_db_backup_*.sql* 2>/dev/null | wc -l" || echo "0")
|
|
log "Total backups on remote VPS: ${BACKUP_COUNT}"
|
|
|
|
log "✅ Backup completed successfully!"
|
|
log " Remote location: ${BACKUP_VPS_HOST}:${BACKUP_VPS_PATH}/${BACKUP_FILENAME}"
|
|
|