Skip to main content

Database Backup and Restore

Complete guide to backing up and restoring the Netasampark database.

Backup Methods​

Full Backup​

mysqldump -u username -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  netasampark_db > backup_$(date +%Y%m%d_%H%M%S).sql

Compressed Backup​

mysqldump -u username -p netasampark_db | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

Specific Tables​

mysqldump -u username -p netasampark_db users voters campaigns > partial_backup.sql

2. Laravel Artisan Command​

Automated Backup​

php artisan db:backup

With Compression​

php artisan db:backup --compress

Scheduled Backups​

Configured in app/Console/Kernel.php:

$schedule->command('db:backup')
    ->daily()
    ->at('02:00');

3. Cloud Backup​

AWS S3​

mysqldump -u username -p netasampark_db | \
  aws s3 cp - s3://bucket/backups/backup_$(date +%Y%m%d).sql

Google Cloud Storage​

mysqldump -u username -p netasampark_db | \
  gsutil cp - gs://bucket/backups/backup_$(date +%Y%m%d).sql

Restore Methods​

1. From SQL File​

mysql -u username -p netasampark_db < backup_20240115_120000.sql

2. From Compressed Backup​

gunzip < backup_20240115_120000.sql.gz | mysql -u username -p netasampark_db

3. Laravel Artisan​

php artisan db:restore backup_20240115_120000.sql

Backup Strategy​

Daily Backups​

  • Full database backup
  • Retain for 7 days
  • Automated at 2 AM

Weekly Backups​

  • Full database backup
  • Retain for 4 weeks
  • Automated on Sundays

Monthly Backups​

  • Full database backup
  • Retain for 12 months
  • Automated on 1st of month

Backup Verification​

Check Backup Integrity​

# Check file size
ls -lh backup.sql

# Verify SQL syntax
mysql -u username -p --execute="SOURCE backup.sql" --force

# Test restore on staging
mysql -u username -p test_db < backup.sql

Automated Backup Script​

#!/bin/bash
# backup.sh

DB_USER="username"
DB_PASS="password"
DB_NAME="netasampark_db"
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME | gzip > $BACKUP_DIR/backup_$DATE.sql.gz

# Remove backups older than 30 days
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +30 -delete

echo "Backup completed: backup_$DATE.sql.gz"

Point-in-Time Recovery​

Enable Binary Logging​

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
expire_logs_days=7

Restore to Point in Time​

# Restore full backup
mysql -u username -p netasampark_db < full_backup.sql

# Apply binary logs
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
  --stop-datetime="2024-01-15 11:00:00" \
  mysql-bin.000001 | mysql -u username -p netasampark_db

Best Practices​

  1. Regular Backups: Daily automated backups
  2. Test Restores: Regularly test restore process
  3. Off-site Storage: Keep backups in multiple locations
  4. Encryption: Encrypt sensitive backups
  5. Documentation: Document backup/restore procedures
  6. Monitoring: Monitor backup success/failure
  7. Retention Policy: Clear retention policy

Monitoring​

Check Backup Status​

# List recent backups
ls -lth /backups/

# Check backup size
du -sh /backups/

# Verify last backup
stat /backups/backup_latest.sql.gz

Alert on Failure​

Configure monitoring to alert if:

  • Backup fails
  • Backup size is unusual
  • Backup is older than expected

Next Steps​