Skip to content

DATABASE_BACKUP.md

Latest commit

 

History

History
317 lines (244 loc) · 8.23 KB

DATABASE_BACKUP.md

File metadata and controls

317 lines (244 loc) · 8.23 KB

Database Backup Documentation

Overview

The Recipe Share application includes a comprehensive database backup system that provides automated, secure, and reliable backup functionality. The system supports multiple database drivers (MySQL, PostgreSQL, SQLite) and includes advanced features like compression, verification, and automatic cleanup.

Features

  • Multi-database support: MySQL, PostgreSQL, SQLite
  • Flexible backup types: Full backup, structure only, data only
  • Compression: Gzip compression with configurable levels
  • Verification: Automatic backup integrity checking
  • Retention policy: Automatic cleanup of old backups
  • Security logging: Complete audit trail through SecurityLogService
  • Command-line interface: Easy-to-use Artisan commands

Configuration

The backup system is configured through config/backup.php. Key settings include:

Storage Configuration

'storage_path' => env('BACKUP_STORAGE_PATH', storage_path('app/backups')),
'filename_pattern' => env('BACKUP_FILENAME_PATTERN', '{database}_{date}_{time}.sql'),

Compression Settings

'compress' => env('BACKUP_COMPRESS', true),
'compression_level' => env('BACKUP_COMPRESSION_LEVEL', 6), // 1-9

Retention Policy

'retention_days' => env('BACKUP_RETENTION_DAYS', 30),
'max_backup_files' => env('BACKUP_MAX_FILES', 50),

Environment Variables

Add these variables to your .env file:

# Backup Configuration
BACKUP_STORAGE_PATH=/secure/backup/path
BACKUP_RETENTION_DAYS=30
BACKUP_MAX_FILES=50
BACKUP_COMPRESS=true
BACKUP_COMPRESSION_LEVEL=6
BACKUP_TYPE=full
BACKUP_VERIFY=true
BACKUP_NOTIFICATIONS_ENABLED=true
BACKUP_NOTIFY_FAILURE=true

Usage

Basic Commands

Create a Backup

# Create a full backup with default settings
php artisan backup:database

# Create a compressed backup
php artisan backup:database --compress

# Create backup without verification
php artisan backup:database --no-verify

Backup Types

# Full backup (structure + data) - default
php artisan backup:database --type=full

# Structure only (schema without data)
php artisan backup:database --type=structure_only

# Data only (no table structures)
php artisan backup:database --type=data_only

List Existing Backups

php artisan backup:database --list

Clean Up Old Backups

# Clean up based on retention policy
php artisan backup:database --cleanup

Specify Database Connection

# Use specific database connection
php artisan backup:database --connection=mysql

Advanced Usage

Combined Options

# Create compressed full backup with verification
php artisan backup:database --type=full --compress

# Create structure-only backup for specific connection
php artisan backup:database --connection=pgsql --type=structure_only --compress

Automated Backups

Cron Job Setup

For automated daily backups, add to your crontab:

# Edit crontab
crontab -e

# Add daily backup at 2:00 AM
0 2 * * * cd /path/to/your/backend && php artisan backup:database --compress > /dev/null 2>&1

# Weekly cleanup at 3:00 AM every Sunday
0 3 * * 0 cd /path/to/your/backend && php artisan backup:database --cleanup > /dev/null 2>&1

Laravel Scheduler

Add to app/Console/Kernel.php:

protected function schedule(Schedule $schedule)
{
    // Daily backup at 2:00 AM
    $schedule->command('backup:database --compress')
             ->dailyAt('02:00')
             ->environments(['production']);
    
    // Weekly cleanup on Sundays at 3:00 AM
    $schedule->command('backup:database --cleanup')
             ->weeklyOn(0, '03:00')
             ->environments(['production']);
}

File Organization

Backup File Naming

Default pattern: {database}_{date}_{time}.sql[.gz]

Example files:

  • recipeshare_2025-09-28_08-55-57.sql.gz (compressed)
  • recipeshare_2025-09-28_08-55-57.sql (uncompressed)

Directory Structure

storage/app/backups/
├── recipeshare_2025-09-28_08-55-57.sql.gz
├── recipeshare_2025-09-27_02-00-01.sql.gz
└── recipeshare_2025-09-26_02-00-01.sql.gz

Security Features

Logging

All backup operations are logged through the SecurityLogService:

  • Backup start/completion
  • File sizes and durations
  • Verification results
  • Cleanup operations
  • Error conditions

Excluded Tables

Sensitive or temporary tables are automatically excluded:

  • cache
  • sessions
  • failed_jobs
  • telescope_entries
  • telescope_entries_tags
  • telescope_monitoring

File Permissions

Backup files are created with restricted permissions (755) and stored in a secure directory outside the web root.

Monitoring and Troubleshooting

Command Output

Successful backup example:

🔄 Starting database backup...
✅ Backup completed successfully!

+------------+----------------------------------------+
| Property   | Value                                  |
+------------+----------------------------------------+
| File       | recipeshare_2025-09-28_08-55-57.sql.gz |
| Size       | 8.76 KB                                |
| Type       | mysql                                  |
| Compressed | Yes                                    |
| Duration   | 0.27s                                  |
+------------+----------------------------------------+

📁 Backup saved to: /path/to/storage/app/backups/recipeshare_2025-09-28_08-55-57.sql.gz

Log Files

Check Laravel logs for backup events:

tail -f storage/logs/laravel.log | grep backup

Common Issues

Permission Errors

# Fix backup directory permissions
chmod 755 storage/app/backups
chown -R www-data:www-data storage/app/backups

MySQL Authentication

If you see "Using a password on the command line interface can be insecure" warning, this is normal and doesn't affect functionality.

Large Database Backups

For very large databases, consider:

  • Increasing PHP memory limit
  • Using structure-only backups for development
  • Implementing incremental backup strategies

Database-Specific Notes

MySQL

  • Uses mysqldump command
  • Requires MySQL client tools
  • Supports all standard MySQL options
  • Single transaction mode for consistency

PostgreSQL

  • Uses pg_dump command
  • Requires PostgreSQL client tools
  • Custom format for optimal compression
  • Verbose output for detailed logging

SQLite

  • Direct file copying
  • No external tools required
  • Instant backup for small databases
  • File locking considerations

Recovery Procedures

MySQL Recovery

# Uncompress if needed
gunzip backup_file.sql.gz

# Restore database
mysql -u username -p database_name < backup_file.sql

PostgreSQL Recovery

# Restore from custom format
pg_restore -h host -U username -d database_name backup_file.sql

SQLite Recovery

# Simply copy the backup file back
cp backup_file.sql database.sqlite

Best Practices

  1. Regular Testing: Periodically test backup restoration to ensure integrity
  2. Off-site Storage: Consider copying backups to remote storage for disaster recovery
  3. Monitoring: Set up alerts for backup failures
  4. Documentation: Keep recovery procedures documented and accessible
  5. Security: Encrypt backups containing sensitive data
  6. Retention: Balance storage costs with recovery requirements

Integration with Other Systems

Cloud Storage

Consider integrating with cloud storage services:

  • AWS S3
  • Google Cloud Storage
  • Azure Blob Storage

Monitoring Systems

Integrate with monitoring tools:

  • Laravel Horizon for queue monitoring
  • Application performance monitoring (APM)
  • Log aggregation services

Command Reference

Command Description
php artisan backup:database Create basic backup
php artisan backup:database --compress Create compressed backup
php artisan backup:database --type=structure_only Structure-only backup
php artisan backup:database --type=data_only Data-only backup
php artisan backup:database --list List all backups
php artisan backup:database --cleanup Clean old backups
php artisan backup:database --connection=name Use specific connection
php artisan backup:database --no-verify Skip verification

For additional help, use:

php artisan help backup:database