Okay, so a few days ago I read Backing up your MySQL data over at NewsForge, whose articles are usually half-assed decent.

In the first part of the article, author Mayank Sharma does a pretty good job at introducing the reader to the mysqldump database backup program. I actually thought the article was going pretty good until I got to the section labeled “Network dumps”, which states:

For backing up on a Linux machine, you must have the Network File System (NFS) set up. Read Understanding NFS and Implementing NFS for help in getting NFS running. For backing up on a Windows machine, you’ll need Samba installed and configured. The Samba page on troubleshooters.com and the Setting up Samba tutorial should help you with this.

Hello!? Someone’s actually suggesting that you need to use either NFS or SMB in order to do a database backup!? Mayank, what are you smoking?

Fortunately for us, those who have to actually backup MySQL databases, there is a much simpler method. I won’t go into the ways of doing it from a Windows machine, since I have no need nor desire to, but I will show you how to do MySQL database backups from one Linux box to another.

The following examples are taken from a method I use in a production environment on production boxes running instances of MySQL holding production data. In short, I don’t want to lose it. I want it backed up.

In our example, 192.168.0.9 (“alpha”) is the host running the main MySQL instance. 192.168.0.34 (“bravo”) will represent a remote Linux box (which just happens to be running Debian GNU/Linux) that we want to back the data up to.

We first need to configure MySQL on “alpha” to bind to a TCP/IP socket so that we can connect to it from “bravo”. This is accomplished by editing MySQL’s config file, in my case /etc/mysql/my.cnf. Here’s how it comes by default:

# The skip-networking option will no longer be set via debconf menu.

You have to manually change it if you want networking i.e. the server

listening on port 3306. The default is “disable” – for security reasons.

skip-networking

As you can see, skip-networking is disabled by default. Enabling it can present a risk to the database, which we’ll mitigate a bit later in the article by introducing netfilter/iptables into the mix. We’ll uncomment out skip-networking line and restart the MySQL server (via /etc/init.d/mysql restart).

Next, we need to create a special user just for backup purposes and grant permissions for that user to the database. For this purpose of this example, our database username will be “backup” and the database we’re going to remotely backup is called “radius”. The following command needs to be run as a MySQL user with permissions to delegate permissions out to other users, generally root. On “alpha”, the MySQL server, we run:

$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 13354 to server version: 4.0.22_Debian-6-log

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql>

This fires up the “mysql” command-line client with a username of root (-u root) and tells it to prompt us on the command-line for the password (-p). Once authenticated and connected, it’s one simply command to grant those permissions I just mentioned:

mysql> GRANT SELECT ON radius.* TO backup@’bravo.example.com’ IDENTIFIED BY ’secretpassword’; Query OK, 0 rows affected (0.00 sec)

That grants SELECT permissions to any table in the “radius” database to a user named “backup” on the host “bravo.example.com” with a password of ’secretpassword’.

Once that’s in place, we can go over to “bravo” and run our first backup:

$ mysqldump -u backup -h alpha.example.com -psecretpassword radius > radius.sql $

That’s it! It’s done, our first backup. Now this is fine and dandy, but we’re Linux administrators. We don’t want to have to be bothered with manually running backups, do we? Nooooo, we wanna automate this sucker! (Side note: you may have noticed the password on the command-line above. This is generally a bad thing, and I’ll show you how I get around that below.)

Enter cron. cron is our friend. cron allows us to tell the system to run a command at a specific time and it will do so. With a little combination consisting of a bash shell script and a cronjob, we can sleep just a little bit easier.

Here’s the full shell script I use, explanation follows:

#!/bin/sh

MAILTO=jlgaddis YESTERDAY=date --date=yesterday "+%Y-%m-%d" FILENAME=”$YESTERDAY.sql”

umask 077 cd /root/database_backups/radius echo “Dumping MySQL RADIUS database to $FILENAME.” /usr/bin/mysqldump -u backup -h alpha.example.com radius > $FILENAME /usr/bin/bzip2 -9 $FILENAME

Okay, how it works… The first line is the “shebang” line, which tells the operating system what program to use to run our script. In this case, it’s the bash interpreter, located at /bin/sh. The next few lines set up some variables for us. First off, $MAILTO is set to my username. This is where I want the e-mail resulting from the cronjob to go. The next line gets us yesterday’s date, in “yyyy-mm-dd” format. This is what we’ll use for the $FILENAME of the output file. I run my backups right at the stroke of midnight, so I want the file named with the previous day’s date. “umask 077″ defines what the file permissions will be on the newly created file. Next, we descend into a directory that we previously created just for holding backups of this database. We print out a line to let the user know that the database is being backed up. Then, we run the actual backup. mysqldump is called to backup a database named “radius” on the host “alpha.example.com” with a username of “backup”. Output is redirected to $FILENAME, which we previously defined. This allows us to retain daily backups for as long as we wish, with no conflicting filenames. It also allows us to quickly find an old backup if we need to. The last step is simply one of convenience. We use “bzip2″ to compress the dumped file in an effort to save a bit of disk space.

Okay, I promised I’d tell you how to get around passing the password on the command-line. This, too, is pretty simple. When any of the command-line mysql clients are invoked (mysql, mysqladmin, mysqldump, etc.), they’ll look for a file named .my.cnf in the home directory of the currently logged in user. Normally, I wouldn’t recommend storing passwords in a file, but in our case the script is executed under the root account. If somebody has access to view the contents of /root/.my.cnf, either they’re a legitimate user with super-user access or we have much bigger problems. We can add a line like:

password = secretpassword

to /root/.my.cnf. When mysqldump is invoked under the context of the root account, mysqldump will look in /root/.my.cnf for the password. It’ll find it, and use that when it connects to the database.

Oh, and remember I mentioned that it’s also generally a bad idea to have MySQL’s TCP/IP port (port 3306/TCP, by the way) open and available to everyone? Well, it is. With a bit of firewalling, however, we can mitigate that risk drastically. We use iptables (included in every release of the Linux kernel since 2.3.something) to control who can and cannot send traffic to that port. Simply insert the following two rules into your iptables firewall (you already have one, right?) and the problem is gone:

/sbin/iptables -A INPUT -s 192.168.0.34 -p tcp -m tcp –dport 3306 -j ACCEPT /sbin/iptables -A INPUT -s 0/0 -p tcp -m tcp –dport 3306 -j DROP

That’s all there is to it. You’ve just stopped any host from connecting to the MySQL server except for 192.168.0.34!

Okay, that’s it. I got distracted from real work and wrote this up. I may have made a mistake. It’s possible. Contrary to what Lindsey thinks (which is another story in itself), I’m not perfect. I just wanted to expand upon what the original author included in his/her article on NewsForge and provide what I feel is a better method. Feel free to leave comments for me about the article.

Later!