PHP MySQL Backup Script

I realize there are better ways to do this, but here is a simple way to backup your MySQL databases to a gzipped file. You can easily automate this as a cron

<?php
/*
 * Cron Name: backup.cron.php
 * Execution: 0  0  *  *  * (Every night at midnight)
 * Author   : Brandon Wamboldt
 * Version  : 1.2
 * Date     : 05/08/2008
 * Purpose  : Create a FULL MySQL Dump for backup purposes
 */

/*
 * The first thing we need to do is check to see if the
 * backup directory exists. If not, it could indicate
 * an error
 */

if ( !file_exists( '/backups/mysql/') )
{
 //The file doesn't exist, stop the script
 echo 'backup.cron.php encountered an error. No such file or directory "/backups/mysql/"';
 exit();
}

/*
 * Now we have to name the backup file. This is done
 * by adding the backup directory, with the date and
 * the extension
 */

if ( !file_exists( '/backups/mysql/' . date( 'Y-m' ) . '/' ) )
{
 mkdir( '/backups/mysql/' . date( 'Y-m' ) . '/' );
}
$backup = '/backups/mysql/' . date( 'Y-m' ) . '/' . date( 'Y-m-d' ) . '.gz';

/*
 * And now we run mysqldump using the system command,
 * which basically passes the paremeter on to the
 * UNIX Command Line/Bash Shell
 */

system( 'mysqldump -h localhost -u sysdump -p\'your_password\' --all-databases | gzip > $backup' );

/*
 * All done, this is a simple cron afterall
 */

mail( '[email protected]' , '[MySQL Backup Report] Backup Complete' ,"mysql-backup.php ran successfully\n1 full MySQL Backup(s) was created\nBackup at: $backup" );

And to be clear, you need to create a user account called sysdump which needs only select and lock table privledges and replace your_password with the password for that account.

Leave a Reply