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;
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->Host = "mail.diglabs.com";
$mail->SMTPAuth = true;
$mail->SMTPSecure = "ssl";
$mail->Host = "smtp.gmail.com";
$mail->Port = 465;
$mail->Username = "email@example.com";
$mail->Password = "password-for-username";
$mail->Subject = 'Database Backup';
$mail->AltBody = 'To view the message, please us an HTML compatible email viewer!';
$mail->MsgHTML('Database backup is attached.');
// Delete the unzipped file from your server
// Note: unlink command did not work here.
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 ‘firstname.lastname@example.org’ 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.