Automated Database Backup Script in PHP

Backing up your database is one of the most critical aspects of maintaining a healthy and resilient web application. Automating this task can save you from catastrophic data loss, reduce human error, and simplify your disaster recovery strategy. In this post, we’ll build a robust automated MySQL database backup script in PHP, enhance it with security considerations, and integrate scheduling using cron jobs for true automation.

Requirements

Before we dive into the script, ensure your environment has:

• PHP 7.0+
• Access to a MySQL/MariaDB server
• Shell access (for cron job setup)
• Appropriate read/write permissions on your backup directory

The PHP Backup Script

<?php
// Configuration
$host = 'localhost';
$username = 'db_user';
$password = 'db_password';
$database = 'db_name';
$backupDir = __DIR__ . '/backups/';
$timestamp = date('Ymd_His');
$backupFile = $backupDir . $database . "_backup_" . $timestamp . ".sql.gz";

// Create backup directory if it doesn't exist
if (!file_exists($backupDir)) {
    mkdir($backupDir, 0755, true);
}

// Execute mysqldump
$command = sprintf(
    'mysqldump --host=%s --user=%s --password=%s %s | gzip > %s',
    escapeshellarg($host),
    escapeshellarg($username),
    escapeshellarg($password),
    escapeshellarg($database),
    escapeshellarg($backupFile)
);

exec($command, $output, $result);

if ($result === 0) {
    echo "Backup successful: $backupFile\n";
} else {
    echo "Backup failed with code $result\n";
}
?>

Key Features

• Uses mysqldump for complete database export
• Gzip compression to save space
• Timestamped backups for version tracking
• Handles missing directories and permission errors

Security Tips

1. Avoid hardcoding credentials: Use environment variables or a .env file parsed by vlucas/phpdotenv.

2. Restrict access to backup files: Configure your server to deny HTTP access to the backup directory:

   # .htaccess
   Deny from all
   

3. Encrypt backups (optional): Use openssl or similar tools.
4. Store backups remotely: Use SCP or integrate with cloud storage providers (e.g., S3, Dropbox).

Automating with Cron (Linux/Unix)

1. Open the crontab editor:

crontab -e

2. Add the cron job to run the script daily at 2:00 AM:

0 2 * * * /usr/bin/php /path/to/backup_script.php >> /path/to/logs/backup.log 2>&1

Optional: Auto-Delete Old Backups

Add this snippet to delete backup files older than 30 days:

$files = glob($backupDir . '*.gz');
$days = 30;

foreach ($files as $file) {
    if (filemtime($file) < time() - ($days * 86400)) {
        unlink($file);
    }
}

Going Beyond: Integrating with Monitoring Tools

Send alerts on failure using email or a messaging API:

if ($result !== 0) {
    mail('admin@example.com', 'Database Backup Failed', 'Check the backup logs for more details.');
}

❓ FAQ

Q1: Why use mysqldump instead of a PHP-only solution like PDO?

A: mysqldump is faster and more reliable for large databases and supports full schema + data export.

Q2: How can I back up multiple databases?

A: Loop through an array of database names and run the script per database.

Q3: Can I back up remote databases?

A: Yes, if the user has remote access privileges and the MySQL port is open.

Q4: How do I restore from a backup?

gunzip < db_backup_20250512.sql.gz | mysql -u db_user -p db_name

Q5: What if I get a “command not found” error for mysqldump?

A: Ensure mysqldump is installed and accessible to PHP. Use the full path to the command if needed (find it using which mysqldump).

Conclusion

Automating database backups in PHP is a smart and scalable way to safeguard your application’s data. With a well-structured script and cron integration, you’ll ensure consistent backups without manual oversight. Don’t forget to periodically test your restores—a backup is only useful if it works when needed.