31.3. Limitations of INFORMATION_SCHEMA

31.3. Limitations of `INFORMATION_SCHEMA`

The INFORMATION_SCHEMA implementation is fairly complete, but there are some kinds of information that it does not yet contain. For example, there are no tables for routine parameters or referential constraints for foreign keys. These omissions may be addressed in the future.

INFORMATION_SCHEMA serves solely as a means of access for metadata. SHOW statements also serve this purpose, but the domain of SHOW extends into other areas as well. Certain SHOW statements provide information about the server's configuration or operational state. Some examples:

SHOW VARIABLES displays system variables that describe server configuration.
SHOW STATUS displays status variables that provide information about current server operation.
SHOW ENGINES lists the storage engines that the server knows about.
SHOW PROCESSLIST provides information about the currently executing server threads.
SHOW MASTER STATUS and SHOW SLAVE STATUS provide information about replication servers.

Chapter 32. Data Backup and Recovery Methods

This chapter discusses techniques for making database backups and how to recover databases from your backups if necessary. It covers the following exam topics:

Types of backups
Making binary and text backups
The role of log and status files in backups
Using a replication slave for backups
Performing data recovery

32.1. Introduction

A MySQL administrator makes database backups to guard against the possibility of system crashes or hardware failures that may result in data loss or corruption. Backups also are useful when users remove databases or tables by mistake. Another use for backups is to move or copy databases to another server, such as when you migrate a MySQL installation from one machine to another or set up a replication slave server.

Backups can be made by copying database files directly, or by using programs designed for that purpose. Such programs include mysqldump , mysqlhotcopy , MySQL Administrator, and InnoDB Hot Backup.

It's necessary to make backups, but a backup is only one of the components needed for data recovery after loss or damage. The other is the binary log, which contains a record of data changes. To recover databases, you use the backup to restore them to their state at backup time, and then re-execute statements contained in the binary log that made data changes after the backup was created.

Here are some principles to keep in mind with regard to backups:

Make backups regularly.
Enable the binary log so that you have a record of changes made after a given backup.
Flush the logs when backing up so that the server will begin a new binary log file that corresponds to the time of the backup. (That is, "checkpoint" the log to the backup.)
Store your data directory and your backups on different physical devices so that a device failure cannot destroy both.
Include your backups in your normal filesystem backup procedures so that you can recover the backup if necessary.

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.

Buy on amazon.com >>

DuBois P., Hinz S., Pedersen C.

<< Previous page

Table of contents

Next page >>