Mysqldump parameter Daquan

Source: Internet
Author: User
Tags compact time zones one table

Mysqldump parameter Daquan (parameter from mysql5.5.19 source)


Parameter description


Export all databases.


--all-tablespaces, Y

Export all table spaces.


--no-tablespaces, Y

No table space information is exported.



Add drop DATABASE statements before each database is created.



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


Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.



Add ' STOP SLAVE ' before ' Change MASTER ' and add ' START SLAVE ' at the end of the export.



Directory of Character Set files



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)


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.



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



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.



Enable compression for all information between client and server



Include all MySQL feature options in the CREATE TABLE statement. (Open by default)



Export several databases. All name parameters after the parameter are treated as database names.

Mysqldump-uroot-p--databases Test MySQL


Output debug information for debugging. The default value is: D:t:o,/tmp/mysqldump.trace


Mysqldump-uroot-p--all-databases--debug= "D:t:o,/tmp/debug.trace"


Check the memory and open the file usage instructions and exit.



Output debug information and exit



Set the default character set, the default value is UTF8



Export data with a time-lapse insert (insert DELAYED)



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



For each table, use/!40000 ALTER TABLE tbl_name DISABLE keys /; and/!40000 ALTER TABLE tbl_name ENABLE KEYS /; Statement reference Inser T statement. 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.



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.




Export events.



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--skip-extended-insert (cancel option)


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= "#"


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= "#"


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 = "#"


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= "#"


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.



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.



Ignores the SQL errors that occur during the export process.



Displays help information and exits.



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.



Host information that needs to be exported



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 ...



The ' Change MASTER ' generated in--dump-slave Add ' master_host= in statement



Use the Insert Ignore statement when inserting rows.



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= "# #"


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.



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.



Attaching warnings and error messages to a given file



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 turns on the--lock-all-tables option unless--single-transaction is also specified (in this case, the global read lock gets a short time when the export is started; the other contents refer to the--single-transaction option below). This option automatically turns off the--lock-tables option.




The maximum packet length that the server sends and receives.



The cache size for TCP/IP and socket connections.



Wrap the table with the Autocommit/commit statement.



Only the data is exported and the Create DATABASE statement is not added.



Exports only data without adding a CREATE TABLE statement.



No data is exported, only the database table structure is exported.



Equivalent to--skip-set-charset



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.



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.



Connect Database Password

--pipe (Windows system available)

Using Named pipes to connect to MySQL



Connection Database Port number


The connection protocols used include: TCP, Socket, pipe, memory.



Non-buffered queries are exported directly to standard output. The default is on, and you use--skip-quick to cancel the option.




Use (') to cause table and column names. The default is on, and you use--skip-quote-names to cancel the option.




Replace insert into with replace into.



Output directly to the specified file. This option should be used on systems that use carriage return (\ r \ n) line wrapping (for example: dos,windows). This option ensures that only one row is used.



Export stored procedures and custom functions.



Add ' SET NAMES default_character_set ' to the output file. The default is on, with the--skip-set-charset off option.




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.



Add the export time to the output file. The default is on, with the--skip-dump-date off option.




Disables the –opt option.



Specifies the location of the socket file that connects to MySQL, the default path/tmp/mysql.sock



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"


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


Export the trigger. This option is enabled by default and is disabled with--skip-triggers.



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.



Specifies the user name of the connection.


Output multiple platform information.


Output mysqldump version information and exit


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 '"


Export the XML format.



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"


The client plug-in uses permissions by default.

Mysqldump-uroot-p--host=localhost--all-databases--default-auth= "/usr/local/lib/plugin/

Mysqldump parameter Daquan

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.