Databases Reference
In-Depth Information
Logical backups have their shortcomings, though:
• The server has to do the work of generating them, so they use more CPU cycles.
• Logical backups can be bigger than the underlying files in some cases.
4
The ASCII
representation of the data isn't always as efficient as the way the storage engine
stores the data. For example, an integer requires 4 bytes to store, but when written
in ASCII, it can require up to 12 characters. You can often compress the files ef-
fectively and get a smaller backup, but this uses more CPU resources. (Logical
backups are typically smaller than raw backups if there are a lot of indexes.)
• Dumping and restoring your data isn't always guaranteed to result in the same
data. Floating-point representation problems, bugs, and so on can cause trouble,
though this is rare.
• Restoring from a logical backup requires MySQL to load and interpret the state-
ments, convert them to the storage format, and rebuild indexes, all of which is very
slow.
The biggest disadvantages are really the cost of dumping the data from MySQL and the
cost of loading data back in via SQL statements. If you use logical backups, it is essential
to test the time required for restoring the data.
The
mysqldump
included with Percona Server can help when you're working with
InnoDB tables, because it formats the output so that it will take advantage of InnoDB's
fast index creation code upon reloading it. Our testing shows that this can reduce the
restore time by two-thirds or more. The more indexes there are, the more beneficial
it is.
Raw backups
Raw backups have the following benefits:
• Raw file backups simply require you to copy the desired files somewhere else for
backup. The raw files don't require any extra work to generate.
• Restoring raw backups can be simpler, depending on the storage engine. For
MyISAM, it can be as easy as just copying the files into their destinations. InnoDB,
however, requires you to stop the server and possibly take other steps as well.
• Raw backups of InnoDB and MyISAM data are very portable across platforms,
operating systems, and MySQL versions. (Logical dumps are, too. We're simply
pointing this out to alleviate any concerns you might have.)
• It can be faster to restore raw backups, because the MySQL server doesn't have to
execute any SQL or build indexes. If you have InnoDB tables that don't fit entirely
in the server's memory, it can be
much
faster to restore raw files—an order of
4. In our experience, logical backups are generally smaller than raw backups, but they aren't always.
