A simple PHP script to periodically backup mysql database

Periodically backing up database is crucial to data safety for DBAs or webmasters. But manually doing this is often boring and easily forgotten.

Below script works on Linux system to backup mysql database. With an easy-to-use webcron, you can setup periodical backup jobs which will happen automatically.

Steps to take:

1. Create an empty PHP file named with whatever you like (e.g. example.php).

2. Copy the following code into the PHP file.

<?php

/* 
 * This script only works on linux.
 * It keeps only 31 backups of past 31 days, and backups of each 1st day of past months.
 */

define('DB_HOST', 'localhost');
define('DB_NAME', 'your_database_name');
define('DB_USER', 'your_database_username');
define('DB_PASSWORD', 'your_username_password');
define('BACKUP_SAVE_TO', 'backup_storage_path'); // without trailing slash

$time = time();
$day = date('j', $time);
if ($day == 1) {
    $date = date('Y-m-d', $time);
} else {
    $date = $day;
}

$backupFile = BACKUP_SAVE_TO . '/' . DB_NAME . '_' . $date . '.gz';
if (file_exists($backupFile)) {
    unlink($backupFile);
}
$command = 'mysqldump --opt -h ' . DB_HOST . ' -u ' . DB_USER . ' -p\'' . DB_PASSWORD . '\' ' . DB_NAME . ' | gzip > ' . $backupFile;
system($command);

?>

3. Change the parameters (DB_HOST, DB_NAME, DB_USER, DB_PASSWORD, BACKUP_SAVE_TO) in the script to reflect the settings of your environment. You would like to keep the database backups from accessing by web visitor, so you may need to set a private path to BACKUP_SAVE_TO or put a .htaccess file with following content

Order deny,allow
Deny from all

into the backup storage folder.

4. Put the PHP file on your server (If you want to use a webcron to trigger the auto backup, you need to make sure the file is accessible via HTTP). If your backup storage folder is not yet existent, please create it. Your folder should be made writable by PHP.

5. You can both use Linux's Cron or a webcron to trigger to script. If you need a webcron, just go to EasyCron.com and register an account to proceed.

Advantages:

• Great performance. Because it calls MySQL's native command to perform the backup.
• Saves your disk space as backups are gzipped.
• Uses a way to keep both last 30 days' backups and each 1st day's backup of past months. It balances the data importance (through earlier to later time) and disk space cost.

Requirements:

• Linux (Though the script can be amend to work on other platforms).
• PHP + MySQL