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

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

MySQL的主從復(fù)制都是單線程的操作，主庫對所有DDL和DML產(chǎn)生的日志寫進binlog，由于binlog是順序?qū)?，所以效率很高?br /> Slave的SQL Thread線程將主庫的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是隨即的，不是順序的，成本高很多。另一方面，由于SQL Thread也是單線程的，當主庫的并發(fā)較高時，產(chǎn)生的DML數(shù)量超過slave的SQL Thread所能處理的速度，或者當slave中有大型query語句產(chǎn)生了鎖等待那么延時就產(chǎn)生了。
常見原因：Master負載過高、Slave負載過高、網(wǎng)絡(luò)延遲、機器性能太低、MySQL配置不合理。

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

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

DQL（Data QUERY Languages）語句：即數(shù)據(jù)庫定義語句，用來查詢SELECT子句，F(xiàn)ROM子句，WHERE子句組成的查詢塊，比如：select–from–where–grouop by–having–order by–limit

DDL（Data Definition Languages）語句：即數(shù)據(jù)庫定義語句，用來創(chuàng)建數(shù)據(jù)庫中的表、索引、視圖、存儲過程、觸發(fā)器等，常用的語句關(guān)鍵字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增刪改表的結(jié)構(gòu)

DML（Data Manipulation Language）語句：即數(shù)據(jù)操縱語句，用來查詢、添加、更新、刪除等，常用的語句關(guān)鍵字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增刪改查。增刪改表的數(shù)據(jù)

DCL（Data Control Language）語句：即數(shù)據(jù)控制語句，用于授權(quán)/撤銷數(shù)據(jù)庫及其字段的權(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.）。常用的語句關(guān)鍵字有：GRANT,REVOKE。

TCL（Transaction Control Language）語句：事務(wù)控制語句，用于控制事務(wù)，常用的語句關(guān)鍵字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

三、主從延時排查方法

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

NULL，表示io_thread或是sql_thread有任何一個發(fā)生故障；

0，該值為零，表示主從復(fù)制良好；

正值，表示主從已經(jīng)出現(xiàn)延時，數(shù)字越大表示從庫延遲越嚴重

四、解決方案

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

半同步復(fù)制

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

主庫配置sync_binlog=1，innodb_flush_log_at_trx_commit=1 sync_binlog的默認值是0，MySQL不會將binlog同步到磁盤，其值表示每寫多少binlog同步一次磁盤。

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

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

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

架構(gòu)方面

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

單個庫讀寫分離，一主多從，主寫從讀，分散壓力。這樣從庫壓力比主庫高，保護主庫。

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

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

使用比主庫更好的硬件設(shè)備作為slave，mysql壓力小，延遲自然會變小。

硬件方面

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

存儲用ssd或者盤陣或者san，提升隨機寫的性能。

主從間保證處在同一個交換機下面，并且是萬兆環(huán)境。

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

mysql主從同步加速

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

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

直接禁用slave端的binlog

.slave端，如果使用的存儲引擎是innodb，innodb_flush_log_at_trx_commit =2

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

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

同步參數(shù)調(diào)整主庫是寫，對數(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來提高sql的執(zhí)行效率

4.2 MySql數(shù)據(jù)庫從庫同步其他問題及解決方案

mysql主從復(fù)制存在的問題：

主庫宕機后，數(shù)據(jù)可能丟失

從庫只有一個sql Thread，主庫寫壓力大，復(fù)制很可能延時

解決方法：

半同步復(fù)制—解決數(shù)據(jù)丟失的問題

并行復(fù)制----解決從庫復(fù)制延遲的問題

半同步復(fù)制mysql semi-sync（半同步復(fù)制）半同步復(fù)制：

5.5集成到mysql，以插件的形式存在，需要單獨安裝

確保事務(wù)提交后binlog至少傳輸?shù)揭粋€從庫

不保證從庫應(yīng)用完這個事務(wù)的binlog

性能有一定的降低，響應(yīng)時間會更長

網(wǎng)絡(luò)異常或從庫宕機，卡主主庫，直到超時或從庫恢復(fù)

主從復(fù)制–異步復(fù)制原理、半同步復(fù)制和并行復(fù)制原理比較

審核編輯：黃飛