honey-be/BACKUP_SETUP.md

Database Backup Setup Guide

This guide explains how to set up automated database backups from your main VPS to your backup VPS.

Overview

• Main VPS: 37.1.206.220 (production server)
• Backup VPS: 5.45.77.77 (backup storage)
• Backup Location: /raid/backup/acc_260182/ on backup VPS
• Database: MySQL 8.0 in Docker container lottery-mysql
• Database Name: lottery_db

Prerequisites

1. SSH access to both VPS servers
2. Root or sudo access on main VPS
3. Write access to /raid/backup/acc_260182/ on backup VPS

Step 1: Set Up SSH Key Authentication

To enable passwordless transfers, set up SSH key authentication between your main VPS and backup VPS.

On Main VPS (37.1.206.220):

# Generate SSH key pair (if you don't have one)
ssh-keygen -t ed25519 -C "backup@lottery-main-vps" -f ~/.ssh/backup_key

# Copy public key to backup VPS
ssh-copy-id -i ~/.ssh/backup_key.pub root@5.45.77.77

# Test connection
ssh -i ~/.ssh/backup_key root@5.45.77.77 "echo 'SSH connection successful'"

Note: If you already have an SSH key, you can use it instead. The script uses the default SSH key (~/.ssh/id_rsa or ~/.ssh/id_ed25519).

Alternative: Use Existing SSH Key

If you want to use an existing SSH key, you can either:

1. Use the default key (no changes needed)
2. Configure SSH to use a specific key by editing ~/.ssh/config:

cat >> ~/.ssh/config << EOF
Host backup-vps
    HostName 5.45.77.77
    User root
    IdentityFile ~/.ssh/backup_key
EOF

Then update the backup script to use backup-vps as the hostname.

Step 2: Configure Backup Script

The backup script is located at scripts/backup-database.sh. It's already configured with:

• Backup VPS: 5.45.77.77
• Backup path: /raid/backup/acc_260182
• MySQL container: lottery-mysql
• Database: lottery_db

If you need to change the backup VPS user (default: root), edit the script:

nano scripts/backup-database.sh
# Change: BACKUP_VPS_USER="root" to your user

Step 3: Make Scripts Executable

cd /opt/app/backend/lottery-be
chmod +x scripts/backup-database.sh
chmod +x scripts/restore-database.sh

Step 4: Test Manual Backup

Run a test backup to ensure everything works:

# Test backup (keeps local copy for verification)
./scripts/backup-database.sh --keep-local

# Check backup on remote VPS
ssh root@5.45.77.77 "ls -lh /raid/backup/acc_260182/ | tail -5"

Step 5: Set Up Automated Backups (Cron)

Set up a cron job to run backups automatically. Recommended schedule: daily at 2 AM.

Option A: Edit Crontab Directly

# Edit root's crontab
sudo crontab -e

# Add this line (daily at 2 AM):
0 2 * * * /opt/app/backend/lottery-be/scripts/backup-database.sh >> /opt/app/logs/backup.log 2>&1

Option B: Create Cron Script

Create a wrapper script for better logging:

cat > /opt/app/backend/lottery-be/scripts/run-backup.sh << 'EOF'
#!/bin/bash
# Wrapper script for automated backups
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
LOG_FILE="/opt/app/logs/backup.log"

# Ensure log directory exists
mkdir -p "$(dirname "$LOG_FILE")"

# Run backup and log output
"${SCRIPT_DIR}/backup-database.sh" >> "$LOG_FILE" 2>&1

# Send email notification on failure (optional, requires mail setup)
if [ $? -ne 0 ]; then
    echo "Backup failed at $(date)" | mail -s "Lottery DB Backup Failed" your-email@example.com
fi
EOF

chmod +x /opt/app/backend/lottery-be/scripts/run-backup.sh

Then add to crontab:

sudo crontab -e
# Add:
0 2 * * * /opt/app/backend/lottery-be/scripts/run-backup.sh

Recommended Backup Schedules

• Daily at 2 AM: 0 2 * * * (recommended)
• Twice daily (2 AM and 2 PM): 0 2,14 * * *
• Every 6 hours: 0 */6 * * *
• Weekly (Sunday 2 AM): 0 2 * * 0

Step 6: Verify Automated Backups

After setting up cron, verify it's working:

# Check cron job is scheduled
sudo crontab -l

# Check backup logs
tail -f /opt/app/logs/backup.log

# List recent backups on remote VPS
ssh root@5.45.77.77 "ls -lht /raid/backup/acc_260182/ | head -10"

Backup Retention

The backup script automatically:

• Keeps last 30 days of backups on remote VPS
• Deletes local backups after successful transfer (unless --keep-local is used)

To change retention period, edit scripts/backup-database.sh:

# Change this line:
ssh "${BACKUP_VPS_USER}@${BACKUP_VPS_HOST}" "find ${BACKUP_VPS_PATH} -name 'lottery_db_backup_*.sql*' -type f -mtime +30 -delete"

# To keep 60 days, change +30 to +60

Restoring from Backup

Restore from Remote Backup

# Restore from backup VPS
./scripts/restore-database.sh 5.45.77.77:/raid/backup/acc_260182/lottery_db_backup_20240101_020000.sql.gz

Restore from Local Backup

# If you kept a local backup
./scripts/restore-database.sh /opt/app/backups/lottery_db_backup_20240101_020000.sql.gz

⚠️ WARNING: Restore will DROP and RECREATE the database. All existing data will be lost!

Backup Script Options

# Standard backup (compressed, no local copy)
./scripts/backup-database.sh

# Keep local copy after transfer
./scripts/backup-database.sh --keep-local

# Don't compress backup (faster, but larger files)
./scripts/backup-database.sh --no-compress

Monitoring Backups

Check Backup Status

# View recent backup logs
tail -50 /opt/app/logs/backup.log

# Count backups on remote VPS
ssh root@5.45.77.77 "ls -1 /raid/backup/acc_260182/lottery_db_backup_*.sql* | wc -l"

# List all backups with sizes
ssh root@5.45.77.77 "ls -lh /raid/backup/acc_260182/lottery_db_backup_*.sql*"

Backup Health Check Script

Create a simple health check:

cat > /opt/app/backend/lottery-be/scripts/check-backup-health.sh << 'EOF'
#!/bin/bash
# Check if backups are running successfully

BACKUP_VPS="5.45.77.77"
BACKUP_PATH="/raid/backup/acc_260182"
DAYS_THRESHOLD=2  # Alert if no backup in last 2 days

LAST_BACKUP=$(ssh root@${BACKUP_VPS} "ls -t ${BACKUP_PATH}/lottery_db_backup_*.sql* 2>/dev/null | head -1")

if [ -z "$LAST_BACKUP" ]; then
    echo "❌ ERROR: No backups found on backup VPS!"
    exit 1
fi

LAST_BACKUP_DATE=$(ssh root@${BACKUP_VPS} "stat -c %Y ${LAST_BACKUP}")
CURRENT_DATE=$(date +%s)
DAYS_SINCE_BACKUP=$(( (CURRENT_DATE - LAST_BACKUP_DATE) / 86400 ))

if [ $DAYS_SINCE_BACKUP -gt $DAYS_THRESHOLD ]; then
    echo "⚠️  WARNING: Last backup is $DAYS_SINCE_BACKUP days old!"
    exit 1
else
    echo "✅ Backup health OK: Last backup $DAYS_SINCE_BACKUP day(s) ago"
    exit 0
fi
EOF

chmod +x /opt/app/backend/lottery-be/scripts/check-backup-health.sh

Troubleshooting

SSH Connection Issues

# Test SSH connection
ssh -v root@5.45.77.77 "echo 'test'"

# Check SSH key permissions
chmod 600 ~/.ssh/id_rsa
chmod 644 ~/.ssh/id_rsa.pub

Permission Denied on Backup VPS

# Verify write access
ssh root@5.45.77.77 "touch /raid/backup/acc_260182/test && rm /raid/backup/acc_260182/test && echo 'Write access OK'"

MySQL Container Not Running

# Check container status
docker ps | grep lottery-mysql

# Start container if needed
cd /opt/app/backend/lottery-be
docker-compose -f docker-compose.prod.yml up -d db

Backup Script Permission Denied

# Make script executable
chmod +x scripts/backup-database.sh

Backup File Naming

Backups are named with timestamp: lottery_db_backup_YYYYMMDD_HHMMSS.sql.gz

Example: lottery_db_backup_20240115_020000.sql.gz

Disk Space Considerations

• Compressed backups: Typically 10-50% of database size
• Uncompressed backups: Same size as database
• 30-day retention: Plan for ~30x daily backup size

Monitor disk space on backup VPS:

ssh root@5.45.77.77 "df -h /raid/backup/acc_260182"

Security Notes

1. SSH Keys: Use SSH key authentication (no passwords)
2. Secret File: Database password is read from /run/secrets/lottery-config.properties (secure)
3. Backup Files: Contain sensitive data - ensure backup VPS is secure
4. Permissions: Backup script requires root access to read secrets

Next Steps

1. ✅ Set up SSH key authentication
2. ✅ Test manual backup
3. ✅ Set up cron job for automated backups
4. ✅ Monitor backup logs for first few days
5. ✅ Test restore procedure (on test environment first!)