MySQL丟數據及主從數據不一致的場景1.相關知識點：innodb_flush_log_at_trx_commitinnodb_flush_log_at_timeoutsync_binlogrelaylog、relayloginfo、masterinfo：master-info-repositoryrelay-log-info-repositorysync_relay_logsync_master_infosync_relay_log_info原文地址：/25704976/viewspace-1318714/隨著對MySQL的學習，發現了MySQL的很多問題，最重要的就是丟數據的問題。對于丟數據問題，我們應該了解丟數據的場景，這樣在以后的學習中多考慮如何去避免及解決這些問題。2.MySQL數據庫層丟數據場景本節我們主要介紹一下在存儲引擎層上是如何會丟數據的。2.1.InnoDB丟數據InnoDB支持事務，同Oracle類似，事務提交需要寫redo、undo。采用日志先行的策略，將數據的變更在內存中完成，并且將事務記錄成redo，順序的寫入redo日志中，即表示該事務已經完成，就可以返回給客戶已提交的信息。但是實際上被更改的數據還在內存中，并沒有刷新到磁盤，即還沒有落地，當達到一定的條件，會觸發checkpoint，將內存中的數據（page）合并寫入到磁盤，這樣就減少了離散寫、IOPS，提高性能。在這個過程中，如果服務器宕機了，內存中的數據丟失，當重啟后，會通過redo日志進行recovery重做。確保不會丟失數據。因此只要redo能夠實時的寫入到磁盤，InnoDB就不會丟數據。先來看一下innodb_flush_log_at_trx_commit這個參數:=0:每秒writecache&flushdisk=1:每次commit都writecache&flushdisk=2:每次commit都writecache，然后根據innodb_flush_log_at_timeout（默認為1s）時間flushdisk從這三個配置來看，顯然innodb_flush_log_at_trx_commit=1最為安全，因為每次commit都保證redo寫入了disk。但是這種方式性能對DML性能來說比較低，在我們的測試中發現，如果設置為2,DML性能要比設置為1高10倍左右。為什么oracle的實時寫要比innodb的實時寫性能更好？線程與進程？后面還需要研究大家可以考慮一下0與2的區別？在某些DML操作頻繁的場景下，庫的innodb_flush_log_at_trx_commit需要設置為2,這樣就存在丟數據的風險：當服務器出現宕機，重啟后進行crashrecovery則會丟失innodb_flush_log_at_timeout秒內的數據。PS:當開啟了內部XA事務（默認開啟），且開啟binlog，情況稍有不一樣，后面會進行介紹。2.2.MylSAM丟數據MyISAM存儲引擎在我們的生產中用的并不多，但是系統的數據字典表元數據等都是存儲在MyISAM引擎下。MyISAM不支持事務，且沒有datacache，所有DML操作只寫到OScache中，flushdisk操作均由OS來完成，因此如果服務器宕機，則這部分數據肯定會丟失。2.3.主從復制不一致主從復制原理：MySQL主庫在事務提交時寫binlog，并通過sync_binlog參數來控制binlog刷新到磁盤“落地”，而備庫通過IO線程從主庫讀取binlog，并記錄到本地的relaylog中，由本地的SQL線程再將relaylog的數據應用到本地數據庫，如下圖所示：從上圖我們可以看到，在主從環境中，增加了binlog，這就增加了環境的復雜性，因此也增加了丟數據以及數據不一致可能。在分析這些丟數據的可能性之前，我們先了解一下binlog的刷新機制以及MySQL的內部XA事務是如何保證binlog與redo的一致性的。2.3.1.binlog刷新機制master寫binlog與innodb引擎寫redo類似，也有參數控制：sync_binlog=0:表示MySQL不控制binlog的刷新，由文件系統自己控制它的緩存的刷新>0:表示每sync_binlog次事務提交，MySQL調用文件系統的刷新操作將緩存刷下去其中最安全的就是=1，表示每次事務提交，MySQL都會把binlog緩存刷下去，這樣在掉電等情況下，系統才有可能丟失1個事務的數據。當sync_binlog設置為1，對系統的IO消耗也是非常大的。2.3.2.內部XA事務原理MySQL的存儲引擎與MySQL服務層之間，或者存儲引擎與存儲引擎之間的分布式事務，稱之為內部XA事務。最為常見的內部XA事務存在與binlog與InnoDB存儲引擎之間。在事務提交時，先寫二進制日志，再寫InnoDB存儲引起的redo日志。對于這個操作要求必須是原子的，即需要保證兩者同時寫入，內部XA事務機制就是保證兩者的同時寫入。XA事務的大致流程:事務提交后，InnoDB存儲引擎會先做一個PREPARE操作，將事務的XID寫入到redo日志中。寫binlog日志再將該事務的commit信息寫到redolog中如果在步驟1和步驟2失敗的情況下，整個事務會回滾，如果在步驟3失敗的情況下，MySQL數據庫在重啟后會先檢查準備的UXID事務是否已經提交，若沒有，則在存儲引擎層再進行一次提交操作。這樣就保證了redo與binlog的一致性，防止丟數據。2.3.3.master庫寫redo、binlog不實時丟數據的場景上面我們介紹了MySQL的內部XA事務流程，但是這個流程并不是天衣無縫的，redo的ib_logfile與binlog日志如果被設置非實時flush，就有可能存在丟數據的情況。redo的trx_prepare未寫入，但binlog寫入，造成從庫數據量比主庫多。redo的trx_prepare與commit都寫入了，但是binlog未寫入，造成從庫數據量比主庫少。從目前來看，只能犧牲性能去換取數據的安全性，必須要設置redo和binlog為實時刷盤，如果對性能要求很高，則考慮使用SSD2.3.4.slave庫寫redo、binlog不實時丟數據的場景master正常，但是slave出現異常的情況下宕機，這個時候會出現什么樣的情況呢？如果數據丟失，slave的SQL線程還會重新應用嗎？這個我們需要先了解SQL線程的機制。slave讀取master的binlog日志后，需要落地3個文件：relaylog、relayloginfo、masterinfo：relaylog:即讀取過來的master的binlog，內容與格式與master的binlog一致relayloginfo:記錄SQLThread應用的relaylog的位置、文件號等信息masterinfo:記錄IOThread讀取master的binlog的位置、文件號、延遲等信息因此如果當這3個文件如果不及時落地，則主機crash后會導致數據的不一致。在MySQL5.6.2之前，slave記錄的master信息以及slave應用binlog的信息存放在文件中，即與。在5.6.2版本之后，允許記錄到table中，參數設置如下：master-info-repository=TABLErelay-log-info-repository=TABLE對應的表分別為mysql.slave_master_info與mysql.slave_relay_log_info，且這兩個表均為innodb引擎表。masterinfo與relayinfo還有3個參數控制刷新：sync_relay_log：默認為10000，即每10000次sync_relay_log事件會刷新到磁盤。為0則表示不刷新，交由OS的cache控制。sync_master_info:若master-info-repository為FILE，當設置為0，則每次sync_master_info事件都會刷新到磁盤，默認為10000次刷新到磁盤；若master-info-repository為TABLE，當設置為0，則表不做任何更新，設置為1，則每次事件會更新表#默認為10000sync_relay_log_info：若relay_log_info_repository為FILE，當設置為0，交由OS刷新磁盤，默認為10000次刷新到磁盤；若relay_log_info_repository為TABLE，且為INNODB存儲，則無論為任何值，則都每次evnet都會更新表。建議參數設置如下：sync_relay_log=1sync_master_info=1sync_relay_log_info=1master-info-repository=TABLErelay-log-info-repository=TABLE當這樣設置，導致調用fsync()/fdatasync()隨著master的事務的增加而增加，且若slave的binlog和redo也實時刷新的話，會帶來很嚴重的IO性能瓶頸。2.3.5.master宕機后無法及時恢復造成的數據丟失當master出現故障后，binlog未及時傳到slave，或者各個slave收到的binlog不一致。且master無法在第一時間恢復，這個時候怎么辦？如果master不切換，則整個數據庫只能只讀，影響應用的運行。如果將別的slave提升為新的master，那么原master未來得及傳到slave的binlog的數據則會丟失，并且還涉及到下面2個問題。各個slave之間接收到的binlog不一致，如果強制拉起一個slave，則slave之間數據會不一致。原master恢復正常后，由于新的master日志丟棄了部分原master的binlog日志，這些多出來的binlog日志怎么處理，重新搭建環境？對于上面出現的問題，一種方法是確保binlog傳到從庫，或者說保證主庫的binlog有多個拷貝。第二種方法就是允許數據丟失，制定一定的策略，保證最小化丟失數據。1.確保binlog全部傳到從庫方案一：使用semisync(半同步)方式，事務提交后，必須要傳到slave，事務才能算結束。對性能影響很大，依賴網絡適合小tps系統。方案二：雙寫binlog，通過DBDROS層的文件系統復制到備機，或者使用共享盤保存binlog日志。方案三：在數據層做文章，比如保證數據庫寫成功后，再異步隊列的方式寫一份，部分業務可以借助設計和數據流解決。2.保證數據最小化丟失上面的方案設計及架構比較復雜，如果能容忍數據的丟失，可以考慮使用淘寶的TMHA復制管理工具。當master宕機后，TMHA會選擇一個binlog接收最大的slave作為master。當原master宕機恢復后，通過binlog的逆向應用，把原master上多執行的事務回退掉。3.總結通過上面的總結分析，MySQL丟數據的場景是五花八門，涉及到單庫的丟數據場景、主從的丟數據場景以及MySQL內部XA事務原理等，相對還比較復雜，有點難以理解。只有當我們了解了這些丟數據的場景，才能更好的去學習，并解決這些問題。根據分布式領域的CAP理論(Consistency、Availability、Partitiontolerance)，任何的分布式系統只能同時滿足2點，沒辦法三者兼顧oMySQL的主從環境滿足Availability，且主從互不干擾，因此滿足Partitiontolerance，但是不滿足Consistency，如果需要滿足Consistency，則肯定會失去Partitiontolerance，因此實現100%高可用性的MySQL主從架構還是非常困難的。只能通過一些設計去犧牲部分特性去滿足另外的特性。后續會重點對MySQL高可用性的架構方案進行進一步測試和研究。PS:關于Partitiontolerance解釋：Partitiontoleranceissacrif