理解–single-transaction 和–lock-all-tables

來源:本站原創 mysql_mariadb 超過207 views圍觀 0條評論

在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: [email protected] 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: [email protected] 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`
————————————————
版權聲明:本文為CSDN博主「rewiner120」的原創文章,遵循CC 4.0 by-sa版權協議,轉載請附上原文出處鏈接及本聲明。
原文鏈接:https://blog.csdn.net/rewiner120/article/details/70598828

文章出自:CCIE那點事 http://www.qdxgqk.live/ 版權所有。本站文章除注明出處外,皆為作者原創文章,可自由引用,但請注明來源。 禁止全文轉載。
本文鏈接:http://www.qdxgqk.live/?p=4186轉載請注明轉自CCIE那點事
如果喜歡:點此訂閱本站
?
?
萌宠夺宝游戏 河北11选5现场直播 抖音的赚钱规则 内蒙古时时彩开奖直播网 成龙秒拍赚钱 泳坛夺金彩票 聋哑人中彩票 gtv网络象棋频道 3d彩票 新11选5走势图 篮球竞彩投注技巧 北京十一选五开奖查询 江苏快3高频彩 老k棋牌app下载官方 17147足彩对阵分析 退市股票涨跌幅限制 如何做到微信合买双色球