Backing up your MySQL data

If you have ever pulled your hair out in frustration over data loss, no doubt the word “backup” has special meaning in your life. Databases offer a nice way to catalog data, but with the amount of data being trusted into MySQL databases these days, the after-effects of an unwise DROP DATABASE command, an unlucky system crash, or a failed hand-edit of the table structure are catastrophic and can be unrecoverable. That is, unless you have a backup to restore from.

Dump the database

MySQL has a built-in command line utility called mysqldump. This program works with the MySQL server to create platform-independent text files full of the database’s content.

To backup a database called sample_db, issue this command:

# mysqldump -u <username> -p <password> -h <host> sample_db > /usr/backups/mysql/sample_db.2004-12-16

The username, password and host will check if you have access privileges to the database. After checking your authenticity, MySQL directs the output of the mysqldump command to the file/location specified. It is a common practice to ‘tag’ the output file with date. In some cases where the backups are done multiple times in a day, the time information is also tagged.

Note: The -u, -p and -h switches will not be displayed in future examples of the mysqldump command in order to make the commands easier to read and understand. You’ll have to include them while testing any of the commands in this article, though, or else MySQL will complain.

If there are certain tables in a database that have been more recently updated than others, mysqldump allows you to dump only these selected tables:

# mysqldump sample_db articles comments links > /usr/backups/mysql/sample_db.art_comm_lin.2004-12-16

This will back up the articles, comments, and links tables in the database. The output file has been named accordingly. This feature comes in handy when dealing with large databases (e.g. a content management system). There are also certain tables that could be grouped under one section to create smaller, more easily managed backup files. They can be given a section name, as in the following example:

# mysqldump sample_db player score season > /usr/backups/mysql/sample_db.playerinfo.2004-12-16

To save space, you can compress the backup file by piping it through gzip:

# mysqldump sample_db | gzip > /usr/backups/mysql/sample_db.2004-12-16.gz

Network dumps

Keeping the backup on the same system as the one which has the original copy is like flashing your secret weapon to the enemy. One rudimentary way of keeping backups on another box on the network is to copy the backup files manually. But his is the digital age, dude — automate!

For this example, we’re going to assume that the IP address of the main machine is 192.168.1.11, and the IP address of the remote machine is 192.168.1.22.

It is better to create a separate partition (in this example we’re calling it ‘archive’) on the remote machine and mount it. This way even if the operating system is rendered useless on the remote machine, you can install a fresh one without worrying about the backup files.

For backing up on a Linux machine, you must have the Network File System (NFS) setup. 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.

Assuming you have NFS set up, open the /etc/exports file in a text editor and add this string:

/archive 192.168.1.11 (rw, no_root_squash)

We are telling NFS to share the /archive directory with the system that has the database. This directory has read-write permissions and the root user connecting from the database machine will have root privileges on the remote machine. After saving the file, type this:

# exportfs -a -r

This re-exports all directories as specified in the /etc/exports file. See man exportfs for details.

Then restart the NFS service:

/etc/rc.d/init.d/nfs start

This sets up our remote machine. On the machine running MySQL, create a backup_share directory under /mnt with this command:

mkdir /mnt/backup_share

And then mount the remote archive folder onto that directory:

mount u2013o soft 192.168.1.22:/archive /mnt/backup_share

This will mount the folder. Now you can create the backups in the mounted remote machine directory. To backup the sample_db onto the remote machine:

# mysqldump sample_db > /mnt/backup_share/sample_db.2004-12-16

If you’re backing up to a remote Windows machine, create a folder called archive and share it with read and write permissions. Next, create a backup_share directory under /mnt on your database server as in the previous section and mount it:

# mount -t smbfs -o username=<username>, password=<password> //192.168.1.22/archive /mnt/backup_share

Replace <username> and <password> with the information required to access the share. Lastly, create the backup (sample_db) on the mounted share:

# mysqldump sample_db > /mnt/backup_share/sample_db.2004-12-16

Automating the process

Now that we know how to back up databases and tables and how to keep the backups at remote locations, it’s time we let Linux handle it for us. We’ll use the cron daemon for this. cron is a Linux program that runs background tasks at specified intervals. The cron daemon wakes up once per minute and checks crontab to see if it has any tasks, and if so, it executes them.

We’ll write a very simple script to take backups and then schedule it using cron. Open a text editor and copy the script below into a new file:

## If you are backing up on Linux, uncomment the line below
# mount u2013o soft 192.168.1.22:/archive /mnt/backup_share

#un-mount the filesystem
umount /mnt/backup_share

Save the file as sample_db_backup.sh

This is a quick and untidy script. In a proper deployment you would first check whether the remote partition has been mounted or not, whether the backup file has successfully traveled to the remote system or not, and other such things.

Give executable permission to the script:

chmod +x ./sample_db_backup.sh

Now comes the cron part. cron gives you four options to run the script: hourly, daily, weekly and monthly. Depending on which you choose, you’ll need to copy the script to its directory in /etc/cron.timeperiod, where timeperiod is the interval at which you want to perform the backup. I'll copy the script under /etc/cron.daily since I want daily backups. When you're done, restart the cron daemon:

/etc/rc.d/init.d/crond restart

And that’s it — your backup will execute at 4:02 am each day. This is the default time for files under the /etc/daily directory as specified in the /etc/crontab file. See the Wikipedia page on crontab for more information.

Using the dumps

The backup files created by mysqldump are simple text files that have various CREATE TABLE and INSERT statements that restore the database. Here’s a sample of an outfile file:

-- MySQL dump 8.23
--
-- Host: localhost Database: geeklog
---------------------------------------------------------
-- Server version 3.23.58

INSERT INTO gl_commentcodes VALUES (0,'Comments Enabled');
INSERT INTO gl_commentcodes VALUES (-1,'Comments Disabled');

To restore your database from this file, you’ll first need to create an empty database. To populate that database with tables and data, select the backup file you want to use:

mysql -u-u<username> -p<password> -h<hostname> sample_db > /mnt/backup_share/sample_db.2004-12-16

Fill the authentication information above and presto! There’s your database as it was when you last saw it.

Conclusion

mysqldump is a very important tool for a MySQL database administrator. Combined with the benefits of Linux scheduling, various file system compatibility and compression techniques, it is an excellent solution for maintaining data integrity and 100% availability.

Mayank Sharma is a freelance technology writer and FLOSS migration consultant in New Delhi, India.