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.