Author: Mayank Sharma
DROP DATABASE command, an unlucky system crash, or a failed hand-edit of the table structure are catastrophic and can be unrecoverable — unless you have a backup to restore from.
MySQL has a built-in command line utility called mysqldump that 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
MySQL uses the username, password, and host to check whether you have access privileges to the database. After checking your authenticity, MySQL directs the output of the mysqldump command to the file and location specified. It is a common practice to ‘tag’ the output file with a date. In some cases where the backups are done multiple times in a day, the time information is also tagged.
Note: I won’t show the -u, -p and -h switches 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 them:
# 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 this 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) 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.
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 command:
# 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 a crontab file 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
## If you are backing up on Windows, uncomment the line below and fill in the username and password
# mount -t smbfs -o username=<username>, password=<password> //192.168.1.22/archive /mnt/backup_share
## At the very end the $(date +%F) command will append the current date information
mysqldump -u <username> -p <password> -h <hostname> sample_db > /mnt/backup_share/sample_db.$(date +%F)
#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, whether the backup file has successfully traveled to the remote system, 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 a.m. 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
--
-- Table structure for table `gl_commentcodes`
--
CREATE TABLE gl_commentcodes (
code tinyint(4) NOT NULL default '0',
name varchar(32) default NULL,
PRIMARY KEY (code)
) TYPE=MyISAM;
--
-- Dumping data for table `gl_commentcodes`
--
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 <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 an important tool for a MySQL database administrator, and 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.
