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

This article introduces the relevant knowledge of "what is the role of mysql dump parameters". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Syntax option description

-h,-- host=name

Hostnam

-P [port_num],-- port=port_num

The TCP/IP port number used to connect to the MySQL server

-- master-data

This option adds the location and file name of binlog to the output. If it is equal to 1, it will be printed as a CHANGE MASTER command; if it is equal to 2, it will be prefixed with comments. And this option automatically turns on-- lock-all-tables, unless you also set-- single-transaction (in which case, the global read lock will only add a short period of time to start the dump, don't forget to read the-- single-transaction section). In any case, all actions in the log occur at the exact time of the export. This option automatically turns off-- lock-tables.

-x,-- lock-all-tables

Lock all tables in all libraries. This is achieved by holding global read locks throughout the dump. Automatically closes-- single-transaction and-- lock-tables.

-- single-transaction

The exported data is a consistent snapshot by encapsulating the export operation in a transaction. It works only if the table uses a storage engine that supports MVCC (currently only InnoDB); other engines cannot guarantee that the export is consistent. To ensure that the export file is valid (correct table data and binary log location) when the-- single-transaction option is turned on for export, make sure that no other connections execute statements such as: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, which will invalidate consistent snapshots. When this option is turned on, it automatically turns off-- lock-tables.

-l,-- lock-tables

Add a read lock to all tables. (it is on by default and closed with-- skip-lock-tables. The above option turns off the-l option.)

-F,-- flush-logs

Refresh the server's log file before starting the export. Note that if you export many databases at once (using the-databases= or-- all-databases options), log refresh will be triggered when each library is exported. The exception is when-- lock-all-tables or-- master-data is used: the log is refreshed only once, when all tables are locked. So if you want your export and log refresh to occur at the same certain time, you need to use-- lock-all-tables, or-- master-data with-- flush-logs.

-- delete-master-logs

Delete the logs on the primary library after the backup is complete. This option automatically opens-master- data`.

-- opt

Same as-add-drop-table,-- add-locks,-- create-options,-- quick,-- extended-insert,-- lock-tables,-- set-charset,-- disable-keys. (default is on,-- skip-opt off means that these options remain at their default values) should give you the fastest possible export for reading into a MySQL server,-- compact almost disables the above options.

When you perform a backup database table structure, you specify the-- skip-opt option, which is equivalent to disabling the following parameters:

-add-drop-table,-add-locks

-create-options,-quick, extended-insert

-lock-tables,-set-charset, and-disable-keys

-Q,-- quick

The query is not buffered and exported directly to stdout. (on by default and off with-- skip-quick) this option is used to dump large tables.

-- set-charset

Add SET NAMES default_character_set to the output. This option is enabled by default. To disable the SET NAMES statement, use-- skip-set-charset.

-- add-drop-tables

Add a DROP TABLE statement before each CREATE TABLE statement. It is on by default.

-- add-locks

Add LOCK TABLES before each table is exported and then UNLOCK TABLE. (for faster insertion into the MySQL). It is on by default.

-- create-option

Include all MySQL options in the CREATE TABLE statement. On by default, use-- skip-create-options to turn it off.

-e-- extended-insert

Use the new multiline INSERT syntax, which is turned on by default (gives more compact and faster insert statements)

-d,-- no-data

No row information is written to the table. This is useful if you only want to get an export of the structure of a table.

-- add-drop-database

DROP DATABASE before the create database, which is turned off by default, so it is generally necessary to ensure that the database already exists when importing.

-- default-character-set=

The default character set used. If not specified, mysqldump uses utf8.

-B,-- databases

Dump several databases. Typically, mysqldump treats the first name parameter on the command line as the database name, and the subsequent name as the table name. With this option, it treats all name parameters as database names. The CREATE DATABASE IF NOT EXISTS db_name and USE db_name statements are included in the output before each new database.

-- tables

Override the-- database option. All parameters after the option are treated as table names.

-u [name],-- user=

The MySQL user name used when connecting to the server.

-p [password],-- password [= password]

The password used to connect to the server. If you use the short option form (- p), you cannot have a space between the option and the password. If you omit the password value after the-- password or-p option on the command line, you will be prompted to enter one.

This is the end of the content of "what is the function of mysql dump parameters". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.