| List: | Commits | « Previous MessageNext Message » | |
| From: | plavin | Date: | April 11 2007 11:51pm |
| Subject: | svn commit - mysqldoc@docsrva: r5841 - trunk/userguide | ||
| View as plain text | |||
Author: plavin Date: 2007-04-11 23:51:58 +0200 (Wed, 11 Apr 2007) New Revision: 5841 Log: Finish mysqldump chapter Modified: trunk/userguide/mysqldump.xml Modified: trunk/userguide/mysqldump.xml =================================================================== --- trunk/userguide/mysqldump.xml 2007-04-11 17:35:32 UTC (rev 5840) +++ trunk/userguide/mysqldump.xml 2007-04-11 21:51:58 UTC (rev 5841) Changed blocks: 22, Lines Added: 250, Lines Deleted: 200; 29680 bytes @@ -14,14 +14,25 @@ <para> The <literal>mysqldump</literal> utility is a database back-up utility capable of copying everything on a specific MySQL server - — both the structure and the data — one particular - database or just specific elements from one table. There are various - other ways to back up MySQL databases or tables; using SQL - statements, making copies of the MySQL data directory, using binary - logs, using MySQL Administrator, and finally the Unix-specific - utility, <literal>mysqlhotcopy</literal>. However, + — both the structure 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 elements 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 + 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, + <literal>mysqlhotcopy</literal>. However, <literal>mysqldump</literal> is the most versatile and accessible - tool for backing up tables and databases. + tool for backing up tables and databases and it is available for all + operating systems. </para> <para> @@ -31,7 +42,7 @@ <listitem> <para> - As replacements for existing files in case of database + As replacements for existing files in the event of database corruption </para> </listitem> @@ -65,15 +76,16 @@ <para> This section identifies the most commonly used options and briefly - describes each one. + describes each one. Examples of using these options are given in + the following sections. </para> <para> Since the <command>mysqldump</command> utility gives access to a - specific MySQL server you must use the <option>--user, --password, - --host</option>, and <option>--port</option> options. In this - respect, <literal>mysqldump</literal> does not differ from the - MySQL client program, <literal>mysql</literal>, or from other + specific MySQL server, you must use the <option>--user, + --password, --host</option>, and <option>--port</option> options. + In this respect, <literal>mysqldump</literal> does not differ from + the MySQL client program, <literal>mysql</literal>, or from other utilities such as <literal>mysqladmin</literal>. </para> @@ -84,8 +96,8 @@ <listitem> <para> - <option>--all-databases, -A</option> – Dump all tables - in all databases + <option>--all-databases, -A </option> – Dump all + tables in all databases </para> </listitem> @@ -98,7 +110,7 @@ <listitem> <para> - <option>--fields-terminated-by</option> – Used in + <option>--fields-terminated-by </option> – Used in conjunction with the <option>--tab</option> option to specify a field terminator </para> @@ -106,14 +118,14 @@ <listitem> <para> - <option>--opt</option> – This option is shorthand for + <option>--opt </option> – This option is shorthand for a group of options </para> </listitem> <listitem> <para> - <option>--skip-opt</option> – Turn off the + <option>--skip-opt </option> – Turn off the <option>--opt</option> group of options </para> </listitem> @@ -121,22 +133,22 @@ <listitem> <para> <option>--tab=<replaceable>path</replaceable>, -T - <replaceable>path</replaceable></option> – Create + <replaceable>path</replaceable> </option> – Create tab-separated data files </para> </listitem> <listitem> <para> - <option>--where='where_condition', -w - 'where_condition'</option> – only dump rows selected - by the where condition + <option>--where='where_condition', -w 'where_condition' + </option> – only dump rows selected by the where + condition </para> </listitem> <listitem> <para> - <option>--xml</option> – dump output as XML + <option>--xml </option> – dump output as XML </para> </listitem> @@ -144,10 +156,10 @@ </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 + 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. This option also adds a <literal>CREATE + databases to copy. Both of these options add a <literal>CREATE DATABASE</literal> statement to the dump file. </para> @@ -159,96 +171,100 @@ table use the <option>--where</option> option. </para> - <para> - The <option>--opt</option> option is on by default so you don't - have to specify it. However, you 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 below a brief description of - these options. - </para> + <section id="opt-group-options"> - <itemizedlist> + <title>The <option>--opt</option> Group of Options</title> - <listitem> - <para> - <option>--add-locks</option> – lock tables before - inserting data - </para> - </listitem> + <para> + The <option>--opt</option> option is on by default so you don't + have to specify it. However, you 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 below + a brief description of these options. - <listitem> - <para> - <option>--add-drop-table</option> – remove tables before - recreating them - </para> - </listitem> + <itemizedlist> - <listitem> - <para> - <option>--create-options</option> – Include all - MySQL-specific table options in the <literal>CREATE - TABLE</literal> statements - </para> - </listitem> + <listitem> + <para> + <option>--add-locks </option> – lock tables before + inserting data + </para> + </listitem> - <listitem> - <para> - <option>--disable-keys</option> – Improve speed by - disabling indexes before inserting data - </para> - </listitem> + <listitem> + <para> + <option>--add-drop-table </option> – remove tables + before recreating them + </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>--create-options </option> – Include all + MySQL-specific table options in the <literal>CREATE + TABLE</literal> statements + </para> + </listitem> - <listitem> - <para> - <option>--lock-tables</option> – lock tables before - dumping them - </para> - </listitem> + <listitem> + <para> + <option>--disable-keys </option> – Improve speed by + disabling indexes before inserting data + </para> + </listitem> - <listitem> - <para> - <option>--quick</option> – retrieve rows from a table - one row at a time - </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>--set-charset</option> – Add <literal>SET - NAMES</literal> - <replaceable>default_character_set</replaceable> to the output - </para> - </listitem> + <listitem> + <para> + <option>--lock-tables </option> – lock tables before + dumping them + </para> + </listitem> - </itemizedlist> + <listitem> + <para> + <option>--quick </option> – retrieve rows from a + table one row at a time, reducing demands on memory + </para> + </listitem> - <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. - </para> + <listitem> + <para> + <option>--set-charset</option> – Add <literal>SET + NAMES</literal> + <replaceable>default_character_set</replaceable> to the + output + </para> + </listitem> - <para> - Some of these options are self-explanatory and need little - explanation. In any case, detailed examples of using these options - are given in the following sections. - </para> + </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. Turning off <option>--extended-insert</option> + creates a separate <literal>INSERT</literal> statement for each + row, making it much easier to remove unwanted rows. + </para> + + </section> + </section> <!-- back-up --> @@ -262,11 +278,12 @@ 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 redirect output to a file in the following way: + forms, you can back up a server and redirect output to a file in + the following way: </para> <programlisting> -shell> <userinput>mysqldump -u <replaceable>username</replaceable>-p -A > <replaceable>dump.sql</replaceable></userinput> +shell> <userinput>mysqldump -u <replaceable>username</replaceable> -p -A > <replaceable>dump.sql</replaceable></userinput> </programlisting> <para> @@ -274,10 +291,11 @@ to using the <option>--user, --password,</option> and <option>--all-databases</option> options. Output is sent to a file using the redirection operator, - «<literal>></literal>». Since the - <option>--host</option> option is not specified it defaults to - <literal>localhost</literal>. Since no password is specified at - the command line, you will be prompted for one. + ‘<literal>></literal>’. Since the + <option>--host</option> option is not specified, it defaults to + <literal>localhost</literal>. Likewise, <option>--port</option> + will default to <literal>33306</literal>. Since no password is given at the + command line, you will be prompted for one. </para> <para> @@ -290,16 +308,17 @@ </para> <para> - Any kind of data can be saved in this fashion — even images - stored as binary data. + If you wish to copy only specific databases, replace + <option>-A</option> with the <option>--databases</option> option + (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> - If you wish to copy only specific databases replace the - <option>-A</option> with the <option>--databases</option> option - (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. + 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"> @@ -313,19 +332,19 @@ </para> <programlisting> -shell> <userinput>mysqldump -u <replaceable>username</replaceable>-p <replaceable>db_name</replaceable> > <replaceable>dump.sql</replaceable></userinput> +shell> <userinput>mysqldump -u <replaceable>username</replaceable> -p <replaceable>db_name</replaceable> > <replaceable>dump.sql</replaceable></userinput> </programlisting> <para> - A specific database is selected by using the database name. - Output is again redirected to the file using the redirection - operator. + 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="mysqldump-options"/>.) To turn off any one of + 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. For example, to create one <literal>INSERT</literal> @@ -335,16 +354,16 @@ <para> On the other hand, if you want to turn off most of the - <option>--opt</option> options, it's easier to specify + <option>--opt</option> options, it may be easier to specify <option>--skip-opt</option> and then list the options you wish - to implement. + 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 specified, it will turn off any of the <option>--opt</option> group of options that precede it. </para> </note> @@ -356,13 +375,13 @@ </para> <programlisting> -shell> <userinput>mysqldump [options] <replaceable>db_name table1 table2</replaceable> </userinput> +shell> <userinput>mysqldump [options] <replaceable>db_name table1 table2</replaceable></userinput> </programlisting> <para> When dumping one or more tables you cannot use the <option>--databases</option> option because this option - interprets each name that follows it as a database. + interprets each name as a database. </para> <para> @@ -372,22 +391,22 @@ </para> <programlisting> -shell> <userinput>mysqldump [options] <replaceable>db_name table1</replaceable> --where=<replaceable>id>1000</replaceable></userinput> +shell> <userinput>mysqldump [options] <replaceable>db_name table1</replaceable> --where=<replaceable>>1000</replaceable></userinput> </programlisting> <para> - The script file created will contain a - <literal>CREATE TABLE</literal>statement for reconstructing <literal>table1</literal> - and any data that meets the condition specified using the <option>--where</option> - option. + 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 + <literal>table1</literal> 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 that follows the equals sign - in quotation marks. + characters special to your command interpreter, then you must + enclose everything in the where condition in quotation marks. </para> </note> @@ -400,22 +419,20 @@ <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: + <option>--databases</option> option. An example using the short + form of the <option>--databases</option> option follows: </para> <programlisting> -shell> <userinput>mysqldump -u <replaceable>username</replaceable>-p -B <replaceable>db_name</replaceable> > <replaceable>dump.sql</replaceable></userinput> +shell> <userinput>mysqldump -u <replaceable>username</replaceable> -p -B <replaceable>db_name</replaceable> > <replaceable>dump.sql</replaceable></userinput> </programlisting> <para> - Using <command>mysqldump</command> to back up all the databases - on a server is an easy way to create replacements in the event - of lost data or database corruption. Backing up a specific - database, either with or without the <option>-B</option> option - is a good way to move a database from development to production. - The next section examines how to restore databases from the - script files created by <command>mysqldump</command>. + Backing up a specific database, either with or without the + <option>-B</option> option is a good way to move a database from + a development server to a production server. The next section + examines how to restore databases from the script files created + by <command>mysqldump</command>. </para> </section> @@ -427,47 +444,52 @@ <title>Restoring Database Dumps</title> <para> - Databases are restored using the <command>mysql</command> client - program. If the script file was created using either the - <option>-A</option> or <option>-B</option> options, recreate them in the - following way: + 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>username</replaceable> -p < <replaceable>dump.sql</replaceable></userinput> </programlisting> - + <warning> <para> - Using the <option>-A</option> or <option>-B</option> option - drops and recreates databases. Any - data in existing databases will be lost. Furthermore, - if you used the <option>-A</option> option the - <literal>mysql</literal> database will be overwritten. Be sure that this - is what you intend. For more information see ... - <remark>xref here</remark> + 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 you must name the database you wish to restore - to: + If you created your dump file without using the + <option>-A</option> or <option>-B</option> options, then the + database you copy the tables to must already exist. Name that + database when invoking <command>mysql</command>: </para> - + <programlisting> shell> <userinput>mysql -u <replaceable>username</replaceable> -p <replaceable>db_name</replaceable>< <replaceable>dump.sql</replaceable></userinput> </programlisting> - - <para> - 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. - </para> <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. </para> </section> @@ -479,14 +501,17 @@ <title>Exporting From MySQL</title> <para> - The most common way of exporting a file in text format is by using - the <option>--tab</option> option and specifying a target - directory. Using the short form, <literal>-T</literal>, this is - done in the following way: + 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> option and specifying a target directory. + Use the short form, <literal>-T</literal>, to do this in the + following way: </para> <programlisting> -shell> <userinput>mysqldump -u <replaceable>username</replaceable> -p<replaceable> db_name</replaceable> -T <replaceable>/tmp</replaceable></userinput> +shell> <userinput>mysqldump -u <replaceable>username</replaceable> -p <replaceable>db_name</replaceable> -T <replaceable>/tmp</replaceable></userinput> </programlisting> <para> @@ -494,11 +519,11 @@ the table structure using the table name and the extension <literal>sql</literal> and a tab-separated file of the data using the table name and the extension <literal>txt</literal>. These - files are created in the directory specified immediately following - the <option>-T</option> option This directory must be writable and - the user indicated by <replaceable>username</replaceable> must - have the <literal>FILE</literal> privilege. For more information - about the <literal>FILE</literal> privilege see ... + files are created in the directory specified with the + <option>-T</option> option This directory must be writable and the + user indicated by <replaceable>username</replaceable> must have + the <literal>FILE</literal> privilege. For more information about + the <literal>FILE</literal> privilege see ... <remark> Add xref to FILE here @@ -524,40 +549,65 @@ 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 «<literal>,</literal>» as + example you can specify a ‘<literal>,</literal>’ 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. To further refine the data - selected, the <option>--tab</option> option can also be used be - used to select data from one table only and can also be used with - the <option>--where</option> option. + <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> For an XML representation of the data and structure of a database use the <option>--xml</option> option. This option creates an XML document in the following format: </para> <programlisting> -<?xml version="1.0"?> -<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> - <database name="resources"> - <table_structure name="specific_link"> - <field Field="id" Type="int(11)" Null="YES" Key="" Extra="" /> - <field Field="url" Type="varchar(255)" Null="YES" Key="" Extra="" /> - <field Field="copy" Type="longtext" Null="YES" Key="" Extra="" /> - <options Name="specific_link" Engine="MyISAM" Version="10" Row_format="Dynamic" Rows="0" Avg_row_length="0" Data_length="0" Max_data_length="281474976710655" Index_length="1024" Data_free="0" Create_time="2006-12-21 17:06:32" Update_time="2006-12-21 17:06:32" Collation="latin1_swedish_ci" Create_options="" Comment="" /> + <?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="specific_link"> - ... - </table_data> - </database> -</mysqldump> + <table_data name="actor"> + [table data] ... + </table_data> + [more table definitions and data] ... + </database> + </mysqldump> </programlisting> <para> @@ -581,8 +631,8 @@ 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 is beyond the scope of the - discussion here. For more information on this topic see + 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>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r5841 - trunk/userguide | plavin | 11 Apr |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.657 sec. using MySQL 5.1.14-beta-log