Home
Interests
Photos
Favorites

Backing up MySQL
August 2004
by Russell J.T. Dyer

As with all data, MySQL databases should be backed up regularly. Backing up MySQL, however, can be approached in several different ways. One possibility is to shut down MySQL first and then to copy the MySQL data files so that the data betweeen tables is consistent. There are also a few utilities available that will perform live backups of databases so that MySQL service isn't interrupted. In this article, I will explore some methods for backing up and restoring databases.

Quick and Simple

A quick and simple method of backing up MySQL databases is to shut down the MySQL service (mysqld) and then copy the data files and other related files. Shutting down mysqld can be done a few different ways, depending on the type of operating system used by the server. On Unix servers, one of the following entries from the filesystem command line should gracefully close all MySQL daemon processes:

mysql.server stop
service mysqld stop
safe_mysqld stop

The first possibility here is for System-V type operating systems. For some Linux systems (e.g., Red Hat), the second choice can be used. For most Unix systems, though, the last line will work. Incidentally, as of MySQL version 4, the command safe_mysqld has been renamed to mysqld_safe.

Once MySQL has been shut down, it's then a simple matter of copying the MySQL files containing the data and related information. These files can be located in a number of possible places on a server. To determine where the data files are kept, check the my.cnf file, which is usually in the /etc directory. By looking in that file using cat or a similar tool for the value of datadir, the path can be found for where data files are stored. For our purposes here, I'll assume that the data directory is /var/mysql. With that bit of information, you can copy all of the data files to a backup directory by typing the following at the command line:

cd /tmp/backup
safe_mysqld stop
cp -r /var/mysql .
safe_mysqld start
tar cfz mysql.tar.gz mysql
rm -rf mysql

This first line changes to a directory that you created, /tmp/backup. With the next line, mysqld is stopped. Then copy all of the MySQL data files (including subdirectories because of the -r option) into the current directory.

There will be a directory for each database and three files for each table within a database's directory. The directories will be named the same as their respective databases. The naming scheme for table files is the table name for each, with a different extension for each type of file: .frm for the table definitions, .MYD for the data, and .MYI for the index files. The fourth line, of course, starts MySQL again. The next command entry uses tar to create an archive file and to compress it. The last line removes the copies of the data files and directories. The -f option is given to force deletion without having to confirm each.

You could create the archive file using tar from the original files while MySQL is down and thereby skip the copy and deletion process. However, you want to get MySQL restarted as soon as possible. To automate and speed this backup method, these command lines could be placed in a shell script to be run either manually or with cron. It might also be useful occasionally to back up MySQL and its configuration file (i.e., /etc/my.cnf), especially if you've modified it.

Restoring a database that has been copied as described above is only a matter of unzipping and untarring the archive file. Then the extracted database that is to be restored needs to be copied to the MySQL data directory. If you only want to restore one table, you could copy just the desired table files into the appropriate database directory:

cd /tmp/backup
tar xfz mysql.tar.gz
safe_mysqld stop
cp -f mysql/db1/table1* /var/mysql/db1
safe_mysqld start

The backup process outlined in this section, especially if it's executed through a shell script, will be very brief. Databases of only a few megabytes in size can be backed up, while MySQL is offline, within a minute or so. But if your database is continuously and heavily used and you don't want to shut down for even a minute, then another method must be used.

Using mysqldump

If you can't afford to shut down MySQL services to make a backup and your databases are not excessively large, a good alternative is mysqldump. This utility (which comes with MySQL) reads each database and table and builds a data text file on the filesystem. This data text file will contain SQL statements and data. If you were to open a data file generated by mysqldump, you would see CREATE TABLE statements and a multitude of INSERT statements, one for each row of data. To export all of the databases in MySQL using mysqldump, enter the following from the filesystem command line:

mysqldump -u root -p -x -e -A > /tmp/backup/dbs.sql

The first set of options here (-u root -p) tells MySQL that this utility is to be executed by the user root and that the user needs to be prompted for a password, which will have to be typed on the next line when asked. If the dump is to be executed by cron by way of a shell script, this option can be changed to -pmypwd, where mypwd is the password; there's no space between the -p and the password. The -x option has MySQL lock all of the tables before performing the backup. The lock won't be released until the process is finished.

To bundle INSERT statements together for each table, add the -e option. This extended insert option will cause the dump file to be smaller and allow any possible future restores to be executed faster. The -A option specifies that all databases are to be exported. Finally, the greater-than sign is a shell redirect of the standard output (STDOUT) to the path and file named after it. By backing up all of the databases at once, the dump results in one large file. However, it might be more useful to stagger backups based on databases (making for possibly several smaller files) to back up certain databases during slower traffic times. To export only one database, something like the following could be entered:

mysqldump -u root -p -x -e -B db1 > /tmp/backup/db1.sql

The only significant difference in this line is that the -A option has been replaced with -B, and the database to be exported has been given. To export multiple databases, just enter them after the -B option, separated by spaces (e.g., -B db1 db2).

For even larger databases, you may want to back up MySQL based on tables. You could back up an entire database weekly and then only back up individual tables (for which data changes often) on a daily basis. To back up just one table, the following could be entered from the command line:

mysqldump -u root -p -x -e db1 table1 > /tmp/backup/db1_table1.sql

Notice that the -B option has been removed and not replaced. The utility is clever enough to recognize that the first name given is a database and the second name is a table name and not another database. To back up multiple tables from a database, just list them after the database name, separated by spaces (e.g., db1 table1 table2).

Restoring a mysqldump file is just a matter of having the mysql client execute all of the SQL statements that the file contains. Something like the following could be entered:

mysql -u root -pmypwd < /tmp/backup/db1_table1.sql

Again, I'm not using mysqldump here, which is only for dumping, not restoring. Instead, I'm using the mysql client that will read the dump file's content in order to batch execute the SQL statements that it contains. Notice that the redirect for STDOUT is not used here, instead I used the redirect for the standard input (STDIN); the less-than sign is used since the dump file is an input source. Also notice that I didn't specify the database. That's given within the dump file. Of course, you may want to stop MySQL before doing a restore and then start it again when done.

Using mysqlhotcopy

Another tool for making backups on an active server is mysqlhotcopy. It's particularly useful with ISAM and MyISAM tables. As mysqlhotcopy is written in Perl, it requires Perl and the Perl DBI module be installed on the server. This utility creates a copy of each table file as in the simple copy method discussed previously, but without shutting down the MySQL service. This results in a separate directory for each database and three files for each table the same as if you made a filesystem copy. It places a read lock on all of the tables in the database while copying them. To restore a database or a table, you must temporarily shut down MySQL and copy the desired table files as shown previously in the simple example. To back up a database using this utility, enter something like the following:

mysqlhotcopy -u root -p mypwd db1 /tmp/backup

There are only a couple of differences from mysqldump here. There is a space after -p and the password; and the shell redirect (the greater-than sign) isn't used. For very large databases, a backup can sometimes be processed faster by not fully exporting the indexes for tables. The addition of the --noindices option will back up only the minimal information so that the index may quickly be reconstructed later if an indexed table is to be restored. To do this, enter something like the following from the command line:

cd /tmp/backup/mysql
safe_mysqld stop
cp -f db1/table1* /var/mysql/db1
safe_mysqld start
myisamchk -r /var/mysql/db1/table1

After MySQL is stopped, the files for the table to be restored are copied to the MySQL data directory. This includes a minimal .MYI file. Next, MySQL is started again. Using the utility myisamchk, the index for the restored table is rebuilt.

Using mysqlsnapshot

The utility mysqlsnapshot is used to back up all of the databases in MySQL without having to shut down the service. There aren't any options to be able to export only certain databases or tables. The results of mysqlsnapshot is saved to a tar file. To run this utility, enter something like the following from the command line:

mysqlsnapshot -u root -p mypwd --split -n -s /tmp/backup/mysql

The --split option instructs the utility to split the output by database and to make a separate tar file for each. Without it, one tar file would be created for all of the databases. The -n option allows the backup to be performed even if the server is not set up with binary logging enabled. The -s option with the file path that follows it specifies the directory to which it is to send the output.

To restore a file created by mysqlsnapshot, follow the steps described in the simple example in the first section of this article. Unzip and untar the database to be restored, then copy the backup database files to MySQL's data directory.

The utility mysqlsnapshot does not come with MySQL. However, it may be downloaded for free from the web site of the tool's developer, Jeremy Zawodny.

Conclusion

Backing up data and table definitions in MySQL is fairly simple. It does require some thought to minimize the amount of interference of service to the users of your databases. I recommend experiementing occasionally with the backup files to ensure that data is being backed up successfully and to ensure that you know how to restore the data quickly. You may also want to write up some documentation of how your particular system backups are made and how they are to be restored, which can be useful for colleagues when you're away from the office. It can also be useful for you when there's a system failure, you can restore data more quickly and easily if you don't have to try to remember all the steps.

Russell Dyer is a Perl programmer, a MySQL developer, and a Web designer living and working on a consulting basis in New Orleans. He is also an adjunct instructor at a technical college where he teaches Linux and other open source software. He can be reached at russell@dyerhouse.com.

Copyright 2004 CMP Media LLC

Questions or problems regarding this web site should be directed to abeckman@outdoorssite.com.

Copyright 2008 Art Beckman. All rights reserved.

Last Modified: March 9, 2008