mysqldump的single-transaction和master-data

先看一下--lock-tables和--lock-all-tables

 --lock-all-tables

一次性鎖定所有資料庫的所有表，在整個dump期間一直獲取global read lock；

該選項自動關閉—lock-tables和—single-transaction；

--lock-tables

以database為單位，dump前鎖定其下所有表；如果是Myisam表則採用 read local模式，允許同時insert；

--opt自動啟用—lock-tables；

注：--opt包含--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

 

再看一下官方對於single-transaction的解釋

--single-transaction

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

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be 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 are 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.

可以總結出以下幾點：

1 只適用於innodb；

2 需要repeatable read模式開啟一個事務

3 執行期間不阻礙DML和DDL，但是不要人工執行alter/create/drop/rename/truncate table；

4  不能與lock-tables共用，後者執行的LOCK TABLES會隱式提交所有pending事務

執行流程

（1）SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

（2）START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

（3）UNLOCK TABLES

如果mysqldump只指定single-transaction，期間不會加鎖也不會阻止任何的事務；

 

--master-data

預設啟用—lock-all-tables，如果顯示指定—single-transaction則棄用—lock-all-tables，此時只在dump開始時短暫獲取global read lock；

執行流程

（1）FLUSH  TABLES

（2）FLUSH TABLES WITH READ LOCK

（3）SHOW MASTER STATUS

同時使用Master-data和single-transaction可以對Innodb進行Online backup

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

執行流程

（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的持續時間很短，獲取當前binlog的位置資訊後立即釋放；

注意：

  在執行時如果當前有一個事務長時間沒有結束，那麼FLUSH TABLES WITH READ LOCK將會一直等待，而更加嚴重的是，阻塞的FLUSH TABLES WITH READ LOCK會進一步阻塞後續的DML，從而造成mysql hang；

--dump-slave

5.5引入了dump-slave選項，可對slave執行mysqldump，得出的change master to卻是指向master，即使用slave建立一個新的slave；

執行期間會停止slave sql thread，完成後自動恢復；

 

 MDL
5.5有了MDL（Meta data lock），所以–single-transaction時，事務內操作過的表都會持有MDL，因此不會被DDL破壞。
例如，mysqldump已經備份了a，b，c表，因為它們在事務內，事務還沒提交，它們的MDL不會釋放，因此另外的執行緒如果做a,b,c中任意一張表的DDL操作，都會出現Waiting for table metadata lock，而還沒備份到的表不會持有MDL，因此還可以做DDL。