MySQL DBA system learning (mysqldump) MySQL backup

Mysqldump BACKUP Database

-B,--databases Dump several databases. Note the difference in usage; In

This case no tables are given. All name arguments are

Regarded as database names. ' Use db_name; ' would be

Included in the output.

-E,--extended-insert

Use Multiple-row INSERT syntax that include several

VALUES lists. MultiRow Insert Data

To ensure the consistency of the data, we have to lock the table in the dump

-F,--flush-logs flush logs file in server before starting dump. Note that

If you dump many databases at once (using the option

--databases= or--all-databases), the logs would be

Flushed for each database dumped. The exception is when

Using--lock-all-tables or--master-data:in this case

The logs is flushed only once and corresponding to the

Moment all tables are locked. So if you want your dump

and the log flush to happen at the same exact moment

Should use--lock-all-tables or--master-data with

--flush-logs.

-X,--lock-all-tables

Locks All tables across all databases. This is achieved

By taking a global read lock for the duration of the

Whole dump. Automatically turns--single-transaction and

--lock-tables off.

-L,--lock-tables lock all tables for read.

--master-data[=#] This causes the binary log position and filename to IS

appended to the output. If equal to 1, 'll print it as a

Change MASTER command; If equal to 2, that command would

Be prefixed with a comment symbol. This option would turn

--lock-all-tables on, unless--single-transaction

Specified too (in which case a global read lock are only

Taken a short time at the beginning of the dump; Don ' t

Forget to read about--single-transaction below). In all

Cases, any action on logs would happen at the exact moment

of the dump. Option automatically turns--lock-tables

Off.

-T,--no-create-info

Don ' t write table creation info.

-D,--no-data no row information.

-N,--no-set-names suppress the Set names statement

--opt Same as--add-drop-table,--add-locks,--create-options,

--quick,--extended-insert,--lock-tables,--set-charset,

and--disable-keys. Enabled By default, disable with

--skip-opt.

-Q,--quick Don ' t buffer query, dump directly to stdout. Do not cache

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/

-R,--routines Dump stored routines (functions and procedures).

--single-transaction

Creates a consistent snapshot by dumping all tables in a

Single transaction. Works only for tables stored in

Storage engines which support multiversioning (currently

only InnoDB does); The dump is not guaranteed to being

Consistent for the other storage engines. While a

--single-transaction dump is in process, to ensure a

Valid dump file (correct table contents and binary log

Position), no other connection should use the following

Statements:alter table, DROP table, RENAME table,

TRUNCATE TABLE, as consistent snapshot is not isolated

from them. Option automatically turns off--lock-tables.

--dump-date put a dump date to the "end of" the output.

--skip-opt Disable--opt. Disables--add-drop-table,--add-locks,

--create-options,--quick,--extended-insert,

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