Several main options of mysqldump

1. mysqldump

First, let's take a look.MysqldumpThe actual working method of several main parameters.

Main mysqldump options
1.-Q
It's very simple. I just added SQL _no_cacheTo ensure that the data in the cache is not read.

081022 17:39:33       7 Connect     root@localhost on7 Query       /*!40100 SET @@SQL_MODE='' */7 Init DB     yejr7 Query       SHOW TABLES LIKE 'yejr'7 Query       LOCK TABLES `yejr` READ /*!32311 LOCAL */7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=17 Query       show create table `yejr`7 Query       show fields from `yejr`7 Query       show table status like 'yejr'7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`7 Query       UNLOCK TABLES7 Quit

2. -- lock-tables
It is similar to the above, but an additionalRead local lockThe lock does not stop reading or new data insertion.

081022 17:36:21       5 Connect     root@localhost on5 Query       /*!40100 SET @@SQL_MODE='' */5 Init DB     yejr5 Query       SHOW TABLES LIKE 'yejr'5 Query       LOCK TABLES `yejr` READ /*!32311 LOCAL */5 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=15 Query       show create table `yejr`5 Query       show fields from `yejr`5 Query       show table status like 'yejr'5 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`5 Query       UNLOCK TABLES5 Quit

3. -- lock-all-tables
This is a bit different. When it requests a global read lock, it will block write operations on all tables to ensure data consistency. After the backup is complete, the session is disconnected and automatically unlocked.

081022 17:36:55       6 Connect     root@localhost on6 Query       /*!40100 SET @@SQL_MODE='' */6 Query       FLUSH TABLES6 Query       FLUSH TABLES WITH READ LOCK6 Init DB     yejr6 Query       SHOW TABLES LIKE 'yejr'6 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=16 Query       show create table `yejr`6 Query       show fields from `yejr`6 Query       show table status like 'yejr'6 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`6 Quit

4. -- master-Data
Except for the previous -- lock-all-tablesShow Master StatusThere are no other changes.

081022 17:59:02       1 Connect     root@localhost on1 Query       /*!40100 SET @@SQL_MODE='' */1 Query       FLUSH TABLES1 Query       FLUSH TABLES WITH READ LOCK1 Query       SHOW MASTER STATUS1 Init DB     yejr1 Query       SHOW TABLES LIKE 'yejr'1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=11 Query       show create table `yejr`1 Query       show fields from `yejr`1 Query       show table status like 'yejr'1 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`1 Quit

5. -- Single-transaction

InnoDB tables are usually enabled during Backup.-- Single-transactionTo ensure the consistency of the backup. In fact, the working principle is to set the isolation level of this session to Repeatable read to ensure that during this session (dump, data submitted by other sessions is not displayed.

081022 17:23:35       1 Connect     root@localhost on1 Query       /*!40100 SET @@SQL_MODE='' */1 Query       SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ1 Query       BEGIN1 Query       UNLOCK TABLES1 Init DB     yejr1 Query       SHOW TABLES LIKE 'yejr'1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=11 Query       show create table `yejr`1 Query       show fields from `yejr`1 Query       show table status like 'yejr'1 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`1 Quit

6. -- Single-transaction and -- master-Data
In this example-- Master-DataTherefore, you need to submit a quick global read lock. Here, we can see that the difference from the above is that the launch is missing.BeginTo explicitly declare the start of the transaction. Here we useStart transaction with consistent SnapshotTo replaceBeginFor the sake of understanding, you can look at the source code for analysis.

081022 17:27:07       2 Connect     root@localhost on2 Query       /*!40100 SET @@SQL_MODE='' */2 Query       FLUSH TABLES2 Query       FLUSH TABLES WITH READ LOCK2 Query       SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2 Query       START TRANSACTION WITH CONSISTENT SNAPSHOT2 Query       SHOW MASTER STATUS2 Query       UNLOCK TABLES2 Init DB     yejr2 Query       SHOW TABLES LIKE 'yejr'2 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=12 Query       show create table `yejr`2 Query       show fields from `yejr`2 Query       show table status like 'yejr'2 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`2 Quit

For more information about the isolation level, see the manual 13.2.10.3. InnoDB and transaction isolation level, or the previous article on this site: [InnoDB series]
-Instance parsing: InnoDB isolation level and lock mode.

AboutStart transaction with consistent SnapshotYou can refer to the following manual description:

The WITH CONSISTENT SNAPSHOT clause starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB  table. See Section 13.2.10.4, “Consistent Non-Locking Read”. The WITH CONSISTENT SNAPSHOT clause does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that allows consistent read (REPEATABLE READ or SERIALIZABLE).

12.4.1. Start transaction, commit, and rollback syntax

Original http://imysql.cn/2008_10_24_deep_into_mysqldump_options