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.

I generally use cat when I want to read from a file, but I just found less, which outputs a file a page at a time.

One of my purposes for getting a VPS was so that I could run BitTorrent 24 hours a day, without having to leave my laptop on to do it. I could just run BitTorrent on my server and that could do all the work for me. After all, I may only have 2Gb hard drive at the moment, but I do have 1000Gb of bandwidth which I'm hardly putting a dent in. Not to mention, at home I have a one meg connection, whereas my server has 100Mbs.

It was going to work something like this: I'd start bittorrent up on the server to start downloading a file. Once it downloads it, I could get the file through SCP or FTP which would probably be quicker than me doing the torrenting myself. It's also helpful when you're downloading a rare file which only a few seeders who aren't uploading when you're machine is on; your server is always on, so it's going to be able to download 100% of the time there's an uploader.

Let's go through the steps to do that, from the beginning.

I decided to go ahead and get BitTornado, which you might have used as a client before. Get it using sudo apt-get install bittornado. Now you've got that, go to the directory you want to download the torrent to. I made a /downloads/ folder in my home directory.

Let's find the .torrent file that we want to download from. In theory, BitTornado will work with just the URL to the .torrent file, but you won't find many trackers that will give you that, instead they'll give you a URL which looks something like http://www.mininova.org/get/1218398. What you can do with that though is wget the torrent file.

Then do this:

btdownloadercursers --max_upload_rate 70 1218398

This sets the maximum upload rate of 70kb/s. Yes, you have a fast server, but you don't want to get in trouble with your providers for hogging all their pipes, so make sure you set it. The 1218398 is the torrent file that's on our machine now, since we just downloaded it.

Now you'll get an application open giving you stats and stuff about your download. You can't send this to the background using ^Z, else it'll stop it. You can quit using ^C or just Q.

The downside of this though, is that my connection is so fast, and my memory so low (64Mb, with 128 swap) that downloading at 600Kb/s crashed my server... three times. So, don't bother trying to use BT unless you have a fairly decent machine.

Sorry I've not posted much in the last few days; I have a few posts lined up though :)

To keep you happy though, here's a cheat sheet for Unix commands. Well, it's a bit huge, more of a cheat novel.

I love my soya burgers, but I don't like them burnt. More than once I've put them on the grill, came to check what's happening on MSN, and totally forgotten about them. Sensible people have egg timers for this type of thing, but we have Debian servers and should frown upon such out dated methods.

Instead, we can send an email to ourselves when we need to turn our burgers over.

We can do that using the at and mail commands. at lets us tell Debian to do something at a particular period of time. We want to have to turn our burgers over in eight minutes, so tell it that.

at now +8 minutes

For some reason, my server lags for a second, but then you go into the at program interface¹. It's here we say what we want to do.

echo "Time to turn your burgers over!" | mail -s "Just a reminder!" shamess@gmail.com

This runs mail, where -s is the subject, and the to address goes at the end. Then, mail expects you to type the body of your message, so we just echo it here (more on pipes later). This sends me an email with "Just a reminder!" as the subject, and "Time to turn..." as the body.

There you go! No more burnt food! Be inventive with other reminders you could give for yourself.

¹When you move away from the standard interface where your CLI all starts with your user@host:/etc$ into the interface where you have mysql: or at:, does that have a name?

After we talked about backing up your MySQL databases last time, I realised it would be much easier if I could just make my server back it up its self, every day. That way I don't have to think about it, and if I lose anything I can just revert to the last day's archive!

To do that, we use a thing call cron. This might have been called "cronjob" or something similar on your shared host, if you ever used it. If you did, then most of this should be obvious for you, since it's exactly the same (at least when I was using Plesk), just in a different way.

If you've ever looked around your /etc/ directory, you'd have noticed the /etc/cron.whatever directories. The files in there are run according to their directory, so the scripts in cron.daily are run once a day.

Each user has their own crontab file, which is where you put all the stuff you want to run. You can see your file with the command crontab -l (that's a lower case L). Chances are you won't have one just yet.

When you do though, it'll be laid out in six, space seperated fields, like this:

*     *     *     *     *  Command to be executed
-     -     -     -     -
|     |     |     |     |
|     |     |     |     +----- Day of week (0-7)
|     |     |     +------- Month (1 - 12)
|     |     +--------- Day of month (1 - 31)
|     +----------- Hour (0 - 23)
+------------- Min (0 - 59)

(Stolen from Debian Admin site, you should check out that article too.)

For day of the week, both 0 and 7 are Sunday, however some versions of Unix don't support 7.

To edit your crontab file, use crontab -e, which will open your crontab file in nano.

The "command to be executed" is the path to the command's binary (sorry if that makes little sense...), which you can find out by using the which command. For instance, if you want to run ls, you'd have to do which ls to find where it's kept. If you wanted to run ls, for some strang reason, you could do:

0   *   *   *   *  /bin/ls

Then, you'd get the output emailed to you, at your local user.

But, that's not what we want to do. We want to do a backup, each night. So, we'd add this:

10   23   *   *   *  "mysqldump -A -u root -p password > /home/shamess/backedup-files/$(date +%s).mysql"

That'll back all the databases up at 10 past 11 each night. The $(date +%s) part will output a Unix timestamp.

You'll probably want to delete really old back ups, and archive the new ones. You can do that by running a bash script, instead of a command, but we'll get to that later.

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!

As an example yesterday, I told you how to download the WordPress installation stuff. So, I might as well tell you how to untar (or, unzip?) it! It's simple really.

Go to the directory you want to unzip it to...

cd /var/www/wordpress/

And then unzip it...

tar xfz /home/shamess/downloads/latest.tar.gz

Here's what those options do...

Some people like to add v too, which shows you a list of the files as it unzips them.

If you're looking to download something from the internet, there's no need to download it to your local machine first, then upload it (which is what I would have done before I found out about this). You can use wget. If I wanted to get WordPress, I'd do the following command:

wget http://wordpress.org/latest.tar.gz

That will download WordPress to your current folder. If you want it somewhere else, you can use the -P switch, like so.

wget -P "/home/shamess/downloads/" http://wordpress.org/latest.tar.gz

You can also log into an FTP server, or any HTTP directories that need authentication, using --user and –password. Which is what I should have done when I was transferring files from my old server to this one.

There are a lot of switches to a lot of commands and sometimes they're just too long to remember, or you're just too lazy to type them. In cases like this, you can use aliases. They're basically shorthand for commands you use a lot.

For instance, to log onto MySQL I have to use the command:

mysql --user=dangermouse --password=letmeinplox

It gets a little boring typing that over and over again. Also, PuTTY doesn't hide the password as you're typing it which can be a little dangerous. An alias fixes both of those problems. Just do:

alias my_mysql='mysql --user=dangermouse --password=letmeinplox'

"alias" is the command, "my_mysql" is the new alias/command I'm creating, and the rest is what I want it to be. So, now I can type "my_mysql" and it'll log me into my MySQL server.

If you pick a name for your alias that already exists, the original one will just got overridden.

Unfortunately, you'll have to create this alias every time you log onto your server. You can fix that by adding it to your .bashrc file, which is in your home directory, (so, mine is /home/shamess/.bashrc). That file should already have a few aliases in it, so take a look around it for examples.

I haven't really mentioned anything about nano, except for the short paragraph I wrote about it in that command quick sheet I wrote, which is odd since it's the default editor for Debian; you'd think I'd wanna learn how to use it effectively.

nano is actually really quite easy for a fairly basic piece of software. You can just type normally from in the app, and it'll be stored as text. The most common functions are in the two bars at the bottom of your window. In case you didn't know, the caret (^) means "press control key and...", so ^C would be control and C.

Why they can't use Windows terminology though, is beyond me.

You can see a lot of other commands by doing ^G for the help.

There's also a few cool things that you can do when starting nano, with switches.

You can see the command manual at the official nano site.

We added a user before to use for ourselves, but I never really went into how to change your password or delete a user, or a lot of other things you can do.

To change your full username, use chfn -f newusername. You can change the other information you added too, just do a manual lookup to get them (man chfn).

You can change your password by using the passwd command. You'll have to put your old password in first, unless you're root, and then your new one.

You can also delete a user, using deluser usertodelete. Obviously, don't go deleting your own user.

If you're using mail functions on PHP after just installing it, you won't get any error message, even though the mail won't get sent yet. Don't worry, we'll fix that now.

Debian comes with Exim4 which is a mail server with a bunch of features. By default it's set to only send emails locally (to other users on your system). If you tried to send any emails to external users at the moment, you'll see something like "** www-data@debian R=nonlocal: Mailing to remote domains not supported" in your error logs.

We just need to reconfigure your Exim4 options, which you can do by typing the command "dpkg-reconfigure exim4-config".  Then you'll get a screen, which you'll have to answer a few options. A lot of them are easy but they can be a bit off putting.

I don't think there's much reason to split configuration files (comment if I'm wrong). Your system mail name should be your URL, including the subdomain if that's where you've pointed your VPS. For instance, mine is "trinity.allroundnews.co.uk", so all email addresses will be 'user@trinity.allroundnews.co.uk'. Let the 'listener daemon' listen to all IPs, by just leaving it blank. Say no to 'Dial-on-Demand'. The 'Root and postmaster mail recipient' is basically the user that you want mail to 'root@trinity.allroundnews.co.uk' to be redirected to (since you shouldn't stay logged in as root). So for me, that's just 'shamess'.

If you've tried to send any mail so far, you'll want to remove it from the queue (sorry if it was important). Do that by getting up the mail queue (using the mailq command). Then you can remove mail by using the command Exim -Mrm <message id>. You can put as many message ID's in the list as you like, space separated. The message ID is the weird string that looks something like ’1JOJBI-0000ag-IH'. Yup, you have to type that.

I'll talk about actually reading mail later, but at least now you can send mail with PHP!

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.

I want to learn about file permissions. Yes, you heard me. I want to learn. I don't actually know much about them yet. Why am I doing an article about them if I don't know what they do, you might ask. And I'd have to reply, "good question. I don't really have an appropriate answer." Onwards?

Unix is a multiuser based operation system, like I mentioned before, so there has to be a way that it controls which files a user can look at. That's done with permissions. At the moment, your /var/www/ is going to be owned by www-user which is a user that Apache made when you installed it. Unless you log in as root, or that user you won't be able to edit or add files in there at the moment. Lets learn how to change that.

First, head to your into your /var/ directory, and then show the files with their data, (if you don't know how to do that, then you should read through a few of the posts I've tagged for commands.)

drwxr-xr-x  4 root www-user 1.0K Feb 12 22:38 www

You'll have something like that for your /www/ directory. The "drwxrwxr-x" is what we're interested in, since that tells us the permissions. The first letter tells us that it's a directory, then the rest are in blocks of three; the first block is for users, then groups, then others.

r means that they can read from that file or directory, the w means they can write to it, and the x is to execute it.

What I did was "chgrp -R shamess /var/www/" and that changed the group so that I was in it (the -R­ switch was to make all the files and directories inside it change too). Then I needed to give the group permission to edit the files; chmod g+w /var/www.

And that was it. Now you can edit files in your /www/ directory without having to be root.

There's a more detailed article on permission types and how they work over at FreeOS; Understanding Linux file permissions.