Building A Server / MySQL backup

I'm just listening to Chad W @t linuxbasement --dot-- com discuss server issues and establishment.

All I have to say is that, wow, Mr. W. is a very knowledgeable individual when it comes to server administration. In this episode, he's discussing a variety of different subjects including running one's own web server. I miss running my own web server. I think I lamented on this in my last post (which is dated the same day as this post, but was written earlier).

Anyway, I am reminded, by this episode, to make sure to backup my mysql files. I am essentially administering the local Moodle server. (Tech guy, when you read this, I need to get you up to speed! I'm starting to get panic attacks about the upcoming release party of our site!) Luckily enough, there is a great tool which allows you to easily run a backup of Mysql data. Today's tutorial will go step-by-step on how to do this.

Step 1: Log in to your linux Distribution and fire up your package manager. (Debian has mysqladmin right in the repo, so easy enough!) Select and install mysqladmin and all the dependencies. If you need to, you can find some more information about mysqladmin at its sourceforge page.

Step 2: Log in to mysqladmin (Ubuntu has it in Applications --> Programming)

Step 3: Select "Backup" from the menu.

Step 4: Select the databases (I do one at a time) and hit the Right Arrow to move them to the Backup Content Window. You should also name your project at this time in the Project Name box. Make sure you give this a meaningful name.

Step 5: Select Advanced Options -- If your running a Moodle Server you want to select Lock All Tables -- this will prevent anything from being written to the database while you're backing up. This means that there won't be any lost data. As an aside, this also means that no one will be allowed to write while you're processing this backup.

Step 6: Schedule Backup. You want to run this backup one time per week. Why not more? Well, there is no real need. If you want, you can follow some optional steps Listed Later.

Step 7: Set this backup to run at a specific time, I would say 03:00 would be a good time because most students will be asleep. Midnight can be a fairly active time for students, so a little later is usually better.

Step 8: Set the time and location of this backup. Make sure you keep it out of your main data directory unless you do not have any better options.

Step 9: Set the configuration for the backup. Ideally, you want a connection that is specifically given backup privileges without other privileges. This process will be running automatically, so it presents itself as a security vulnerability if you do not have a specific backup mysql user.

(Optional step 10: Daily backup. Start a new backup task. Backup everything the same as before, but you will not be selecting "LOCK Tables," you will be selecting the unstable Normal option. This is more unstable, but quick and dirty. If you're running both backups, you'll have a shot at getting an up-to-date backup or a totally stable, possibly older, backup)

I hope that this gives some insight on how to do MySQL backup the easy way. Let me know if you need any help!