Backing up and restoring MySQL databases

by Guest Contributor | Jul 14, 2004 7:00:00 AM

Tags: Guest Contributor

Takeaway: MySQL users have at their disposal a built-in suite of advanced tools that simplify backing up and restoring databases. Here are some tips for putting them to work.

Database administrators and developers are well aware of the importance of regular data backups: In the event of a disk failure or server crash, a backup can often make the difference between losing a year's worth of work and getting back up to speed in a few hours. Fortunately, if you're using MySQL you have at your disposal a built-in suite of advanced tools that simplify backing up and restoring databases. And if you work on different platforms, these tools also make it easy to move MySQL databases from one platform to another, and to export and import records in different formats.

Copying database files

When it comes to backing up MySQL databases, don't ignore the most obvious solution: making copies of the database files themselves.

Because MySQL uses the same table format on different platforms, it's actually possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you're using the same version of MySQL on both platforms).

So which files should you copy? MySQL stores all its databases in a special data/ directory, which is further divided into subdirectories, one for each database. Tables and table indexes are represented as files, with the file name equal to the table name plus an extension.

The easiest approach is to just copy the entire data/ directory to backup media, and archive it so that you can get it back at any time. You might want to write a script to automate the copy. You can then pop the script into your crontab so it can run on a daily or weekly basis, depending on how frequently your databases get updated.

If you're moving files between Windows and UNIX platforms, there is one thing that can trip you up. UNIX file names are case-sensitive, while Windows names are not. Therefore, mixed-case MySQL table names are likely to get corrupted when moved between Windows and UNIX (though the data in the tables will be fine). If your SQL code uses these mixed-case table names, it might not work correctly until you check and fix the names and/or the code. For best results, always use lowercase table names, so that you don't encounter this problem.

Exporting tables to text files

An alternative to the simple file copy is using the mysqldump tool included in MySQL. It can dump a table, a database, or all databases to a text file.

Using mysqldump is simplicity itself—just run the program with the name of the database to be exported, as below:

$ mysqldump -u root -psecret stocksdb

The mysqldump tool will connect to the MySQL server, log in using the credentials provided, and print out the table structure and data as a series of reproducible SQL commands. Here is a short snippet of the output:

--
-- Dumping data for table 'portfolio'
--

INSERT INTO portfolio VALUES (1,'DHDU',2589,77.56); INSERT INTO portfolio VALUES (2,'YHOO',3200,45.65); INSERT INTO portfolio VALUES (3,'WMT',100,53.29);

Of course, printing it to the console is not really useful. What you need is to write it to a file which can then be backed up and restored. Simply redirect the output to a file by revising the previous command:

$ mysqldump -u root -psecret stocksdb > stocksdb.sql [/code]

To only back up a particular table, follow the database name with the table name. This next command only retrieves the structure and data for the users table in the stocksdb database:

$ mysqldump -u root -psecret stocksdb users > users.sql

And to dump all the databases on the system, use the --all-databases shortcut:

$ mysqldump -u root -psecret --all-databases > backup.sql

Note: If you use the --all-databases option, the SQL output of mysqldump contains a CREATE DATABASE statement to initialize each database. This makes it easier to restore the entire set of databases at once (as you will see on the next page).

If you'd like the backup to only contain the table structure, use the --no-data option:

$ mysqldump -u root -psecret --no-data stocksdb > stocksdb.sql

This is extremely useful if you need to create a new, empty copy of a database—say, for example, when you first install an application and need to initialize a set of tables with no records.

You can do the reverse as well—back up just the data, without backing up the table structure:

$ mysqldump -u root -psecret --no-create-info stocksdb > stocksdb.sql

Now that we have the basics of backing up our data, we need to figure out how to restore from a backup.

Restoring MySQL tables from text backup files

Backing up your databases and tables is only one half of the puzzle: The flip side involves knowing how to restore them in the event of a crash.

Luckily, MySQL makes it simple. Since the output of mysqldump is a series of SQL statements, this output can simply be piped back into a MySQL client to re-create the original database structure and contents.

Assuming you backed up all your databases to a file named backup.sql with the mysqldump --all-databases command, you can restore them with the following:

$ mysql -u root -psecret < backup.sql

If you didn't use the --all-databases option but instead selectively backed up one or more tables or databases, you need to tell MySQL which database to place them in when restoring them. We do that by adding the -D option to the command line above. Here's an example which restores the tables in the file stocksdb.sql to the database named stocks2:

$ mysql -u root -psecret -D stocks2 < stocksdb.sql

Exporting and importing in different formats

SQL isn't the only output format you can save your MySQL databases in. The mysqldump program lets you save records in a variety of different formats, including CSV or with your own custom delimiters. Just add the --fields-terminated-by argument to the mysqldump command line:

$ mysqldump -u root -psecret --no-create-info --tab=/tmp --fields-terminated-by=',' stocksdb

mysqldump will now go to work generating a file in /tmp containing the records from the stocksdb table, with each field separated by a comma. If you'd like a custom line-terminator, you can also use the --lines-terminated-by argument to mark each line with a particular delimiter.

And just as you can export data into different formats, you can also import in different formats. For example, if you had a tab-separated file containing table records, like this:

1 DHDU 2589 77.56
2 YHOO 3200 45.65
3 WMT 100 53.29

you could use the mysqlimport tool to read the data into a MySQL database, like this:

$ mysqlimport -u root -psecret --fields-terminated-by='\t' test /tmp/portfolio.txt

Note that the base name of the source file determines the table into which the data is inserted.

As part of your backup regimen, always keep at least two copies of each backup set (one in a different location). And read the MySQL manual pages for the various data export and import tools to see how best to adapt them to your environment.

Hopefully, you won't ever have to restore a crashed database from your backups. But you'll sleep much easier knowing you have the tools to do it efficiently and with minimal inconvenience to your users if the worst should ever happen.

Print/View all Posts Comments on this article

Making mysqldump more reliable RexWorld | 07/14/04

RE: Backing up and restoring MySQL databases jhargrove12 | 06/12/09