"Pick" MySQL backup restore Database mysqldump instance and parameters detailed description

Original http://www.cnblogs.com/xuejie/archive/2013/01/11/2856911.html

We will certainly encounter the backup database in the course of running the project, restore the database situation, we generally use two ways to deal with:

1. Import and export backup data using into outfile and load data infile

The advantage of this method is that the exported data can be self-defined format, and the export is pure data, there is no table information, you can directly import another database with the different tables, compared to mysqldump more flexible.

Let's take a look at the following example:

(1) The following MySQL command exports the data from the MyTable table of Select to the/home/db_bak2012 file.

SELECT * FROM MyTable where status!=0 and name!= ' to outfile '/home/db_bak2012 ' fields terminated by ' | ' enclosed by ' ' Lines terminated by ' \ r \ n ';

To import the data you just backed up, you can use the load file method, the following MySQL command, to import the exported data into the Mytable_bak table:

Load data infile '/home/db_bak2012 ' into table Mytable_bakfields terminated by ' | ' enclosed by ' ' lines terminated by ' \ r \ n ';

2. Exporting a fixed-condition database using mysqldump

Let's look at a few common use cases:
(1) Export the entire database

Mysqldump-u user name-p database name > exported filename mysqldump-u wcnc-p smgp_apps_wcnc > Wcnc.sql

(2) Export a table

Mysqldump-u user name-P database name table name > exported filename mysqldump-u wcnc-p smgp_apps_wcnc users> wcnc_users.sql

(3) Export a database structure

(4) Import database, common Source command

#进入mysql数据库控制台, mysql-u root-p mysql>use Database
Mysql>set names UTF8; (First confirm the encoding, if not set may be garbled, note is not UTF-8) #然后使用source命令, the following parameters are script files (as used here. sql) Mysql>source D:\wcnc_db.sql

The above example is only the most basic, sometimes we may need to batch export multiple libraries, we can add--databases or-B, the following statement:

Mysqldump  -uroot-p--databases test MySQL #空格分隔

There are times when we may need to back up all the libraries in the database, we can use-all-databases, the following statement:

Mysqldump  -uroot-p-all-databases

Maybe we will have more demand, below is I find on the Internet the feeling of the more complete parameter description, posted out for your reference.

Parameter description

--all-databases,-A

Export all databases.

Mysqldump-uroot-p--all-databases

--all-tablespaces, Y

Export all table spaces.

Mysqldump-uroot-p--all-databases--all-tablespaces

--no-tablespaces, Y

No table space information is exported.

Mysqldump-uroot-p--all-databases--no-tablespaces

--add-drop-database

Add drop DATABASE statements before each database is created.

Mysqldump-uroot-p--all-databases--add-drop-database

--add-drop-table

Add Drop data table statements before each data table is created. (The default is on, using the--skip-add-drop-table cancel option)

Mysqldump-uroot-p--all-databases (Add drop statement by default)

Mysqldump-uroot-p--all-databases–skip-add-drop-table (Cancel drop statement)

--add-locks

Add lock tables before each table is exported and then unlock table. (The default is on, using the--skip-add-locks cancel option)

Mysqldump-uroot-p--all-databases (default add lock statement)

Mysqldump-uroot-p--all-databases–skip-add-locks (Cancel lock statement)

--allow-keywords

Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.

Mysqldump-uroot-p--all-databases--allow-keywords

--apply-slave-statements

Add ' STOP SLAVE ' before ' Change MASTER ' and add ' START SLAVE ' at the end of the export.

Mysqldump-uroot-p--all-databases--apply-slave-statements

--character-sets-dir

Directory of Character Set files

Mysqldump-uroot-p--all-databases--character-sets-dir=/usr/local/mysql/share/mysql/charsets

--comments

Additional comment information. The default is open, you can cancel with--skip-comments

Mysqldump-uroot-p--all-databases (Default record comment)

Mysqldump-uroot-p--all-databases--skip-comments (uncomment)

--compatible

The exported data will be compatible with other databases or older versions of MySQL. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. ，

To use several values, separate them with commas. It is not guaranteed to be fully compatible, but is as compatible as possible.

Mysqldump-uroot-p--all-databases--compatible=ansi

--compact

Export less output information (for debugging). Remove the structure of annotations and Kinsoku. Available options:--skip-add-drop-table--skip-add-locks--skip-comments--skip-disable-keys

Mysqldump-uroot-p--all-databases--compact

--complete-insert,-C

Use the full INSERT statement (containing the column name). This can improve insertion efficiency, but may be affected by the Max_allowed_packet parameter, causing the insert to fail.

Mysqldump-uroot-p--all-databases--complete-insert

--compress,-C

Enable compression for all information between client and server

Mysqldump-uroot-p--all-databases--compress

--create-options,-A

Include all MySQL feature options in the CREATE TABLE statement. (Open by default)

Mysqldump-uroot-p--all-databases

--databases,-B

Export several databases. All name parameters after the parameter are treated as database names.

Mysqldump-uroot-p--databases Test MySQL

--debug

Output debug information for debugging. The default value is: D:t:o,/tmp/mysqldump.trace

Mysqldump-uroot-p--all-databases--debug

Mysqldump-uroot-p--all-databases--debug= "D:t:o,/tmp/debug.trace"

--debug-check

Check the memory and open the file usage instructions and exit.

Mysqldump-uroot-p--all-databases--debug-check

--debug-info

Output debug information and exit

Mysqldump-uroot-p--all-databases--debug-info

--default-character-set

Set the default character set, the default value is UTF8

Mysqldump-uroot-p--all-databases--default-character-set=latin1

--delayed-insert

Export data with a time-lapse insert (insert DELAYED)

Mysqldump-uroot-p--all-databases--delayed-insert

--delete-master-logs

Delete log after master backup. This parameter will automatically activate the--master-data.

Mysqldump-uroot-p--all-databases--delete-master-logs

--disable-keys

For each table, use/*!40000 ALTER table tbl_name DISABLE keys */; and/*!40000 ALTER TABLE tbl_name ENABLE KEYS */; Statements reference INSERT statements. This allows you to import the dump file faster because it creates an index after inserting all the rows. This option is only suitable for MyISAM tables, which are turned on by default.

Mysqldump-uroot-p--all-databases

--dump-slave

This option causes the primary binlog location and file name to be appended to the exported data file. When set to 1 o'clock, the Change Master command is output to the data file and set to 2 o'clock to add descriptive information before the command. This option will open--lock-all-tables unless--single-transaction is specified. This option automatically turns off the--lock-tables option. The default value is 0.

Mysqldump-uroot-p--all-databases--dump-slave=1

Mysqldump-uroot-p--all-databases--dump-slave=2

--events,-E

Export events.

Mysqldump-uroot-p--all-databases--events

--extended-insert,-E

Use the Insert syntax with multiple values columns. This makes the export file smaller and speeds up the import. The default is on, using the--skip-extended-insert cancel option.

Mysqldump-uroot-p--all-databases

Mysqldump-uroot-p--all-databases--skip-extended-insert (cancel option)

--fields-terminated-by

The given field is ignored in the export file. Used with the--tab option and cannot be used for--databases and--all-databases options

Mysqldump-uroot-p test test--tab= "/home/mysql"--fields-terminated-by= "#"

--fields-enclosed-by

Each field in the output file is wrapped with a given character. Used with the--tab option and cannot be used for--databases and--all-databases options

Mysqldump-uroot-p test test--tab= "/home/mysql"--fields-enclosed-by= "#"

--fields-optionally-enclosed-by

Each field in the output file is optionally wrapped with a given character. Used with the--tab option and cannot be used for--databases and--all-databases options

Mysqldump-uroot-p test test--tab= "/home/mysql"--fields-enclosed-by= "#"--fields-optionally-enclosed-by = "#"

--fields-escaped-by

Each field in the output file ignores the given character. Used with the--tab option and cannot be used for--databases and--all-databases options

mysqldump-uroot-p mysql user--tab= "/home/mysql"--fields-escaped-by= "#"

--flush-logs

Refreshes the log before starting the export.

Note: If you export more than one database at a time (using option--databases or--all-databases), the log will be refreshed on a per-database basis. In addition to using--lock-all-tables or--master-data. In this case, the log will be refreshed once, so the table is locked at the same time. Therefore, if you intend to export and refresh the logs at the same time, you should use--lock-all-tables or--master-data and--flush-logs.

Mysqldump-uroot-p--all-databases--flush-logs

--flush-privileges

After exporting the MySQL database, issue a flush privileges statement. For proper recovery, this option should be used at any time to export the MySQL database and rely on MySQL database data.

Mysqldump-uroot-p--all-databases--flush-privileges

--force

Ignores the SQL errors that occur during the export process.

Mysqldump-uroot-p--all-databases--force

--help

Displays help information and exits.

Mysqldump--help

--hex-blob

Export binary string fields using hexadecimal format. This option must be used if you have binary data. The field types affected are binary, VARBINARY, BLOB.

Mysqldump-uroot-p--all-databases--hex-blob

--host,-H

Host information that needs to be exported

Mysqldump-uroot-p--host=localhost--all-databases

--ignore-table

The specified table is not exported. Specifies that when multiple tables are ignored, you need to repeat them multiple times, one table at a time. Each table must specify both the database and the table name. For example:--ignore-table=database.table1--ignore-table=database.table2 ...

Mysqldump-uroot-p--host=localhost--all-databases--ignore-table=mysql.user

--include-master-host-port

The ' Change MASTER ' generated in--dump-slave Add ' master_host=

Mysqldump-uroot-p--host=localhost--all-databases--include-master-host-port

--insert-ignore

Use the Insert Ignore statement when inserting rows.

Mysqldump-uroot-p--host=localhost--all-databases--insert-ignore

--lines-terminated-by

Each line of the output file is divided by the given string. Used with the--tab option and cannot be used for--databases and--all-databases options.

Mysqldump-uroot-p--host=localhost test test--tab= "/tmp/mysql"--lines-terminated-by= "# #"

--lock-all-tables,-X

The commit request locks all tables in all databases to ensure data consistency. This is a global read lock, and the--single-transaction and--lock-tables options are turned off automatically.

Mysqldump-uroot-p--host=localhost--all-databases--lock-all-tables

--lock-tables,-l

Locks all tables before starting the export. Lock the table with read local to allow the MyISAM table to be inserted in parallel. Tables that support transactions, such as InnoDB and bdb,--single-transaction, are a better choice because they do not need to lock the table at all.

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

Mysqldump-uroot-p--host=localhost--all-databases--lock-tables

--log-error

Attaching warnings and error messages to a given file

Mysqldump-uroot-p--host=localhost--all-databases--log-error=/tmp/mysqldump_error_log.err

--master-data

This option appends the location and file name of the Binlog to the output file. If 1, the Change Master command is output, and if 2, the output is added with comment information before the change Master command. This option turns on the--lock-all-tables option unless--single-transaction is also specified (in this case, the global read lock gets a short time when the export is started; the other contents refer to the--single-transaction option below). This option automatically turns off the--lock-tables option.

Mysqldump-uroot-p--host=localhost--all-databases--master-data=1;

Mysqldump-uroot-p--host=localhost--all-databases--master-data=2;

--max_allowed_packet

The maximum packet length that the server sends and receives.

Mysqldump-uroot-p--host=localhost--all-databases--max_allowed_packet=10240

--net_buffer_length

The cache size for TCP/IP and socket connections.

Mysqldump-uroot-p--host=localhost--all-databases--net_buffer_length=1024

--no-autocommit

Wrap the table with the Autocommit/commit statement.

Mysqldump-uroot-p--host=localhost--all-databases--no-autocommit

--no-create-db,-N

Only the data is exported and the Create DATABASE statement is not added.

Mysqldump-uroot-p--host=localhost--all-databases--no-create-db

--no-create-info,-t

Exports only data without adding a CREATE TABLE statement.

Mysqldump-uroot-p--host=localhost--all-databases--no-create-info

--no-data,-D

No data is exported, only the database table structure is exported.

Mysqldump-uroot-p--host=localhost--all-databases--no-data

--no-set-names,-N

Equivalent to--skip-set-charset

Mysqldump-uroot-p--host=localhost--all-databases--no-set-names

--opt

Equivalent to--add-drop-table,--add-locks,--create-options,--quick,--extended-insert,--lock-tables,--set-charset,-- Disable-keys This option is turned on by default and can be disabled with--skip-opt.

Mysqldump-uroot-p--host=localhost--all-databases--opt

--order-by-primary

If there is a primary key, or the first unique key, the records for each table are sorted. Valid when exporting the MyISAM table to the InnoDB table, but it can make the export work a long time.

Mysqldump-uroot-p--host=localhost--all-databases--order-by-primary

--password,-P

Connect Database Password

--pipe (Windows system available )

Using Named pipes to connect to MySQL

Mysqldump-uroot-p--host=localhost--all-databases--pipe

--port,-P

Connection Database Port number

--protocol

The connection protocols used include: TCP, Socket, pipe, memory.

Mysqldump-uroot-p--host=localhost--all-databases--protocol=tcp

--quick,-Q

Non-buffered queries are exported directly to standard output. The default is on, and you use--skip-quick to cancel the option.

Mysqldump-uroot-p--host=localhost--all-databases

Mysqldump-uroot-p--host=localhost--all-databases--skip-quick

--quote-names,-q

Use (') to cause table and column names. The default is on, and you use--skip-quote-names to cancel the option.

Mysqldump-uroot-p--host=localhost--all-databases

Mysqldump-uroot-p--host=localhost--all-databases--skip-quote-names

--replace

Replace insert into with replace into.

Mysqldump-uroot-p--host=localhost--all-databases--replace

--result-file,-R

Output directly to the specified file. This option should be used on systems that use carriage return pairs (\\r\\n) for line breaks (for example: dos,windows). This option ensures that only one row is used.

Mysqldump-uroot-p--host=localhost--all-databases--result-file=/tmp/mysqldump_result_file.txt

--routines,-R

Export stored procedures and custom functions.

Mysqldump-uroot-p--host=localhost--all-databases--routines

--set-charset

Add ' SET NAMES default_character_set ' to the output file. The default is on, with the--skip-set-charset off option.

Mysqldump-uroot-p--host=localhost--all-databases

Mysqldump-uroot-p--host=localhost--all-databases--skip-set-charset

--single-transaction

This option submits a begin SQL statement before exporting the data, and begin does not block any applications and ensures consistent state of the database at the time of export. It only works with multiple versions of the storage engine, only InnoDB. This option and the--lock-tables option are mutually exclusive because lock tables causes any pending transactions to be implicitly committed. To export large tables, you should use the--quick option together.

Mysqldump-uroot-p--host=localhost--all-databases--single-transaction

--dump-date

Add the export time to the output file. The default is on, with the--skip-dump-date off option.

Mysqldump-uroot-p--host=localhost--all-databases

Mysqldump-uroot-p--host=localhost--all-databases--skip-dump-date

--skip-opt

Disables the –opt option.

Mysqldump-uroot-p--host=localhost--all-databases--skip-opt

--socket,-s

Specifies the location of the socket file that connects to MySQL, the default path/tmp/mysql.sock

Mysqldump-uroot-p--host=localhost--all-databases--socket=/tmp/mysqld.sock

--tab,-t

Creates a tab-separated text file for each table at the given path. Note: only for mysqldump and MYSQLD servers running on the same machine.

Mysqldump-uroot-p--host=localhost test test--tab= "/home/mysql"

--tables

Override the--databases (-B) parameter to specify the name of the table you want to export.

Mysqldump-uroot-p--host=localhost--databases Test--tables test

--triggers

Export the trigger. This option is enabled by default and is disabled with--skip-triggers.

Mysqldump-uroot-p--host=localhost--all-databases--triggers

--tz-utc

Set the time zone time_zone= ' +00:00 ' at the top of the export to ensure that timestamp data that is exported in different time zones or when data is moved in another time zone is correct.

Mysqldump-uroot-p--host=localhost--all-databases--TZ-UTC

--user,-U

Specifies the user name of the connection.

--verbose,--v

Output multiple platform information.

--version,-V

Output mysqldump version information and exit

--where,-W

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.

Mysqldump-uroot-p--host=localhost--all-databases--where= "user= ' root '"

--xml,-X

Export the XML format.

Mysqldump-uroot-p--host=localhost--all-databases--xml

--plugin_dir

The directory of the client plug-in for compatibility with different plug-in versions.

Mysqldump-uroot-p--host=localhost--all-databases--plugin_dir= "/usr/local/lib/plugin"

--default_auth

The client plug-in uses permissions by default.

Mysqldump-uroot-p--host=localhost--all-databases--default-auth= "/usr/local/lib/plugin/<plugin>"

"Pick" MySQL backup restore Database mysqldump instance and parameters detailed description