Author: paul
Date: 2008-03-07 20:23:38 +0100 (Fri, 07 Mar 2008)
New Revision: 10162
Log:
r29803@frost: paul | 2008-03-07 13:21:08 -0600
Merge material for BACKUP DATABASE/RESTORE into 6.0 manual.
Modified:
trunk/refman-6.0/backup.xml
trunk/refman-6.0/restrictions.xml
trunk/refman-6.0/sql-syntax.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:29799
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:29889
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:29803
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:29889
Modified: trunk/refman-6.0/backup.xml
===================================================================
--- trunk/refman-6.0/backup.xml 2008-03-07 18:12:40 UTC (rev 10161)
+++ trunk/refman-6.0/backup.xml 2008-03-07 19:23:38 UTC (rev 10162)
Changed blocks: 1, Lines Added: 891, Lines Deleted: 0; 26472 bytes
@@ -1176,6 +1176,897 @@
</section>
+ <section id="backup-database-restore">
+
+ <title>Using <literal>BACKUP DATABASE</literal> and <literal>RESTORE</literal></title>
+
+ <para>
+ The <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal> statements are available as of MySQL
+ 6.0.5. They provide a way to make a copy of a MySQL server
+ instance (data and metadata) at a given point in time, and a way
+ to restore the instance to its state as of that time. Restoring a
+ backup can be combined with use of the binary log to accomplish
+ point-in-time recovery: If the restore operation is done because
+ data loss has occurred after the backup was made, restored
+ databases can be brought up to the time of data loss by executing
+ the data changes in the binary log between the times when the
+ backup was made and when the data loss occurred.
+ </para>
+
+ <para>
+ A goal of the <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal> statements is to enable other database
+ operations to proceed concurrently, to make it unnecessary to take
+ databases offline or prevent clients from accessing them.
+ <literal>BACKUP DATABASE</literal> must block some operations from
+ occurring (such as dropping tables from a database while it is
+ being backed up), but the attempt is made to keep blocking to a
+ minimum. Generally, blocked operations are those involving Data
+ Definition Language (DDL) statements. <literal>RESTORE</literal>
+ must do more blocking because it writes database contents rather
+ than just reading them.
+ </para>
+
+ <para>
+ The following discussion covers these aspects of <literal>BACKUP
+ DATABASE</literal> and <literal>RESTORE</literal>:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Quick guide to making backups and restoring them
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ How <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal> work
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Status reporting and monitoring for backup and restore
+ operations
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For additional information about the <literal>BACKUP
+ DATABASE</literal> and <literal>RESTORE</literal> statements, see
+ these sections of the manual:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <xref linkend="backup-database"/>, and
+ <xref linkend="restore"/>, describes the syntax for these
+ statements.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Limitations on the use of these statements are discussed in
+ <xref linkend="backup-database-restore-limitations"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <section id="backup-database-restore-quick-guide">
+
+ <title>Quick Guide to <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal></title>
+
+ <para>
+ Use the <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal> statements like this:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>BACKUP DATABASE</literal> backs up one or more
+ databases to a named file:
+ </para>
+
+<programlisting>
+BACKUP DATABASE world TO '/tmp/mybackupfile';
+</programlisting>
+
+ <para>
+ To back up more than one database, separate the names by
+ commas:
+ </para>
+
+<programlisting>
+BACKUP DATABASE world, sakila TO '/tmp/mybackupfile';
+</programlisting>
+
+ <para>
+ To select all databases for backup, use the
+ <literal>*</literal> selector as a shortcut:
+ </para>
+
+<programlisting>
+BACKUP DATABASE * TO '/tmp/mybackupfile';
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>RESTORE</literal> restores databases using the
+ contents of the backup file:
+ </para>
+
+<programlisting>
+RESTORE FROM '/tmp/mybackupfile';
+</programlisting>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For more information about the operation of the <literal>BACKUP
+ DATABASE</literal> and <literal>RESTORE</literal> statements,
+ see <xref linkend="backup-database"/>, and
+ <xref linkend="restore"/>.
+ </para>
+
+ </section>
+
+ <section id="backup-database-restore-implementation">
+
+ <title>How <literal>BACKUP DATABASE</literal> and <literal>RESTORE</literal>
+ Work</title>
+
+ <para>
+ A backup operation creates a backup of one or more databases at
+ a given point in time and saves it as a backup image, a file
+ that contains the backup data (table contents) and metadata
+ (definitions for databases, tables, and other objects, and
+ server information).
+ </para>
+
+ <para>
+ The backup is intended to provide a consistent snapshot of the
+ backed-up data as of the point at which the operation began, and
+ it is intended to provide online operation as much as possible
+ that allows other server activity to proceed without blocking.
+ </para>
+
+ <para>
+ A backup operation begins at time <replaceable>t1</replaceable>
+ and ends at time <replaceable>t2</replaceable>, producing a
+ backup image that contains the backup state (server instance
+ state) at time <replaceable>t</replaceable>, where
+ <replaceable>t1</replaceable> < <replaceable>t</replaceable>
+ < <replaceable>t2</replaceable>. The time
+ <replaceable>t</replaceable> is called the validity point of the
+ backup image. It represents the time when all storage engines
+ are synchronized for the backup. Restoring this image restores
+ the state to be the same as it was at time
+ <replaceable>t</replaceable>.
+ </para>
+
+ <para>
+ Consistency of the backup means that these constraints must be
+ true:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Data from transactional tables is included only for
+ committed transactions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Data from non-transactional tables is included only for
+ completed statements.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Referential integrity is maintained between all backed-up
+ tables within a given backup image.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The referential-integrity constraint does not necessarily hold
+ if two tables are related but only one of them is included in a
+ backup. Restoring the backup then would restore only the
+ backed-up table, which can produce tables for which referential
+ integrity no longer holds.
+ </para>
+
+ <para>
+ For a backup to proceed properly, certain types of server
+ activity must be blocked, so the backup system incorporates a
+ commit blocker and a DDL blocker.
+ </para>
+
+ <para>
+ The commit blocker has these properties:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Changes for non-transactional tables must be blocked.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Changes for transactional tables are not blocked, but only
+ changes that have been committed when the backup occurs
+ appear in the backup. Changes that occur during the backup
+ operation are not included in the backup image.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ When a backup or restore operation is in progress, it is not
+ allowable to modify the structure of databases or tables being
+ backed up. Consequently, the DDL blocker prevents these
+ statements from executing during the operation:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>CREATE TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>DROP TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>ALTER TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>RENAME TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>REPAIR TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>OPTIMIZE TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>TRUNCATE TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>CREATE INDEX</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>DROP INDEX</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>CREATE DATABASE </literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>DROP DATABASE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>RENAME DATABASE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>ALTER DATABASE </literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Currently, all instances of the the DDL statements in the list
+ are blocked, even for database or table objects that are not
+ included in the backup. Eventually, the goal is to block only
+ DDL statements for objects in the backup.
+ </para>
+
+ <para>
+ Blocking works in both directions. A backup or restore blocks
+ DDL statements, but if a backup or restore operation is
+ initiated while DDL statements are in progress, the operation
+ waits until the statements have finished.
+ </para>
+
+ <para>
+ Implementation of <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal> uses an architecture with the
+ following design:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The MySQL server communicates with the backup kernel.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The backup kernel is responsible for communicating with
+ backup engines and for handling metadata (definitions for
+ databases, tables, and other objects, and server
+ information).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each backup engine provides backup and restore drivers for
+ the backup kernel to use.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ An engine's backup and restore drivers perform actual
+ transfer of data (table contents).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The backup system chooses from among the backup engines
+ available to it:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ There is a default backup engine to be used if a better one
+ is not found. This engine provides default backup and
+ restore drivers that use a blocking algorithm. For example,
+ the backup driver locks all tables at the start of the
+ backup and unlocks them after the last one is processed
+ (which may occur before the operation is complete).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A consistent-snapshot engine implements the same kind of
+ backup as that made by <command>mysqldump
+ --single-transaction</command>.
+ </para>
+
+ <para>
+ The backup driver for the snapshot engine works with only
+ those storage engines that support consistent read via the
+ handler interface, which currently includes only
+ <literal>InnoDB</literal> and <literal>Falcon</literal>. The
+ backup driver creates a logical backup because it reads rows
+ one at a time and returns them to the backup kernel to be
+ stored in the backup image.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ A backup image must have contents that are consistent with the
+ binary log coordinates taken from the time of the backup.
+ Otherwise, point-in-time recovery using the backup image plus
+ the binary log contents will not work correctly. <literal>BACKUP
+ DATABASE</literal> synchronizes with binary logging to make sure
+ that the backup image and binary log are consistent with each
+ other. This way, if data loss occurs later, use of the backup
+ image combined with the binary log makes makes point-in-time
+ recovery possible:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Restore the backup image
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Re-execute binary log contents beginning from the
+ coordinates of the backup's validity point up to the desired
+ point of recovery
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ </section>
+
+ <section id="backup-database-restore-monitoring">
+
+ <title><literal>BACKUP DATABASE</literal> and <literal>RESTORE</literal> Status
+ Reporting and Monitoring</title>
+
+ <para>
+ MySQL provides information about the status or progress of
+ <literal>BACKUP DATABASE</literal> or <literal>RESTORE</literal>
+ operations in the following ways:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>SHOW PROCESSLIST</literal> displays information
+ while a thread performing a backup or restore is executing.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Upon successful completion, the <literal>BACKUP
+ DATABASE</literal> and <literal>RESTORE</literal> statements
+ return a result set with the backup number. (This number is
+ the ID for the corresponding row or rows in the metadata
+ tables described later.) Warnings produced during the
+ operation can be displayed with <literal>SHOW
+ WARNINGS</literal>.
+ </para>
+
+ <para>
+ If errors occur during a backup or restore operation, they
+ are written to the error log, recorded in the progress
+ tables, and are available via the <literal>SHOW
+ ERRORS</literal> and <literal>SHOW WARNINGS</literal>
+ statements.
+ </para>
+
+ <para>
+ If a fatal error occurs, the <literal>BACKUP
+ DATABASE</literal> or <literal>RESTORE</literal> statement
+ reports it to the user.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The server maintains <literal>online_backup</literal> and
+ <literal>online_backup_progress</literal> tables in the
+ <literal>mysql</literal> database that contain metadata. (If
+ you upgrade to MySQL 6.0.5 or later from an older version,
+ be sure to run <command>mysql_upgrade</command> to ensure
+ that these tables exist.)
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The <literal>online_backup</literal> table contains a
+ row for each backup and restore operation. A row is
+ created when an operation begins and is updated as the
+ operation progresses. The rows in this table serve as a
+ history of all backup and restore operations performed
+ on the server. The table can be queried to obtained
+ detailed information about the operations or as a means
+ to create a summary of the operations. The rows are not
+ removed from the table by the server. Any table
+ maintenance, such as removing old rows, is intended to
+ be performed by the database administrator.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>online_backup_progress</literal> table
+ contains progress data describing the steps in the most
+ recent backup or restore operation. There may be
+ multiple rows for the operation. Rows are added to this
+ table over the course of the operation and are not
+ updated. This enables the table to be used to track the
+ current progress of the operation. Each row in the table
+ represents a step in the operation and may contain
+ informational statements, errors, and other pertinent
+ information. The data in this table has a limited
+ lifetime. At the start of each operation, the table is
+ truncated and new data is added. The database
+ administrator should not need to perform maintenance for
+ this data.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Currently, there are no
+ <literal>INFORMATION_SCHEMA</literal> tables corresponding
+ to the <literal>online_backup</literal> and
+ <literal>online_backup_progress</literal> tables.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <remark role="todo">
+ There is some question about the actual time zone; it may be
+ UTC. Originally I wrote this: Times stored in the metadata
+ tables are expressed with respect to the server's system time
+ zone (the value of the <literal>system_time_zone</literal>
+ system variable).
+ </remark>
+
+ <para>
+ The <literal>online_backup</literal> table has this structure:
+ </para>
+
+<programlisting>
+CREATE TABLE online_backup (
+ backup_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
+ process_id INT UNSIGNED NOT NULL,
+ binlog_pos INT UNSIGNED DEFAULT 0,
+ binlog_file CHAR(64),
+ backup_state ENUM('complete', 'starting', 'validity point',
+ 'running', 'error', 'cancel') NOT NULL,
+ operation ENUM('backup', 'restore') NOT NULL,
+ error_num INT NOT NULL DEFAULT 0,
+ num_objects INT UNSIGNED NOT NULL DEFAULT 0,
+ total_bytes BIGINT UNSIGNED,
+ validity_point_time DATETIME,
+ start_time DATETIME,
+ stop_time DATETIME,
+ host_or_server_name CHAR (30),
+ username CHAR (30),
+ backup_file CHAR (100),
+ user_comment VARCHAR (200) DEFAULT NULL,
+ command VARCHAR (512),
+ engines VARCHAR (100),
+) ENGINE=MYISAM;
+</programlisting>
+
+ <para>
+ The <literal>online_backup</literal> columns are used as
+ follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>backup_id</literal>
+ </para>
+
+ <para>
+ The ID for the table row. <literal>BACKUP DATABASE</literal>
+ and <literal>RESTORE</literal> return a result set
+ containing a backup ID, which is the value that tells you
+ which row in the <literal>online_backup</literal> table
+ corresponds to the backup or restore operation.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>process_id</literal>
+ </para>
+
+ <para>
+ The process ID that the operation ran as.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>binlog_pos</literal>,
+ <literal>binlog_file</literal>
+ </para>
+
+ <para>
+ For a backup, the binary log position and filename at the
+ time the validity point is generated (the time when all
+ storage engines are synchronized). Before that time, the
+ values are 0 and <literal>NULL</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>backup_state</literal>
+ </para>
+
+ <para>
+ The status of the operation.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>operation</literal>
+ </para>
+
+ <para>
+ The type of operation.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>error_num</literal>
+ </para>
+
+ <para>
+ The error from this operation (0 = no error).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>num_objects</literal>
+ </para>
+
+ <para>
+ The number of objects in the backup.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>total_bytes</literal>
+ </para>
+
+ <para>
+ The size of the backup image file in bytes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>validity_point_time</literal>
+ </para>
+
+ <para>
+ For a backup, this is the time that the validity point was
+ generated. Before that time, the value is
+ <literal>NULL</literal>. For a restore, the value currently
+ is always <literal>NULL</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>start_time</literal>, <literal>stop_time</literal>
+ </para>
+
+ <para>
+ The date and time when the operation started and stopped.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>host_or_server_name</literal>
+ </para>
+
+ <para>
+ The server name where the operation ran.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>username</literal>
+ </para>
+
+ <para>
+ The name of the user who ran the operation.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>backup_file</literal>
+ </para>
+
+ <para>
+ The name of the backup image file.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>user_comment</literal>
+ </para>
+
+ <para>
+ The comment from the user entered at the command line.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>command</literal>
+ </para>
+
+ <para>
+ The statement used to perform the operation.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>engines</literal>
+ </para>
+
+ <para>
+ The names of the storage engines used in the operation.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The <literal>online_backup_progress</literal> table has this
+ structure:
+ </para>
+
+<programlisting>
+CREATE TABLE online_backup_progress (
+ backup_id BIGINT UNSIGNED NOT NULL
+ object CHAR (30) NOT NULL
+ start_time DATATIME
+ stop_time DATATIME
+ total_bytes BIGINT
+ progress BIGINT UNSIGNED
+ error_num INT NOT NULL DEFAULT 0
+ notes CHAR(100)
+) ENGINE=MYISAM;
+</programlisting>
+
+ <para>
+ The <literal>online_backup_progress</literal> columns are used
+ as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>backup_id</literal>
+ </para>
+
+ <para>
+ The <literal>backup_id</literal> value of the
+ <literal>online_backup</literal> table row with which the
+ rows in the <literal>online_backup_progress</literal> table
+ are associated.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>object</literal>
+ </para>
+
+ <para>
+ The object being operated on.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>start_time</literal>, <literal>stop_time</literal>
+ </para>
+
+ <para>
+ The date and time when the operation started and stopped.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>total_bytes</literal>
+ </para>
+
+ <para>
+ The size of the object in bytes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>progress</literal>
+ </para>
+
+ <para>
+ The number of bytes processed.
+ </para>
+
+ <remark role="todo">
+ so far?
+ </remark>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>error_num</literal>
+ </para>
+
+ <para>
+ The error from this operation (0 = no error).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>notes</literal>
+ </para>
+
+ <para>
+ Commentary from the backup engine.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ </section>
+
<section id="point-in-time-recovery">
<title>Point-in-Time Recovery</title>
Modified: trunk/refman-6.0/restrictions.xml
===================================================================
--- trunk/refman-6.0/restrictions.xml 2008-03-07 18:12:40 UTC (rev 10161)
+++ trunk/refman-6.0/restrictions.xml 2008-03-07 19:23:38 UTC (rev 10162)
Changed blocks: 1, Lines Added: 61, Lines Deleted: 0; 2042 bytes
@@ -1184,6 +1184,67 @@
</section>
+ <section id="backup-database-restore-restrictions">
+
+ <title>Restrictions on <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal></title>
+
+ <indexterm>
+ <primary>BACKUP DATABASE restrictions</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>restrictions</primary>
+ <secondary>BACKUP DATABASE</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>RESTORE restrictions</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>restrictions</primary>
+ <secondary>RESTORE</secondary>
+ </indexterm>
+
+ <para>
+ Currently, the <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal> statements have these limitations:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ There is no way to determine the contents of a backup image
+ produced with <literal>BACKUP DATABASE</literal>. That is, you
+ cannot determine which databases are in a given backup image
+ and will be restored by using it with
+ <literal>RESTORE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>RESTORE</literal> is a destructive operation. Each
+ restored database is first dropped and then created and
+ populated with the tables contained in the backup image. There
+ is no warning about existing data being overwritten.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>BACKUP DATABASE</literal> and
+ <literal>RESTORE</literal> cannot be used in stored routines,
+ triggers, or events.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
<section id="limits">
<title>Limits in MySQL</title>
Modified: trunk/refman-6.0/sql-syntax.xml
===================================================================
--- trunk/refman-6.0/sql-syntax.xml 2008-03-07 18:12:40 UTC (rev 10161)
+++ trunk/refman-6.0/sql-syntax.xml 2008-03-07 19:23:38 UTC (rev 10162)
Changed blocks: 2, Lines Added: 217, Lines Deleted: 1; 7713 bytes
@@ -18805,6 +18805,222 @@
</section>
+ <section id="backup-restore-sql">
+
+ <title>Backup and Restore Statements</title>
+
+ <remark role="help-category" condition="Backup and Restore"/>
+
+ <section id="backup-database">
+
+ <title><literal>BACKUP DATABASE</literal> Syntax</title>
+
+ <indexterm>
+ <primary>BACKUP DATABASE</primary>
+ </indexterm>
+
+ <remark role="help-topic" condition="BACKUP DATABASE"/>
+
+ <remark role="help-keywords">
+ BACKUP DATABASE SCHEMA
+ </remark>
+
+ <remark role="help-syntax"/>
+
+<programlisting>
+BACKUP {DATABASE | SCHEMA}
+ { * | <replaceable>db_name</replaceable> [, <replaceable>db_name</replaceable>] ... }
+ TO '<replaceable>image_file_name</replaceable>';
+</programlisting>
+
+ <remark role="todo">
+ Interpretation of relative filenames? Privileges required?
+ </remark>
+
+ <remark role="help-description-begin"/>
+
+ <para>
+ This statement backs up one or more databases and writes the
+ backup contents to an image file (a file containing database
+ contents). The file must be named as a literal string and must
+ not already exist. The file is written to the server host. Its
+ location must be in a directory where the server can create
+ and write files.
+ </para>
+
+ <para>
+ The databases to back up may be specified using
+ <literal>*</literal> to name all databases, or using a
+ comma-separated list of one or more names. All specified
+ databases are backed up to the same image file. If databases
+ are named, no name can appear more than once, and all the
+ databases must exist.
+ </para>
+
+<programlisting>
+BACKUP DATABASE * TO '/tmp/all.backup';
+BACKUP DATABASE world TO '/tmp/world.backup';
+BACKUP DATABASE db1, db2 TO '/tmp/db1-db2.backup';
+</programlisting>
+
+ <para>
+ The resulting image file contains information about which
+ databases it cantains and can be used later with a
+ <literal>RESTORE</literal> statement to restore the contents
+ of those databases to their state at the time of the backup
+ operation.
+ </para>
+
+ <para>
+ Upon successful completion, the <literal>BACKUP
+ DATABASE</literal> statement returns a result set with the
+ backup number. Warnings produced during the operation can be
+ displayed with <literal>SHOW WARNINGS</literal>. If the backup
+ operation fails, it returns an error and any file created by
+ the operation is deleted.
+ </para>
+
+<programlisting>
+mysql> <userinput>BACKUP DATABASE test TO '/tmp/world.backup';</userinput>
++-----------+
+| backup_id |
++-----------+
+| 8 |
++-----------+
+</programlisting>
+
+ <para>
+ <literal>BACKUP DATABASE</literal> backs up database and table
+ definitions, table data, stored routines, triggers, events,
+ and views.
+ </para>
+
+ <para>
+ The <literal>BACKUP DATABASE</literal> statement does not back
+ up the <literal>mysql</literal> or
+ <literal>INFORMATION_SCHEMA</literal> databases. The statement
+ silently ignores them if you use the <literal>*</literal>
+ database selector syntax. Do not include them in the list of
+ names if you specify database names explicitly.
+ </para>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ The <literal>BACKUP DATABASE</literal> statement is not
+ written to the binary log and does not replicate to slave
+ servers.
+ </para>
+
+ <para>
+ For general information about <literal>BACKUP
+ DATABASE</literal> and <literal>RESTORE</literal>, see
+ <xref linkend="backup-database-restore"/>.
+ </para>
+
+ <para>
+ <literal>BACKUP DATABASE</literal> was added in MySQL 6.0.5.
+ </para>
+
+ </section>
+
+ <section id="restore">
+
+ <title><literal>RESTORE</literal> Syntax</title>
+
+ <indexterm>
+ <primary>RESTORE</primary>
+ </indexterm>
+
+ <remark role="help-topic" condition="RESTORE"/>
+
+ <remark role="help-keywords">
+ RESTORE FROM
+ </remark>
+
+ <remark role="help-syntax"/>
+
+<programlisting>
+RESTORE FROM '<replaceable>image_file_name</replaceable>';
+</programlisting>
+
+ <remark role="todo">
+ Interpretation of relative filenames? Privileges required?
+ </remark>
+
+ <remark role="help-description-begin"/>
+
+ <para>
+ Given a backup image file created by the <literal>BACKUP
+ DATABASE</literal> statement, <literal>RESTORE</literal>
+ restores the databases contained in the image. The image file
+ must be named as a literal string. Its location must be in a
+ directory where the server can read files.
+ </para>
+
+ <para>
+ The <literal>RESTORE</literal> statement takes no database
+ names specifying which databases to restore. It restores the
+ entire contents of the image file. The databases are restored
+ to their state at the time that the image file was created.
+ Restoring the image file can be combined with use of the
+ binary log to achieve point-in-time recovery (see
+ <xref linkend="point-in-time-restore"/>).
+ </para>
+
+ <para>
+ Upon successful completion, the <literal>RESTORE</literal>
+ statement returns a result set with the backup number.
+ Warnings produced during the operation can be displayed with
+ <literal>SHOW WARNINGS</literal>. If the restore operation
+ fails, it returns an error.
+ </para>
+
+<programlisting>
+mysql> <userinput>RESTORE FROM '/tmp/world.backup';</userinput>
++-----------+
+| backup_id |
++-----------+
+| 9 |
++-----------+
+</programlisting>
+
+ <caution>
+ <para>
+ <literal>RESTORE</literal> is a destructive operation. Each
+ restored database is first dropped and then created and
+ populated with the tables contained in the backup image.
+ </para>
+ </caution>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ During a <literal>RESTORE</literal> operation, foreign key
+ constraints are disabled so that the operation can create and
+ populate tables without causing warnings or errors related to
+ foreign keys.
+ </para>
+
+ <para>
+ The <literal>RESTORE</literal> statement is not written to the
+ binary log and does not replicate to slave servers.
+ </para>
+
+ <para>
+ For general information about <literal>BACKUP
+ DATABASE</literal> and <literal>RESTORE</literal>, see
+ <xref linkend="backup-database-restore"/>.
+ </para>
+
+ <para>
+ <literal>RESTORE</literal> was added in MySQL 6.0.5.
+ </para>
+
+ </section>
+
+ </section>
+
<section id="set-option">
<title><literal>SET</literal> Syntax</title>
@@ -18927,7 +19143,7 @@
<secondary>UNIQUE_CHECKS</secondary>
</indexterm>
- <remark role="help-category" condition="Data Manipulation"/>
+ <remark role="help-category" condition="Administration"/>
<remark role="help-topic" condition="SET"/>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r10162 - in trunk: . refman-6.0 | paul | 7 Mar |
