MySQL database backup command mysqldump parameters detailed

Mysqldump is a good command for MySQL database backup, and it comes with MySQL.
- D: Only the table structure is backed up, backup files are SQL statements, only statements that create tables are backed up, and the inserted data is not backed up.

- t: Backs up data only, data is text, table structure is not backed up

- t [--tab]: Table structure and data separation, table structure is SQL file, data is normal file

-A: Export all databases

- B: Export the specified database

-X,--lock-all-tables: Lock table
Lock table principle: From the execution of the scheduled backup script (with the-x parameter), can not be updated to the table, but the shortcomings, the lock list can not be updated, if the single library generally in the trough, such as the early hours of the morning, multi-Library, from the Library lock table backup (and from the library is not, only backup)
Locks All tables across all databases. This was achieved by taking a global read lock for the duration of the whole dump.
Automatically turns--single-transaction and--lock-tables off. Enabling this option will automatically turn off--single-transaction and--lock-tables.

-L,--lock-tables: Read-only lock table
Lock all tables before dumping them
Lock all tables for read.
(Defaults to In; Use--skip-lock-tables to disable.)
This option is turned on by default, as mentioned above. Its role is to lock all tables during the export process. --single-transaction and--lock-all-tables will turn this option off.
When you explicitly add a table lock to a table by using lock tables, you must also get all the locks that relate to the table, that is, after you perform lock tables, you can only access those tables that are explicitly locked, and you cannot access the unlocked tables, and if you add a read lock, you can only perform query operations on the lock table. MyISAM always gets all the locks required by the SQL statement at once. This is why the MyISAM table does not appear deadlocked (Deadlock free).

--single-transaction
--single-transaction can get consistent export results. He achieves the purpose by putting the export behavior into a transaction.
It has some requirements: only the InnoDB engine; During the export process, no one can execute a DDL statement such as ALTER TABLE, DROP table, rename table, TRUNCATE table.
The DDL is actually blocked because the transaction holds the shared lock of the metadata lock on the table, and the DDL requests metadata Lock's mutex, so it is blocked.
The--single-transaction automatically turns off the--lock-tables option, which we said mysqldump by default,--lock-tables is turned on, and it locks all tables during the export process.
Because--single-transaction automatically turns off--lock-tables, using--single-transaction alone does not use locks. Locks are shared with--master-data.

-Q: No buffer queries, direct to standard output

- R: Exporting stored procedures and functions

- e,--events: Export scheduled events

--add-drop-database
Add a drop DATABASE statement before the CREATE DATABASE statement, typically with--all-databases or--databases, because only one of these is used to record the CREATE DATABASE statement.

--add-drop-table
Add the CREATE TABLE statement before the CREATE TABLE statement.

--add-drop-trigger
Add a drop TRIGGER statement before the CREATE TRIGGER statement

--all-tablespaces, Y
Export all table spaces. This parameter is currently only available on the MySQL cluster table (NDB engine)

--add-locks
Add lock tables before each table is exported and then unlock table. (The default is on, using the--skip-add-locks cancel option)

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

- F,--flush-logs: Refresh Binlog Log

--master-data
Mysqldump export data, when the value of this parameter is 1, the mysqldump out of the backup file will include the change master to this statement, change master to immediately after the file and position records, The statement is executed when the data is imported on slave, and salve is copied from the master side according to the Binlog file and the location point in the Binlog log file specified later in the change Master to. By default, this value is 1. Operations are often used to this parameter, master-slave replication, this parameter is a good function, but also can do incremental recovery.
When the value of this parameter is 2, the backup file mysqldump will also contain the change MASTER to statement, but the statement is commented out and will not take effect, just provide a message.
--master-data will also refresh the Binlog log.

MySQL database backup command mysqldump parameters detailed