Author: plavin
Date: 2007-10-17 21:12:43 +0200 (Wed, 17 Oct 2007)
New Revision: 8156
Log:
Add backups.xml remove the inaptly named disaster-prevention
Added:
trunk/userguide/backups.xml
Removed:
trunk/userguide/disaster-prevention.xml
Added: trunk/userguide/backups.xml
===================================================================
--- trunk/userguide/backups.xml (rev 0)
+++ trunk/userguide/backups.xml 2007-10-17 19:12:43 UTC (rev 8156)
Changed blocks: 1, Lines Added: 788, Lines Deleted: 0; 30211 bytes
@@ -0,0 +1,788 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+<!ENTITY % all.entities SYSTEM "all-entities.ent">
+%all.entities;
+]>
+<chapter id="userguide-creating-backups">
+
+ <title>Backing Up Data</title>
+
+ <section id="userguide-backups-introduction">
+
+ <title>Introduction</title>
+
+ <para>
+ Say something about disaster recovery.
+ </para>
+
+ </section>
+
+ <section id="userguide-mysqldump-intro">
+
+ <title>Using <command>mysqldump</command></title>
+
+ <formalpara role="contributor">
+
+ <title>MySQL Contributor</title>
+
+ <para>
+ This section was contributed by MySQL staff. For more
+ information see <ulink url="http://mysql.com"/>.
+ </para>
+
+ </formalpara>
+
+ <para>
+ The <command>mysqldump</command> utility is a database back-up
+ program capable of copying everything on a specific MySQL server
+ — both the database objects and the data. It can also be
+ used to copy a number of databases, one particular database, one
+ or more tables from a specific database, or just specific
+ records from one table. Any kind of data can be saved using this
+ utility — even images stored as binary data.
+ </para>
+
+ <para>
+ The <command>mysqldump</command> utility creates a script file
+ of SQL statements that recreate the database objects selected
+ and it also creates <literal>INSERT</literal> statements to
+ restore data. There are various other ways to back up MySQL
+ databases or tables; using the <command>mysql</command> client
+ program and SQL statements, copying the MySQL data directory,
+ using binary logs, using MySQL Administrator, and also the
+ Unix-specific utility, <command>mysqlhotcopy</command>. However,
+ <command>mysqldump</command> is the most versatile and
+ accessible tool for backing up tables and databases and it is
+ available for all operating systems.
+ </para>
+
+ <para>
+ The reasons for creating back-up files vary:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ As replacements for existing files in the event of
+ database corruption
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To transfer files from a development server to a
+ production server
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To migrate to another file format
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ <para>
+ This chapter shows how to use <command>mysqldump</command> for
+ each of these tasks. This is not meant as a definite treatment
+ of <literal>mysqldump</literal>; for complete coverage of this
+ utility see
+ <ulink url="&base-url-refman;5.0/en/mysqldump.html"/>.
+ </para>
+
+<!-- start options -->
+
+ <section id="mysqldump-options">
+
+ <title>Options</title>
+
+ <para>
+ This section identifies the most commonly used options and
+ briefly describes each one. Examples of using these options
+ are given in subsequent sections.
+ </para>
+
+ <para>
+ Since the <command>mysqldump</command> utility gives access to
+ a specific MySQL server, you must have credentials on that
+ server; you must explicitly or implicitly provide a
+ <option>--user</option> and <option>--password</option>.
+ Likewise you must provide <option>--host</option> and
+ <option>--port</option> options. In this respect,
+ <command>mysqldump</command> does not differ from the MySQL
+ client program, <command>mysql</command>, or from other
+ utilities such as <command>mysqladmin</command>.
+ </para>
+
+ <para>
+ Other common options are:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <option>--all-databases, -A </option> – Dump all
+ tables in all databases.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--databases, -B </option> – Specify this
+ option and <command>mysqldump</command> regards all name
+ arguments as database names
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--fields-terminated-by </option> – Used in
+ conjunction with the <option>--tab</option> option to
+ specify a field terminator.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--no-create-db</option> – Used in
+ conjunction with the <option>--all-databases</option> or
+ the <option>--databases</option> option to suppress the
+ <literal>CREATE DATABASE</literal> statement
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--no-data</option> – Save database objects
+ but not data.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--opt </option> – This option is shorthand
+ for a group of options. See
+ <xref linkend="opt-group-options"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--skip-opt </option> – Turn off the
+ <option>--opt</option> group of options.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--tab=<replaceable>path</replaceable>, -T
+ <replaceable>path</replaceable> </option> – Create
+ tab-separated data files in the named directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--tables </option> – Override the
+ <option>--databases</option> option.
+ <command>mysqldump</command> regards all name arguments
+ following this option as table names.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--where=<replaceable>'where_condition'</replaceable>,
+ -w <replaceable>'where_condition'</replaceable>
+ </option> – Only dump rows selected by the where
+ condition.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--xml </option> – Dump output as XML.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ <para>
+ The <option>--all-databases</option> option is used when you
+ want to dump the entire contents of a server. On the other
+ hand, the <option>--databases</option> option lets you specify
+ particular databases to copy. Both of these options add a
+ <literal>CREATE DATABASE</literal> statement to the dump file.
+ To turn off this feature use the
+ <option>--no-create-db</option> option. You can also choose
+ not to save any data by using the <option>--no-data</option>
+ option.
+ </para>
+
+ <para>
+ The <option>--tables</option> option makes it possible to use
+ the <option>--databases</option> option and also specify which
+ tables you would like to dump.
+ </para>
+
+ <para>
+ Use the <option>--tab</option> and
+ <option>--fields-terminated-by</option> options, to dump a
+ database in a variety of text formats. For XML format, use the
+ <option>--xml</option> option. To select only specific rows
+ from a table use the <option>--where</option> option.
+ </para>
+
+ <para>
+ For a complete list of all the available options see
+ <ulink url="&base-url-refman;5.0/en/mysqldump.html"/>.
+ </para>
+
+ <section id="opt-group-options">
+
+ <title>The <option>--opt</option> Group of Options</title>
+
+ <para>
+ The <option>--opt</option> option is on by default so you
+ don't have to specify it. However, you do need to know what
+ it does. Using <option>--opt</option> is shorthand for
+ specifying <option> --add-locks, --add-drop-table,
+ --create-options, --disable-keys, --extended-insert,
+ --lock-tables, --quick,</option> and
+ <option>--set-charset</option>. Find a brief description of
+ these options in what follows.
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <option>--add-locks </option> – Lock tables
+ before inserting data.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--add-drop-table </option> – Remove
+ tables before recreating them.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--create-options </option> – Include all
+ MySQL-specific table options in the <literal>CREATE
+ TABLE</literal> statements.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--disable-keys </option> – Improve speed
+ by disabling indexes before inserting data. (Applies
+ only to MyISAM tables and only to non-unique indexes.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--extended-insert </option> – Use
+ multiple-row <literal>INSERT</literal> syntax that
+ includes a <literal>VALUES</literal> list for each
+ row.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--lock-tables </option> – Lock tables
+ before dumping them.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--quick </option> – Retrieve rows from a
+ table one row at a time, reducing demands on memory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--set-charset</option> – Add
+ <literal>SET NAMES</literal>
+ <replaceable>default_character_set</replaceable> to
+ the output.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ <para>
+ Each of these options can be turned off individually by
+ using the
+ <option>--skip-<replaceable>option-name</replaceable></option>
+ syntax. For example, if you want to ensure that you recreate
+ tables as the server default table type, you can turn off
+ <option>--create-options</option> by specifying
+ <option>--skip-create-options</option>. No engine or
+ character set will be specified in the <literal>CREATE
+ TABLE</literal> statement. Turn off
+ <option>--extended-insert</option> by specifying the
+ <option>--skip-extended-inserts</option> option. Doing this
+ creates a separate <literal>INSERT</literal> statement for
+ each row, making it much easier to remove individual
+ <literal>INSERT</literal> statements.
+ </para>
+
+ </section>
+
+ </section>
+
+<!-- back-up -->
+
+ <section id="backing-up">
+
+ <title>Backing Up Data and Database Objects</title>
+
+ <para>
+ To back up the contents of a server and create replacements
+ for all existing databases invoke the
+ <command>mysqldump</command> utility specifying your
+ credentials and the <option>--all-databases</option> option.
+ Using the option short forms, you can back up a server and
+ redirect output to a file in the following way:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump -u <replaceable>user_name</replaceable> -p -A > <replaceable>dump.sql</replaceable></userinput>
+</programlisting>
+
+ <para>
+ Using the short forms shown in the preceding listing is
+ equivalent to using the <option>--user, --password,</option>
+ and <option>--all-databases</option> options. Output is sent
+ to a file using the redirection operator,
+ <quote><literal>></literal></quote>. Since the
+ <option>--host</option> option is not specified, it defaults
+ to <literal>localhost</literal>. Likewise,
+ <option>--port</option> will default to
+ <literal>3306</literal>. Since no password is given at the
+ command line, you will be prompted for one.
+ </para>
+
+ <para>
+ Specifying your password at the command line is allowed but
+ note that you cannot leave a space between the option and your
+ password; it must appear as
+ <option>-p<replaceable>password</replaceable></option>. If a
+ space was allowed, the <option>-A</option> option in the
+ preceding listing would be interpreted as the password.
+ </para>
+
+ <para>
+ If you wish to copy only specific databases, replace
+ <option>-A</option> with the <option>--databases</option>
+ option (or its short form, <option>-B</option>) followed by
+ the names of the databases that you wish to back up. The file
+ created by this command will contain only the databases
+ specified.
+ </para>
+
+ <para>
+ Using <command>mysqldump</command> to back up specific
+ databases or all the databases on a server is an easy way to
+ create replacements in the event of lost data or database
+ corruption.
+ </para>
+
+ <section id="refining-objects-selected">
+
+ <title>Further Refining the Objects and Data Selected</title>
+
+ <para>
+ If you wish to copy only one database, you do not need to
+ use the <option>--databases</option> option. Simply specify
+ your credentials and the database name in the following
+ fashion:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump -u <replaceable>user_name</replaceable> -p <replaceable>db_name</replaceable> > <replaceable>dump.sql</replaceable></userinput>
+</programlisting>
+
+ <para>
+ A specific database is selected by using the database name
+ — no option is necessary. Output is again redirected
+ to the file using the redirection operator.
+ </para>
+
+ <para>
+ Remember that the <option>--opt</option> group of options is
+ on by default. (For a complete list of this group of options
+ see <xref linkend="opt-group-options"/>.) To turn off any
+ one of these options you can use the
+ <option>--skip-<replaceable>option-name</replaceable></option>
+ option.
+ </para>
+
+ <para>
+ On the other hand, if you want to turn off most of the
+ <option>--opt</option> options, it may be easier to specify
+ <option>--skip-opt</option> and then list the options you
+ wish to use.
+ </para>
+
+ <note>
+ <para>
+ If you choose to do things this way, make sure that you
+ specify <option>--skip-opt</option> first. If it is the
+ last option specified, it will turn off any of the
+ <option>--opt</option> group of options that precede it.
+ </para>
+ </note>
+
+ <para>
+ In some cases you may want to copy only selected tables from
+ a database. This is done by naming the desired tables
+ immediately following the database name. For example:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump <replaceable>[options]</replaceable> <replaceable>db_name table1 table2</replaceable></userinput>
+</programlisting>
+
+ <para>
+ When dumping a specific table, the data selection can be
+ further refined by adding a <option>--where</option> option
+ in the following way:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump [options] <replaceable>db_name table1</replaceable> --where=<replaceable>'field_name>1000'</replaceable></userinput>
+</programlisting>
+
+ <para>
+ When using the <option>--where</option> option only one
+ table may be specified. The script file created will contain
+ a <literal>CREATE TABLE</literal> statement for
+ reconstructing the table and any data that meets the
+ condition specified using the <option>--where</option>
+ option.
+ </para>
+
+ <note>
+ <para>
+ If the <option>--where</option> option contains spaces or
+ characters special to your command interpreter, then you
+ must enclose everything in the where condition in
+ quotation marks.
+ </para>
+ </note>
+
+ <para>
+ Using a database name at the command line creates a copy of
+ the tables and the data from the specified database.
+ However, no database is created when this syntax is used.
+ </para>
+
+ <para>
+ To dump only one database and add a <literal>CREATE
+ DATABASE</literal> statement, you must use the
+ <option>--databases</option> option. An example using the
+ short form of the <option>--databases</option> option
+ follows:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump -u <replaceable>user_name</replaceable> -p -B <replaceable>db_name</replaceable> > <replaceable>dump.sql</replaceable></userinput>
+</programlisting>
+
+ <para>
+ If you wish your dump file to contain a <literal>CREATE
+ DATABASE</literal> statement and you only wish to dump
+ selected tables use the <option>--tables</option> option as
+ shown in the following:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump -u <replaceable>user_name</replaceable> -p -B <replaceable>db_name</replaceable> <option>--tables <replaceable>table1</replaceable></option> > <replaceable>dump.sql</replaceable></userinput>
+</programlisting>
+
+ <para>
+ If you don't specify the <option>--tables</option> option,
+ the <option>-B</option> option interprets each name as a
+ database.
+ </para>
+
+ <para>
+ The next section examines how to restore databases from the
+ script files created by <command>mysqldump</command>.
+ </para>
+
+ </section>
+
+ </section>
+
+ <section id="restoring-database-dumps">
+
+ <title>Restoring Database Dumps</title>
+
+ <para>
+ Databases are restored by redirecting the script file to the
+ <command>mysql</command> client program.
+
+ <remark>
+ xref here?
+ </remark>
+
+ If the script file was created using either the
+ <option>-A</option> or <option>-B</option> options, restore
+ the dumped files in the following way:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql -u <replaceable>user_name</replaceable> -p < <replaceable>dump.sql</replaceable></userinput>
+</programlisting>
+
+ <warning>
+ <para>
+ Using the <option>-A</option> or <option>-B</option> option
+ with <command>mysqldump</command> creates a script that
+ drops and recreates databases. Any data in existing
+ databases will be lost. Furthermore, if you backed up all
+ databases then the <literal>mysql</literal> database will be
+ overwritten. Be sure that this is what you intend. For more
+ information see ...
+
+ <remark>
+ xref here
+ </remark>
+ </para>
+ </warning>
+
+ <para>
+ If you created your dump file without using the
+ <option>-A</option> or <option>-B</option> options, then the
+ database that you copy the tables to must already exist. Name
+ that database when invoking <command>mysql</command>:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql -u <replaceable>user_name</replaceable> -p <replaceable>db_name</replaceable> < <replaceable>dump.sql</replaceable></userinput>
+</programlisting>
+
+ <para>
+ In this case, you need not worry about overwriting an existing
+ database, but you will overwrite any tables in the existing
+ database that have the same names as tables in the back-up
+ file if the file contains <literal>DROP TABLE</literal> and
+ <literal>CREATE TABLE</literal> statements. To remove these
+ statements from a dump file, create it using the
+ <option>--skip-add-drop-table</option> and
+ <option>--no-create-info</option> options.
+ </para>
+
+ <para>
+ If you are uploading a database dump file to a remote database
+ then you will have to specify the <option>--host</option>
+ option. If you don't have access to your MySQL server from a
+ remote location, copy your script file to the server, log in
+ using <command>ssh</command>, and then run
+ <command>mysql</command>. If neither of these options is
+ available to you, you may be able to upload and execute the
+ script file using a program such as phpMyAdmin.
+ </para>
+
+ </section>
+
+<!-- Start Exporting -->
+
+ <section id="exporting-from-mysql">
+
+ <title>Exporting From MySQL</title>
+
+ <para>
+ To use the data from a MySQL database in another application
+ — a word processor or a spreadsheet, for example —
+ you might want to export data in text format. The most common
+ way of exporting a file in text format is by using the
+ <option>--tab</option> or <option>-T</option> option and
+ specifying the full pathname to the target directory:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump -u <replaceable>user_name</replaceable> -p <replaceable>db_name</replaceable> -T <replaceable>/tmp</replaceable></userinput>
+</programlisting>
+
+ <para>
+ Dumping a database specifying this option creates a script
+ file of each table's structure using the table name and the
+ extension <literal>sql</literal> as the file name and a
+ tab-separated file of each table's data using the table name
+ and the extension <literal>txt</literal> as the file name.
+ These files are created in the directory specified with the
+ <option>-T</option> option. This directory must be writable
+ and the user indicated by <replaceable>user_name</replaceable>
+ must have the <literal>FILE</literal> privilege. For more
+ information about the <literal>FILE</literal> privilege see
+ ...
+
+ <remark>
+ Add xref to FILE here
+ </remark>
+ </para>
+
+ <para>
+ File permissions are not usually a problem on Windows systems
+ but the file separator and spaces in file names can present
+ difficulties. Use a forward slash to separate directories and,
+ if a directory contains spaces, enclose the path in quotation
+ marks, for example, <quote>C:/Documents and
+ Settings/peter/Desktop/</quote>. Failure to include quotation
+ marks results in the following error:
+ </para>
+
+<programlisting>
+mysqldump: Got error: 1049; Unknown database 'and' when selecting the database
+</programlisting>
+
+ <para>
+ Despite its name, the <option>--tab</option> option can be
+ used to created files with a field terminator other than the
+ <literal>tab</literal> character. The field terminator is
+ changed by using the <option>--fields-terminated-by</option>
+ option. For example you can specify a
+ <quote><literal>,</literal></quote> as the terminator in order
+ to use a table in a spreadsheet program.
+ </para>
+
+ <para>
+ The <option>--tab</option> option is designed to extract data
+ from one database only and cannot be used with the
+ <option>--databases</option> option, or with the
+ <option>--all-databases</option> option. Whenever it is used a
+ database name must be one of the arguments to
+ <command>mysqldump</command>.
+ </para>
+
+ <para>
+ To further refine the data selected, the
+ <option>--tab</option> option can also be used to select data
+ from one table only. This is done by naming the desired table
+ after selecting the database. The <option>--tab</option>
+ option can also be used with the <option>--where</option>
+ option as shown in
+ <xref linkend="refining-objects-selected"/>.
+ </para>
+
+ <para>
+ Often, when creating text files there is no need for the
+ script file that creates the table structure — you're
+ simply interested in exporting the data. In cases like this it
+ would be nice to have an option to copy only data. No such
+ option exists but we will see how to do this when we discuss
+ <literal>SELECT ... INTO OUTFILE</literal>. For more
+ information see ...
+ </para>
+
+ <para>
+ For an XML representation of the data and the database objects
+ use the <option>--xml</option> option. This option creates an
+ XML document in the following format:
+ </para>
+
+<programlisting>
+<replaceable>
+ <?xml version="1.0"?>
+ <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
+ <database name="sakila">
+ <table_structure name="actor">
+ <field Field="actor_id" Type="smallint(5) unsigned" Null="NO" Key="PRI"
+ Extra="auto_increment" />
+ <field Field="first_name" Type="varchar(45)" Null="NO" Key=""
+ Default="" Extra="" />
+ <field Field="last_name" Type="varchar(45)" Null="NO" Key="MUL"
+ Default="" Extra="" />
+ <field Field="last_update" Type="timestamp" Null="NO" Key=""
+ Default="CURRENT_TIMESTAMP" Extra="" />
+ <key Table="actor" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1"
+ Column_name="actor_id" Collation="A" Cardinality="0" Null=""
+ Index_type="BTREE" Comment="" />
+ <key Table="actor" Non_unique="1" Key_name="idx_actor_last_name"
+ Seq_in_index="1" Column_name="last_name" Collation="A"
+ Cardinality="0" Null="" Index_type="BTREE" Comment="" />
+ <options Name="actor" Engine="InnoDB" Version="10" Row_format="Compact"
+ Rows="0" Avg_row_length="0" Data_length="16384" Max_data_length="0"
+ Index_length="16384" Data_free="0" Auto_increment="1"
+ Create_time="2007-04-11 19:35:58" Collation="utf8_general_ci"
+ Create_options="" Comment="InnoDB free: 10240 kB" />
+ </table_structure>
+ <table_data name="actor">
+ [table data] ...
+ </table_data>
+ [more table definitions and data] ...
+ </database>
+ </mysqldump>
+</replaceable>
+</programlisting>
+
+ <para>
+ Given the ease with which a database can be converted to XML
+ you might wonder whether conversion to HTML is also possible.
+ Unfortunately, there is no <command>mysqldump</command> option
+ for creating HTML output. However, this can be done by
+ starting <command>mysql</command> using the
+ <option>--html</option> and <option>--tee</option> options.
+ For instructions on doing this see <xref linkend="mysql-client-other-options"/>.
+
+ <remark>
+ xref when available
+ </remark>
+ </para>
+
+<!--Leave out? add Migration Toolkit reference? para>
+ Apart from exporting databases in various text formats, it is also
+ possible to export to other database formats such as MS SQL
+ Server, PostgreSQL, or even to convert to earlier versions of
+ MySQL. This is done using the
+ <option>– –compatible=<replaceable>name</replaceable></option>
+ option. Using this option makes a best guess at transforming MySQL
+ to the named format. However, this topic is beyond the scope of
+ this discussion. For more information see
+ <ulink url="&base-url-refman;5.0/en/mysqldump.html"/>.
+ </para-->
+
+ </section>
+
+ </section>
+
+<!-- end mysqldump section -->
+
+ <section id="user-guide-replication">
+
+ <title>Replication</title>
+
+ <para></para>
+
+ </section>
+
+ <section id="user-guide-backup-other">
+
+ <title>Other Options</title>
+
+ <para></para>
+
+ </section>
+
+
+
+<!-- END backup -->
+
+</chapter>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r8156 - trunk/userguide | plavin | 17 Oct |
