MySQL

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.

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.

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:

Lullabot developer Addi Berry put together some great screencasts on how to setup local Drupal test environments on Windows, Mac, and Ubuntu. Even though I have been running Kubuntu as my primary OS at home for over a month now, I hadn't actually set up my test server yet! To make sure I didn't make a misstep, I used Addi's video as the basis for the setup I did on my machine, but command lineified it a bit:

1. Open Konsole
2. Login as root
   • sudo -s