Automated MySQL Backup Script

<?php
/**
 * Auto MySQL Backup Script
 * Run via Cron: 0 0 * * * php /path/to/backup.php
 */

// CONFIGURATION
$keep_backups = 5; // Number of files to keep per DB
$backup_dir   = __DIR__; // Or specific path like '/var/backups/'

$databases = [
    [
        'host' => 'localhost',
        'user' => 'db_user',
        'pass' => 'db_password',
        'dbs'  => ['my_app_db', 'another_db']
    ]
];

foreach ($databases as $config) {
    $host = $config['host'];
    $user = $config['user'];
    $pass = $config['pass'];

    foreach ($config['dbs'] as $db_name) {
        
        // 1. Create Backup
        $date = date('Y-m-d_H-i-s');
        $filename = $backup_dir . "/" . $db_name . "_" . $date . ".sql";
        
        // mysqldump command (add --routines if needed)
        $cmd = sprintf(
            'mysqldump --no-tablespaces -h %s -u %s -p%s %s > %s',
            escapeshellarg($host),
            escapeshellarg($user),
            escapeshellarg($pass),
            escapeshellarg($db_name),
            escapeshellarg($filename)
        );

        exec($cmd, $output, $return_var);

        if ($return_var === 0) {
            echo "[SUCCESS] Backup created: $filename\n";
            
            // 2. Rotate Old Backups
            $files = glob($backup_dir . "/" . $db_name . "_*.sql");
            
            if (count($files) > $keep_backups) {
                // Sort by date (oldest first)
                array_multisort(
                    array_map('filemtime', $files),
                    SORT_NUMERIC,
                    SORT_ASC,
                    $files
                );
                
                // Delete excess files
                $to_delete = array_slice($files, 0, count($files) - $keep_backups);
                foreach ($to_delete as $file) {
                    unlink($file);
                    echo "[CLEANUP] Deleted old file: " . basename($file) . "\n";
                }
            }
        } else {
            echo "[ERROR] Failed to backup $db_name\n";
        }
    }
}
?>