Mysqldump parameter description _ MySQL

Mysqldump

BitsCN.com

MysqldumpParameter description (parameter sourceMysql5.5.19Source code)

Parameters
Parameter description
-- All-databases,-
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 the drop database statement before each database is created.
Mysqldump-uroot-p -- all-databases -- add-drop-database
-- Add-drop-table
Add the drop TABLE statement before creating each data table. (It is enabled by default. use the -- skip-add-drop-table cancel option)
Mysqldump-uroot-p -- all-databases (the drop statement is added by default)
Mysqldump-uroot-p -- all-databases-skip-add-drop-table (cancel the drop statement)
-- Add-locks
Add lock tables and unlock table before each TABLE is exported. (It is enabled by default. use the -- skip-add-locks cancel option)
Mysqldump-uroot-p -- all-databases (the LOCK statement is added by default)
Mysqldump-uroot-p -- all-databases-skip-add-locks (cancel LOCK statement)
-- Allow-keywords
Names of columns allowed to be created as keywords. This is done by the table name prefix on 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
Character Set file directory
Mysqldump-uroot-p -- all-databases -- character-sets-dir =/usr/local/mysql/share/mysql/charsets
-- Comments
Add comments. It is enabled by default and can be canceled using -- 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 old MySQL versions. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc,
Use commas to separate the values. It does not guarantee full compatibility, but is as compatible as possible.
Mysqldump-uroot-p -- all-databases -- compatible = ansi
-- Compact
Export less output information (for debugging ). Remove comments, headers, and tails. Option: -- skip-add-drop-table -- skip-add-locks -- skip-comments -- skip-disable-keys
Mysqldump-uroot-p -- all-databases -- compact
-- Complete-insert,-c
Use the complete insert statement (including the column name ). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure.
Mysqldump-uroot-p -- all-databases -- complete-insert
-- Compress,-C
Enable compression between the client and server to pass all information
Mysqldump-uroot-p -- all-databases -- compress
-- Create-options,-
The create table statement contains all MySQL feature options. (Enabled by default)
Mysqldump-uroot-p -- all-databases
-- Databases,-B
Export several databases. All name parameters following the parameter are considered as the database name.
Mysqldump-uroot-p -- databases test mysql
-- Debug
Outputs debug information for debugging. Default value: 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 memory and open file usage 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. the default value is utf8.
Mysqldump-uroot-p -- all-databases -- default-character-set = latin1
-- Delayed-insert
Use insert delayed to export data
Mysqldump-uroot-p -- all-databases -- delayed-insert
-- Delete-master-logs
The log is deleted after the 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 */; the statement references the INSERT statement. This allows you to import dump files faster, because it creates an index after inserting all rows. This option is only applicable to MyISAM tables. it is enabled by default.
Mysqldump-uroot-p -- all-databases
-- Dump-slave
This option will cause the primary binlog location and file name to be appended to the exported data file. When set to 1, the command change master will be output to the data file; when set to 2, the instructions will be added before the command. This option will enable -- lock-all-tables unless -- single-transaction is specified. This option will automatically disable 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. In this way, the exported file is smaller and the import speed is accelerated. The value is enabled by default, and the -- skip-extended-insert cancel option is used.
Mysqldump-uroot-p -- all-databases
Mysqldump-uroot-p -- all-databases -- skip-extended-insert (cancel option)
-- Fields-terminated-
Ignore the specified field in the exported file. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.
Mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-terminated-by = "#"
-- Fields-enclosed-
Each field in the output file is enclosed by a given character. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.
Mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#"
-- Fields-optionally-enclosed-
Fields in the output file are selectively wrapped with the given characters. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.
Mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#" -- fields-optionally-enclosed-by = "#"
-- Fields-escaped-
The fields in the output file ignore the given characters. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.
Mysqldump-uroot-p mysql user -- tab = "/home/mysql" -- fields-escaped-by = "#"
-- Flush-logs
Refresh the log before you start exporting.
Note: If you export multiple databases at a time (using options -- databases or -- all-databases), the logs will be refreshed one by one. Except for -- lock-all-tables or -- master-data. In this case, the log is refreshed once, and the corresponding table is locked at the same time. Therefore, if you want to export and refresh logs at the same time, 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. To restore data correctly, this option should be used to export data from the mysql database and dependent mysql database at any time.
Mysqldump-uroot-p -- all-databases -- flush-privileges
-- Force
Ignore SQL errors during export.
Mysqldump-uroot-p -- all-databases -- force
-- Help
Displays help information and exits.
Mysqldump -- help
-- Hex-blob
Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include 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
Do not export the specified table. When you specify to ignore multiple tables, you need to repeat multiple times for each table. The database and table names must be specified for each table. Example: -- ignore-table = database. table1 -- ignore-table = database. table2 ......
Mysqldump-uroot-p -- host = localhost -- all-databases -- ignore-table = mysql. user
-- Include-master-host-port
In the 'change master to .. 'statement generated by -- dump-slave, add 'Master _ HOST = , MASTER_PORT = '
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-
Each line of the output file is divided by a given string. It is used with the -- tab option and cannot be used with the -- databases and -- all-databases options.
Mysqldump-uroot-p -- host = localhost 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 disabled.
Mysqldump-uroot-p -- host = localhost -- all-databases -- lock-all-tables
-- Lock-tables,-l
Lock All tables before export. Use read local to lock the table to allow concurrent insertion of MyISAM tables. For tables that support transactions 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 respectively. Therefore, this option does not guarantee the logical consistency between the tables in the exported files in the database. The export statuses 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 a given file
Mysqldump-uroot-p -- host = localhost -- all-databases -- log-error =/tmp/mysqldump_error_log.err
-- Master-data
This option adds the location and file name of binlog to the output file. If the value is 1, the change master Command is output. if the value is 2, the comments are added before the change master Command. This option will enable the -- lock-all-tables option, unless -- single-transaction is also specified (in this case, the Global read lock gets a very short time at the start of export; for other content, refer to the -- single-transaction option below ). This option automatically disables 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 length of packets sent and received by the server.
Mysqldump-uroot-p -- host = localhost -- all-databases -- max_allowed_packet = 10240
-- Net_buffer_length
The cache size of 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 the create database statement.
Mysqldump-uroot-p -- host = localhost -- all-databases -- no-create-db
-- No-create-info,-t
Only export data without adding the create table statement.

Mysqldump-uroot-p -- host = localhost -- all-databases -- no-create-info
-- No-data,-d
Only the database table structure is exported without exporting any data.
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 a primary key exists or the first unique key exists, sort the records of each table. It is valid when exporting a MyISAM table to an InnoDB table, but it takes a long time to export the table.
Mysqldump-uroot-p -- host = localhost -- all-databases -- order-by-primary
-- Password,-p
Database connection password
-- Pipe (windows System availability )
Connect to mysql using a named pipe
Mysqldump-uroot-p -- host = localhost -- all-databases -- pipe
-- Port,-P
Database connection port number
-- Protocol
The connection protocol used, including tcp, socket, pipe, and memory.
Mysqldump-uroot-p -- host = localhost -- all-databases -- protocol = tcp
-- Quick,-q
Directly export the data to the standard output without buffering the query. This option is enabled by default. you can 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 tables and column names. This option is enabled by default. -- skip-quote-names is used to cancel this option.
Mysqldump-uroot-p -- host = localhost -- all-databases
Mysqldump-uroot-p -- host = localhost -- all-databases -- skip-quote-names
-- Replace
Replace into to replace insert.
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 breaks (// r // n) (such as DOS and 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 user-defined functions.
Mysqldump-uroot-p -- host = localhost -- all-databases -- routines
-- Set-charset
Add 'set NAMES default_character_set 'to the output file. The default value is "open". you can use the -- skip-set-charset option to disable it.
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 ensures database consistency during export. It is only applicable to multi-version storage engines and only InnoDB. This option and the -- lock-tables option are mutually exclusive, because lock tables will implicitly commit any pending transactions. To export a large table, 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 value is "open", and the -- skip-dump-date option is used to disable it.
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
Specifies the location of the socket file connecting 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 in the given path. Note: it is only used for mysqldump and mysqld servers to run on the same machine.
Mysqldump-uroot-p -- host = localhost test -- tab = "/home/mysql"
-- Tables
Overwrite the -- databases (-B) parameter and specify 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. use -- skip-triggers to disable it.
Mysqldump-uroot-p -- host = localhost -- all-databases -- triggers
-- Tz-utc
Set TIME_ZONE = '+' at the top of the export to ensure the correctness of 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
Specify the connection user name.
-- Verbose, -- v
Outputs multiple platform information.
-- Version,-V
Output the mysqldump version information and exit
-- Where,-w
Only Records selected by the WHERE condition are dumped. Note that if the condition contains special spaces or characters for the command interpreter, you must 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
Client plug-in Directory, used to be compatible with different plug-in versions.
Mysqldump-uroot-p -- host = localhost -- all-databases -- plugin_dir = "/usr/local/lib/plugin"
-- Default_auth
Default client plug-in permission.
Mysqldump-uroot-p -- host = localhost -- all-databases -- default-auth = "/usr/local/lib/plugin/ "

BitsCN.com