一、MySQL數據庫主從同步延遲產生的原因

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

二、關于DDL和DML

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

DQL（Data QUERY Languages）語句：即數據庫定義語句，用來查詢SELECT子句，FROM子句，WHERE子句組成的查詢塊，比如：select–from–where–grouop by–having–order by–limit

DDL（Data Definition Languages）語句：即數據庫定義語句，用來創建數據庫中的表、索引、視圖、存儲過程、觸發器等，常用的語句關鍵字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增刪改表的結構

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

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

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

三、主從延時排查方法

通過監控 show slave status 命令輸出的Seconds_Behind_Master參數的值來判斷：

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

0，該值為零，表示主從復制良好；

正值，表示主從已經出現延時，數字越大表示從庫延遲越嚴重

四、解決方案

解決數據丟失的問題：

半同步復制

從MySQL5.5開始，MySQL已經支持半同步復制了，半同步復制介于異步復制和同步復制之間，主庫在執行完事務后不立刻返回結果給客戶端，需要等待至少一個從庫接收到并寫到relay log中才返回結果給客戶端。相對于異步復制，半同步復制提高了數據的安全性，同時它也造成了一個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表示每一次事務提交或事務外的指令都需要把日志flush到磁盤。

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

4.1 解決從庫復制延遲的問題：

架構方面

業務的持久化層的實現采用分庫架構，mysql服務可平行擴展，分散壓力。

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

服務的基礎架構在業務和mysql之間加入memcache或者redis的cache層。降低mysql的讀壓力。

不同業務的mysql物理上放在不同機器，分散壓力。

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

硬件方面

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

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

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

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

mysql主從同步加速

sync_binlog在slave端設置為0

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

直接禁用slave端的binlog

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

從文件系統本身屬性角度優化

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

同步參數調整主庫是寫，對數據安全性較高，比如sync_binlog=1，

innodb_flush_log_at_trx_commit = 1 之類的設置是需要的而slave則不需要這么高的數據安全，完全可以講sync_binlog設置為0或者關閉binlog，innodb_flushlog也可以設置為0來提高sql的執行效率

4.2 MySql數據庫從庫同步其他問題及解決方案

mysql主從復制存在的問題：

主庫宕機后，數據可能丟失

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

解決方法：

半同步復制—解決數據丟失的問題

并行復制----解決從庫復制延遲的問題

半同步復制mysql semi-sync（半同步復制）半同步復制：

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

確保事務提交后binlog至少傳輸到一個從庫

不保證從庫應用完這個事務的binlog

性能有一定的降低，響應時間會更長

網絡異常或從庫宕機，卡主主庫，直到超時或從庫恢復

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

審核編輯：黃飛