MySQL mysqldump Data Export detailed--dump-slave considerations

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]

Inserting 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 exports all databases, including the system database

Mysqldump-uroot-proot--all-databases >/tmp/all.sql

2. Export all data for DB1, DB2 two databases

Mysqldump-uroot-proot--databases db1 DB2 >/tmp/user.sql

3. Export the A1, A2 tables in the DB1

Note the export of the specified table can only be exported for one database, and the exported content is not the same as the export database, the export of the specified table does not have a judgment statement to create the database, only delete table-create table-Import data

Mysqldump-uroot-proot--databases db1--tables A1 A2  >/tmp/db1.sql

4. Conditional export, export data from DB1 table A1 id=1

Conditional export can only export a single table

Mysqldump-uroot-proot--databases db1--tables A1--where= ' id=1 '  >/tmp/a1.sql

5. Generate a new Binlog file,-F

Sometimes you will 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. Export only table structure does not export data,--no-data

Mysqldump-uroot-proot--no-data--databases db1 >/tmp/db1.sql

7. Export Import data across servers

Mysqldump--host=h1-uroot-proot--databases db1 |mysql--host=h2-uroot-proot DB2

Import all the data from the DB1 database in the H1 server into the DB2 database in H2, and the DB2 database must be present or the error will be

Add the-c parameter to enable compression delivery.

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

Note: The--dump-slave command if the current server is from the server then use this command to perform a stop slave to obtain the master Binlog files and locations, and then automatically perform a start slave boot from the server when the backup is complete. However, if a large amount of data backup will give a greater delay from and to the main, using--dump-slave to obtain only the current data from the server execution to the location of the main binglog is (relay_mater_log_file,exec_master_log_ POS), rather than the location of the current binlog execution of the primary server, is mainly dependent on the master-slave data delay.

This parameter is executed from the server, which is equivalent to executing show slave status. When set to 1 o'clock, it will be output to the data file with the Change Master command, and the setting to 2 o'clock will be preceded by a comment.

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

The--lock-tables option is turned off automatically when you finish executing. --dump-slave default is 1

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 location and file name of the current server's binlog to the output file,--master-data

This parameter is the same as the--dump-slave method, except that it is the binlog of the current server, which is equivalent to the value of executing show Master status, State (File,position).

Note:--master-data does not stop the current server's master-slave service

10--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

11 Guaranteed Export Consistency Status--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 applies to multi-version storage engine (it does not show lock by judging version to compare data), 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.

--quick,-Q non-buffered query, direct export to standard output. The default is on, and you use--skip-quick to cancel the option.

12--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.

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

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

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 drop database statements 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. (Default is on, use --skip-add-drop-table to cancel the 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 export and UNLOCK TABLE after. (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 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. On by default, can be canceled with --skip-comments
mysqldump -uroot -p --all-databases (default log comments)
mysqldump -uroot -p --all-databases --skip-comments (uncomment)
--compatible
The exported data will be compatible with other databases or older versions of MySQL. The value 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 to be as compatible as possible.
mysqldump -uroot -p --all-databases --compatible = ansi
--compact
Export less output (for debugging). Remove comments and head and tail structures. You can use options: --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
mysqldump -uroot -p --all-databases --compact
--complete-insert, -c
Use full insert statements (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 following the parameters are treated 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 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 = utf8
--delayed-insert
Use INSERT DELAYED to export data
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 the / *! 40000 ALTER TABLE tbl_name DISABLE KEYS * /; and / *! 40000 ALTER TABLE tbl_name ENABLE KEYS * /; statements to reference the INSERT statement. This makes it faster to import the dumped file because it creates the index after inserting all the rows. This option is only suitable for MyISAM tables, which is turned on by default.
mysqldump -uroot -p --all-databases
--dump-slave
This option appends the master's binlog location and file name to the exported data file (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. 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
--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 of the CHANGE MASTER command. This option turns on the --lock-all-tables option, unless --single-transaction is also specified (in this case, global read locks get a short time when starting the export; see --single below for everything -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 process. 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, 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, 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 optionally wrapped with the given characters. Used with the --tab option, 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
Individual fields in the output file ignore the given characters. Used with the --tab option, 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 option --databases or --all-databases), the logs will be refreshed one by one. Except using --lock-all-tables or --master-data. In this case, the log will be refreshed once, so all tables are locked at the same time. Therefore, if you plan to export and flush 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 to export the mysql database and depend on mysql database data anytime.
mysqldump -uroot -p --all-databases --flush-privileges
--force
Ignore SQL errors during export.
mysqldump -uroot -p --all-databases --force
--help
Display help information and exit.
mysqldump --help
--hex-blob
Exports a binary string field in hexadecimal format. This option must be used if there is binary data. The affected field types 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
Does not export the specified table. When specifying to ignore multiple tables, you need to repeat multiple times, one table at a time. Each table must specify both a database and a 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>’
mysqldump -uroot -p --host = localhost --all-databases --include-master-host-port
--insert-ignore
Use 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 with the --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
Lock all tables before starting the export. Lock the table with READ LOCAL to allow parallel insertion of MyISAM tables. For transaction-supporting tables such as InnoDB and BDB, --single-transaction is a better choice because it does not require locking tables at all.
Note that when exporting multiple databases, --lock-tables locks the tables for each database separately. Therefore, this option does not guarantee the logical consistency of the tables in the export 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 a 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 for TCP / IP and socket connections.
mysqldump -uroot -p --host = localhost --all-databases --net_buffer_length = 1024
--no-autocommit
Wrap the table with an autocommit / commit statement.
mysqldump -uroot -p --host = localhost --all-databases --no-autocommit
--no-create-db, -n
Export data only without adding CREATE DATABASE statements.
mysqldump -uroot -p --host = localhost --all-databases --no-create-db
--no-create-info, -t
Export data only without adding CREATE TABLE statements.
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 Options are 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 it will take a long time to export.
mysqldump -uroot -p --host = localhost --all-databases --order-by-primary
--password, -p
Connection database password
--pipe (available on windows systems)
Connect MySQL using named pipes
mysqldump -uroot -p --host = localhost --all-databases --pipe
--port, -P
Connection database port number
--protocol
Connection protocols used, including: tcp, socket, pipe, memory.
mysqldump -uroot -p --host = localhost --all-databases --protocol = tcp
--quick, -q
Unbuffered queries are exported 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
Output directly to the specified file. This option should be used on systems that use carriage return and linefeed pairs (\\ r \\ n) to wrap lines (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 does not block any applications and guarantees the consistent state of the database when exporting. It only works for 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, use the --quick option.
mysqldump -uroot -p --host = localhost --all-databases --single-transaction
--dump-date
Add the export time to the output file. It is on by default. Use --skip-dump-date to turn it off.
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 is /tmp/mysql.sock
mysqldump -uroot -p --host = localhost --all-databases --socket = / tmp / mysqld.sock
--tab, -T
Create a tab-separated text file for each table at the given path. Note: Only used for mysqldump and mysqld server running on the same machine.
mysqldump -uroot -p --host = localhost test test --tab = "/ home / mysql"
--tables
Override the --databases (-B) parameter and 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 trigger Device. This option is enabled by default and 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 the correctness of TIMESTAMP data exported in different time zones or when data is moved to other time zones.
mysqldump -uroot -p --host = localhost --all-databases --tz-utc
--user, -u
Specify the username of the connection.
--verbose, --v
Output a variety of platform information.
--version, -V
Output mysqldump version information and exit
--where, -w
Dump only records selected by the given WHERE condition. Note that if the condition contains spaces or characters that are exclusive 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
Directory of client plugins for compatibility with different plugin versions.
mysqldump -uroot -p --host = localhost --all-databases --plugin_dir = ”/ usr / local / lib / plugin”
--default_auth
Client plugins use permissions by default.
mysqldump -uroot -p --host = localhost --all-databases --default-auth = ”/ usr / local / lib / plugin / <PLUGIN>

 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" and so on. The import and export functions of the client are also good choices, such as workbench, navicat; among them, there are many file formats to choose from in the navicat export wizard.

 

 

Remarks:

     pursuer.chen

     Blog: http://www.cnblogs.com/chenmh

All essays on this site are original, and you are welcome to reprint them; however, when reprinting, you must indicate the source of the article and give a clear link at the beginning of the article.

"Welcome exchange discussions"

Detailed MySQL mysqldump data export --dump-slave notes