mysqldump parameter Daquan These parameters are different from those of MySQL : 2 types of parameters mean something differently.
Mysqldump parameter Daquan (parameter from mysql5.5.19 source)
Parameters
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
No table space 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 data table statements before each data table is created. (The default is on, using the--skip-add-drop-table 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. (The default is on, using the--skip-add-locks cancel option)
Mysqldump-uroot-p--all-databases (default add lock statement)
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 with 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. The default is open, you can cancel with--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 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 is as compatible as possible.
Mysqldump-uroot-p--all-databases--compatible=ansi
--compact
Export less output information (for debugging). Remove the structure of annotations and Kinsoku. Available options:--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 (containing the column name). This can improve insertion efficiency, but may be affected by the Max_allowed_packet parameter, causing the insert to fail.
Mysqldump-uroot-p--all-databases--complete-insert
--compress,-C
Enable compression for 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. (Open by default)
Mysqldump-uroot-p--all-databases
--databases,-B
Export several databases. All name parameters after the parameter are treated as database names.
Mysqldump-uroot-p--databases Test MySQL
--debug
Output debug information for debugging. The default value is: 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 the memory and open the file usage 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=latin1
--delayed-insert
Export data with a time-lapse insert (insert DELAYED)
Mysqldump-uroot-p--all-databases--delayed-insert
--delete-master-logs
Delete log after master backup. This parameter will automatically activate the--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 reference INSERT statements. This allows you to import the dump file faster because it creates an index after inserting all the rows. This option is only suitable for 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 exported data file. When set to 1 o'clock, the Change Master command is output to the data file and set to 2 o'clock to add descriptive information 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 the 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 and cannot be used for--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 a given character. Used with the--tab option and cannot be used for--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 a given character. Used with the--tab option and cannot be used for--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 for--databases and--all-databases options
mysqldump-uroot-p mysql user--tab= "/home/mysql"--fields-escaped-by= "#"
--flush-logs
Refreshes the log before starting the export.
Note: If you export more than one database at a time (using option--databases or--all-databases), the log will be refreshed on a per-database basis. In addition to using--lock-all-tables or--master-data. In this case, the log will be refreshed once, so the table is locked at the same time. Therefore, if you intend to export and refresh the 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 MySQL database data.
Mysqldump-uroot-p--all-databases--flush-privileges
--force
Ignores the SQL errors that occur during the export process.
Mysqldump-uroot-p--all-databases--force
--help
Displays help information and exits.
Mysqldump--help
--hex-blob
Export binary string fields using hexadecimal format. This option must be used if you have binary data. The field types affected are binary, VARBINARY, BLOB.
Mysqldump-uroot-p--all-databases--hex-blob
--host,-H
Host information that needs 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, you need to repeat them 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 ' generated in--dump-slave Add ' master_host=
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 string. Used with the--tab option and cannot be used for--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
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.
Mysqldump-uroot-p--host=localhost--all-databases--lock-tables
--log-error
Attaching warnings 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 appends the location and file name of the Binlog to the output file. If 1, the Change Master command is output, and if 2, the output is added with comment information before the change Master command. This option opens the--lock-all-tables option unless--single-transaction is also specified (in this case, global read locks get a short time to start exporting Other contents 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 that the server sends and receives.
Mysqldump-uroot-p--host=localhost--all-databases--max_allowed_packet=10240
--net_buffer_length
The cache size for TCP/IP and socket connections.
Mysqldump-uroot-p--host=localhost--all-databases--net_buffer_length=1024
--no-autocommit
Wrap the table with the Autocommit/commit statement.
Mysqldump-uroot-p--host=localhost--all-databases--no-autocommit
--no-create-db,-N
Only the data is exported and the Create DATABASE statement is not added .
Mysqldump-uroot-p--host=localhost--all-databases--no-create-db
--no-create-info,-t
Exports only 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 This option is turned on 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, the records for each table are sorted. Valid when exporting the MyISAM table to the InnoDB table, but it can make the export work a long time.
Mysqldump-uroot-p--host=localhost--all-databases--order-by-primary
--password,-P
Connect Database Password
--pipe (Windows system available )
Using 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 include: TCP, Socket, pipe, memory.
Mysqldump-uroot-p--host=localhost--all-databases--protocol=tcp
--quick,-Q
Non-buffered queries are exported directly to standard output. The default is on, and you use--skip-quick to cancel the 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 you use--skip-quote-names to cancel the option.
Mysqldump-uroot-p--host=localhost--all-databases
Mysqldump-uroot-p--host=localhost--all-databases--skip-quote-names
--replace
Replace insert into with replace 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 pairs (\\r\\n) for line breaks (for example: dos,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 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, with 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, and begin does not block any applications and ensures consistent state of the database at the time of export. It only works with multiple versions of the storage engine, 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.
Mysqldump-uroot-p--host=localhost--all-databases--single-transaction
--dump-date
Add the export time to the output file. The default is on, with 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
Disables the –opt option.
Mysqldump-uroot-p--host=localhost--all-databases--skip-opt
--socket,-s
Specifies 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-separated 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 you want to export.
Mysqldump-uroot-p--host=localhost--databases Test--tables test
--triggers
Export the trigger. This option is enabled by default and is 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 that timestamp data that is exported in different time zones or when data is moved in another time zone is correct.
Mysqldump-uroot-p--host=localhost--all-databases--TZ-UTC
--user,-U
Specifies the user name of the connection.
--verbose,--v
Output multiple platform information.
--version,-V
Output mysqldump version information and exit
--where,-W
Dumps only the records selected by the given where condition. Note that if the condition contains a special space or character for the command interpreter, you must refer to the condition.
Mysqldump-uroot-p--host=localhost--all-databases--where= "user= ' root '"
--xml,-X
Export the XML format.
Mysqldump-uroot-p--host=localhost--all-databases--xml
--plugin_dir
The directory of the client plug-in 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>"
Mysqldump parameter Daquan These parameters are different from those of MySQL (there are links at the beginning of the article): 2 types of parameters are not the same