Skip to main content

​
Backup & Restore

Strategi backup dan recovery untuk database MySQL di MStore Backend, mencakup full backup, incremental backup, dan point-in-time recovery.

​
🎯 Backup Strategy

​
Backup Types

TypeFrequencyScopeUse Case
Full BackupDaily (off-peak)Entire databaseComplete recovery
IncrementalHourlyChanges onlyFaster backup
DifferentialEvery 6 hoursChanges since last fullBalance speed & size
Transaction LogContinuousBinary logsPoint-in-time recovery

​
Backup Tools

Primary: mysqldump 
# Full backup
mysqldump -u root -p --all-databases > backup_full_2025_12_17.sql

# Specific database
mysqldump -u root -p mstore-monolith > backup_mstore_2025_12_17.sql

# With compression
mysqldump -u root -p mstore-monolith | gzip > backup_mstore_2025_12_17.sql.gz
Alternative: Percona XtraBackup 
# Full backup
xtrabackup --backup --target-dir=/backup/full_2025_12_17

# Incremental backup
xtrabackup --backup --target-dir=/backup/incr_2025_12_17 \
  --incremental-basedir=/backup/full_2025_12_17
Cloud: AWS RDS Snapshots 
# Create snapshot
aws rds create-db-snapshot \
  --db-instance-identifier mstore-prod \
  --db-snapshot-identifier mstore-snapshot-2025-12-17

# List snapshots
aws rds describe-db-snapshots \
  --db-instance-identifier mstore-prod

​
📋 Backup Scheduling

​
Daily Backup Schedule

00:00 — Full backup (off-peak)
06:00 — Incremental backup
12:00 — Incremental backup
18:00 — Incremental backup

​
Backup Retention Policy

Backup TypeRetentionStorage
Daily Full7 daysLocal + S3
Incremental24 hoursLocal
Weekly Full4 weeksS3
Monthly Full12 monthsGlacier

​
Automated Backup Script

#!/bin/bash
# backup.sh

BACKUP_DIR="/backups/mysql"
DB_NAME="mstore-monolith"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/backup_${DB_NAME}_${DATE}.sql.gz"

# Create backup
mysqldump -u root -p$DB_PASSWORD $DB_NAME | gzip > $BACKUP_FILE

# Upload to S3
aws s3 cp $BACKUP_FILE s3://mstore-backups/mysql/

# Keep only last 7 days
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

# Log backup
echo "Backup completed: $BACKUP_FILE" >> /var/log/backup.log

​
Cron Job

# Daily full backup at 00:00
0 0 * * * /usr/local/bin/backup.sh

# Incremental backup every 6 hours
0 */6 * * * /usr/local/bin/backup_incremental.sh

​
🔄 Restore Procedures

​
Full Restore (Complete Database)

# From SQL dump
mysql -u root -p < backup_mstore_2025_12_17.sql

# From compressed dump
gunzip < backup_mstore_2025_12_17.sql.gz | mysql -u root -p

# From Percona backup
xtrabackup --prepare --target-dir=/backup/full_2025_12_17
xtrabackup --copy-back --target-dir=/backup/full_2025_12_17

​
Selective Restore (Specific Table)

# Extract single table from backup
mysqldump -u root -p mstore-monolith merchants > merchants_backup.sql

# Restore specific table
mysql -u root -p mstore-monolith < merchants_backup.sql

# Restore with different name
mysql -u root -p mstore-monolith < merchants_backup.sql --table=merchants_restored

​
Point-in-Time Recovery (PITR)

# Find binary log position
mysqlbinlog /var/log/mysql/mysql-bin.000001 | grep "2025-12-17 14:30:00"

# Restore to specific time
mysqlbinlog --stop-datetime="2025-12-17 14:30:00" \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# Restore to specific position
mysqlbinlog --stop-position=12345 \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p

​
Restore from AWS RDS Snapshot

# Restore to new instance
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier mstore-restored \
  --db-snapshot-identifier mstore-snapshot-2025-12-17

# Restore to point-in-time
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier mstore-prod \
  --target-db-instance-identifier mstore-pitr \
  --restore-time 2025-12-17T14:30:00Z

​
✅ Backup Verification

​
Verify Backup Integrity

# Check backup file size
ls -lh backup_mstore_2025_12_17.sql.gz

# Verify SQL syntax
mysql -u root -p --syntax-check < backup_mstore_2025_12_17.sql

# Test restore on staging
mysql -u root -p mstore_staging < backup_mstore_2025_12_17.sql

# Compare row counts
mysql -u root -p -e "SELECT COUNT(*) FROM mstore-monolith.merchants;"

​
Backup Monitoring

-- Check binary log status
SHOW MASTER STATUS;

-- Check backup status
SELECT * FROM mysql.backup_history;

-- Verify replication lag (if using replication)
SHOW SLAVE STATUS\G

​
🚨 Disaster Recovery Plan

​
Recovery Time Objective (RTO)

  • Full backup restore: 30 minutes
  • Incremental restore: 15 minutes
  • PITR restore: 10 minutes

​
Recovery Point Objective (RPO)

  • Maximum data loss: 1 hour (hourly incremental backups)

​
Recovery Steps

  1. Assess Damage
    • Identify what data is corrupted/lost
    • Determine recovery point needed
    • Notify stakeholders
  2. Stop Application 
    systemctl stop mstore-backend
  3. Backup Current Database (for forensics) 
    mysqldump -u root -p mstore-monolith > backup_corrupted_2025_12_17.sql
  4. Restore from Backup 
    # Option A: Full restore
mysql -u root -p < backup_mstore_2025_12_17.sql

# Option B: PITR restore
mysqlbinlog --stop-datetime="2025-12-17 14:00:00" \
  /var/log/mysql/mysql-bin.* | mysql -u root -p
  5. Verify Data Integrity 
    -- Check row counts
SELECT COUNT(*) FROM merchants;
SELECT COUNT(*) FROM transactions;

-- Check for orphaned records
SELECT * FROM branches WHERE merchant_id NOT IN (SELECT id FROM merchants);
  6. Restart Application 
    systemctl start mstore-backend
  7. Monitor Logs 
    tail -f /var/log/mstore-backend/app.log

​
📊 Backup Checklist

​
Pre-Backup

  • Verify backup disk space
  • Check database connectivity
  • Verify backup script permissions
  • Test backup on staging
  • Notify team of maintenance window

​
During Backup

  • Monitor backup progress
  • Check CPU/disk usage
  • Verify no application errors
  • Monitor network bandwidth

​
Post-Backup

  • Verify backup file integrity
  • Verify backup file size (should be reasonable)
  • Upload to S3/cloud storage
  • Test restore on staging
  • Document backup completion
  • Update backup log

​
🚀 Best Practices

​
DO ✅

  • Backup regularly (daily minimum)
  • Test restores on staging
  • Keep backups off-site (S3, cloud)
  • Encrypt backups at rest
  • Monitor backup completion
  • Document recovery procedures
  • Use automated backup scripts
  • Maintain backup retention policy
  • Verify backup integrity regularly

​
DON’T ❌

  • Skip backup testing
  • Store backups only locally
  • Use unencrypted backups
  • Backup during peak hours
  • Ignore backup failures
  • Keep backups forever (cost)
  • Restore to production without testing
  • Forget to document recovery steps

MySQL Migrations

Migration strategy

Data Seeding

Seed data strategy

MySQL Schema

Complete schema reference

DevOps

DevOps procedures