mysqldump command Import and Export database method and instance summary

Usage of the mysqldump command

1. Export all libraries

System command Line

Mysqldump-uusername-ppassword--all-databases > All.sql

2. Import all libraries

MySQL command line

Mysql>source All.sql;

3. Export some libraries

System command Line

Mysqldump-uusername-ppassword--databases db1 DB2 > Db1db2.sql

4. Import some libraries

MySQL command line

Mysql>source Db1db2.sql;

5. Import a library

System command Line

Mysql-uusername-ppassword DB1 < Db1.sql;

or MySQL command line

Mysql>source Db1.sql;

6. Export some data tables

System command Line

Mysqldump-uusername-ppassword db1 table1 table2 > Tb1tb2.sql

7. Import some data tables

System command Line

Mysql-uusername-ppassword DB1 < Tb1tb2.sql

or MySQL command line

Mysql>

User db1;

SOURCE Tb1tb2.sql;

8. mysqldump Character Set

Mysqldump-uusername-ppassword--default-character-set=gb2312 DB1 table1 > Tb1.sql

The mysqldump client can be used to dump a database or collect a database for backup or to transfer data to another SQL Server (not necessarily a MySQL server). The dump contains SQL statements that create tables and/or Mount tables.

If you are backing up on the server and the tables are MyISAM tables, you should consider using mysqlhotcopy because you can make backups and restores faster.

There are 3 ways to call Mysqldump:

Shell> mysqldump [Options] db_name [tables]

Shell> mysqldump [Options]---database db1 [DB2 db3 ...]

Shell> mysqldump [Options]--all--database

If no tables are specified or if the---database or--all--database option is used, the entire database is dumped.

To get your version of the mysqldump support option, perform mysqldump---help.

If you run mysqldump without the--quick or--opt option, mysqldump loads the entire result set into memory before dumping the results. Problems may occur if you dump a large database. This option is enabled by default, but can be disabled with--skip-opt.

If you use the latest version of the mysqldump program to generate a dump reload to an older version of the MySQL server, you should not use the--opt or-e option.

Mysqldump supports the following options:

---help,-?

Displays the help message and exits.

--add-drop--database

Add a drop DATABASE statement before each CREATE DATABASE statement.

--add-drop-tables

Add a drop TABLE statement before each CREATE TABLE statement.

--add-locking

Each table dump is referenced with the lock tables and unlock tables statements. Reload the dump file when it is inserted faster.

--all--database,-a

Dumps all tables in all databases. In the same way that you use the---database option, name all databases on the command line.

--allow-keywords

Allows you to create a keyword column name. The table name prefix should precede each column name.

---comments[={0|1}]

If set to 0, prevents other information in the dump file, such as the program version, server version, and host. The--skip-comments is the same as the result of the---comments=0. The default value is 1, which includes additional information.

--compact

Produces a small amount of output. This option disables annotations and enables--skip-add-drop-tables 、--no-set-names 、--skip-disable-keys and--skip-add-locking options.

--compatible=name

Produce output that is more compatible with other database systems or older MySQL servers. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, or no_field_options. To use several values, separate them with commas. These values have the same meaning as the corresponding options for setting the server SQL mode.

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently able to make the dump output more compatible. For example,--compatible=oracle does not map Oracle types or data types that use Oracle annotation syntax.

--complete-insert,-c

Use the complete INSERT statement that includes the column name.

--compress,-c

Compresses all information that is sent between the client and the server, if both support compression.

--create-option

Include all MySQL table options in the CREATE TABLE statement.

---database,-b

Dumps several databases. In general, mysqldump the 1th name parameter in the command line as the database name, followed by the name as the table name. With this option, it considers all the name parameters as database names. The CREATE database if not exists db_name and use db_name statements are included in the output before each new database.

---debug[=debug_options],-# [debug_options]

Write the debug log. The debug_options string is usually ' d:t:o,file_name '.

--default-character-set=charset

Use the Charsetas default character set. If not specified, Mysqldump uses UTF8.

--delayed-insert

Insert a row using the Insert delayed statement.

--delete-master-logs

On the primary replication server, delete the binary log after the dump operation is complete. This option automatically enables--master-data.

--disable-keys,-k

For each table, use/*!40000 ALTER TABLE tbl_name disable keys */; and/*!40000 ALTER TABLE tbl_name enable keys */; Statements reference INSERT statements. This will load the dump file faster because the index is created after all rows have been inserted. This option is only suitable for MyISAM tables.

--extended-insert,-e

Use the multi-line insert syntax that includes several values lists. This makes the dump file smaller and can speed up insertion when the file is overloaded.

--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by= ...,--line-terminated-by= ...

These options are used in conjunction with the-t option and have the same meaning as the corresponding clause in load data infile.

--first-slave,-x

Deprecated, now renamed to--lock-all-tables.

--flush-logs,-f

Flush the MySQL server log file before starting the dump. This option requires Reload permissions. Note If you use this option with the--all--database (or-a) option, the log is refreshed based on the database for each dump. The exception is when using--lock-all-tables or--master-data: In this case, the log is refreshed only once and refreshed after all tables have been locked. If you want to dump and refresh the log at the same time, you should use--flush-logs with--lock-all-tables or--master-data.

--force,-f

Even if a SQL error occurs during the table dump, it continues.

--host=host_name,-h host_name

Dumps data from a given host's MySQL server. The default host is localhost.

--hex-blob

Dumps binary string columns using hexadecimal notation (for example, ' abc ' becomes 0x616263). The columns affected are binary, varbinary, blob.

--lock-all-tables,-x

All tables in all databases are locked. Implemented through global read locks during the overall dump. This option automatically turns off--single-transaction and--lock-tables.

--lock-tables,-l

Locks all tables before starting the dump. Lock the table with read local to allow the MyISAM table to be inserted in parallel. For transactional tables such as InnoDB and bdb,--single-transaction is a better option because it does not need to lock the table at all.

Note that when you dump multiple databases, the--lock-tables locks the table for each database separately. Therefore, this option does not guarantee logical consistency between the tables in the dump file and the database. The dump state of different database tables can be completely different.

--master-data[=value]

This option writes the location and file name of the binary log to the output. This option requires Reload permissions, and binary logging must be enabled. If the option value equals 1, the location and file name are written to the dump output as the Change master statement, and if you use the SQL dump master server to set up from the server, start from the server from the correct location of the primary server binary log. If the option value equals 2,change The Master statement is written as a SQL comment. If value is omitted, this is the default action.

The--master-data option enables--lock-all-tables unless you also specify--single-transaction (in this case, a global read lock is obtained only for a short time when the dump is just started. See--single-transaction again. In either case, the log-related action occurs at the time of the dump. This option automatically turns off--lock-tables.

--no-create-db,-n

This option disables the CREATE database/*!32312 if not exists*/db_name statement, which is included in the output if the---database or--all--database option is given.

--no-create-info,-t

CREATE TABLE statements that recreate each dump table are not written.

--no-data,-d

Do not write any row information for the table. This is useful if you only want to dump the structure of the table.

--opt

This option is shorthand, equivalent to specifying--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables--quick-- Set-charset. It can give a quick dump operation and generate a dump file that can be loaded into the MySQL server quickly. This option is turned on by default, but can be disabled with--skip-opt. To disable only the options that are sure to be enabled with-opt, use the--skip form; For example,--skip-add-drop-tables or--skip-quick.

--password[=password],-p[password]

The password to use when connecting to the server. If you use the Short option form (-P), you cannot have a space between the options and the password. If the password value after the--password or-p option is ignored on the command line, you will be prompted to enter one.

--port=port_num,-p Port_num

The TCP/IP port number used for the connection.

--PROTOCOL={TCP | socket | pipe | memory}

The connection protocol used.

--quick,-q

This option is used to dump large tables. It forces mysqldump to retrieve rows from the server one row at a time instead of retrieving all the rows and caching it in memory before the output.

--quote-names,-q

Reference the database, table, and column names with the ' ' character. If the server SQL mode includes the Ansi_quotes option, use the ' "' character reference name. This option is enabled by default. can be disabled with--skip-quote-names, but this option should be followed by other options, such as--compatible that can enable--quote-names.

--result-file=file,-r file

Shifts the output to a given file. This option is applied in Windows because it prohibits the conversion of the new line ' \ n ' characters to ' \ r \ n ' carriage returns, return/new line sequence.

--routines,-r

Store stored procedures (functions and programs) in the staging database. The output produced using the---routines contains the CREATE procedure and create function statements to recreate the subroutine. However, these statements do not include attributes, such as subroutine definitions or the creation and modification of timestamps. This means that when you overload a subroutine, the person who defines it is set to overload the user, and the timestamp equals the reload time.

If you need to create a subroutine that uses the original definition and timestamp properties, do not use--routines. Instead, use a MySQL account with the appropriate MySQL database permissions to dump and reload the contents of the Mysql.proc table directly.

This option is added in MySQL 5.1.2. Before this, the stored program does not dump.

--set-charset

Add the Set names Default_character_set to the output. This option is enabled by default. To disable the set NAMES statement, use--skip-set-charset.

--single-transaction

This option issues a BEGIN SQL statement before dumping data from the server. It applies only to transaction tables, such as InnoDB and BDB, because then it dumps a consistent database state when it issues a begin without blocking any applications.

When you use this option, you should keep in mind that only the InnoDB table can be dumped in a consistent state. For example, the MyISAM or heap table of any dump can still change state when using this option.

The--single-transaction option and the--lock-tables option are mutually exclusive because the lock tables causes any pending transactions to be implicitly committed.

This option should be used in conjunction with--quick to dump large tables.

--socket=path,-s Path

The socket file used when connecting localhost (for the default host).

--skip--comments

See---The description of the comments option.

--tab=path,-t Path

Generates a tab-separated data file. For each dump table, Mysqldump creates a Tbl_name.sql file that contains the CREATE TABLE statement that created it, and a tbl_name.txt file that contains its data. The option value is the directory to which the file is written.

By default, the format of a. txt data file is to use the tab character between the column values and the new rows following each row. Formats can be clearly specified using the--fields-xxx and-line--xxx options.

Note: This option is only available when mysqldump is running on the same machine as the MYSQLD server. You must have file permissions, and the server must have a permission to write files in the directory you specify.

--tables

Overrides the---database or-b option. All parameters following the option are treated as table names.

--triggers

Dump triggers for each dump of the table. This option is enabled by default; disable it with--skip-triggers.

--tz-utc

Add set time_zone= ' +00:00 ' to the dump file so that the timestamp column can be dumped and overloaded between servers with different time zones. (without this option, the timestamp column is dumped and reloaded between the source server and the destination server with the local time zone). --TZ-UTC can also protect changes due to daylight saving time. --TZ-UTC is enabled by default. To disable it, use--SKIP-TZ-UTC. This option is added in MySQL 5.1.2.

--user=user_name,-u user_name

The MySQL user name to use when connecting to the server.

--verbose,-v

Verbose mode. Print out the details of the program action.

--version,-v

Displays the version information and exits.

--where= ' Where-condition ',-w ' where-condition '

Dumps only the records selected by the given where condition. Note that if the condition contains a special space or character for the command interpreter, you must refer to the condition.

For example:

"--where=user= ' JIMF '"

"-wuserid>1"

"-wuserid<1"

--xml,-x

Write the dump output as XML.

You can also set the following variables using the--var_name=value option:

Max_allowed_packet

Maximum size of the buffer for communication between client/server. The maximum is 1GB.

Net_buffer_length

The initial size of the buffer between the client/server communication. When you create a multiline INSERT statement (as with options--extended-insert or--opt), Mysqldump creates a row of length up to net_buffer_length. If you add this variable, you should also make sure that the net_buffer_length variable is at least as large in the MySQL server.

You can also use the--set-variable=var_name=value or-o var_name=value syntax to set variables. However, the use of this syntax is now deprecated.

Mysqldump is most commonly used to back up an entire database:

shell> mysqldump--opt db_name > Backup-file.sql

You can read the dump file back to the server in this way:

shell> MySQL db_name < backup-file.sql

or for:

shell> mysql-e "Source/path-to--backup/backup-file.sql" db_name

Mysqldump can also be used to mount a database when replicating data from one MySQL server to another server:

shell> mysqldump--opt db_name | MySQL--host=remote_host-c db_name

You can dump several databases with one command:

shell> mysqldump---database db_name1 [db_name2 ...] > My_databases.sql

If you want to dump all databases, use the--all--database option:

shell> mysqldump--all-databases > All_databases.sql

If the table is stored in the InnoDB storage engine, Mysqldump provides a way to back up online (see the command below). This backup requires a global read lock on all tables at the start of the dump (using flush tables with read lock). After the lock is acquired, the corresponding contents of the binary log are read and the lock is freed. So if and only when the flush is issued ... When a long UPDATE statement is being executed, the MySQL server stops until the long statement ends, and then the dump releases the lock. So if the MySQL server only receives short ("Short Execution Time") Update statements, even if there are a large number of statements, it will not notice the lock period.

shell> mysqldump--all-databases--single-transaction > All_databases.sql

For Point-to-point recovery (also known as "roll Forward", when you need to restore an old backup and replay subsequent changes to that backup), it is useful to loop the binary log or at least know the binary log content that corresponds to the dump:

shell> mysqldump--all-databases--master-data=2 > All_databases.sql

Or

shell> mysqldump--all-databases--flush-logs--master-data=2 > All_databases.sql

If the table is saved in the InnoDB storage engine, using both--master-data and--single-transaction provides a convenient way to make an online backup for Point-to-point recovery.

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://20145520.blog.51cto.com/9112173/1912069

mysqldump command Import and Export database method and instance summary