Automate Your Database Backups

I have a few blogs and other applications that leverage MySQL as a database. I wanted to automate the backup of the associated database data. My specific requirements are:

• Needs to work on Windows Server (my hosting machine).
• My database is MySQL.
• It needed to be automatic. In other words, no human interaction.
• I wanted it to happen twice per day. This minimized the risk of loss to 12 hours
• The backup couldn’t reside on the same machine. It had to be more durable.
• I wanted an alert that the backup occurred.

After some searching I found the following PHP script that would export the database to a file.

$fileName = 'dbName'.date("Y-m-d").'.sql';
$folder = 'c:\temp\';
$fullName = $folder.$fileName;

$cmd = 'mysqldump --user='.DB_USER.' --password='.DB_PASS.' --host='.DB_HOST.' --port='.DB_PORT.' dbName > '.$fullName;

system($cmd);

This bit of script executes the MySql command to backup a database. This could probably be done other ways (e.g. batch file), but this was convenient for testing. I could manually navigate to the PHP file with this script in a browser.

This got exported the SQL data to a file on the local disk (temp folder in the above case). Now I needed to get it off of this system. Each of the database files are fairly small, so I chose to simply email them to my gmail account. If I got into a situation where these were bigger or I would probably offload them either to my DropBox or AWS S3 storage. For now they are small and the email serves as an alert that the backup occurred. Here is a bit of code that emailed the backup file:

$mail = new PHPMailer(true);

$mail->IsSMTP();
$mail->Host = "mail.diglabs.com";
$mail->SMTPAuth = true;
$mail->SMTPSecure = "ssl";
$mail->Host = "smtp.gmail.com";
$mail->Port = 465;
$mail->Username = "username@diglabs.com";
$mail->Password = "password-for-username";
$mail->AddReplyTo('username@diglabs.com');
$mail->SetFrom('username@diglabs.com');
$mail->Subject = 'Database Backup';
$mail->AltBody = 'To view the message, please us an HTML compatible email viewer!';
$mail->MsgHTML('Database backup is attached.');
$mail->AddAttachment($fullName, $fileName);
$mail->Send();

// Delete the unzipped file from your server
//	Note: unlink command did not work here.
system("del $fullName");

echo 'Backup completed, email sent';

In the above code I am using PhpM@iler as an email transport class. In this particular example, I am sending an email to ‘username@diglabs.com’ with the database back up file attached. The domain ‘diglabs.com’ is my domain and I use Google Apps for email. The above code sends email via Google Apps (smtp.gmail.com, port 465, ssl). Finally, I remove the backup file from the local disk.

Hope you find this useful. If you have suggestions or improvements, please leave them in the comments.