The mysqldump parameter in detail

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

MysqldumpParameters Daquan (parameters derived frommysql5.5.19Source code)

Parameters
Explanation of parameters
--all-databases , -A
Export all database.
mysqldump -uroot -p --all-databases
--all-tablespaces , -Y
Export all table spaces.
mysqldump -uroot -p --all-databases --all-tablespaces
--no-tablespaces , -y
Do not derive any tablespace information.
mysqldump -uroot -p --all-databases --no-tablespaces
--add-drop-database
Before each database to create the add database drop statement.
mysqldump -uroot -p --all-databases --add-drop-database
--add-drop-table
Each data table is created before adding drop table statement. (the default is open, use --skip-add-drop-table to cancel option)
Mysqldump -uroot -p --all-databases (default add an drop statement)
mysqldump -uroot -p --all-databases –Skip-add-drop-table (without drop statements)
--add-locks
Increase of LOCK TABLES before and after each table derived UNLOCK TABLE. (the default is open, use --skip-add-locks to cancel option)
Mysqldump -uroot -p --all-databases (default add an LOCK statement)
mysqldump -uroot -p --all-databases –Skip-add-locks (without LOCK statements)
--allow-keywords
Allows to create is a column name keyword. This table name prefix to each column do.
mysqldump -uroot -p --all-databases --allow-keywords
--apply-slave-statements
Adding'STOP SLAVE'in'CHANGE MASTER', and are added at the end of'START SLAVE'.
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
Additional annotation information. The default is open, you can use the --skip-comments to cancel
Mysqldump -uroot -p --all-databases (default recording notes)
Mysqldump -uroot -p --all-databases --skip-comments (uncomment)
--compatible
Derived data would be compatible with other databases or the old version of MySQL. 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, separated by a comma. It does not guarantee that is fully compatible with, but as far as possible compatibility.
mysqldump -uroot -p --all-databases --compatible=ansi
--compact
The output information is derived less (for debugging). Remove the notes and head to tail structure. You can use the options: --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
mysqldump -uroot -p --all-databases --compact
--complete-insert, -c
Use the insert statement to complete (contains column names). Doing so can improve the efficiency of insertion, but may be affected by the max_allowed_packet parameters and lead to insert failed.
mysqldump -uroot -p --all-databases --complete-insert
--compress, -C
Compressed transfer all information between the client and the server
mysqldump -uroot -p --all-databases --compress
--create-options, -a
Includes all the MySQL option in the CREATE TABLE statement. (the default is open)
mysqldump -uroot -p --all-databases
--databases, -B
Export database. Parameter name behind all parameters are considered to be the database name.
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 used to open a file 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
Set the default character set, the default value is utf8
mysqldump -uroot -p --all-databases --default-character-set=latin1
--delayed-insert
The delay insertion mode (INSERT DELAYED) derived data
mysqldump -uroot -p --all-databases --delayed-insert
--delete-master-logs
Delete log master backup. This parameter will be automatically activated --master-data.
mysqldump -uroot -p --all-databases --delete-master-logs
--disable-keys
For each table, with 40000 ALTER TABLE / *! Tbl_name DISABLE KEYS * /; / * 40000 ALTER and TABLE tbl_name ENABLE KEYS! * /; statement references the INSERT statement. So you can quickly import dump file, because it is to create indexes on the insert all after. This option is only suitable for the MyISAM table, the default for the open state.
mysqldump -uroot -p --all-databases
--dump-slave
This option will cause the main binlog location and file name is appended to the derived data file. When set to 1, will use the CHANGE MASTER command is output to the data file; when set to 2, suggesting that increased information in the command before. This option will open the --lock-all-tables, unless --single- transaction is specified. This option will automatically close the --lock-tables options. 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
With more than one VALUES column of the INSERT syntax. So the export file into smaller, and accelerate the speed of. Default is open, use --skip-extended-insert to cancel option.
mysqldump -uroot -p --all-databases
Mysqldump -uroot -p --all-databases--skip-extended-insert (cancel option)
--fields-terminated-by
Neglect of a given field in the export file. Used together with the --tab option, the --databases and the --all-databases option is not used for
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”
--fields-enclosed-by
Each field in the output file package with a given character. Used together with the --tab option, the --databases and the --all-databases option is not used for
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”
--fields-optionally-enclosed-by
Each field in the output file with the given character selective package. Used together with the --tab option, the --databases and the --all-databases option is not used for
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by =”#”
--fields-escaped-by
Each field in the output file to ignore a given character. Used together with the --tab option, the --databases and the --all-databases option is not used for
mysqldump -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”
--flush-logs
Start before exporting refresh log.
Please note: if an export multiple database (using the option --databases or --all- databases), will by the database refresh log. In addition to using --lock-all-tables or --master-data. In this case, the log will be updated once, the corresponding so the table is locked at the same time. Therefore, if plans to export and refresh the log should use --lock-all-tables or --master-data and --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
--flush-privileges
After the export MySQL database, sends a FLUSH PRIVILEGES statement. In order to correctly restored, this option should be used to export MySQL database and MySQL database data any time dependence.
mysqldump -uroot -p --all-databases --flush-privileges
--force
Ignore the SQL error during the export process.
mysqldump -uroot -p --all-databases --force
--help
Display a help message and exit.
mysqldump --help
--hex-blob
The use of sixteen hexadecimal format binary string field. If a binary data must use this option. Affected by the field types have BINARY, VARBINARY, BLOB.
mysqldump -uroot -p --all-databases --hex-blob
--host, -h
Need to export the host information
mysqldump -uroot -p --host=localhost --all-databases
--ignore-table
Do not export the specified table. The specified ignore multiple tables, need to be repeated many times, each time a table. Each table must also specify the database and 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 increase in --dump-slave produced by'CHANGE MASTER in the TO..'statement'MASTER_HOST=<host>, MASTER_PORT=<port>'
mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port
--insert-ignore
Use the INSERT IGNORE statement in the insert row.
mysqldump -uroot -p --host=localhost --all-databases --insert-ignore
--lines-terminated-by
Each line with the given string into the output file. Used together with the --tab option, the --databases and the --all-databases option is not used for.
mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql” --lines-terminated-by=”##”
--lock-all-tables, -x
Submit the request to lock all the database, in order to ensure the consistency of the data. This is a global read lock, and automatically shut down --single-transaction and --lock-tables options.
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables, -l
Start before export, lock all table. Lock table to allow the MyISAM table parallel insertion using READ LOCAL. For transactional tables such as InnoDB and BDB, --single-transaction is a better choice, because it does not need to lock table.
Please note that when the export multiple databases, --lock-tables separately for each database lock table. Therefore, this option cannot guarantee the consistency of logic in the export file table in the database. Export status of different tables in the database can be completely different.
mysqldump -uroot -p --host=localhost --all-databases --lock-tables
--log-error
Additional 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 will binlog location and file name is appended to the output file. If 1, will output CHANGE MASTER command; if 2, add comments, information output of the CHANGE command of MASTER. This option will open --lock-all-tables options, unless --single-transaction is also specified (in this case, global read lock in the beginning is derived for a short time; other content reference the --single-transaction option). Automatic closing the --lock-tables option this 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 server sends and receives the maximum packet length.
mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
--net_buffer_length
TCP/The buffer size of the IP and socket connection.
mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024
--no-autocommit
Use the autocommit/commit statement to package list.
mysqldump -uroot -p --host=localhost --all-databases --no-autocommit
--no-create-db, -n
Only the data, without the addition of CREATE DATABASE statement.
mysqldump -uroot -p --host=localhost --all-databases --no-create-db
--no-create-info, -t
Only the data, without the addition of CREATE TABLE statement.

mysqldump -uroot -p --host=localhost --all-databases --no-create-info
--no-data, -d
Do not derive any data, only the database table structure.
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, you can use --skip-opt to disable.
mysqldump -uroot -p --host=localhost --all-databases --opt
--order-by-primary
If there is a primary key or unique key, first, to sort the records for each table. In deriving the MyISAM table to the InnoDB table is effective, but will make the derived work takes a long time.
mysqldump -uroot -p --host=localhost --all-databases --order-by-primary
--password, -p
Connect to the database password
--pipe(windowsSystem availability)
Using named pipes connections MySQL
mysqldump -uroot -p --host=localhost --all-databases --pipe
--port, -P
The port number to connect to the database
--protocol
The connection agreement to use, including: tcp, socket, pipe, memory.
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
--quick, -q
No buffer query, direct export to standard output. The default is open, use --skip-quick to cancel the selection.
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-quick
--quote-names,-Q
Use (`) caused by the table and column names. The default is open, use --skip-quote-names to cancel the selection.
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
Direct output to the specified file. This option should be used in the use of new line of (\r\n) line system (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 user-defined function.
mysqldump -uroot -p --host=localhost --all-databases --routines
--set-charset
Add'SET NAMES default_character_set'to the output file. The default is the open state, the use of --skip-set-charset shutdown option.
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset
--single-transaction
The option to submit a BEGIN SQL statement in the derived data, BEGIN does not block any application which can ensure the consistency condition is derived when the database. It is only suitable for the multi version storage engine, only InnoDB. This option and the --lock-tables option is mutual exclusion, because LOCK TABLES can make any pending transactions implicit commit. If you want to export large sheets, should be used in conjunction with the --quick option.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
--dump-date
Add export time into the output file. The default is the open state, the use of --skip-dump-date shutdown 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
Specifies the connection MySQL socket file location, the default path/tmp/mysql.sock
mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
--tab,-T
For each table to create the tab at a given path segmentation of text files. Note: only for mysqldump and mysqld server running on the same machine.
mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"
--tables
Coverage of --databases (-B) parameters, a table name is specified first.
mysqldump -uroot -p --host=localhost --databases test --tables test
--triggers
Export trigger. This option is enabled by default, disable it with --skip-triggers.
mysqldump -uroot -p --host=localhost --all-databases --triggers
--tz-utc
The time zone setting in the TIME_ZONE='+00:00'derived top, to ensure the accuracy of TIMESTAMP data in different time zones or export data are moved by the time zone.
mysqldump -uroot -p --host=localhost --all-databases --tz-utc
--user, -u
Specify the connection user name.
--verbose, --v
Multi platform information.
--version, -V
Mysqldump output version information and exit
--where, -w
Just dump the given WHERE condition records. Please note that if the condition contains command interpretation symbol special spaces or characters, it must be conditional reference.
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 client plug-in directory, for compatibility with different versions of the plug-in.
mysqldump -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”
--default_auth
The plug-in in client default permissions.
mysqldump -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/<PLUGIN>”

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Carter at December 01, 2013 - 9:45 AM