1,--single-transactionInnoDB table when backing up, the option
--single-transaction is usually enabled to guarantee the consistency of the backup , in fact it works by setting the isolation level for this session to: repeatable READ To ensure that this session (dump) does not see data that has been submitted by other sessions.
2 、--master-dataBy default,-lock-all-tables is enabled, and if you explicitly specify-single-transaction, the-lock-all-tables is discarded, and the global read lock is only briefly acquired at dump start time;Execution Process(1) FLUSH TABLES(2) FLUSH TABLES with READ LOCK(3) SHOW MASTER STATUSOnline backup for InnoDB can be done using both Master-data and Single-transactionroot> mysqldump--all-databases--master-data--single-transaction > All_databases.sqlExecution Process(1) FLUSH TABLES(2) FLUSH TABLES with READ LOCK(3) SET SESSION TRANSACTION Isolation level repeatable READ(4) START TRANSACTION/*!40100 with consistent SNAPSHOT */(5) SHOW MASTER STATUS(6) UNLOCK TABLESGlobal read lock has a short duration and is released immediately after obtaining the location information of the current Binlog ; Note:flush TABLES With read lock will wait until the current transaction has been executed for a long time, and more seriously, the blocked flush TABLES with read lock will further block subsequent DML, thus causing MySQL hang;
2 、--single-transaction and--master-datadue to the addition of the option
--master-data, a fast global read lock is also required. The single-transaction option and the Lock-all-tables option are two , which is to set the transaction isolation state at the beginning of the export and start the transaction with a consistent snapshot, and then immediately unlock tables , and then perform the export, the export process does not affect other transactions or business connections, but it only supports engines similar to the InnoDB multi-version feature, because it is important to ensure that data is changed even when other operations (transaction point T2) during export, and when the export begins with the transaction point T1. And lock-all-tables the first FLUSH TABLES with READ LOCK; Add a global read lock until the dump is complete.
3 、--Quick,-QThis option is used to dump large tables. It forces mysqldump to retrieve rows from the server one row at a time instead of retrieving all the rows and caching it in memory before the output.
4 、--optThis option is shorthand, equivalent to specifying--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables-- Quick--set-charset. It can give a quick dump operation and generate a dump file that can be loaded into the MySQL server quickly. This option is turned on by default, but can be disabled with--skip-opt. To disable opt-enabled options only, use the--skip form, for example,--skip-add-drop-tables or--skip-quick.
5 、--all--database,-aDumps all tables in all databases. In the same way that you use the---database option, name all databases on the command line.
6 、--comments[={0|1}]if set to 0, prevents other information in the dump file, such as the program version, server version, and host. The--skip-comments is the same as the result of the---comments=0. The default value is 1, which includes additional information.
7 、--Compactproduces a small amount of output. This option disables annotations and enables--skip-add-drop-tables 、--no-set-names 、--skip-disable-keys and--skip-add-locking options.
8 、--flush-logs,-fflush the MySQL server log file before starting the dump. This option requires Reload permissions. Note If you use this option with the--all--database (or-a) option, the log is refreshed based on the database for each dump. The exception is when using--lock-all-tables or--master-data: In this case, the log is refreshed only once and refreshed after all tables have been locked. If you want to dump and refresh the log at the same time, you should use--flush-logs with--lock-all-tables or--master-data.
Database Backup tool mysqldump Important parameters
