"Reprint" Mysqldump of Single-transaction and Master-data

Original address: Mysqldump's single-transaction and Master-data Myownstars

Let's take a look at--lock-tables and--lock-all-tables.

--lock-all-tables

Lock all tables in all databases at once, acquiring global read lock throughout the dump;

This option automatically turns off-lock-tables and-single-transaction;

--lock-tables

In database, the dump locks all the tables below it, and if it is a MyISAM table, the read local mode is used to allow simultaneous insert;

--opt automatically enable-lock-tables;

Note:--opt contains--add-drop-table--add-locks--create-options--disable-keys--extended-insert--lock-tables--quick-- Set-charset

And look at the official explanation for Single-transaction.

--single-transaction

This option sets the transaction isolation mode to repeatable READ and sends a START transaction SQL statement to the Serv ER before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the The time when START TRANSACTION is issued without blocking any applications.

When using the This option, you should keep on mind that is only InnoDB tables is dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped and using this option, the still change state.

While a--single-transaction dump was in process, to ensure a valid dump file (correct table contents and binary log Coordi Nates), no other connection should use the following Statements:alter table, CREATE TABLE, DROP table, RENAME table, Trun CATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to being dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The--single-transaction option and the--lock-tables option is mutually exclusive because lock tables causes any pending Transactions to be committed implicitly.

To dump large tables, combine the--single-transaction option with the--quick option.

The following points can be summed up:

1 only applicable to InnoDB;

2 requires repeatable read mode to open a transaction

3 The execution period does not obstruct DML and DDL, but do not manually execute Alter/create/drop/rename/truncate table;

4 cannot be shared with Lock-tables, the latter execution of lock tables implicitly commits all pending transactions

Execution process

(1) SET SESSION TRANSACTION isolation level repeatable READ

(2) START TRANSACTION/*!40100 with consistent SNAPSHOT */

(3) UNLOCK TABLES

If the mysqldump only specifies Single-transaction, the period will not lock and will not block any transactions;

--master-data

-lock-all-tables is enabled by default, and if the specified-single-transaction is displayed, 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 STATUS

Online backup for InnoDB can be done using both Master-data and single-transaction

shell> mysqldump--all-databases--master-data--single-transaction > All_databases.sql

Execution 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 TABLES

Global read lock has a short duration and is released immediately after obtaining the location information of the current binlog;

Attention:

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, resulting in MySQL Hang

-- Dump-slave

5.5 introduced the Dump-slave option, the slave execution mysqldump, the resulting change master to the master, that is, the use of slave to create a new slave;

The slave SQL thread is stopped during execution and is automatically restored upon completion;

MDL
5.5 Has the MDL (Meta data Lock), so when –single-transaction, the tables that have been manipulated within the transaction hold the MDL and are therefore not corrupted by the DDL.
For example, Mysqldump has backed up the A,b,c table because they are within the transaction, the transactions are not committed, their MDL is not freed, and the other thread will appear waiting for table metadata lock if it does DDL operations on any table in the A,b,c , and a table that has not been backed up does not hold the MDL, so it can also do DDL.
Http://www.penglixun.com/tech/database/the_process_of_mysqldump.html

"Reprint" Mysqldump of Single-transaction and Master-data