Backup and Recovery - High Performance MySQL - page 627

such as Percona XtraBackup and MySQL Enterprise Backup also have throttling op-
tions, and you can use pv with the --rate-limit option to limit the throughput of scripts
you write yourself.
Logical or Raw Backups?
There are two major ways to back up MySQL's data: with a logical backup (also called
a “dump”), and by copying the raw files . A logical backup contains the data in a form
that MySQL can interpret either as SQL or as delimited text. 3 The raw files are the files
as they exist on disk.
Each type of backup has advantages and disadvantages.
Logical backups
Logical backups have the following advantages:
• They're normal files you can manipulate and inspect with editors and command-
line tools such as grep and sed . This can be very helpful when restoring data, or
when you just want to inspect the data without restoring.
• They're simple to restore. You can just pipe them into mysql or use mysqlimport .
• You can back up and restore across the network—that is, on a different machine
from the MySQL host.
• They can work for systems such as Amazon RDS, where you have no access to the
underlying filesystem.
• They can be very flexible, because mysqldump —the tool most people prefer to use
to make them—can accept lots of options, such as a WHERE clause to restrict what
rows are backed up.
• They're storage engine-independent. Because you create them by extracting data
from the MySQL server, they abstract away differences in the underlying data
storage. Thus, you can back up from InnoDB tables and restore to MyISAM tables
with very little work. You can't do this with raw file copies.
• They can help avoid data corruption. If your disk drives are failing and you copy
the raw files, you'll get an error and/or make a partial or corrupt backup, and unless
you check the backup, you won't notice it and it'll be unusable later. If the data
MySQL has in memory is not corrupt, you can sometimes get a trustworthy logical
backup when you can't get a good raw file copy.
3. Logical backups produced by mysqldump are not always text files. SQL dumps can contain many different
character sets, and can even include binary data that's not valid character data at all. Lines can be too
long for many editors, too. Still, many such files will contain data a text editor can open and read, especially
if you run mysqldump with the —hex-blob option.