Raspberry PI LAMP Server Tuning and Automated DB Backup

One the previous post, I talked about running OpenAtrium on the Raspberry Pi for your home/small office document management system. If you decided to store any important documents, the next best course of action to take will be tuning and backing up your data.

Of course if we want to backup the entire SDCard, we can always do a full image backup as per my article here. However we don’t really want to take down the server that often for backup, plus image backup requires a lot of time to restore if we have to.  So today we are going to talk about these two topics: some tuning and backup.

Apache Tuning

On our last instructions for OpenAtrium we suggested installing the apache mpm prefork. Now we want to reduce the number of forked processes to begin with and reduce the maximum amount of allowed processes. This is again, to reduce the likelihood of the Raspberry Pi running out of RAM while processing Apache requests.

To do this, we have to modify our apache configuration. So open the apache.conf with VI or your favourite editor.

Then adjust the max spare servers and min spare servers configurations. These are the configurations that worked for me so far:

You can always adjust those values as you go further and gauge how much you use your Raspberry Pi server.

MySQL Tuning

The good thing about MySQL is that it comes with an example configuration file for small systems, with RAM under 64MB. Since the Raspberry Pi only have 256MB of RAM including video RAM, we need every bit of RAM possible.

So first, make a backup of your current MySQL configuration:

Then copy the small version of MySQL configuration.

Lastly restart your MySQL server

Mounting a USB Drive for Logs and Backup

I have with me a 4GB USB key that I don’t really use that much. So what better usage than using this for the Raspberry Pi backup.

Of course the Raspberry Pi do support auto USB mounting when restarting the machine or in X. However I found the mount based on /dev/sda a bit of hit and miss. Therefore we will use the device ID instead. To be sure that we have the right uuid, run this command:

You’ll get a list of the drives that are attached to the Raspberry Pi. This is something like the picture below:

But how do we know that we got the right device? There are a lot of UUIDs there, so we crosscheck with the list generated with this command. I’m able to identify the drive because of the size, remember that I just plugged in a 4GB device.

So run:

Then crosscheck your devices:

So our ID is: 0EE8-C3B5

Lastly we setup the auto mount

Create the directory where you want to mount the drive. In this case we’ll make this dduckusb just because the USB is Donald Duck.

Open your fstab file:

At the last line of the file, add the following command to mount the drive. Please note that this command is for FAT32 drive and will make all the contents readable and writeable by everyone. For more information please refer to this Ubuntu documentation.

Now your USB drive will be auto mounted and ready for use. Don’t forget to restart your Raspberry Pi to test it out.

Backup Your Database

Remember we just setup our USB on the /media/dduckusb. In this example we’ll save our db dump on /media/dduckusb/mysqldump.

Our aim for this section is to create a script that would export the complete data of our database and save it as a compressed bz2 file. In addition the script must be able to automatically postfix the archive file with today’s date. Then after we completed the script we will schedule it to run automatically every night.

First let’s create the script that will run the mysqldump command based on our database information. The database is called atrium because this is what I use for my Raspberry Pi Open Atrium home groupware.

So we’ll just create the script in our home directory for simplicity’s sake.

Here is the content of the simple db backup script.

Then lastly we set this script to be executable.

Make it Automatic

Of course we don’t want to run the above backup script manually every day. So what we want to do is get our Raspberry Pi do this for us automatically. Fortunately we have cron and it’s very easy to set it up.

So use the crontab to run it script as administrator.

Then put the following command in to run the backup every 00.01am.

Closing

Nightly auto backup

So there you go, by following the article today, you’ll get:

1. A Raspberry Pi running with optimised memory usage both for Apache and MySQL.
2. Permanently auto mounted external USB storage.
3. A bash script for database backup and a cron setting to auto run the script.

I hope this post help you and I hope you have fun implementing the things we talked about today. This technique surely help me get some peace of mind in regards to my database data.

Categorised in: Raspberry Pi