ejhildreth'sblog

In a previous post, I walked the user through how to setup a cron job to automatically create a nightly mySQL backup (see http://ejhildreth.com/content/setting-cron-mysql-backups). This is great but has one huge flaw: The database backups keep piling up unless I manually remove them. Since scripting makes the computer work for me, I fired up VIM and created some new shell scripts.

Earlier today, I was giving a former co-worker (@mlsamuelson) a hard time on Twitter about the contents of the whiteboard that he left behind. Like the true good friend he is, he immediately gave me a hard time back! In response to his counterstrike, I took matters in my own hands and took the whiteboard hostage with my weapon of choice, a copy of today's newspaper to prove what day it was, and sent him the photo:

Objective:

• Create a limited mySQL user account (e.g. Read-Only) to use in the batch file to follow the The Principle of Least Privilege methodology
• Place multiple mySQL database backups in a specified folder
• Add a timestamp to the mySQL dump file to easily distinguish between versions
• Have multiple mySQL databases get backed up at a specified time

One of the "best" best practices is to backup any important data that you do not want to loose. This is especially important when you are storing a bunch of other people's data in a database. Problem is, if it is something that we have to think about, it just isn't going to happen. Luckily, all modern operating systems have the ability to run user generated scripts to help automate this task.

For a recent web design that I was creating, I wanted to spice up the look and feel a little bit by having a silhouette background image. The site that I was working on is an information portal about working and jobs, so I wanted to have something that conveyed team work. Also, to speed up development time, I wanted to convert an existing photo into my silhouette. Enter Flickr Creative Commons search (part of their advanced search http://www.flickr.com/search/advanced/).

Why Creative Commons? Simply enough, because the copyright holder expressly sets the permission for what you can do with their work beforehand. I typically look for content that can be added to or enhanced by me (Creative Commons Remix) and content that can be used Commercially. After a little bit of searching, I found this photo:

Accessed from http://flickr.com/photos/a4gpa/2312527906 - Flickr Username: a4gpa.
Image is licensed under the Attribution-Share Alike 2.0 Generic license.

Problem: Having a Drupal site automatically run the cron.php script and automatically backup a mySQL database at regular intervals on a Slicehost slice that is running Ubuntu.

Solution: Shell Scripting and Crontab

Turns out, this wasn't so much of a problem after all and it has a really straightforward solution. Since I am using Ubuntu, it already has cron functionality built into it. To tap into this functionality, it is as easy as editing a text file and telling the system to tun the tasks you want (including shell scripts).

MySQL cron backup step-by-step:

FYI: I just reran the doxpara.com test using CableOne's DNS servers, and it appears to finally been updated. Thank you CableOne for being on top of this so I didn't have to run all over the valley this weekend updating routers to OpenDNS!