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.

Comments
  1. Shailen Sukul
  2. storage
  3. Altaf Hussain
  4. Mohammad

Leave a Reply

Your email address will not be published. Required fields are marked *

*