32.2. Binary Versus Textual Backups


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 preserves 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 cp or tar), mysqlhotcopy, 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 SQL statement, mysqldump, and MySQL Administrator.

The two backup formats have different strengths and weaknesses. The general tradeoff 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 methods, 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 writes the tables. An example of the latter approach is the mysqldump client, which receives table contents from the server and writes them out as INSERT 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 backed up.

The procedure for making binary backups depends on which storage engine created the tables, and generally 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.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net