Backup Database Bundle Laravel Package

Getting Started

Minimal Setup

1. Install the Bundle

   composer require andreas-a/backup-database-bundle
   

   Ensure AndreasABackupDatabaseBundle is registered in config/bundles.php (Symfony Flex typically handles this automatically).

2. Configure the Bundle Create config/packages/andreas_a_backup_database.yaml with a basic setup:

   andreas_a_backup_database:
       database_url: '%env(resolve:DATABASE_URL)%'
       target_directory: '%kernel.project_dir%/var/backup'
   

3. Verify Dependencies Ensure your server has:

   • mysqldump (MySQL/MariaDB)
   • bzip2 (for compression)
   • mkfifo (Unix-like OS requirement)

4. First Backup Run the command manually to test:

   php bin/console andreas-a:backup:database
   

   Check var/backup/ for the generated .sql.bz2 file (timestamped).

First Use Case: Pre-Deployment Backup

Integrate the backup into your deployment workflow (e.g., via deploy.php or CI/CD):

# Example in a deployment script
php bin/console andreas-a:backup:database --no-interaction

• Why? Ensures a clean backup before migrations or updates.

Implementation Patterns

Workflow: Scheduled Backups

1. Cron Job Integration Schedule via crontab (Linux/macOS):

   0 3 * * * cd /path/to/project && php bin/console andreas-a:backup:database >> /var/log/db_backup.log 2>&1
   

   • Best Practice: Log output to debug failures.

2. Artifact Retention Use a post-backup script to clean old files (e.g., keep last 7 days):

   find /path/to/project/var/backup -type f -mtime +7 -delete
   

Integration Tips

1. Custom Filename Formatting Override the default timestamp format by extending the command:

   // src/Command/CustomBackupCommand.php
   namespace App\Command;
   
   use AndreasA\BackupDatabaseBundle\Command\BackupDatabaseCommand;
   use Symfony\Component\Console\Input\InputInterface;
   use Symfony\Component\Console\Output\OutputInterface;
   
   class CustomBackupCommand extends BackupDatabaseCommand {
       protected function getFilename(): string {
           return 'custom_backup_' . date('Y-m-d') . '.sql.bz2';
       }
   }
   

   Register the new command in config/services.yaml:

   services:
       App\Command\CustomBackupCommand:
           tags: ['console.command']
   

2. Excluding Sensitive Data Leverage ignored_tables to exclude non-essential tables (e.g., logs, cache):

   andreas_a_backup_database:
       mysql:
           ignored_tables:
               - 'user_sessions'
               - 'failed_jobs'
   

3. Remote Storage Combine with AWS S3 or similar for off-site backups:

   # Post-backup script example
   aws s3 cp var/backup/latest.sql.bz2 s3://your-bucket/backups/
   

Gotchas and Tips

Pitfalls

1. Permission Issues

   • Symptom: mysqldump fails with "access denied" or "no such file or directory."
   • Fix: Ensure:
     • The PHP process has read/write access to target_directory.
     • The MySQL user has SELECT privileges on all non-ignored tables.
     • mysqldump is in $PATH (or use absolute paths in config).

2. Unsupported mysqldump Options

   • Symptom: Backup fails with mysqldump: unrecognized option.
   • Fix: Remove unsupported platform_specific_options or update MySQL client.

3. Large Database Performance

   • Symptom: Backups take hours or time out.
   • Fix:
     • Use --single-transaction (if supported) to avoid locks:

       andreas_a_backup_database:
           mysql:
               options:
                   - '--single-transaction'
       

     • Schedule during low-traffic periods.

Debugging

1. Dry Run Mode Add --dry-run to the command to preview the mysqldump command without executing:

   php bin/console andreas-a:backup:database --dry-run
   

   • Output: Shows the exact mysqldump command being run (copy-paste to test manually).

2. Verbose Logging Enable debug mode for detailed output:

   php bin/console andreas-a:backup:database -vvv
   

Extension Points

1. Pre/Post Backup Hooks Override the command’s execute() method to add logic:

   protected function execute(InputInterface $input, OutputInterface $output): int {
       $this->io->section('Pre-backup steps');
       // Custom logic (e.g., notify Slack)
   
       $result = parent::execute($input, $output);
   
       $this->io->section('Post-backup steps');
       // Custom logic (e.g., upload to S3)
   
       return $result;
   }
   

2. Custom Compression Modify the compress() method in the bundle’s service to use gzip instead of bzip2:

   // src/Service/BackupService.php (override)
   public function compress(string $source, string $destination): void {
       shell_exec("gzip -c $source > $destination");
   }
   

Configuration Quirks

1. MariaDB Compatibility

   • Note: The bundle may work with MariaDB, but test thoroughly.
   • Fix: If issues arise, explicitly set MariaDB-specific options:

     andreas_a_backup_database:
         mysql:
             options:
                 - '--skip-lock-tables'  # MariaDB-specific
     

2. Environment-Specific Configs Use Symfony’s environment-aware config (e.g., backup_production.yaml):

   # config/packages/andreas_a_backup_database_prod.yaml
   andreas_a_backup_database:
       target_directory: '%kernel.project_dir%/var/backups/production'
       mysql:
           ignored_tables: ['logs', 'sessions']
   

   Load it in config/packages/andreas_a_backup_database.yaml:

   imports:
       - { resource: andreas_a_backup_database_%env(default::PROD)%.yaml }
   

Maintenance Tips

1. Backup Verification Add a post-backup script to validate integrity:

   # Example: Restore to a temp DB and run a query
   bzip2 -d var/backup/latest.sql.bz2
   mysql -u user -p db_name < latest.sql
   mysql -u user -p db_name -e "SELECT COUNT(*) FROM users;"
   

2. Monitoring Track backup success/failure via:

   • Symfony Monolog: Configure the command to log to a dedicated channel.
   • External Tools: Integrate with tools like Datadog or Sentry for alerts.