As I've started to use MySQL databases more and more for varying priority stuff I decided that I should probably be backing up my database everyday. Around the same time I also noticed that I'm still only using 3% of my Gmail account, and what better way to fill it up than with backups?

To do what I wanted, I had to use a mixture of articles I've written in the past. I knew I was going to need to be emailing myself attachments, which required mutt. And a way to backup my databases which was where mysqldump came in. Also, a bit of a crontab job too. Basically what I want is to make a file with my mysqldump, and then have it mailed to me, then delete the file from my server.

I couldn't just put the command into the cron file like we would usually, since I have a few commands I'll need to be running, so it'll be cleaner to put it in a bash script. Bash is just a simple language to proform basic tasks.

Lets get started then...

#!/bin/sh

This line goes at the start of every bash script, to tell it where it should be looking to be compiled and ran. It's pretty standard but it may be in a different place in really strange situations (I'll get to that later).

DATE=`date +%Y%m%d`
FILENAME="$DATE.mysql"

Here I'm setting up my filename. DATE and FILENAME are both variables. It's just convention to put variables in upper case. I'm putting the date (using the date function) into the DATE variable, and then adding that to the .mysql suffix (so it acts as a filename).

mysqldump --all-databases --user=root --password="yourpassword" > /home/youruser/$FILENAME

Then we just do the backup, like we did in the earlier article on mysqldump. Just switch out yourpassword and youruser to the right details. This'll just put the backup in your home directory (though, it won't be there for long).

echo | mutt -a /home/youruser/$FILENAME -s "Scheduled MySQL backup" you@gmail.com

You should recognise sending an attachment from the article before. Usually, you'd be promted to enter the body of the email once you've entered that, but we won't have any interaction with this script so the echo | just puts a nothing into the body. You can change that if you like.

rm /home/youruser/$FILENAME

Then delete the file!

All you have to do is save that to a file somewhere, and then set up your cronjob. Open it by running crontab -e and putting in the data. I want to back mine up at 5am, every morning so my cron line looks like this:

0 5  *   *   0,2,4,6  /home/shamess/backupMySQLtoEmail

That file name is where I've saved my script to. Now I get emailed my database backup every night. I just have to remember to log into gmail and delete a few old ones every now and then.

Just like I predicted, I had my first mess up. On the bright side though, it wasn't because of Debian. It was because I wrote a script and replaced all the entries with a blank line... That's not important though, what is important is that I should have backed up, which I didn't. (I had to go through my entries in Google Reader - thank God for them archiving feeds and not getting them live - and copy and paste them back.)

Anyway, backing up your MySQL databases is easy actually. There's a page about it on the MySQL documentation. I chose the mysqldump option.

From PuTTY (you don't need to be in MySQL, mysqldump is part of the mysql-client package, but executable by itself) I did this:

mysqldump -A -u root -p > /home/shamess/backedup-files/20080312.mysql

The -A means "all databases", if you only want one database you can append the database name at the end (and miss out this switch). If you only want one table, put the table name at the end of that too.

The next switch is me giving the MySQL username. I decided to use root, since it has access to all the databases. The "-p" is saying that I'm going to use a password. I didn't put my password after it, because you can leave it out and it'll prompt you for it after you press enter (thanks Marc for that).

Then there's something new for us to get excited about. The greater than symbol (>) means "write whatever output to this file". I did that because I couldn't find the switch to do it for mysqldump, but I'm sure there is one (comment if you find it).

On a side note: if you look through the site and notice an entry is out of place, or the subject has nothing to do with the post (meaning I've pasted the wrong post) then please comment!

Now we have our web server set up and you can upload your files and things securely, we need to make sure all your PHP files are being complied properly.  You'll probably be using MySQL too, so let's do that at the same time.

Start up PuTTY and connect. Installing PHP is shockingly simple. Type apt-get install php5, you'll have to be logged in as root. PHP4 is about to have its last security updates, so there's no point in getting that now. After that, I don't actually remember having to do anything other than restart Apache (/etc/init.d/apache2/ restart).

There's an article on AboutDebian about some web server stuff. That gives a few more steps in installing PHP, but I don't remember having to do those. That could be because they're using Sarge (I'm using Etch) and PHP4 (we're installing PHP5).

Now to install MySQL. This is simple too, just use apt-get again, but this time you're looking for mysql-server. If you have to, tell it to start at boot up, so that it runs automatically without you having to start it manually.

Then you'll need to install the php5-mysql package to make them work together. Then we need to let PHP know to load that module, to do that we need to edit the php.ini file. You can do that by typing (if you have the same set up):

nano /etc/php5/apache2/php.ini

Then, do a search for:

;extension mysql.so

And simply uncomment that line. Save it, and then restart Apache and everything should be up and running. Go and make a phpinfo() file and it should list MySQL as a module.