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 = "email@example.com"; $mail->Password = "password-for-username"; $mail->AddReplyTo('firstname.lastname@example.org'); $mail->SetFrom('email@example.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 ‘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.