Mysqldump Php Laravel Package

## Getting Started
### Minimal Steps
1. **Installation**
   ```bash
   composer require ifsnop/mysqldump-php

Add the service provider in config/app.php:

'providers' => [
    // ...
    Ifsnop\Mysqldump\MysqldumpServiceProvider::class,
],

2. First Use Case: Basic Dump (v2.11+)

   use Ifsnop\Mysqldump\Mysqldump;
   
   $mysqldump = new Mysqldump('mysql:host=localhost;dbname=test', 'username', 'password');
   $mysqldump->start();
   $dump = $mysqldump->getDump();
   file_put_contents('backup.sql', $dump);
   

   Note: For large databases, prefer streaming to avoid memory issues (fixed in v2.11).

3. Where to Look First

   • GitHub Repository (check CHANGELOG.md for v2.11 fixes)
   • src/Mysqldump.php for core functionality (memory handling improvements)
   • tests/ for edge cases (e.g., large database tests)

Implementation Patterns

Core Workflows

1. Programmatic Backups (Memory-Safe)

   $mysqldump = new Mysqldump($dsn, $user, $pass, [
       'add-drop-table' => true,
       'no-create-info' => false,
   ]);
   $mysqldump->start();
   // Use streaming for large databases (v2.11+ handles this better)
   $mysqldump->getDump(function ($chunk) {
       file_put_contents('backup.sql', $chunk, FILE_APPEND);
   });
   

2. Streaming Large Dumps (Recommended for v2.11+)

   $mysqldump = new Mysqldump($dsn, $user, $pass);
   $mysqldump->start();
   $mysqldump->getDump(function ($chunk) {
       // Process chunk (e.g., upload to S3, compress, etc.)
       Storage::disk('backups')->append('backup.sql', $chunk);
   });
   

3. Laravel Integration (v2.11+)

   • Artisan Command (Memory-Optimized):

     use Ifsnop\Mysqldump\Mysqldump;
     
     class BackupCommand extends Command {
         protected function handle() {
             $mysqldump = new Mysqldump(
                 config('database.connections.mysql.database'),
                 config('database.connections.mysql.username'),
                 config('database.connections.mysql.password')
             );
             $mysqldump->start();
             $mysqldump->getDump(function ($chunk) {
                 Storage::disk('backups')->append('backup-'.now().'.sql', $chunk);
             });
         }
     }
     

   • Event Listeners: Trigger backups on job.failed or model.deleted (use streaming).

4. Multi-Database Dumps (v2.11+)

   $connections = [
       ['dsn' => 'mysql:host=localhost;dbname=db1', 'user' => 'user1', 'pass' => 'pass1'],
       ['dsn' => 'mysql:host=localhost;dbname=db2', 'user' => 'user2', 'pass' => 'pass2'],
   ];
   
   foreach ($connections as $conn) {
       $mysqldump = new Mysqldump($conn['dsn'], $conn['user'], $conn['pass']);
       $mysqldump->start();
       $mysqldump->getDump(function ($chunk) use ($conn) {
           file_put_contents("db_{$conn['dsn']}.sql", $chunk, FILE_APPEND);
       });
   }
   

5. Compression (v2.11+)

   use ZipArchive;
   
   $mysqldump = new Mysqldump($dsn, $user, $pass);
   $mysqldump->start();
   $zip = new ZipArchive();
   $zip->open('backup.zip', ZipArchive::CREATE);
   $mysqldump->getDump(function ($chunk) use ($zip) {
       $zip->addFromString('backup.sql', $chunk);
   });
   $zip->close();
   

Integration Tips

• Queue Jobs: Use Laravel Queues with streaming to avoid memory issues:

  BackupJob::dispatch($dsn, $user, $pass, 'backup.sql');
  

  // Inside BackupJob
  $mysqldump = new Mysqldump($this->dsn, $this->user, $this->pass);
  $mysqldump->start();
  $mysqldump->getDump(function ($chunk) {
      Storage::disk('backups')->append($this->filename, $chunk);
  });
  

• Logging: Log backup metadata (size, time) without loading full dump into memory:

  $start = microtime(true);
  $size = 0;
  $mysqldump->getDump(function ($chunk) use (&$size) {
      $size += strlen($chunk);
  });
  Log::info('Backup completed', [
      'size' => $size,
      'time' => microtime(true) - $start,
  ]);
  

• Environment Config: Store credentials in .env:

  DB_BACKUP_USER=backup_user
  DB_BACKUP_PASS=secure_password
  

• Custom Directories: Use Laravel’s storage_path():

  $path = storage_path('app/backups/'.now().'.sql');
  $mysqldump->getDump(function ($chunk) use ($path) {
      file_put_contents($path, $chunk, FILE_APPEND);
  });
  

Gotchas and Tips

Pitfalls

1. Memory Limits (Fixed in v2.11)

   • Old Behavior: Large databases could exhaust memory with getDump().
   • New Behavior: Streaming (getDump() callback) is now the default safe approach.
   • Workaround: Always use streaming for databases >100MB:

     $mysqldump->getDump(function ($chunk) { /* ... */ });
     

2. Character Encoding

   • Default encoding is utf8. For non-UTF8 databases, specify:

     $mysqldump = new Mysqldump($dsn, $user, $pass, ['default-character-set' => 'latin1']);
     

3. Foreign Key Constraints

   • Disabling constraints speeds up imports but may cause errors. Use:

     $mysqldump->setOptions(['skip-add-drop-table', 'disable-keys']);
     

4. Binary Data

   • BLOBs and large TEXT fields may corrupt during dump. Use --skip-extended-insert:

     $mysqldump->setOptions(['skip-extended-insert']);
     

5. Permissions

   • Ensure the PHP process has write permissions for the backup directory:

     chmod -R 775 storage/app/backups
     

Debugging

1. Verbose Output Enable debug mode to log SQL queries:

   $mysqldump->setDebug(true);
   

2. Connection Issues

   • Test the DSN manually:

     mysql --host=localhost --user=user --password --database=dbname
     

   • Use PDO to verify credentials:

     try {
         new PDO($dsn, $user, $pass);
     } catch (PDOException $e) {
         Log::error('Connection failed', ['error' => $e->getMessage()]);
     }
     

3. Dump Validation

   • Validate the dump by importing it into a test database:

     $import = new Mysqldump($testDsn, $user, $pass);
     $import->setOptions(['allow-keywords' => true]);
     $import->import($dump); // Note: Use streaming for large imports
     

Extension Points

1. Custom Commands (v2.11+) Extend Mysqldump to add pre/post-dump hooks with streaming support:

   class CustomMysqldump extends Mysqldump {
       public function start() {
           $this->log('Backup started');
           parent::start();
       }
   
       public function getDump($callback = null) {
           if ($callback) {
               $this->log('Streaming backup');
               parent::getDump($callback);
           } else {
               $this->log('Loading full dump (not recommended for large DBs)');
               return parent::getDump();
           }
       }
   
       private function log($message) {
           Log::info('[Backup] ' . $