32.2. Binary Versus Textual
When you back up databases, you have a choice of two backup formats:
A binary backup is a copy of the files in which database contents are stored. Copying these files
the databases in exactly the same format in which MySQL itself stores them on disk. Restoration involves copying the files back to their original locations. Techniques for making binary backups include file copy commands (such as
, and InnoDB Hot Backup.
A text backup is a dump of database contents into text files. Restoration involves loading the file contents back into databases by processing them through the server. Techniques for making text backups include the
SELECT ... INTO OUTFILE
, and MySQL Administrator.
The two backup formats have different strengths and weaknesses. The general
is speed versus portability.
It's faster to make a binary backup because it involves only file copy operations that need know nothing about the internal structure of the files. However, if the backup is to be used for transferring databases to another machine that uses a different architecture, the files must be binary portable. Binary portability means that the files are machine independent and that you can directly copy them from one MySQL server to another on a different machine and the second server will be able to access their contents with no problems. (See Section 32.3.4, "Conditions for Binary Portability.") With binary backup
, it's necessary to make sure that the server does not modify the files while the backup is in progress.
It's slower to make a text backup because the server must read tables (which involves the overhead of interpreting their contents) and then either write the contents out to disk files itself or send the contents to a client program that
the tables. An example of the latter approach is the
client, which receives table contents from the server and writes them out as
statements that can be reloaded to re-create tables. Text backups are portable, so a text backup made on one machine can be reloaded into the MySQL server on another machine, regardless of whether the two machines have the same architecture. With text backup methods, the server must be running because it must read the files that are to be
The procedure for making binary backups depends on which storage engine created the tables, and
can be used only for the local MySQL server. Text backup procedures are more general and can be used for tables created by any storage engine. Some methods can be used with either local or remote MySQL servers.