快捷導(dǎo)航

MySQL主從同步延遲原因與解決方案

更新時(shí)間：2023年11月14日 16:17:46 作者：逆流°只是風(fēng)景-bjhxcc

本文主要介紹了MySQL主從同步延遲原因與解決方案,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

一、MySQL數(shù)據(jù)庫(kù)主從同步延遲產(chǎn)生的原因

MySQL的主從復(fù)制都是單線程的操作，主庫(kù)對(duì)所有DDL和DML產(chǎn)生的日志寫進(jìn)binlog，由于binlog是順序?qū)?，所以效率很高?/p>

Slave的SQL Thread線程將主庫(kù)的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是隨即的，不是順序的，成本高很多。

另一方面，由于SQL Thread也是單線程的，當(dāng)主庫(kù)的并發(fā)較高時(shí)，產(chǎn)生的DML數(shù)量超過(guò)slave的SQL Thread所能處理的速度，或者當(dāng)slave中有大型query語(yǔ)句產(chǎn)生了鎖等待那么延時(shí)就產(chǎn)生了。

常見(jiàn)原因：Master負(fù)載過(guò)高、Slave負(fù)載過(guò)高、網(wǎng)絡(luò)延遲、機(jī)器性能太低、MySQL配置不合理。

二、關(guān)于DDL和DML

SQL語(yǔ)言共分為以下幾大類：查詢語(yǔ)言DQL，控制語(yǔ)言DCL，操縱語(yǔ)言DML，定義語(yǔ)言DDL。事務(wù)控制TCL.

DQL（Data QUERY Languages）語(yǔ)句：即數(shù)據(jù)庫(kù)定義語(yǔ)句，用來(lái)查詢SELECT子句，F(xiàn)ROM子句，WHERE子句組成的查詢塊，比如：select–from–where–grouop by–having–order by–limit
DDL（Data Definition Languages）語(yǔ)句：即數(shù)據(jù)庫(kù)定義語(yǔ)句，用來(lái)創(chuàng)建數(shù)據(jù)庫(kù)中的表、索引、視圖、存儲(chǔ)過(guò)程、觸發(fā)器等，常用的語(yǔ)句關(guān)鍵字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增刪改表的結(jié)構(gòu)
DML（Data Manipulation Language）語(yǔ)句：即數(shù)據(jù)操縱語(yǔ)句，用來(lái)查詢、添加、更新、刪除等，常用的語(yǔ)句關(guān)鍵字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增刪改查。增刪改表的數(shù)據(jù)
DCL（Data Control Language）語(yǔ)句：即數(shù)據(jù)控制語(yǔ)句，用于授權(quán)/撤銷數(shù)據(jù)庫(kù)及其字段的權(quán)限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的語(yǔ)句關(guān)鍵字有：GRANT,REVOKE。
TCL（Transaction Control Language）語(yǔ)句：事務(wù)控制語(yǔ)句，用于控制事務(wù)，常用的語(yǔ)句關(guān)鍵字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

三、主從延時(shí)排查方法

通過(guò)監(jiān)控 show slave status 命令輸出的Seconds_Behind_Master參數(shù)的值來(lái)判斷：

NULL，表示io_thread或是sql_thread有任何一個(gè)發(fā)生故障；
0，該值為零，表示主從復(fù)制良好；
正值，表示主從已經(jīng)出現(xiàn)延時(shí)，數(shù)字越大表示從庫(kù)延遲越嚴(yán)重

四、解決方案

解決數(shù)據(jù)丟失的問(wèn)題：

半同步復(fù)制
從MySQL5.5開(kāi)始，MySQL已經(jīng)支持半同步復(fù)制了，半同步復(fù)制介于異步復(fù)制和同步復(fù)制之間，主庫(kù)在執(zhí)行完事務(wù)后不立刻返回結(jié)果給客戶端，需要等待至少一個(gè)從庫(kù)接收到并寫到relay log中才返回結(jié)果給客戶端。相對(duì)于異步復(fù)制，半同步復(fù)制提高了數(shù)據(jù)的安全性，同時(shí)它也造成了一個(gè)TCP/IP往返耗時(shí)的延遲。

主庫(kù)配置sync_binlog=1，innodb_flush_log_at_trx_commit=1 sync_binlog的默認(rèn)值是0，MySQL不會(huì)將binlog同步到磁盤，其值表示每寫多少binlog同步一次磁盤。

innodb_flush_log_at_trx_commit為1表示每一次事務(wù)提交或事務(wù)外的指令都需要把日志flush到磁盤。

注意:將以上兩個(gè)值同時(shí)設(shè)置為1時(shí)，寫入性能會(huì)受到一定限制，只有對(duì)數(shù)據(jù)安全性要求很高的場(chǎng)景才建議使用，比如涉及到錢的訂單支付業(yè)務(wù)，而且系統(tǒng)I/O能力必須可以支撐！

4.1 解決從庫(kù)復(fù)制延遲的問(wèn)題：

架構(gòu)方面

業(yè)務(wù)的持久化層的實(shí)現(xiàn)采用分庫(kù)架構(gòu)，mysql服務(wù)可平行擴(kuò)展，分散壓力。
單個(gè)庫(kù)讀寫分離，一主多從，主寫從讀，分散壓力。這樣從庫(kù)壓力比主庫(kù)高，保護(hù)主庫(kù)。
服務(wù)的基礎(chǔ)架構(gòu)在業(yè)務(wù)和mysql之間加入memcache或者redis的cache層。降低mysql的讀壓力。
不同業(yè)務(wù)的mysql物理上放在不同機(jī)器，分散壓力。
使用比主庫(kù)更好的硬件設(shè)備作為slave，mysql壓力小，延遲自然會(huì)變小。

硬件方面

采用好服務(wù)器，比如4u比2u性能明顯好，2u比1u性能明顯好。
存儲(chǔ)用ssd或者盤陣或者san，提升隨機(jī)寫的性能。
主從間保證處在同一個(gè)交換機(jī)下面，并且是萬(wàn)兆環(huán)境。

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

mysql主從同步加速

sync_binlog在slave端設(shè)置為0
–logs-slave-updates 從服務(wù)器從主服務(wù)器接收到的更新不記入它的二進(jìn)制日志。
直接禁用slave端的binlog
.slave端，如果使用的存儲(chǔ)引擎是innodb，innodb_flush_log_at_trx_commit =2

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

master端修改linux、Unix文件系統(tǒng)中文件的etime屬性，由于每當(dāng)讀文件時(shí)OS都會(huì)將讀取操作發(fā)生的時(shí)間回寫到磁盤上，對(duì)于讀操作頻繁的數(shù)據(jù)庫(kù)文件來(lái)說(shuō)這是沒(méi)必要的，只會(huì)增加磁盤系統(tǒng)的負(fù)擔(dān)影響I/O性能?？梢酝ㄟ^(guò)設(shè)置文件系統(tǒng)的mount屬性，組織操作系統(tǒng)寫atime信息，在linux上的操作為：打開(kāi)/etc/fstab，加上noatime參數(shù)/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系統(tǒng)#mount -oremount /data
同步參數(shù)調(diào)整主庫(kù)是寫，對(duì)數(shù)據(jù)安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設(shè)置是需要的而slave則不需要這么高的數(shù)據(jù)安全，完全可以講sync_binlog設(shè)置為0或者關(guān)閉binlog，innodb_flushlog也可以設(shè)置為0來(lái)提高sql的執(zhí)行效率