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

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

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ò)異?；驈膸戾礄C，卡主主庫，直到超時或從庫恢復(fù)

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

到此這篇關(guān)于MySQL主從同步延遲原因與解決方案的文章就介紹到這了,更多相關(guān)MySQL主從同步延遲內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論