Faffle [faf-uhl]

Following on from yesterday’s adventures, I decided it was time to set up a regular backup of the Drupal database. This time I won’t discuss the history of how I discovered the answer.

After yesterday, I already knew that the database could be exported via the command
mysqldump --user=username --password=password dbname > dump.sql
I also knew, via past experience, that schedules can be set up via the Linux cron utility. My first thought was to actually have this command in the cron file itself, but I’ve been burned by that in the past when something went wrong and it just died quietly. The next idea was to have it in a shell script, but I don’t have all that much experience with that and it would have been a bit of a fiddle.

This support page comment suggested that I put the code in a PHP script. The advantage there is that I don’t need to be ssh’ed in to the server to do a manual backup; I can just access the php page via a browser. That page also includes instructions for how to back up the Drupal files, but I decided that it wasn’t important enough to be scheduled automatically and take up server space. Drupal can be reinstalled fairly easily, but mySQL holds the unreplaceable data.

I’ve amended the php script slightly:

I followed the instructions lower down to compress the backup. This is important as the original file was 3MB whereas the compressed file is less than 200kb. I changed the filename to have date format “Y-m-d” rather than just a two digit day-of-the-month number. I have also commented out the lines to do with the file size. This is because I’m saving the backups outside of the www folder of my web server, so I was getting error messages when PHP wasn’t able to open the file.

Now to add the task to the cron. I’ve done it manually via the shell before and it’s certainly doable, but I would highly recommend the CPanel ‘Cron Jobs’ tool. It’s much more difficult to screw up as you can’t miss a column or get one of the time ranges confused with another. I have set this task to occur every Sunday and Thursday at 3am with the following line:
0 3 * * 0,4 /usr/bin/wget -O --q -t 1 http://URL/sql_backup.php
The /usr/bin/wget -O - -q -t 1 section is to get the shell to load the webpage. That is a capital O, by the way, not a zero.

While you’re at it, add the Drupal cron.php script (as found in this article). I have mine set to to occur every day at 2am.
0 2 * * * /usr/bin/wget -O - -q -t 1 http://URL_to_Drupal/cron.php

So, there we go. Next task will be to automatically send the backup off the server. I’ll probably get it to email it to my gmail. Updates as they come.