MySQL主從同步延遲怎么解決-創(chuàng)新互聯(lián)

本文主要給大家簡(jiǎn)單講講MySQL主從同步延遲怎么解決，相關(guān)專業(yè)術(shù)語(yǔ)大家可以上網(wǎng)查查或者找一些相關(guān)書(shū)籍補(bǔ)充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL主從同步延遲怎么解決這篇文章可以給大家?guī)?lái)一些實(shí)際幫助。                              

創(chuàng)新互聯(lián)建站堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：做網(wǎng)站、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的奉新網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

Mysql主從基本原理，主要形式以及主從同步延遲原理 (讀寫(xiě)分離)導(dǎo)致主庫(kù)從庫(kù)數(shù)據(jù)不一致問(wèn)題的及解決方案

一、主從數(shù)據(jù)庫(kù)的區(qū)別

從數(shù)據(jù)庫(kù)(Slave)是主數(shù)據(jù)庫(kù)的備份，當(dāng)主數(shù)據(jù)庫(kù)(Master)變化時(shí)從數(shù)據(jù)庫(kù)要更新，這些數(shù)據(jù)庫(kù)軟件可以設(shè)計(jì)更新周期。這是提高信息安全的手段。主從數(shù)據(jù)庫(kù)云服務(wù)器不在一個(gè)地理位置上，當(dāng)發(fā)生意外時(shí)數(shù)據(jù)庫(kù)可以保存。

(1) 主從分工

其中Master負(fù)責(zé)寫(xiě)操作的負(fù)載，也就是說(shuō)一切寫(xiě)的操作都在Master上進(jìn)行，而讀的操作則分?jǐn)偟絊lave上進(jìn)行。這樣一來(lái)的可以大大提高讀取的效率。在一般的互聯(lián)網(wǎng)應(yīng)用中，經(jīng)過(guò)一些數(shù)據(jù)調(diào)查得出結(jié)論，讀/寫(xiě)的比例大概在 10：1左右 ，也就是說(shuō)大量的數(shù)據(jù)操作是集中在讀的操作，這也就是為什么我們會(huì)有多個(gè)Slave的原因。但是為什么要分離讀和寫(xiě)呢？熟悉DB的研發(fā)人員都知道，寫(xiě)操作涉及到鎖的問(wèn)題，不管是行鎖還是表鎖還是塊鎖，都是比較降低系統(tǒng)執(zhí)行效率的事情。我們這樣的分離是把寫(xiě)操作集中在一個(gè)節(jié)點(diǎn)上，而讀操作其其他的N個(gè)節(jié)點(diǎn)上進(jìn)行，從另一個(gè)方面有效的提高了讀的效率，保證了系統(tǒng)的高可用性。

(2) 基本過(guò)程
1)、Mysql的主從同步就是當(dāng)master（主庫(kù)）發(fā)生數(shù)據(jù)變化的時(shí)候，會(huì)實(shí)時(shí)同步到slave（從庫(kù)）。
2)、主從復(fù)制可以水平擴(kuò)展數(shù)據(jù)庫(kù)的負(fù)載能力，容錯(cuò)，高可用，數(shù)據(jù)備份。

3)、不管是delete、update、insert，還是創(chuàng)建函數(shù)、存儲(chǔ)過(guò)程，都是在master上，當(dāng)master有操作的時(shí)候，slave會(huì)快速的接受到這些操作，從而做同步。

(3) 用途和條件
1)、mysql主從復(fù)制用途
 ●實(shí)時(shí)災(zāi)備，用于故障切換
 ●讀寫(xiě)分離，提供查詢服務(wù)
 ●備份，避免影響業(yè)務(wù)
2)、主從部署必要條件：
 ●主庫(kù)開(kāi)啟binlog日志（設(shè)置log-bin參數(shù)）
 ●主從server-id不同
 ●從庫(kù)云服務(wù)器能連通主庫(kù)

二、主從同步的粒度、原理和形式：

(1)、 三種主要實(shí)現(xiàn)粒度
詳細(xì)的主從同步主要有三種形式：statement、row、mixed
1)、statement: 會(huì)將對(duì)數(shù)據(jù)庫(kù)操作的sql語(yǔ)句寫(xiě)道binlog中
2)、row: 會(huì)將每一條數(shù)據(jù)的變化寫(xiě)道binlog中。
 3)、mixed: statement與row的混合。Mysql決定何時(shí)寫(xiě)statement格式的binlog, 何時(shí)寫(xiě)row格式的binlog。

(2)、主要的實(shí)現(xiàn)原理、具體操作、示意圖

1)、在master機(jī)器上的操作：
 當(dāng)master上的數(shù)據(jù)發(fā)生變化時(shí)，該事件變化會(huì)按照順序?qū)懭隻in-log中。當(dāng)slave鏈接到master的時(shí)候，master機(jī)器會(huì)為slave開(kāi)啟binlog dump線程。當(dāng)master的binlog發(fā)生變化的時(shí)候，bin-log dump線程會(huì)通知slave，并將相應(yīng)的binlog內(nèi)容發(fā)送給slave。
2)、在slave機(jī)器上操作：

 當(dāng)主從同步開(kāi)啟的時(shí)候，slave上會(huì)創(chuàng)建兩個(gè)線程：I\O線程。該線程連接到master機(jī)器，master機(jī)器上的binlog dump 線程會(huì)將binlog的內(nèi)容發(fā)送給該I\O線程。該I/O線程接收到binlog內(nèi)容后，再將內(nèi)容寫(xiě)入到本地的relay log；sql線程。該線程讀取到I/O線程寫(xiě)入的ralay log。并且根據(jù)relay log。并且根據(jù)relay log 的內(nèi)容對(duì)slave數(shù)據(jù)庫(kù)做相應(yīng)的操作。

3)、MySQL主從復(fù)制原理圖如下:

從庫(kù)生成兩個(gè)線程，一個(gè)I/O線程，一個(gè)SQL線程；
i/o線程去請(qǐng)求主庫(kù) 的binlog，并將得到的binlog日志寫(xiě)到relay log（中繼日志） 文件中；
主庫(kù)會(huì)生成一個(gè) log dump 線程，用來(lái)給從庫(kù) i/o線程傳binlog；
SQL 線程，會(huì)讀取relay log文件中的日志，并解析成具體操作，來(lái)實(shí)現(xiàn)主從的操作一致，而最終數(shù)據(jù)一致；

(2)、主從形式

mysql主從復(fù)制 靈活
 ● 一主一從
 ● 主主復(fù)制
 ● 一主多從---擴(kuò)展系統(tǒng)讀取的性能，因?yàn)樽x是在從庫(kù)讀取的；
 ● 多主一從---5.7開(kāi)始支持

● 聯(lián)級(jí)復(fù)制---

三、主從同步的延遲等問(wèn)題、原因及解決方案：

(1)、mysql數(shù)據(jù)庫(kù)從庫(kù)同步的延遲問(wèn)題

1）相關(guān)參數(shù)：

首先在云服務(wù)器上執(zhí)行show slave satus;可以看到很多同步的參數(shù)：　

Master_Log_File：            SLAVE中的I/O線程當(dāng)前正在讀取的主云服務(wù)器二進(jìn)制日志文件的名稱
Read_Master_Log_Pos：     在當(dāng)前的主云服務(wù)器二進(jìn)制日志中，SLAVE中的I/O線程已經(jīng)讀取的位置
Relay_Log_File：             SQL線程當(dāng)前正在讀取和執(zhí)行的中繼日志文件的名稱
Relay_Log_Pos：             在當(dāng)前的中繼日志中，SQL線程已讀取和執(zhí)行的位置
Relay_Master_Log_File：    由SQL線程執(zhí)行的包含多數(shù)近期事件的主云服務(wù)器二進(jìn)制日志文件的名稱
Slave_IO_Running：         I/O線程是否被啟動(dòng)并成功地連接到主云服務(wù)器上
Slave_SQL_Running：        SQL線程是否被啟動(dòng)
Seconds_Behind_Master：   從屬云服務(wù)器SQL線程和從屬云服務(wù)器I/O線程之間的時(shí)間差距，單位以秒計(jì)。
從庫(kù)同步延遲情況出現(xiàn)的● show slave status顯示參數(shù)Seconds_Behind_Master不為0，這個(gè)數(shù)值可能會(huì)很大
● show slave status顯示參數(shù)Relay_Master_Log_File和Master_Log_File顯示bin-log的編號(hào)相差很大，說(shuō)明bin-log在從庫(kù)上沒(méi)有及時(shí)同步，所以近期執(zhí)行的bin-log和當(dāng)前IO線程所讀的bin-log相差很大
● mysql的從庫(kù)數(shù)據(jù)目錄下存在大量mysql-relay-log日志，該日志同步完成之后就會(huì)被系統(tǒng)自動(dòng)刪除，存在大量日志，說(shuō)明主從同步延遲很厲害

(2)、MySql數(shù)據(jù)庫(kù)從庫(kù)同步的延遲問(wèn)題

1)、MySQL數(shù)據(jù)庫(kù)主從同步延遲原理mysql主從同步原理：主庫(kù)針對(duì)寫(xiě)操作，順序?qū)慴inlog，從庫(kù)單線程去主庫(kù)順序讀”寫(xiě)操作的binlog”，從庫(kù)取到binlog在本地原樣執(zhí)行（隨機(jī)寫(xiě)），來(lái)保證主從數(shù)據(jù)邏輯上一致。mysql的主從復(fù)制都是單線程的操作，主庫(kù)對(duì)所有DDL和DML產(chǎn)生binlog，binlog是順序?qū)?，所以效率很高，slave的Slave_IO_Running線程到主庫(kù)取日志，效率比較高，下一步，問(wèn)題來(lái)了，slave的Slave_SQL_Running線程將主庫(kù)的DDL和DML操作在slave實(shí)施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產(chǎn)生lock爭(zhēng)用，由于Slave_SQL_Running也是單線程的，所以一個(gè)DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會(huì)等待這個(gè)DDL執(zhí)行完才會(huì)繼續(xù)執(zhí)行，這就導(dǎo)致了延時(shí)。有朋友會(huì)問(wèn)：“主庫(kù)上那個(gè)相同的DDL也需要執(zhí)行10分，為什么slave會(huì)延時(shí)？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。

2)、MySQL數(shù)據(jù)庫(kù)主從同步延遲是怎么產(chǎn)生的？當(dāng)主庫(kù)的TPS并發(fā)較高時(shí)，產(chǎn)生的DDL數(shù)量超過(guò)slave一個(gè)sql線程所能承受的范圍，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語(yǔ)句產(chǎn)生了鎖等待。首要原因：數(shù)據(jù)庫(kù)在業(yè)務(wù)上讀寫(xiě)壓力太大，CPU計(jì)算負(fù)荷大，網(wǎng)卡負(fù)荷大，硬盤(pán)隨機(jī)IO太高次要原因：讀寫(xiě)binlog帶來(lái)的性能影響，網(wǎng)絡(luò)傳輸延遲。

(3)、MySql數(shù)據(jù)庫(kù)從庫(kù)同步的延遲解決方案

1)、架構(gòu)方面

1.業(yè)務(wù)的持久化層的實(shí)現(xiàn)采用分庫(kù)架構(gòu)，mysql服務(wù)可平行擴(kuò)展，分散壓力。

2.單個(gè)庫(kù)讀寫(xiě)分離，一主多從，主寫(xiě)從讀，分散壓力。這樣從庫(kù)壓力比主庫(kù)高，保護(hù)主庫(kù)。

3.服務(wù)的基礎(chǔ)架構(gòu)在業(yè)務(wù)和mysql之間加入memcache或者redis的cache層。降低mysql的讀壓力。

4.不同業(yè)務(wù)的mysql物理上放在不同機(jī)器，分散壓力。

5.使用比主庫(kù)更好的硬件設(shè)備作為slave總結(jié)，mysql壓力小，延遲自然會(huì)變小。

2)、硬件方面

1.采用好云服務(wù)器，比如4u比2u性能明顯好，2u比1u性能明顯好。

2.存儲(chǔ)用ssd或者盤(pán)陣或者san，提升隨機(jī)寫(xiě)的性能。

3.主從間保證處在同一個(gè)交換機(jī)下面，并且是萬(wàn)兆環(huán)境。

總結(jié)，硬件強(qiáng)勁，延遲自然會(huì)變小。一句話，縮小延遲的解決方案就是花錢(qián)和花時(shí)間。

3)、mysql主從同步加速

1、sync_binlog在slave端設(shè)置為0

2、–logs-slave-updates 從云服務(wù)器從主云服務(wù)器接收到的更新不記入它的二進(jìn)制日志。

3、直接禁用slave端的binlog

4、slave端，如果使用的存儲(chǔ)引擎是innodb，innodb_flush_log_at_trx_commit =2

4)、從文件系統(tǒng)本身屬性角度優(yōu)化

master端修改linux、Unix文件系統(tǒng)中文件的etime屬性， 由于每當(dāng)讀文件時(shí)OS都會(huì)將讀取操作發(fā)生的時(shí)間回寫(xiě)到磁盤(pán)上，對(duì)于讀操作頻繁的數(shù)據(jù)庫(kù)文件來(lái)說(shuō)這是沒(méi)必要的，只會(huì)增加磁盤(pán)系統(tǒng)的負(fù)擔(dān)影響I/O性能?？梢酝ㄟ^(guò)設(shè)置文件系統(tǒng)的mount屬性，組織操作系統(tǒng)寫(xiě)atime信息，在linux上的操作為：打開(kāi)/etc/fstab，加上noatime參數(shù)/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系統(tǒng)#mount -oremount /data

5)、同步參數(shù)調(diào)整主庫(kù)是寫(xiě)，對(duì)數(shù)據(jù)安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類(lèi)的設(shè)置是需要的而slave則不需要這么高的數(shù)據(jù)安全，完全可以講sync_binlog設(shè)置為0或者關(guān)閉binlog，innodb_flushlog也可以設(shè)置為0來(lái)提高sql的執(zhí)行效率

1、sync_binlog=1 oMySQL提供一個(gè)sync_binlog參數(shù)來(lái)控制數(shù)據(jù)庫(kù)的binlog刷到磁盤(pán)上去。默認(rèn)，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系統(tǒng)自己控制它的緩存的刷新。這時(shí)候的性能是最好的，但是風(fēng)險(xiǎn)也是大的。一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會(huì)被丟失。

如果sync_binlog>0，表示每sync_binlog次事務(wù)提交，MySQL調(diào)用文件系統(tǒng)的刷新操作將緩存刷下去。最安全的就是sync_binlog=1了，表示每次事務(wù)提交，MySQL都會(huì)把binlog刷下去，是最安全但是性能損耗大的設(shè)置。這樣的話，在數(shù)據(jù)庫(kù)所在的主機(jī)操作系統(tǒng)損壞或者突然掉電的情況下，系統(tǒng)才有可能丟失1個(gè)事務(wù)的數(shù)據(jù)。但是binlog雖然是順序IO，但是設(shè)置sync_binlog=1，多個(gè)事務(wù)同時(shí)提交，同樣很大的影響MySQL和IO性能。雖然可以通過(guò)group commit的補(bǔ)丁緩解，但是刷新的頻率過(guò)高對(duì)IO的影響也非常大。

對(duì)于高并發(fā)事務(wù)的系統(tǒng)來(lái)說(shuō)，“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫(xiě)入性能差距可能高達(dá)5倍甚至更多。所以很多MySQL DBA設(shè)置的sync_binlog并不是最安全的1，而是2或者是0。這樣犧牲一定的一致性，可以獲得更高的并發(fā)和性能。默認(rèn)情況下，并不是每次寫(xiě)入時(shí)都將binlog與硬盤(pán)同步。因此如果操作系統(tǒng)或機(jī)器(不僅僅是MySQL云服務(wù)器)崩潰，有可能binlog中最后的語(yǔ)句丟失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫(xiě)入后與硬盤(pán)同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時(shí)，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性。

2、innodb_flush_log_at_trx_commit （這個(gè)很管用）抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調(diào)整這個(gè)值。默認(rèn)值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫(xiě)入（flush）硬盤(pán)，這是很費(fèi)時(shí)的。特別是使用電池供電緩存（Battery backed up cache）時(shí)。設(shè)成2對(duì)于很多運(yùn)用，特別是從MyISAM表轉(zhuǎn)過(guò)來(lái)的是可以的，它的意思是不寫(xiě)入硬盤(pán)而是寫(xiě)入系統(tǒng)緩存。日志仍然會(huì)每秒flush到硬 盤(pán)，所以你一般不會(huì)丟失超過(guò)1-2秒的更新。設(shè)成0會(huì)更快一點(diǎn)，但安全方面比較差，即使MySQL掛了也可能會(huì)丟失事務(wù)的數(shù)據(jù)。而值2只會(huì)在整個(gè)操作系統(tǒng) 掛了時(shí)才可能丟數(shù)據(jù)。

3、ls(1) 命令可用來(lái)列出文件的 atime、ctime 和 mtime。

atime 文件的access time 在讀取文件或者執(zhí)行文件時(shí)更改的ctime 文件的create time 在寫(xiě)入文件，更改所有者，權(quán)限或鏈接設(shè)置時(shí)隨inode的內(nèi)容更改而更改mtime 文件的modified time 在寫(xiě)入文件時(shí)隨文件內(nèi)容的更改而更改ls -lc filename 列出文件的 ctimels -lu filename 列出文件的 atimels -l filename 列出文件的 mtimestat filename 列出atime，mtime，ctimeatime不一定在訪問(wèn)文件之后被修改因?yàn)椋菏褂胑xt3文件系統(tǒng)的時(shí)候，如果在mount的時(shí)候使用了noatime參數(shù)那么就不會(huì)更新atime信息。這三個(gè)time stamp都放在 inode 中.如果mtime,atime 修改,inode 就一定會(huì)改, 既然 inode 改了,那ctime也就跟著改了.之所以在 mount option 中使用 noatime, 就是不想file system 做太多的修改, 而改善讀取效能

(4)、MySql數(shù)據(jù)庫(kù)從庫(kù)同步其他問(wèn)題及解決方案

1)、mysql主從復(fù)制存在的問(wèn)題：  ● 主庫(kù)宕機(jī)后，數(shù)據(jù)可能丟失  ● 從庫(kù)只有一個(gè)sql Thread，主庫(kù)寫(xiě)壓力大，復(fù)制很可能延時(shí)2)、解決方法：  ● 半同步復(fù)制---解決數(shù)據(jù)丟失的問(wèn)題  ● 并行復(fù)制----解決從庫(kù)復(fù)制延遲的問(wèn)題

3)、半同步復(fù)制mysql semi-sync（半同步復(fù)制）半同步復(fù)制：  ● 5.5集成到mysql，以插件的形式存在，需要單獨(dú)安裝  ● 確保事務(wù)提交后binlog至少傳輸?shù)揭粋€(gè)從庫(kù)  ● 不保證從庫(kù)應(yīng)用完這個(gè)事務(wù)的binlog  ● 性能有一定的降低，響應(yīng)時(shí)間會(huì)更長(zhǎng)  ● 網(wǎng)絡(luò)異?；驈膸?kù)宕機(jī)，卡主主庫(kù)，直到超時(shí)或從庫(kù)恢復(fù)4)、主從復(fù)制--異步復(fù)制原理、半同步復(fù)制和并行復(fù)制原理比較

a、異步復(fù)制原理：

b、半同步復(fù)制原理：

事務(wù)在主庫(kù)寫(xiě)完binlog后需要從庫(kù)返回一個(gè)已接受，才放回給客戶端；5.5集成到mysql，以插件的形式存在，需要單獨(dú)安裝確保事務(wù)提交后binlog至少傳輸?shù)揭粋€(gè)從庫(kù)不保證從庫(kù)應(yīng)用完成這個(gè)事務(wù)的binlog性能有一定的降低網(wǎng)絡(luò)異?；驈膸?kù)宕機(jī)，卡主庫(kù)，直到超時(shí)或從庫(kù)恢復(fù)

c、并行復(fù)制mysql并行復(fù)制  ● 社區(qū)版5.6中新增  ● 并行是指從庫(kù)多線程apply binlog  ● 庫(kù)級(jí)別并行應(yīng)用binlog，同一個(gè)庫(kù)數(shù)據(jù)更改還是串行的(5.7版并行復(fù)制基于事務(wù)組)設(shè)置set global slave_parallel_workers=10;設(shè)置sql線程數(shù)為10

原理：從庫(kù)多線程apply binlog在社區(qū)5.6中新增庫(kù)級(jí)別并行應(yīng)用binlog，同一個(gè)庫(kù)數(shù)據(jù)更改還是串行的5.7版本并行復(fù)制基于事務(wù)組

MySQL主從同步延遲怎么解決就先給大家講到這里，對(duì)于其它相關(guān)問(wèn)題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會(huì)捕捉一些行業(yè)新聞及專業(yè)知識(shí)分享給大家的。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

文章標(biāo)題：MySQL主從同步延遲怎么解決-創(chuàng)新互聯(lián)
當(dāng)前網(wǎng)址：http://muchs.cn/article44/ddpche.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、做網(wǎng)站、小程序開(kāi)發(fā)、服務(wù)器托管、企業(yè)建站、動(dòng)態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)