Mysql database using mysqldump Export data detailed _mysql

Mysqldump is a client program that MySQL uses to store databases. It mainly produces a series of SQL statements that can be encapsulated into a file that contains all the SQL commands needed to rebuild your database, such as CREATE database,create Table,insert, and so on. Can be used to implement lightweight, fast migration or recovery databases. is a way to implement a logical backup of the MySQL database.

In the day-to-day maintenance work will often need to export data, and mysqldump is the process of exporting data used very frequently a tool, it comes with a lot of functional parameters, the article will enumerate some commonly used operations, at the end of the article will be all the parameters detailed description listed.

Grammar:

Export with no parameters by default, the text content is roughly 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 ...]

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);

1. Export all databases

This command exports all databases, including system databases

2. Export all data for DB1, DB2 two databases

3. Export the A1, A2 table in DB1

Note Exporting the specified table can only be exported for one database, and the exported content is not the same as the exported database, exporting the specified table does not have a decision to create the database in the exported text, only delete the table-create table-Import data

4. Conditional export, export the data of id=1 in DB1 table A1

Conditional exports can export only a single table

5. Generate a new Binlog file,-F

Sometimes you want to generate a new Binlog file after you export the data, just add the-f argument

6. Export only the table structure does not export data,--no-data

7. Export Import data across servers

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

Import all the data from the DB1 database in the H1 server into the DB2 database in H2, the DB2 database must exist or it will be an error

mysqldump--host=192.168.80.137-uroot-proot-c--databases test |mysql--host=192.168.80.133-uroot-proot Test
The-c parameter enables compression delivery.

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

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

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

The--lock-tables option is automatically turned off after execution. --dump-slave default is 1

9. Append the Binlog location and filename of the current server to the output file,--master-data

Changing the parameters is the same as the--dump-slave method, except that it records the current server's Binlog, which is equivalent to performing show master status.

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.

11 ensure the consistent status of the export--single-transaction

This option submits a BEGIN SQL statement before the data is exported, and the begin does not block any applications and guarantees the consistency state of the database at the time of export. It applies only to the multiple-version storage engine (it does not show that the lock is compared to the data by the judging version), only InnoDB. This option and the--lock-tables option are mutually exclusive, because lock tables will cause any pending transactions to be implicitly committed. To export a large table, you should use the--quick option in combination.

--quick, Q

Do not buffer queries, export directly to standard output. The default is on, and use--skip-quick to cancel the option.

12--lock-tables, L

Lock 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 of the tables in the export file between the databases. The export status of different database tables can be completely different.

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

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. (The 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 the 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. Options available: --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 considered 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
Print 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
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 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 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 will turn 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 at any time to export the mysql database and rely on the mysql database data.
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 you specify 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>' to the 'CHANGE MASTER TO ..' statement produced by --dump-slave
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 need to lock the table 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 of 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 only the 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 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. 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)
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 data. BEGIN does not block any applications and guarantees the consistent state of the database at the time of export. 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. 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 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, specifying the name of the table to be exported.
mysqldump -uroot -p --host = localhost --databases test --tables test
--triggers
Export trigger. 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 guarantee different time zones
The accuracy of the exported TIMESTAMP data or when the data was moved to another time zone.
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
Print 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>”