A summary of mysqldump

Mysqldump for database backup

1) In terms of performance mysqldump more suitable for InnoDB backup, and MyISAM is more suitable for mysqlhotcopy

Mysqldump backup data can be backed up in two ways: by placing the entire table in the buffer and then backing it up; one row of backups (without memory buffers)

2) mysqldump--opt (-mysqldump own default--opt, it automatically enable--quick) one line of backup does not occupy the buffer area, cancel this mode with--skip-quick.

3) Common wording:

Shell> mysqldump [Options] db_name [tbl_name ...]
Shell> mysqldump [options]--databases db_name ...
Shell> mysqldump [Options]--all-databases
4) Common options:

--opt (default): the equivalent of adding the following information:--add-drop-table,--add-locks,--create-options,--disable-keys,---extended -insert,--lock-tables,--quick,--set-charset.

--opt--skip-extended-insert--skip-quick: Indicates no backup Extended-insert and takes buffer other opt-in information All have.

--skip-opt--disable-keys--lock-tables:opt content is not, only--disable-keys--lock-tables.

--compact: equivalent to adding the following information:--skip-add-drop-table,--skip-add-locks,--skip-comments,--skip-disable-key S,--skip-set-charset options.

--dump-date (default): Add a comment after each backup (the comment is the backup date), cancel the default by:--skip-dump-date

--flush-logs,-F: Save Log before backup

--ignore-table=db_name.tbl_name: Ignore table (or view), do not back up multiple tables, reuse the statement multiple times

--order-by-primary: Back up each row in the order of the primary key or unique index

--master-data[=value] (value default is 1): In this way can be generated by the change of the host file to set the host's                                                                                if value is 2, that change master to just information is not actually                                                                                  operationIf value is 1, then the reload backup data when the change                                                                        The         master to file will work. (I don't know well here, but I'll add it later.)

--result-file=file_name,-R file_name: Sets the destination file for the backup, and the backup data is written to the file in a overwritten manner.

--single-transaction: Backup does not change any state of the InnoDB table, other types of table state may change.

--where= ' Where_condition ',-W ' where_condition ': Conditional backup (commonly used for specified row backups of a specified table)

Example:--where= "user= ' JIMF '"
-W "userid>1"
-W "userid<1"

5) Usage Example:

shell> mysqldump--all-databases--master-data=2 > All_databases.sql

Or:

shell> mysqldump--all-databases--flush-logs--master-data=2
> All_databases.sql

A summary of mysqldump