close
Automatically backing up your MySQL database is critical for production websites and applications. Manual backups are error-prone and often forgotten, while automated backups ensure your data is safe even if something goes wrong.
In this tutorial, you’ll learn how to auto backup a MySQL database using PHP and Cron (Linux/macOS) or Task Scheduler (Windows). We’ll use a CLI-based PHP script that creates compressed MySQL backups, stores them in a secure folder, and automatically deletes old backups based on a retention policy.
Create a simple folder structure like below:
mysql-auto-backup/
│── backup.php
│── config.php
└── backups/
– backup.php: The main PHP script that performs the backup.
– config.php: Configuration file for database credentials and backup settings.
– backups/: Directory where backups will be stored.
Make sure the backups/ directory is writable by the user running the PHP script.
Create a config.php file to store database credentials and backup settings in a clean and reusable way.
Key Configuration Options:
<?php
return [
'db_host' => '127.0.0.1',
'db_user' => 'root',
'db_pass' => '',
'db_name' => 'your_database',
// Directory where backups will be stored
'backup_dir' => __DIR__ . '/backups',
// Automatically delete backups older than X days
'retention_days' => 7,
// Path to mysqldump binary (use 'mysqldump' if it's on PATH or set full path on Windows)
'mysqldump_path' => 'mysqldump',
];
?>
Create the main backup script backup.php that will perform the database backup using the configuration from config.php. This script will:
mysqldump command to create a database dump<?php
// Load configuration
$config = require __DIR__ . '/config.php';
// Ensure backup directory exists
$backupDir = $config['backup_dir'];
if (!is_dir($backupDir)) {
if (!mkdir($backupDir, 0755, true)) {
fwrite(STDERR, "Failed to create backup directory: $backupDir\n");
exit(2);
}
}
// Prepare filename
$dbName = $config['db_name'];
$date = date('Y-m-d_H-i-s');
$filename = sprintf('%s/%s_%s.sql.gz', rtrim($backupDir, '/\\'), $dbName, $date);
// Prepare mysqldump command
$mysqldump = $config['mysqldump_path'];
// Build command without exposing password in process list on *nix where possible.
$cmdParts = [];
$cmdParts[] = escapeshellcmd($mysqldump);
$cmdParts[] = '--host=' . escapeshellarg($config['db_host']);
$cmdParts[] = '--user=' . escapeshellarg($config['db_user']);
// Use --password=... (mysqldump requires it) — on some systems consider using ~/.my.cnf for safety.
$cmdParts[] = '--password=' . escapeshellarg($config['db_pass']);
$cmdParts[] = '--single-transaction';
$cmdParts[] = '--quick';
$cmdParts[] = escapeshellarg($dbName);
$cmd = implode(' ', $cmdParts);
$descriptors = [
1 => ['pipe', 'w'], // stdout
2 => ['pipe', 'w'], // stderr
];
// Execute mysqldump process
$process = @proc_open($cmd, $descriptors, $pipes);
if (!is_resource($process)) {
fwrite(STDERR, "Failed to start mysqldump process\n");
exit(3);
}
$out = $pipes[1];
$err = $pipes[2];
// Open gzip file for writing
$gz = @gzopen($filename, 'w9');
if ($gz === false) {
fclose($out);
fclose($err);
proc_close($process);
fwrite(STDERR, "Failed to open output file for writing: $filename\n");
exit(4);
}
// Stream dump output into gz file
while (!feof($out)) {
$chunk = fread($out, 8192);
if ($chunk === false) break;
if ($chunk !== '') gzwrite($gz, $chunk);
}
// Capture any stderr output
$stderrOutput = stream_get_contents($err);
fclose($out);
fclose($err);
// Close process and gzip file
$exitCode = proc_close($process);
gzclose($gz);
// Check for errors
if ($exitCode !== 0) {
@unlink($filename);
fwrite(STDERR, "mysqldump failed (code $exitCode): $stderrOutput\n");
exit($exitCode);
}
echo "Backup created: $filename\n";
// Rotate old backups
$retention = (int)$config['retention_days'];
if ($retention > 0) {
$pattern = rtrim($backupDir, '/\\') . '/' . $dbName . '_*.sql.gz';
foreach (glob($pattern) as $file) {
if (filemtime($file) < time() - ($retention * 86400)) {
@unlink($file);
}
}
}
exit(0);
?>
Before scheduling the backup script, it’s important to test it manually to ensure it works as expected.
backup.php is located.php backup.php
If everything is configured correctly, you’ll see:
Backup created: /path/to/your/project/backups/your_database_YYYY-MM-DD_HH-MM-SS.sql.gz
backups/ directory to verify that a new backup file has been created.Now that you have the backup script ready, you need to schedule it to run automatically at regular intervals.
crontab -e
0 2 * * * /usr/bin/php /path/to/your/project/backup.php
Make sure to replace /usr/bin/php with the path to your PHP executable and /path/to/your/project/backup.php with the actual path to your script.
Cron Timing Format:
* * * * * │ │ │ │ │ │ │ │ │ └── Day of week │ │ │ └──── Month │ │ └────── Day of month │ └──────── Hour └────────── Minute
C:\path\to\php.exe).backup.php script (e.g., C:\path\to\your\project\backup.php)..htaccessAutomating MySQL backups using PHP and Cron is a simple yet powerful solution for protecting your application data. With compression, retention cleanup, and proper scheduling, this script is production-ready and easy to maintain.
You can safely run this script on shared hosting, VPS, or dedicated servers with minimal configuration.
Here are some additional enhancements you might consider:
Happy coding and stay backed up! 🚀
Looking for expert assistance to implement or extend this script’s functionality? Submit a Service Request
💰 Budget-friendly • 🌍 Global clients • 🚀 Production-ready solutions
