Mysqldump usage Database Export

Welcome to the Linux community forum and interact with 2 million technical staff. Go to -- master-data [value]. This option writes the location and file name of the binary log to the output. This option requires RELOAD permission and binary logs must be enabled. If the option value is equal to 1, the position and file name are written to the dump output in the form of CHANGEMASTER statements,

Welcome to the Linux community forum, interact with 2 million technical staff> go to -- master-data [= value] This option writes the location and file name of the binary log to the output. This option requires RELOAD permission and binary logs must be enabled. If the option value is equal to 1, the location and file name are written to the dump output in the form of the change master statement,

Welcome to the Linux community forum and interact with 2 million technicians>

-- Master-data [= value]

This option writes the location and file name of the binary log to the output. This option requires RELOAD permission and binary logs must be enabled. If the option value is equal to 1, the location and file name are written to the dump output in the form of the change master statement. If you use this SQL statement to dump the MASTER server to set the slave server, start from the correct location of the binary log of the master server. If the option value is 2, the change master statement is written as an SQL comment. If the value is omitted, This is the default action.

-- Master-data

Option to enable -- lock-all-tables, unless -- single-transaction is also specified (in this case, the global read lock is obtained only for a short time when the dump is started. See also -- single-transaction. In any case, log-related actions occur during dump. This option is automatically disabled -- lock-tables.

-- No-create-db,-n

This option disables create database /*! 32312 if not exists */db_name statement, IF the --- database or -- all -- database option is provided, it is included in the output.

-- No-create-info,-t

Do not re-CREATE the create table statement for each dump TABLE.

-- No-data,-d

Do not write any row information of the table. This is useful if you only want to dump the table structure.

-- Opt

This option is stenographer; it is equivalent to specifying -- add-drop-tables -- add-locking -- create-option -- disable-keys -- extended-insert -- lock-tables -- quick -- set-charset. it provides a fast dump operation and generates a dump file that can be quickly loaded into the MySQL server. This option is enabled by default, but can be disabled with -- skip-opt. To disable only the option enabled with-opt, use the -- skip format. For example, -- skip-add-drop-tables or -- skip-quick.

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

The password used to connect to the server. If you use the short option format (-p), there cannot be a space between the option and password. If the password value after the -- password or-p option is ignored in the command line, you are prompted to enter one.

-- Port = port_num,-P port_num

The TCP/IP Port used for connection.

-- Protocol = {TCP | SOCKET | PIPE | MEMORY}

The connection protocol used.

-- Quick,-q

This option is used to dump large tables. It forces mysqldump to retrieve the rows in the table from the server one row at a time, instead of retrieving all rows and caching them to the memory before the output.

-- Quote-names,-Q

Use the '''' character to reference databases, tables, and column names. If the server SQL mode includes the ANSI_QUOTES option, use the '"' character to reference the name. This option is enabled by default. You can disable it with -- skip-quote-names, but this option should be followed by other options. For example, you can enable -- quote-names -- compatible.

-- Result-file = file,-r file

Redirects the output to the specified file. This option is applied in Windows because it prohibits the conversion of the new line '\ n' to' \ r \ n' and return/New Line sequence.

-- Routines,-R

Dump stored programs (functions and programs) in the dumped database ). The output produced using --- routines contains the create procedure and create function statements to recreate the subroutine. However, these statements do not include attributes, such as subprogram definers or create or modify timestamps. This indicates that when the subprograms are overloaded, the user should be set as the overload user when they are created, and the timestamp is equal to the overload time.

If the created subroutine uses the original definer and timestamp attributes, -- routines is not used. instead, use a mysql account with the corresponding permissions of the MySQL database to directly dump and reload mysql. the content of the proc table.

This option is added in MySQL 5.1.2. Before that, the storage program does not dump.

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

-- Single-transaction

This option issues a begin SQL statement before dumping data from the server. It only applies to transaction tables, such as InnoDB and BDB, because it will then dump consistent database states when a BEGIN is issued without blocking any applications.

When using this option, remember that only InnoDB tables can be dumped in a consistent state. For example, when this option is used, any dumped MyISAM or HEAP table can still change the status.

The -- single-transaction option and -- lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions.

To dump a large table, use -- quick to use this option.

-- Socket = path,-S path

The socket file used when connecting to localhost (the default host.

-- Skip -- comments

See the description of the --- comments option.

-- Tab = path,-T path

Generate a tab-separated data file. For each dump table, mysqldump creates a tbl_name. SQL file containing the CREATE TABLE statement of the table to be created, and a tbl_name.txt file containing its data. The option value is the directory where the file is written.

The format of the. txt data file is to use a tab character between the column value and the new line after each line. You can use the -- fields-xxx and -- Row -- xxx options to explicitly specify the format.

Note: This option is only applicable when the mysqldump and mysqld servers run on the same machine. You must have the FILE permission and the server must have the permission to write files in the directory you specified.

-- Tables

Overwrite the --- database or-B option. All parameters following the option are treated as table names.

-- Triggers

Dump triggers for each dump table. This option is enabled by default, and -- skip-triggers is used to disable it.

-- Tz-utc

Add SET TIME_ZONE = '+ 00:00' to the dump file so that the TIMESTAMP column can be dumped and reloaded between servers with different time zones. (If this option is not used, the TIMESTAMP column is dumped and reloaded between the source and target servers with the local time zone ). -- Tz-utc can also protect the changes caused by timeout. -- Tz-utc is enabled by default. To disable it, use -- skip-tz-utc. This option is added to MySQL 5.1.2.

-- User = user_name,-u user_name

[1] [2] [3]