PHP Tip: How to Backup Your Database

November 11, 2009

Here's an easy script to use to backup your database. Copy the contents of this script on your site as DBBackup.php and then you can create a PHP Cron Job to run it from here, once a day!

<?
// Run until complete
set_timeout_length(0);

// DB connection parameters
$host = 'localhost'; //enter YOUR server
$username = 'username'; //enter YOUR username
$password = 'password'; //change to YOUR password
$database = 'database_name'; //update with your DB
$path = '/home/username/backups/'; //Your backup directory (must be writable!)

$filename = $database.'_'.date('Y-m-d_H:i:s').'.gz';
$backup = $path.$filename;

$backup_command = "mysqldump --opt -h $host -u $username -p $password $database | gzip > $backup";

if(exec($backup_command)=='' && file_exists($backup))
{
    echo "Backup successfully executed";
}
else
{
    echo "Error creating backup";
}
?>

There are more sophisticated ways to backup your database, one table at a time such as described in this article, but they haven't been moved to PHP5 yet so I would suggest this for advanced users only.

A great way to use your free cron jobs.