Mysql主從基本原理，主要形式以及主從同步延遲原理 (讀寫分離)導致主庫從庫資料不一致問題的及解決方案
一、主從資料庫的區別
從資料庫(Slave)是主資料庫的備份，當主資料庫(Master)變化時從資料庫要更新，這些資料庫軟體可以設計更新周期，這是提高資訊安全的手段，主從資料庫服務器不在一個地理位置上，當發生意外時資料庫可以保存，
(1) 主從分工
其中Master負責寫操作的負載，也就是說一切寫的操作都在Master上進行，而讀的操作則分攤到Slave上進行，這樣一來的可以大大提高讀取的效率，在一般的互聯網應用中，經過一些資料調查得出結論，讀/寫的比例大概在 10：1左右 ，也就是說大量的資料操作是集中在讀的操作，這也就是為什么我們會有多個Slave的原因，但是為什么要分離讀和寫呢？熟悉DB的研發人員都知道，寫操作涉及到鎖的問題，不管是行鎖還是表鎖還是塊鎖，都是比較降低系統執行效率的事情，我們這樣的分離是把寫操作集中在一個節點上，而讀操作其其他的N個節點上進行，從另一個方面有效的提高了讀的效率，保證了系統的高可用性，
(2) 基本程序
1)、Mysql的主從同步就是當master（主庫）發生資料變化的時候，會實時同步到slave（從庫），
2)、主從復制可以水平擴展資料庫的負載能力，容錯，高可用，資料備份，
3)、不管是delete、update、insert，還是創建函式、存盤程序，都是在master上，當master有操作的時候，slave會快速的接受到這些操作，從而做同步，
(3) 用途和條件
1)、mysql主從復制用途
●實時災備，用于故障切換
●讀寫分離，提供查詢服務
●備份，避免影響業務
2)、主從部署必要條件：
●主庫開啟binlog日志（設定log-bin引數）
●主從server-id不同
●從庫服務器能連通主庫
二、主從同步的粒度、原理和形式：
(1)、 三種主要實作粒度
詳細的主從同步主要有三種形式：statement、row、mixed
1)、statement: 會將對資料庫操作的sql陳述句寫道binlog中
2)、row: 會將每一條資料的變化寫道binlog中，
3)、mixed: statement與row的混合，Mysql決定何時寫statement格式的binlog, 何時寫row格式的binlog，
(2)、主要的實作原理、具體操作、示意圖
1)、在master機器上的操作：
當master上的資料發生變化時，該事件變化會按照順序寫入bin-log中，當slave鏈接到master的時候，master機器會為slave開啟binlog dump執行緒，當master的binlog發生變化的時候，bin-log dump執行緒會通知slave，并將相應的binlog內容發送給slave，
2)、在slave機器上操作：
當主從同步開啟的時候，slave上會創建兩個執行緒：I\O執行緒，該執行緒連接到master機器，master機器上的binlog dump 執行緒會將binlog的內容發送給該I\O執行緒，該I/O執行緒接收到binlog內容后，再將內容寫入到本地的relay log；sql執行緒，該執行緒讀取到I/O執行緒寫入的ralay log，并且根據relay log，并且根據relay log 的內容對slave資料庫做相應的操作，
3)、MySQL主從復制原理圖如下:
從庫生成兩個執行緒，一個I/O執行緒，一個SQL執行緒；
i/o執行緒去請求主庫 的binlog，并將得到的binlog日志寫到relay log（中繼日志） 檔案中；
主庫會生成一個 log dump 執行緒，用來給從庫 i/o執行緒傳binlog；
SQL 執行緒，會讀取relay log檔案中的日志，并決議成具體操作，來實作主從的操作一致，而最終資料一致；
(2)、主從形式
mysql主從復制 靈活
● 一主一從
● 主主復制
● 一主多從---擴展系統讀取的性能，因為讀是在從庫讀取的；
● 多主一從---5.7開始支持
● 聯級復制---
三、主從同步的延遲等問題、原因及解決方案：
(1)、mysql資料庫從庫同步的延遲問題
1）相關引數：
首先在服務器上執行show slave satus;可以看到很多同步的引數：
Master_Log_File： SLAVE中的I/O執行緒當前正在讀取的主服務器二進制日志檔案的名稱 Read_Master_Log_Pos： 在當前的主服務器二進制日志中，SLAVE中的I/O執行緒已經讀取的位置 Relay_Log_File： SQL執行緒當前正在讀取和執行的中繼日志檔案的名稱 Relay_Log_Pos： 在當前的中繼日志中，SQL執行緒已讀取和執行的位置 Relay_Master_Log_File： 由SQL執行緒執行的包含多數近期事件的主服務器二進制日志檔案的名稱 Slave_IO_Running： I/O執行緒是否被啟動并成功地連接到主服務器上 Slave_SQL_Running： SQL執行緒是否被啟動 Seconds_Behind_Master： 從屬服務器SQL執行緒和從屬服務器I/O執行緒之間的時間差距，單位以秒計，
從庫同步延遲情況出現的 ● show slave status顯示引數Seconds_Behind_Master不為0，這個數值可能會很大 ● show slave status顯示引數Relay_Master_Log_File和Master_Log_File顯示bin-log的編號相差很大，說明bin-log在從庫上沒有及時同步，所以近期執行的bin-log和當前IO執行緒所讀的bin-log相差很大 ● mysql的從庫資料目錄下存在大量mysql-relay-log日志，該日志同步完成之后就會被系統自動洗掉，存在大量日志，說明主從同步延遲很厲害
(2)、MySql資料庫從庫同步的延遲問題
1)、MySQL資料庫主從同步延遲原理mysql主從同步原理：主庫針對寫操作，順序寫binlog，從庫單執行緒去主庫順序讀”寫操作的binlog”，從庫取到binlog在本地原樣執行（隨機寫），來保證主從資料邏輯上一致，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執行緒卻不可以，
2)、MySQL資料庫主從同步延遲是怎么產生的？當主庫的TPS并發較高時，產生的DDL數量超過slave一個sql執行緒所能承受的范圍，那么延時就產生了，當然還有就是可能與slave的大型query陳述句產生了鎖等待，首要原因：資料庫在業務上讀寫壓力太大，CPU計算負荷大，網卡負荷大，硬碟隨機IO太高次要原因：讀寫binlog帶來的性能影響，網路傳輸延遲，
(3)、MySql資料庫從庫同步的延遲解決方案
1)、架構方面
1.業務的持久化層的實作采用分庫架構，mysql服務可平行擴展，分散壓力，
2.單個庫讀寫分離，一主多從，主寫從讀，分散壓力，這樣從庫壓力比主庫高，保護主庫，
3.服務的基礎架構在業務和mysql之間加入memcache或者redis的cache層，降低mysql的讀壓力，
4.不同業務的mysql物理上放在不同機器，分散壓力，
5.使用比主庫更好的硬體設備作為slave總結，mysql壓力小，延遲自然會變小，
2)、硬體方面
1.采用好服務器，比如4u比2u性能明顯好，2u比1u性能明顯好，
2.存盤用ssd或者盤陣或者san，提升隨機寫的性能，
3.主從間保證處在同一個交換機下面，并且是萬兆環境，
總結，硬體強勁，延遲自然會變小，一句話，縮小延遲的解決方案就是花錢和花時間，
3)、mysql主從同步加速
1、sync_binlog在slave端設定為0
2、–logs-slave-updates 從服務器從主服務器接收到的更新不記入它的二進制日志，
3、直接禁用slave端的binlog
4、slave端，如果使用的存盤引擎是innodb，innodb_flush_log_at_trx_commit =2
4)、從檔案系統本身屬性角度優化
master端修改linux、Unix檔案系統中檔案的etime屬性， 由于每當讀檔案時OS都會將讀取操作發生的時間回寫到磁盤上，對于讀操作頻繁的資料庫檔案來說這是沒必要的，只會增加磁盤系統的負擔影響I/O性能，可以通過設定檔案系統的mount屬性，組織作業系統寫atime資訊，在linux上的操作為：打開/etc/fstab，加上noatime引數/dev/sdb1 /data reiserfs noatime 1 2然后重新mount檔案系統#mount -oremount /data
5)、同步引數調整主庫是寫，對資料安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設定是需要的而slave則不需要這么高的資料安全，完全可以講sync_binlog設定為0或者關閉binlog，innodb_flushlog也可以設定為0來提高sql的執行效率
1、sync_binlog=1 oMySQL提供一個sync_binlog引數來控制資料庫的binlog刷到磁盤上去，默認，sync_binlog=0，表示MySQL不控制binlog的重繪，由檔案系統自己控制它的快取的重繪，這時候的性能是最好的，但是風險也是最大的，一旦系統Crash，在binlog_cache中的所有binlog資訊都會被丟失，
如果sync_binlog>0，表示每sync_binlog次事務提交，MySQL呼叫檔案系統的重繪操作將快取刷下去，最安全的就是sync_binlog=1了，表示每次事務提交，MySQL都會把binlog刷下去，是最安全但是性能損耗最大的設定，這樣的話，在資料庫所在的主機作業系統損壞或者突然掉電的情況下，系統才有可能丟失1個事務的資料，但是binlog雖然是順序IO，但是設定sync_binlog=1，多個事務同時提交，同樣很大的影響MySQL和IO性能，雖然可以通過group commit的補丁緩解，但是重繪的頻率過高對IO的影響也非常大，
對于高并發事務的系統來說，“sync_binlog”設定為0和設定為1的系統寫入性能差距可能高達5倍甚至更多，所以很多MySQL DBA設定的sync_binlog并不是最安全的1，而是2或者是0，這樣犧牲一定的一致性，可以獲得更高的并發和性能，默認情況下，并不是每次寫入時都將binlog與硬碟同步，因此如果作業系統或機器(不僅僅是MySQL服務器)崩潰，有可能binlog中最后的陳述句丟失了，要想防止這種情況，你可以使用sync_binlog全域變數(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入后與硬碟同步，即使sync_binlog設定為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性，
2、innodb_flush_log_at_trx_commit （這個很管用）抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調整這個值，默認值1的意思是每一次事務提交或事務外的指令都需要把日志寫入（flush）硬碟，這是很費時的，特別是使用電池供電快取（Battery backed up cache）時，設成2對于很多運用，特別是從MyISAM表轉過來的是可以的，它的意思是不寫入硬碟而是寫入系統快取，日志仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新，設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的資料，而值2只會在整個作業系統 掛了時才可能丟資料，
3、ls(1) 命令可用來列出檔案的 atime、ctime 和 mtime，
atime 檔案的access time 在讀取檔案或者執行檔案時更改的ctime 檔案的create time 在寫入檔案，更改所有者，權限或鏈接設定時隨inode的內容更改而更改mtime 檔案的modified time 在寫入檔案時隨檔案內容的更改而更改ls -lc filename 列出檔案的 ctimels -lu filename 列出檔案的 atimels -l filename 列出檔案的 mtimestat filename 列出atime，mtime，ctimeatime不一定在訪問檔案之后被修改因為：使用ext3檔案系統的時候，如果在mount的時候使用了noatime引數那么就不會更新atime資訊，這三個time stamp都放在 inode 中.如果mtime,atime 修改,inode 就一定會改, 既然 inode 改了,那ctime也就跟著改了.之所以在 mount option 中使用 noatime, 就是不想file system 做太多的修改, 而改善讀取效能
(4)、MySql資料庫從庫同步其他問題及解決方案
1)、mysql主從復制存在的問題： ● 主庫宕機后，資料可能丟失 ● 從庫只有一個sql Thread，主庫寫壓力大，復制很可能延時2)、解決方法： ● 半同步復制---解決資料丟失的問題 ● 并行復制----解決從庫復制延遲的問題
3)、半同步復制mysql semi-sync（半同步復制）半同步復制： ● 5.5集成到mysql，以插件的形式存在，需要單獨安裝 ● 確保事務提交后binlog至少傳輸到一個從庫 ● 不保證從庫應用完這個事務的binlog ● 性能有一定的降低，回應時間會更長 ● 網路例外或從庫宕機，卡主主庫，直到超時或從庫恢復4)、主從復制--異步復制原理、半同步復制和并行復制原理比較
a、異步復制原理：
b、半同步復制原理：
事務在主庫寫完binlog后需要從庫回傳一個已接受，才放回給客戶端；5.5集成到mysql，以插件的形式存在，需要單獨安裝確保事務提交后binlog至少傳輸到一個從庫不保證從庫應用完成這個事務的binlog性能有一定的降低網路例外或從庫宕機，卡主庫，直到超時或從庫恢復
c、并行復制mysql并行復制 ● 社區版5.6中新增 ● 并行是指從庫多執行緒apply binlog ● 庫級別并行應用binlog，同一個庫資料更改還是串行的(5.7版并行復制基于事務組)設定set global slave_parallel_workers=10;設定sql執行緒數為10
原理：從庫多執行緒apply binlog在社區5.6中新增庫級別并行應用binlog，同一個庫資料更改還是串行的5.7版本并行復制基于事務組
參考：https://blog.csdn.net/hao_yunfeng/article/details/82392261
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/58196.html
標籤：MySQL
上一篇：【Linux Mysql主從配置】整理主從配置遇到的坑！
下一篇：MySQL 資料備份