在mysqldump過程中，之前其實一直不是很理解為什麼加了--single-transaction就能保證innodb的資料是完全一致的，而myisam引擎無法保證，必須加--lock-all-tables，前段時間抽空詳細地檢視了整個mysqldump過程。

理解master-data和--dump-slave

--master-data=2表示在dump過程中記錄主庫的binlog和pos點，並在dump檔案中註釋掉這一行；

--master-data=1表示在dump過程中記錄主庫的binlog和pos點，並在dump檔案中不註釋掉這一行，即恢復時會執行；

--dump-slave=2表示在dump過程中，在從庫dump，mysqldump程式也要在從庫執行，記錄當時主庫的binlog和pos點，並在dump檔案中註釋掉這一行；

--dump-slave=1表示在dump過程中，在從庫dump，mysqldump程式也要在從庫執行，記錄當時主庫的binlog和pos點，並在dump檔案中不註釋掉這一行；

注意:在從庫上執行備份時，即--dump-slave=2，這時整個dump過程都是stop io_thread的狀態

深入理解--single-transaction：

開啟general_log，準備一個資料量較小的db，開啟備份，新增--single-transaction和--master-data=2引數，檢視general_log，資訊如下，每一步新增了我的理解

整個dump過程是同一個連線id 32，這樣能保證在設定session級別的變數的時候不影響到其他連線

thread_id: 32
argument: ucloudbackup@localhost on
*************************** 14. row ***************************
thread_id: 32
argument: /*!40100 SET @@SQL_MODE='' */
*************************** 15. row ***************************
thread_id: 32
argument: /*!40103 SET TIME_ZONE='+00:00' */
*************************** 16. row ***************************
thread_id: 32
argument: FLUSH /*!40101 LOCAL */ TABLES
*************************** 17. row ***************************
thread_id: 32
argument: FLUSH TABLES WITH READ LOCK
批註：因為開啟了--master-data=2，這時就需要flush tables with read lock鎖住全庫，記錄當時的master_log_file和master_log_pos點
*************************** 18. row ***************************
thread_id: 32
argument: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
批註：--single-transaction引數的作用，設定事務的隔離級別為可重複讀，即REPEATABLE READ，這樣能保證在一個事務中所有相同的查詢讀取到同樣的資料，也就大概保證了在dump期間，如果其他innodb引擎的執行緒修改了表的資料並提交，對該dump執行緒的資料並無影響，然而這個還不夠，還需要看下一條
*************************** 19. row ***************************
thread_id: 32
argument: START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
這時開啟一個事務，並且設定WITH CONSISTENT SNAPSHOT為快照級別（如果mysql版本高於某一個版本值，我還不大清楚40100代表什麼版本）。想象一下，如果只是可重複讀，那麼在事務開始時還沒dump資料時，這時其他執行緒修改並提交了資料，那麼這時第一次查詢得到的結果是其他執行緒提交後的結果，而WITH CONSISTENT SNAPSHOT能夠保證在事務開啟的時候，第一次查詢的結果就是事務開始時的資料A，即使這時其他執行緒將其資料修改為B，查的結果依然是A，具體的測試看我下面的測試結果
*************************** 20. row ***************************
thread_id: 32
argument: SHOW MASTER STATUS
這時候執行這個命令來記錄當時的master_log_file和master_log_pos點，注意為什麼這個時候記錄，而不是再18 row和19 row之間就記錄，個人認為應該都是可以的，這裡是測試結果，start transaction並不會產生binlog的移動，而18 row和19 row的動作也在同一個thread id中
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 1690 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

*************************** 21. row ***************************
thread_id: 32
argument: UNLOCK TABLES
等記錄完成後，就立即釋放了，因為現在已經在一個事務中了，其他執行緒再修改資料已經無所謂，在本執行緒中已經是可重複讀，這也是這一步必須在19 rows之後的原因，如果20 rows和21 rows都在19 rows之前的話就不行了，因為這時事務還沒開啟，一旦釋放，其他執行緒立即就可以更改資料，從而無法保證得到事務開啟時最準確的pos點。*************************** 22. row ***************************
thread_id: 32
argument: SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME IN ('user'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
*************************** 23. row ***************************
thread_id: 32
argument: SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME IN ('user')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
*************************** 24. row ***************************
thread_id: 32
argument: mysql
*************************** 25. row ***************************
thread_id: 32
argument: SHOW TABLES LIKE 'user'
*************************** 26. row ***************************
thread_id: 32
argument: show table status like 'user'
dump表以前都需要show一下各自資訊，確保表，檢視等不損壞，可用，每一步錯了mysqldump都會報錯並中斷，給出對應的錯誤碼，常見的myqldump錯誤請參考我的另外一篇blog http://blog.csdn.net/cug_jiang126com/article/details/49359699
*************************** 27. row ***************************
thread_id: 32
argument: SET OPTION SQL_QUOTE_SHOW_CREATE=1
*************************** 28. row ***************************
thread_id: 32
argument: SET SESSION character_set_results = 'binary'
*************************** 29. row ***************************
thread_id: 32
argument: show create table `user`
*************************** 30. row ***************************
thread_id: 32
argument: SET SESSION character_set_results = 'utf8'
*************************** 31. row ***************************
thread_id: 32
argument: show fields from `user`
*************************** 32. row ***************************
thread_id: 32
argument: SELECT /*!40001 SQL_NO_CACHE */ * FROM `user`
這就是我們show processlist時看到的資訊，而資料是怎麼透過一條select語句就dump到本地檔案裡的呢，並且還轉成成相應的create和insert語句，這就是mysqldump這個客戶端工具的工作了，這裡不做討論
*************************** 33. row ***************************
最後並沒有看到commit，因為在整個事務中，其實並沒有修改任何資料，只是為了保證可重複讀得到備份時間點一致性的快照，dump完成後提交不提交應該無所謂了。

myisam引擎為什麼無法保證在--single-transaction下得到一致性的備份？

因為它壓根就不支援事務，自然就無法實現上述的過程，雖然新增了--single-transaction引數的myisam表處理過程和上面的完全一致，但是因為不支援事務，在整個dump過程中無法保證可重複讀，無法得到一致性的備份。而innodb在備份過程中，雖然其他執行緒也在寫資料，但是dump出來的資料能保證是備份開始時那個binlog pos的資料。

myisam引擎要保證得到一致性的資料的話，他是如何實現的呢？

它是透過新增--lock-all-tables，這樣在flush tables with read lock後，直到整個dump過程結束，斷開執行緒後才會unlock tables釋放鎖(沒必要主動發unlock tables指令)，整個dump過程其他執行緒不可寫，從而保證資料的一致性

如果我一定要在mysiam引擎中也新增--single-transaction引數，再用這個備份去建立從庫或恢復到指定時間點，會有什麼樣的影響？

我個人的理解是如果整個dump過程中只有簡單的insert操作，是沒有關係的，期間肯定會有很多的主鍵重複錯誤，直接跳過或忽略就好了。如果是update操作，那就要出問題了，分幾種情況考慮

1）如果是基於時間點的恢復，假設整個dump過程有update a set id=5 where id=4之類的操作，相當於重複執行兩次該操作，應該問題不大
2）如果是建立從庫，遇到上面的sql從庫會報錯，找不到該記錄，這時跳過就好

3）不管是恢復還是建立從庫，如果dump過程中有update a set id=id+5 之類的操作，那就有問題，重複執行兩次，資料全變了。

深入理解--lock-all-tables

開啟general_log，準備一個資料量較小的db，開啟備份，新增--lock-all-tables(其實也是預設設定)和--master-data=2引數，檢視general_log，資訊如下，理解--lock-all-tables怎麼保證資料一致性

mysql> select thread_id,argument from general_log where thread_id=185\G
*************************** 1. row ***************************
thread_id: 185
argument: ucloudbackup@10.10.108.15 on
*************************** 2. row ***************************
thread_id: 185
argument: /*!40100 SET @@SQL_MODE='' */
*************************** 3. row ***************************
thread_id: 185
argument: /*!40103 SET TIME_ZONE='+00:00' */
*************************** 4. row ***************************
thread_id: 185
argument: FLUSH /*!40101 LOCAL */ TABLES
*************************** 5. row ***************************
thread_id: 185
argument: FLUSH TABLES WITH READ LOCK
這裡flush tables with read lock之後就不會主動unlock tables，保證整個dump過程整個db資料不可更改，也沒有事務的概念了
*************************** 6. row ***************************
thread_id: 185
argument: SHOW MASTER STATUS
同樣記錄主庫的位置
*************************** 7. row ***************************
thread_id: 185
argument: SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
*************************** 8. row ***************************
thread_id: 185
argument: SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
*************************** 9. row ***************************
thread_id: 185
argument: SHOW DATABASES
*************************** 10. row ***************************
thread_id: 185
argument: jjj
*************************** 11. row ***************************
thread_id: 185
argument: SHOW CREATE DATABASE IF NOT EXISTS `jjj`

測試可重複讀和快照讀(WITH CONSISTENT SNAPSHOT )

準備工作3.1（測試可重讀）

session 1:
mysql> select * from xx;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
設定事務隔離級別為可重複讀

mysql> START TRANSACTION ;
Query OK, 0 rows affected (0.00 sec)
我們先不開快照讀觀察現象

session 2:
mysql> insert into xx values (5);
Query OK, 1 row affected (0.00 sec)

session 1:
mysql> select * from xx;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
批註：這時因為沒有設定快照讀，所以當session 2有資料更新時，可查到該資料，接

下來我們繼續在session 2 插入資料
session 2:
mysql> insert into xx values (6);
Query OK, 1 row affected (0.00 sec)

這時再觀察session 1的資料
session 1
mysql> select * from xx;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
查詢發現還是隻有5條，表示可重複實現了。

準備工作3.2（測試快照讀）

session 1
mysql> select * from xx;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */;
Query OK, 0 rows affected (0.00 sec)

這時我們在session 2插入資料
session 2：
mysql> insert into xx values (2);
Query OK, 1 row affected (0.00 sec)

這時我們再觀察session 1的結果
session 1：
mysql> select * from xx;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
發現還是隻有一條資料，證明實現了快照讀
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from xx;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
事務1 提交後方可看見第二條記錄

深入理解mysqldump原理 --single-transaction --lock-all-tables --master-data