mysql 主從同步過程詳解、主從延遲原理分析

來自： http://blog.csdn.net//clh604/article/details/19680291

復制的基本過程如下：

1. Slave上面的IO進程連接上Master，并請求從指定日志文件的指定位置（或者從最開始的日志）之后的日志內容；
2. Master接收到來自Slave的IO進程的請求后，通過負責復制的IO進程根據請求信息讀取制定日志指定位置之后的日志信息，返回給Slave 的IO進程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經到Master端的bin-log文件的名稱以及bin-log的位置；
3. Slave的IO進程接收到信息后，將接收到的日志內容依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往后的日志內容，請發給我”；
4. Slave的Sql進程檢測到relay-log中新增加了內容后，會馬上解析relay-log的內容成為在Master端真實執行時候的那些可執行的內容，并在自身執行。

Mysql為了解決這個風險并提高復制的性能，將Slave端的復制改為兩個進程來完成。提出這個改進方案的人是Yahoo!的一位工程師“Jeremy Zawodny”。這樣既解決了性能問題，又縮短了異步的延時時間，同時也減少了可能存在的數據丟失量。當然，即使是換成了現在這樣兩個線程處理以后，同樣也還是存在slave數據延時以及數據丟失的可能性的，畢竟這個復制是異步的。只要數據的更改不是在一個事物中，這些問題都是會存在的。如果要完全避免這些問題，就只能用mysql的cluster來解決了。不過mysql的cluster是內存數據庫的解決方案，需要將所有數據都load到內存中，這樣就對內存的要求就非常大了，對于一般的應用來說可實施性不是太大。

復制常用架構

Mysql復制環境90%以上都是一個Master帶一個或者多個Slave的架構模式，主要用于讀壓力比較大的應用的數據庫端廉價擴展解決方案。因為只要master和slave的壓力不是太大（尤其是slave端壓力）的話，異步復制的延時一般都很少很少。尤其是自slave端的復制方式改成兩個進程處理之后，更是減小了slave端的延時。而帶來的效益是，對于數據實時性要求不是特別的敏感度的應用，只需要通過廉價的pc server來擴展slave的數量，將讀壓力分散到多臺slave的機器上面，即可解決數據庫端的讀壓力瓶頸。這在很大程度上解決了目前很多中小型網站的數據庫壓力瓶頸問題，甚至有些大型網站也在使用類似方案解決數據庫瓶頸。

2. MySQL數據庫主從同步延遲原理。
要說延時原理，得從mysql的數據庫主從復制原理說起，mysql的主從復制都是單線程的操作，
主庫對所有DDL和DML產生binlog，binlog是順序寫，所以效率很高，slave的Slave_IO_Running線程到主庫取日志，效率很比較高，下一步，問題來了，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產生lock爭用，由于Slave_SQL_Running也是單線程的，所以一個DDL卡主了，需要執行10分鐘，那么所有之后的DDL會等待這個DDL執行完才會繼續執行，這就導致了延時。有朋友會問：“主庫上那個相同的DDL也需要執行10分，為什么slave會延時？”，答案是master可以并發，Slave_SQL_Running線程卻不可以。

3. MySQL數據庫主從同步延遲是怎么產生的。
當主庫的TPS并發較高時，產生的DDL數量超過slave一個sql線程所能承受的范圍，那么延時就產生了，當然還有就是可能與slave的大型query語句產生了鎖等待。

4. MySQL數據庫主從同步延遲解決方案。
丁奇的transefer是一個不錯的方案，不過一般公司受限于對mysql的代碼修改能力的限制和對mysql的掌控能力，還是不太適合。
最簡單的減少slave同步延時的方案就是在架構上做優化，盡量讓主庫的DDL快速執行。還有就是主庫是寫，對數據安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設置，而slave則不需要這么高的數據安全，完全可以講sync_binlog設置為0或者關閉binlog，innodb_flushlog也可以設置為0來提高sql的執行效率。另外就是使用比主庫更好的硬件設備作為slave。
mysql-5.6.3已經支持了多線程的主從復制。原理和丁奇的類似，丁奇的是以表做多線程，oracle使用的是以數據庫(schema)為單位做多線程，不同的庫可以使用不同的復制線程。

sync_binlog=1 o
     This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
     默認情況下，并不是每次寫入時都將binlog與硬盤同步。因此如果操作系統或機器(不僅僅是MySQL服務器)崩潰，有可能binlog中最后的語句丟 失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入后與硬盤 同步。即使sync_binlog設置為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，MySQL服務器 處理COMMIT語句，它將整個事務寫入binlog并將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB回滾，但仍 然存在binlog中。可以用--innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的 binlog(sync_binlog =1)和(默認情況為真)InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務后，MySQL服務器從binlog剪切回滾的 InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數據等，并使從服務器保持與主服務器保持同步(不接收 回滾的語句)。

innodb_flush_log_at_trx_commit （這個很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調整這個值。默認值1的意思是每一次事務提交或事務外的指令都需要把日志寫入（flush）硬盤，這是很費時的。特別是使用電 池供電緩存（Battery backed up cache）時。設成2對于很多運用，特別是從MyISAM表轉過來的是可以的，它的意思是不寫入硬盤而是寫入系統緩存。日志仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的數據。而值2只會在整個操作系統 掛了時才可能丟數據。