Databases Reference
In-Depth Information
tmp/mysql_recover.sock
options to ensure that it is unavailable to existing applications
until we've checked it and brought it back online. This is especially important for logical
backups, which are loaded in pieces.
Restoring Raw Files
Restoring raw files tends to be pretty straightforward—which is another way of saying
there aren't many options. This can be a good or a bad thing, depending on your re-
covery requirements. The usual procedure is simply to copy the files into place.
Whether you need to shut down MySQL depends on the storage engine. MyISAM's
files are generally independent from one another, and simply copying each ta-
ble's
.frm, .MYI
, and
.MYD
files works well, even if the server is running. The server
will find the table as soon as anyone queries it or otherwise makes the server look for
it (for example, by executing
SHOW TABLES
). If the table is open when you copy in these
files, it'll probably cause trouble, so before doing so you should either drop or rename
the table, or use
LOCK TABLES
and
FLUSH TABLES
to close it.
InnoDB is another matter. If you're restoring a traditional InnoDB setup, where all
tables are stored in a single tablespace, you'll have to shut down MySQL, copy or move
the files into place, and then restart. You also need to ensure that InnoDB's transaction
log files match its tablespace files. If the files don't match—for example, if you replace
the tablespace files but not the transaction log files—InnoDB will refuse to start. This
is one reason it's crucial to back up the transaction log along with the data files.
If you're using the InnoDB file-per-table feature (
innodb_file_per_table
), InnoDB
stores the data and indexes for each table in a
.ibd
file, which is like a combination of
MyISAM's
.MYI
and
.MYD
files. You can back up and restore individual tables by
copying these files, and you can do it while the server is running, but it's not as simple
as with MyISAM. The individual files are not independent from InnoDB as a whole.
Each
.ibd
file has internal information that tells InnoDB how the file is related to the
main (shared) tablespace. When you restore such a file, you have to tell InnoDB to
“import” the file.
There are many restrictions on this process, which you can read about in the MySQL
manual section on using per-table tablespaces. The biggest is that you can only restore
a table to the server from which you backed it up. It's not impossible to back up and
restore tables in this configuration, but it's trickier than you might think.
Percona Server and Percona XtraBackup have some enhancements that
lift some of the restrictions on this process, such as the same-server
restriction.
All this complexity means that restoring raw files can be very tedious, and it's easy to
get it wrong. A good rule of thumb is that the harder and more complex your recovery
