MySQL mysqldump Data Export detailed

Transfer from: http://www.cnblogs.com/chenmh/p/5300370.html introduction

In daily maintenance work often need to export the data, and mysqldump is to export the data process is a very frequent use of a tool, it comes with a lot of function parameters, the article will list some common operations, at the end of the article will be all the parameters detailed list.

Grammar:

Export without parameters, the export text content is probably as follows: CREATE DATABASE Judgment Statement-delete table-CREATE table-Lock table-Disable index-insert data-enable index-unlock table.

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3 ...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
Insert test data

CREATE DATABASE db1 DEFAULT CHARSET utf8;
USE db1;
CREATE TABLE a1 (id int);
insert into a1 () values (1), (2);
CREATE TABLE a2 (id int);
insert into a2 () values (2);
CREATE TABLE a3 (id int);
insert into a3 () values (3);


CREATE DATABASE db2 DEFAULT CHARSET utf8;
USE db2;
CREATE TABLE b1 (id int);
insert into b1 () values (1);
CREATE TABLE b2 (id int);
insert into b2 () values (2);
 

1. Export all databases

This command will export all databases including the system database

mysqldump -uroot -proot --all-databases> /tmp/all.sql
2. Export all data of db1, db2 two databases

mysqldump -uroot -proot --databases db1 db2> /tmp/user.sql
3. Export the a1 and a2 tables in db1

Note that the export specified table can only be exported for one database, and the content of the export is different from the exported database. There is no judgment statement to create a database in the export text of the export specified table, only the delete table-create table-import data

mysqldump -uroot -proot --databases db1 --tables a1 a2> /tmp/db1.sql
4. Conditional export, export data with id = 1 in db1 table a1

If multiple tables have the same conditions, you can export multiple tables at once

Field is plastic

mysqldump -uroot -proot --databases db1 --tables a1 --where = ‘id = 1‘> /tmp/a1.sql
The field is a string, and the exported sql does not contain drop table, create table

mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where = "id =‘ a ‘"> /tmp/a1.sql
5. Generate a new binlog file, -F

Sometimes you want to generate a new binlog file after exporting the data, just add the -F parameter

mysqldump -uroot -proot --databases db1 -F> /tmp/db1.sql
6. Only export the table structure without exporting data, --no-data

mysqldump -uroot -proot --no-data --databases db1> /tmp/db1.sql
7. Export and import data across servers

mysqldump --host = h1 -uroot -proot --databases db1mysql--host = h2 -uroot -proot db2
Import all data from the db1 database in the h1 server to the db2 database in h2. The db2 database must exist or an error will be reported

mysqldump --host = 192.168.80.137 -uroot -proot -C --databases test mysql --host = 192.168.80.133 -uroot -proot test
 Adding the -C parameter enables compression delivery.

8. Add the binlog location and file name of the main library to the exported data file, --dump-slave

Note: --dump-slave command If the current server is a slave server, use this command to execute a stop slave to obtain the master binlog file and location. After the backup is complete, it will automatically execute start slave to start the slave server. However, if it is a large amount of data backup, the delay between the slave and the master will become larger. The only thing that you can get with --dump-slave is the current binglog of the slave server data (relay_mater_log_file, exec_master_log_pos ), Instead of the current binlog execution position of the master server, mainly depends on the data delay of the master and slave.

This parameter is executed on the slave server, which is equivalent to executing show slave status. When set to 1, it will be output to the data file with the CHANGE MASTER command; when set to 2, a comment will be added before the change.

This option will turn on --lock-all-tables unless --single-transaction is specified.

After the execution is finished, the --lock-tables option will be automatically closed. --dump-slave is 1 by default

mysqldump -uroot -proot --dump-slave = 1 --databases db1> /tmp/db1.sql
mysqldump -uroot -proot --dump-slave = 2 --database db1> /tmp/db1.sql
9. Append the current server's binlog location and file name to the output file, --master-data

This parameter is the same as the --dump-slave method, except that it records the binlog of the current server, which is equivalent to the value of executing show master status and file status.

Note: --master-data will not stop the master-slave service of the current server

10--opt

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

mysqldump -uroot -p --host = localhost --all-databases --opt
11 Ensure the consistent state of the export-single-transaction

This option submits a BEGIN SQL statement before exporting the data. BEGIN will not block any applications and can guarantee the consistent state of the database during export. It only applies to multi-version storage engines (it does not show locking to compare data by judging version), only InnoDB. This option and the --lock-tables option are mutually exclusive, because LOCK TABLES will implicitly commit any pending transactions. To export large tables, the --quick option should be used together.

--quick, -q
Without buffering the query, export directly to standard output. The default is on, use --skip-quick to cancel this option.
12--lock-tables, -l

Before starting the export, lock all tables. Lock the table with READ LOCAL to allow MyISAM tables to be inserted in parallel. For tables that support transactions such as InnoDB and BDB, --single-transaction is a better choice because it does not require locking tables at all.

Please note that when exporting multiple databases, --lock-tables locks the tables for each database separately. Therefore, this option cannot guarantee the logical consistency of the tables in the exported file between databases. The export status of different database tables can be completely different.

13 Export stored procedures and custom functions --routines, -R

mysqldump -uroot -p --host = localhost --all-databases --routines
14 Compressed backup

Compressed backup
mysqldump -uroot -proot --databases abc 2> / dev / nullgzip> /abc.sql.gz
reduction
gunzip -c abc.sql.gz | mysql -uroot -proot abc
 

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
Does not export any table space information.
mysqldump -uroot -p --all-databases --no-tablespaces
--add-drop-database
Add a drop database statement before each database is created.
mysqldump -uroot -p --all-databases --add-drop-database
--add-drop-table
Add a drop data table statement before each data table is created. (The default is on, use --skip-add-drop-table to cancel the option)
mysqldump -uroot -p --all-databases (drop statement is added by default)
mysqldump -uroot -p --all-databases --skip-add-drop-table (cancel drop statement)
--add-locks
Add LOCK TABLES before each table export and UNLOCK TABLE afterwards. (The default is on, use --skip-add-locks to cancel the option)
mysqldump -uroot -p --all-databases (LOCK statement is added by default)
mysqldump -uroot -p --all-databases --skip-add-locks (cancel LOCK statement)
--allow-keywords
Allow creation of column names that are keywords. This is done by prefixing the table name to each column name.
mysqldump -uroot -p --all-databases --allow-keywords
--apply-slave-statements
Add ‘STOP SLAVE’ before ‘CHANGE MASTER’ and ‘START SLAVE’ at the end of export
mysqldump -uroot -p --all-databases --apply-slave-statements
--character-sets-dir
Character set file directory
mysqldump -uroot -p --all-databases --character-sets-dir = / usr / local / mysql / share / mysql / charsets
--comments
Additional annotation information. The default is open, you can use --skip-comments to cancel
mysqldump -uroot -p --all-databases (default logging 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 does not guarantee complete compatibility, but as much compatibility as possible.
mysqldump -uroot -p --all-databases --compatible = ansi
--compact
Export less output information (for debugging). Remove comments and head and tail structures. Available options: --skip-add-drop-table --skip-add-locks --skip-comments --skip-dis
able-keys
mysqldump -uroot -p --all-databases --compact
--complete-insert, -c
Use the complete insert statement (including column names). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter and cause the insertion to fail.
mysqldump -uroot -p --all-databases --complete-insert
--compress, -C
Enable compression to pass 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. (Default is on)
mysqldump -uroot -p --all-databases
--databases, -B
Export several databases. All name parameters after the parameter are regarded as database names.
mysqldump -uroot -p --databases test mysql
--debug
Output debug information for debugging. The default value is: d: t, / tmp / mysqldump.trace
mysqldump -uroot -p --all-databases --debug
mysqldump -uroot -p --all-databases --debug = ”d: t, / tmp / debug.trace”
--debug-check
Check memory and open file instructions and exit.
mysqldump -uroot -p --all-databases --debug-check
--debug-info
Output debugging 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 = utf8
--delayed-insert
Export data using INSERT DELAYED
mysqldump -uroot -p --all-databases --delayed-insert
--delete-master-logs
Delete logs after master backup. This parameter will automatically activate --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 to refer to the INSERT statement. This can import the dumped file faster because it creates the index after inserting all rows. This option is only suitable for MyISAM tables, and is open by default.
mysqldump -uroot -p --all-databases
--dump-slave
This option appends the main binlog location and file name to the exported data file (show slave status). When set to 1, the CHANGE MASTER command will be output to the data file; when set to 2, a comment will be added before the change. This option will turn on --lock-all-tables unless --single-transaction is specified. This option will automatically turn 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
--master-data
This option appends the location and file name of the current server's binlog to the output file (show master status). If it is 1, the CHANGE MASTER command will be output; if it is 2, the comment information will be added before the output CHANGE MASTER command. This option will turn on the --lock-all-tables option unless --single-transaction is also specified (in this case, the global read lock is acquired for a short time when the export begins; for other contents, refer to --single -transaction option). 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;
--events, -E
Export events.
mysqldump -uroot -p --all-databases --events
--extended-insert, -e
Use INSERT syntax with multiple VALUES columns. This makes the export file smaller and speeds up the import. The default is on, use --skip-extended-insert to cancel the 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 with the --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 the given characters. Used with the --tab option and cannot be used with the --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 selectively wrapped with the given characters. Used with the --tab option and cannot be used with the --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 with the --databases and --all-databases options
mysqldump -uroot -p mysql user --tab = ”/ home / mysql” --fields-escaped-by = ”#”
--flush-logs
Refresh the log before starting the export.
Please note: If you export multiple databases at a time (using the options --databases or --all-databases), the logs will be refreshed database by database. In addition to using --lock-all-tables or --master-data. In this case, the log will be refreshed once, and the corresponding table will be locked at the same time. Therefore, if you plan to export and refresh 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 whenever exporting mysql database and relying on mysql database data.
mysqldump -uroot -p --all-databases --flush-privileges
--force
Ignore SQL errors during the export process.
mysqldump -uroot -p --all-databases --force
--help
Display help information and exit.
mysqldump --help
--hex-blob
Export binary string fields using hexadecimal format. If you have binary data, you must use this option. The field types affected are BINARY, VARBINARY, and BLOB.
mysqldump -uroot -p --all-databases --hex-blob
--host, -h
Host information to be exported
mysqldump -uroot -p --host = localhost --all-databases
--ignore-table
The specified table is not exported. When ignoring multiple tables, you need to repeat 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
Add ‘MASTER_HOST = <host>, MASTER_PORT = <port>’ to the ‘CHANGE MASTER TO ..’ statement generated by --dump-slave
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 character string. Used with the --tab option and cannot be used with the --databases and --all-databases options.
mysqldump -uroot -p --host = localhost test test --tab = ”/ tmp / mysql” --lines-terminated-by = ”##”
--lock-all-tables, -x
Submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock, and the --single-transaction and --lock-tables options are automatically turned off.
mysqldump -uroot -p --host = localhost --all-databases --lock-all-tables
--lock-tables, -l
Before starting the export, lock all tables. Lock the table with READ LOCAL to allow MyISAM tables to be inserted in parallel. For tables that support transactions such as InnoDB and BDB, --single-transaction is a better choice because it does not require locking tables at all.
Please note that when exporting multiple databases, --lock-tables locks the tables for each database separately. Therefore, this option cannot guarantee the logical consistency of the tables in the exported file between databases. The export status of different database tables can be completely different.
mysqldump -uroot -p --host = localhost --all-databases --lock-tables
--log-error
Append warning and error messages to the given file
mysqldump -uroot -p --host = localhost --all-databases --log-error = / tmp / mysqldump_error_log.err
--max_allowed_packet
The maximum packet length sent and received by the server.
mysqldump -uroot -p --host = localhost --all-databases --max_allowed_packet = 10240
--net_buffer_length
The buffer size of TCP / IP and socket connections.
mysqldump -uroot -p --host = localhost --all-databases --net_buffer_length = 1024
--no-autocommit
Use autocommit / commit statements to wrap the table.
mysqldump -uroot -p --host = localhost --all-databases --no-autocommit
--no-create-db, -n
Only export data without adding CREATE DATABASE statements.
mysqldump -uroot -p --host = localhost --all-databases --no-create-db
--no-create-info, -t
Only export the data without adding the 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 The option is enabled 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, sort the records of each table. It is effective when exporting MyISAM tables to InnoDB tables, but will make the export work take a long time.
mysqldump -uroot -p --host = localhost --all-databases --order-by-primary
--password, -p
Password for connecting to the database
--pipe (available for windows systems)
Connect MySQL with named pipes
mysqldump -uroot -p --host = localhost --all-databases --pipe
--port, -P
Connect to the database port number
--protocol
The connection protocols used include: tcp, socket, pipe, memory.
mysqldump -uroot -p --host = localhost --all-databases --protocol = tcp
--quick, -q
Without buffering the query, export directly to standard output. The default is on, use --skip-quick to cancel this 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, use --skip-quote-names to cancel this option.
mysqldump -uroot -p --host = localhost --all-databases
mysqldump -uroot -p --host = localhost --all-databases --skip-quote-names
--replace
Use REPLACE INTO instead of INSERT INTO.
mysqldump -uroot -p --host = localhost --all-databases --replace
--result-file, -r
Direct output to the specified file. This option should be used on systems that use carriage return and line feed pairs (\\ r \\ n) for line breaks (eg DOS, Windows). This option ensures that only one line 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, use --skip-set-charset to turn off the 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. BEGIN will not block any applications and can guarantee the consistent state of the database during export. It only applies to multi-version storage engines, only InnoDB. This option and the --lock-tables option are mutually exclusive, because LOCK TABLES will implicitly commit any pending transactions. To export large tables, the --quick option should be used together.
mysqldump -uroot -p --host = localhost --all-databases --single-transaction
--dump-date
Add the export time to the output file. The default is on, use --skip-dump-date to turn off the option.
mysqldump -uroot -p --host = localhost --all-databases
mysqldump -uroot -p --host = localhost --all-databases --skip-dump-date
--skip-opt
Disable the --opt option.
mysqldump -uroot -p --host = localhost --all-databases --skip-opt
--socket, -S
Specify the socket file location to connect to mysql, the default path /tmp/mysql.sock
mysqldump -uroot -p --host = localhost --all-databases --socket = / tmp / mysqld.sock
--tab, -T
Create tab-separated text files at the given path for each table. Note: Only used for mysqldump and mysqld server running on the same machine. Note that using --tab cannot specify the --databases parameter
mysqldump -uroot -p --host = localhost test test --tab = "/ home / mysql"
--tables
Override the --databases (-B) parameter to specify the name of the table to be exported. In later versions, table will be used instead of tables.
mysqldump -uroot -p --host = localhost --databases test --tables test
--triggers
Export triggers. This option is enabled by default, use --skip-triggers to disable it.
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 the accuracy of the TIMESTAMP data exported in different time zones or the data is moved to other time zones.
mysqldump -uroot -p --host = localhost --all-databases --tz-utc
--user, -u
Specify the user name for the connection.
--verbose, --v
Output multiple platform information.
--version, -V
Output mysqldump version information and exit
--where, -w
Dump only the records selected by the given WHERE condition. Please note that if the condition contains spaces or characters dedicated to the command interpreter, be sure to quote the condition.
mysqldump -uroot -p --host = localhost --all-databases --where = "user =’ root ’"
--xml, -X
Export XML format.
mysqldump -uroot -p --host = localhost --all-databases --xml
--plugin_dir
The directory of client plug-ins, used 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>"
Error handling
1.unknown option ‘--no-beep’

The first method: delete the no-beep parameter under my.ini [client];
The second method: add --no-defaults parameter after mysqldump.
 

to sum up
The article lists some common export operations, and there are many other parameters that are often used, including "--add-drop-database", "--apply-slave-statements", "--triggers", etc. The client's import and export functions are also good choices, such as workbench and navicat; among them, navicat's export wizard can have many file formats to choose from.

--tab fast export and import data is a good method, it will generate a sql table structure file and a text data file in the specified directory

 

Detailed explanation of MySQL mysqldump data export