Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the use of the parameters of the mysqldump command in MySQL. It is very detailed and has a certain reference value. Friends who are interested must read it!

Parameter description

-all-databases,-A

Export all databases.

Mysqldump-uroot-p-all-databases

-all-tablespaces,-Y

Export all tablespaces.

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

-no-tablespaces,-y

No tablespace 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 datasheet statements before each datasheet is created. (default is on, use-- skip-add-drop-table to 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. (default is on, use-- skip-add-locks to cancel option)

Mysqldump-uroot-p-- all-databases (add LOCK statement by default)

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 to 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. It is on by default. You can cancel it with-- skip-comments.

Mysqldump-uroot-p-- all-databases (default record comments)

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 as compatible as possible.

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

-- compact

Export less output (for debugging). Remove comments and head-tail structures. You can use the option:-- 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 (including column names). Doing so can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter and cause the insert to fail.

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

-compress,-C

Enable compression to pass all information between the client and the 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 parameters are treated as database names.

Mysqldump-uroot-p-databases test mysql

-- debug

Output debug information for debugging.

-- debug-check

Check the 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

Sets the default character set, which is utf8

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

-- delayed-insert

Export data by delayed insertion (INSERT DELAYED)

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

-- delete-master-logs

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

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

-- disable-keys

For each table, reference the insert statement with / *! 40000 ALTER TABLE tbl_name DISABLE KEYS * /; and / *! 40000 ALTER TABLE tbl_name ENABLE KEYS * /; statements. This makes it faster to import the file from dump because it creates the index after all rows have been inserted. This option applies only to 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 file where the data is exported. When set to 1, the CHANGE MASTER command is output to the data file; when set to 2, the description information is added 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 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, not for the-- databases and-- all-databases options

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

-- fields-enclosed-by

The fields in the output file are wrapped in the given characters. Used with the-- tab option, not for 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 a given character. Used with the-- tab option, not for 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, not for 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.

Note: if you export more than one database at a time (using the option-- databases or-- all-databases), the logs will be refreshed one by one. Except for 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 to export mysql databases and rely on mysql database data at any time.

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 in hexadecimal format. You must use this option if you have binary data. 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. Specifies that when multiple tables are ignored, they need to be repeated 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 TO..' produced by-- dump-slave Add 'MASTER_HOST=,MASTER_PORT='' to the statement

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 a 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 submit request locks all tables in all databases to ensure data consistency. This is a global read lock and automatically turns off the-- single-transaction and-- lock-tables options.

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

Lock-tables,-l

Lock all tables before you start exporting. Lock the table with READ LOCAL to allow MyISAM table inserts in parallel. Tables that support transactions, such as InnoDB and BDB,--single-transaction, are a better choice because it does not need to lock the table at all.

Note that when exporting multiple databases,-- lock-tables locks tables for each database. Therefore, this option does not 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

Attach warning and error messages to the 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; if 2, comment information is added before the output 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 is given a short time to start the export; for more information, 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 sent and accepted by the server.

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

-- net_buffer_length

Cache size for TCP/IP and socket connections.

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

-- no-autocommit

Use the autocommit/commit statement 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 data without adding CREATE TABLE statements.

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

No-data,-d

No data is exported, only 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 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 for each table. Valid when exporting MyISAM tables to InnoDB tables, but it can 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 system)

Use 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, including: tcp, socket, pipe, memory.

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

-quick,-Q

The query is not buffered and exported directly to standard output. The default is on, and 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, and 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 line feed (\ r\ n) line feed (for example: 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, using 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. BEGIN does not block any applications and ensures the consistent state of the database at the time of export. It applies only to multi-version storage engines, InnoDB only. This option and the-- lock-tables option are mutually exclusive because LOCK TABLES will implicitly commit any pending transaction. If you want to export a large table, 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, using 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

Disable the-opt option.

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

-- socket,-S

Specify 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-split 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 to be exported.

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

-- triggers

Export triggers. 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 the TIMESTAMP data exported in different time zones or when the data is moved to other time zones.

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

-user,-u

Specifies the user name of the connection.

-verbose,-v

Output a variety of platform information.

-version,-V

Output mysqldump version information and exit

-where,-w

Only records selected by a given WHERE condition are dumped. Note that if the condition contains special spaces or characters for the command interpreter, be sure to reference 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 the client plug-in, which is compatible 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/"

These are all the contents of the article "what are the parameters of the mysqldump command in MySQL?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.