Backing up your MySQL data
Monday December 27, 2004
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
MySQL has a built-in command line utility called
To backup a database called sample_db, issue this command:
MySQL uses the username, password, and host to check whether
access privileges to the database. After checking your
authenticity, MySQL directs the output of the
Note: I won't show the
If there are certain tables in a database that have been more
recently updated than others,
This will back up the
To save space, you can compress the backup file by piping it
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:
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:
This re-exports all directories as specified in the
/etc/exports file. See
Then restart the NFS service:
This sets up our remote machine. On the machine running MySQL, create a backup_share directory under /mnt with this command:
And then mount the remote archive folder onto that directory:
This will mount the folder. Now you can create the backups in
the mounted remote machine directory. To backup the
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:
Replace <username> and <password> with the information required to access the share. Lastly, create the backup (sample_db) on the mounted share:
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
We'll write a very simple script to take backups and then
schedule it using
Save the file as
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:
Now comes the
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
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:
Fill the authentication information above, and presto! There's your database as it was when you last saw it.
Mayank Sharma is a freelance technology writer and FLOSS migration consultant in New Delhi, India.© Copyright 2004 - OSTG, Inc., All Rights Reserved
Questions or problems regarding this web site should be directed to firstname.lastname@example.org.
Copyright © 2008 Art Beckman. All rights reserved.
Last Modified: March 9, 2008