MYSQL主從不同步延遲原理分析及解決方案
更新時間：2013年09月22日 15:20:28 作者：
這篇文章介紹了MYSQL主從不同步延遲原理分析及解決方案，有需要的朋友可以參考一下
1. MySQL數(shù)據(jù)庫主從同步延遲原理。
要說延時原理，得從mysql的數(shù)據(jù)庫主從復(fù)制原理說起，mysql的主從復(fù)制都是單線程的操作，
主庫對所有DDL和DML產(chǎn)生binlog，binlog是順序?qū)懀孕屎芨?，slave的Slave_IO_Running線程到主庫取日志，效率很比較高，下一步，問題來了，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產(chǎn)生lock爭用，由于Slave_SQL_Running也是單線程的，所以一個DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會等待這個DDL執(zhí)行完才會繼續(xù)執(zhí)行，這就導致了延時。有朋友會問：“主庫上那個相同的DDL也需要執(zhí)行10分，為什么slave會延時？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。
2. MySQL數(shù)據(jù)庫主從同步延遲是怎么產(chǎn)生的。
當主庫的TPS并發(fā)較高時，產(chǎn)生的DDL數(shù)量超過slave一個sql線程所能承受的范圍，那么延時就產(chǎn)生了，當然還有就是可能與slave的大型query語句產(chǎn)生了鎖等待。
3. MySQL數(shù)據(jù)庫主從同步延遲解決方案。
丁奇的transefer是一個不錯的方案，不過一般公司受限于對mysql的代碼修改能力的限制和對mysql的掌控能力，還是不太適合。
最簡單的減少slave同步延時的方案就是在架構(gòu)上做優(yōu)化，盡量讓主庫的DDL快速執(zhí)行。還有就是主庫是寫，對數(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í)行效率。另外就是使用比主庫更好的硬件設(shè)備作為slave。
mysql-5.6.3已經(jīng)支持了多線程的主從復(fù)制。原理和丁奇的類似，丁奇的是以表做多線程，oracle使用的是以數(shù)據(jù)庫(schema)為單位做多線程，不同的庫可以使用不同的復(fù)制線程。
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
默認情況下，并不是每次寫入時都將binlog與硬盤同步。因此如果操作系統(tǒng)或機器(不僅僅是MySQL服務(wù)器)崩潰，有可能binlog中最后的語句丟 失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入后與硬盤 同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性。如果使用InnoDB表，MySQL服務(wù)器 處理COMMIT語句，它將整個事務(wù)寫入binlog并將事務(wù)提交到InnoDB中。如果在兩次操作之間出現(xiàn)崩潰，重啟時，事務(wù)被InnoDB回滾，但仍 然存在binlog中?？梢杂?-innodb-safe-binlog選項來增加InnoDB表內(nèi)容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事務(wù)支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務(wù)的 binlog(sync_binlog =1)和(默認情況為真)InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務(wù)后，MySQL服務(wù)器從binlog剪切回滾的 InnoDB事務(wù)。這樣可以確保binlog反饋InnoDB表的確切數(shù)據(jù)等，并使從服務(wù)器保持與主服務(wù)器保持同步(不接收 回滾的語句)。
innodb_flush_log_at_trx_commit （這個很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調(diào)整這個值。默認值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫入（flush）硬盤，這是很費時的。特別是使用電 池供電緩存（Battery backed up cache）時。設(shè)成2對于很多運用，特別是從MyISAM表轉(zhuǎn)過來的是可以的，它的意思是不寫入硬盤而是寫入系統(tǒng)緩存。日志仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設(shè)成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務(wù)的數(shù)據(jù)。而值2只會在整個操作系統(tǒng) 掛了時才可能丟數(shù)據(jù)。
要說延時原理，得從mysql的數(shù)據(jù)庫主從復(fù)制原理說起，mysql的主從復(fù)制都是單線程的操作，
主庫對所有DDL和DML產(chǎn)生binlog，binlog是順序?qū)懀孕屎芨?，slave的Slave_IO_Running線程到主庫取日志，效率很比較高，下一步，問題來了，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產(chǎn)生lock爭用，由于Slave_SQL_Running也是單線程的，所以一個DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會等待這個DDL執(zhí)行完才會繼續(xù)執(zhí)行，這就導致了延時。有朋友會問：“主庫上那個相同的DDL也需要執(zhí)行10分，為什么slave會延時？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。
2. MySQL數(shù)據(jù)庫主從同步延遲是怎么產(chǎn)生的。
當主庫的TPS并發(fā)較高時，產(chǎn)生的DDL數(shù)量超過slave一個sql線程所能承受的范圍，那么延時就產(chǎn)生了，當然還有就是可能與slave的大型query語句產(chǎn)生了鎖等待。
3. MySQL數(shù)據(jù)庫主從同步延遲解決方案。
丁奇的transefer是一個不錯的方案，不過一般公司受限于對mysql的代碼修改能力的限制和對mysql的掌控能力，還是不太適合。
最簡單的減少slave同步延時的方案就是在架構(gòu)上做優(yōu)化，盡量讓主庫的DDL快速執(zhí)行。還有就是主庫是寫，對數(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í)行效率。另外就是使用比主庫更好的硬件設(shè)備作為slave。
mysql-5.6.3已經(jīng)支持了多線程的主從復(fù)制。原理和丁奇的類似，丁奇的是以表做多線程，oracle使用的是以數(shù)據(jù)庫(schema)為單位做多線程，不同的庫可以使用不同的復(fù)制線程。
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
默認情況下，并不是每次寫入時都將binlog與硬盤同步。因此如果操作系統(tǒng)或機器(不僅僅是MySQL服務(wù)器)崩潰，有可能binlog中最后的語句丟 失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入后與硬盤 同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性。如果使用InnoDB表，MySQL服務(wù)器 處理COMMIT語句，它將整個事務(wù)寫入binlog并將事務(wù)提交到InnoDB中。如果在兩次操作之間出現(xiàn)崩潰，重啟時，事務(wù)被InnoDB回滾，但仍 然存在binlog中?？梢杂?-innodb-safe-binlog選項來增加InnoDB表內(nèi)容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事務(wù)支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務(wù)的 binlog(sync_binlog =1)和(默認情況為真)InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務(wù)后，MySQL服務(wù)器從binlog剪切回滾的 InnoDB事務(wù)。這樣可以確保binlog反饋InnoDB表的確切數(shù)據(jù)等，并使從服務(wù)器保持與主服務(wù)器保持同步(不接收 回滾的語句)。
innodb_flush_log_at_trx_commit （這個很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調(diào)整這個值。默認值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫入（flush）硬盤，這是很費時的。特別是使用電 池供電緩存（Battery backed up cache）時。設(shè)成2對于很多運用，特別是從MyISAM表轉(zhuǎn)過來的是可以的，它的意思是不寫入硬盤而是寫入系統(tǒng)緩存。日志仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設(shè)成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務(wù)的數(shù)據(jù)。而值2只會在整個操作系統(tǒng) 掛了時才可能丟數(shù)據(jù)。
相關(guān)文章
MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法(最新推薦)
在MySQL中,如果我們想要在一個條件函數(shù)如CASE內(nèi)部使用聚合函數(shù)如MAX獲取某個字段的最大值,我們通常需要在外部查詢或子查詢中執(zhí)行這個聚合操作,并將結(jié)果作為參數(shù)傳遞給條件函數(shù),下面通過實例代碼講解MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法,感興趣的朋友一起看看吧2024-05-05
查看修改mysql編碼方式讓它支持中文(gbk或者utf8)
MySQL的默認編碼是Latin1，不支持中文，要支持中文需要把數(shù)據(jù)庫的默認編碼修改為gbk或者utf8,真的是很麻煩啊，不過本文提供了詳細的修改教程，感興趣的你可不要走開啊，希望本文對你有所幫助2013-01-01
本文主要介紹了Docker搭建MySQL的實現(xiàn)示例,包括MySQL 8.0和MySQL 5.7,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-05-05
這篇文章主要給大家介紹了關(guān)于MySQL查看和修改時區(qū)的方法，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-08-08
64位Win10系統(tǒng)安裝Mysql5.7.11的方法(案例詳解)
小編在安裝64位Win10系統(tǒng)的mac book筆記本上用mysql-installer-community-5.7.11.0安裝Mysql5.7.11，在配置mysql server時老是卡住，報錯。下面小編把安裝方法分享給大家，供大家參考2016-08-08
gearman + mysql方式實現(xiàn)持久化操作示例
這篇文章主要介紹了gearman + mysql方式實現(xiàn)持久化操作,簡單描述了持久化的概念、原理,并結(jié)合實例形式分析了gearman + mysql持久化操作相關(guān)實現(xiàn)技巧,需要的朋友可以參考下2020-02-02
這篇文章主要介紹了Windows 下 MySQL 8.X 的安裝教程，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2019-11-11
MySQL?優(yōu)化利器?SHOW?PROFILE?的實現(xiàn)原理及細節(jié)展示
這篇文章主要介紹了MySQL優(yōu)化利器SHOW?PROFILE的實現(xiàn)原理,通過實例代碼展示SHOW PROFILE的用法,需要的朋友可以參考下2024-12-12
MySQL支持多種表連接（JOIN）方式,每種方式都有其特定的使用場景和優(yōu)缺點,以下是?MySQL?中主要的數(shù)據(jù)連接方式及其特點,有需要的小伙伴可以了解下2025-06-06
這篇文章主要介紹了MySQL5.6.40在CentOS7 64下安裝過程,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-06-06