MYSQL主從不同步延遲原理分析及解決方案
更新時(shí)間：2013年09月22日 15:20:28 作者：
這篇文章介紹了MYSQL主從不同步延遲原理分析及解決方案，有需要的朋友可以參考一下
1. MySQL數(shù)據(jù)庫(kù)主從同步延遲原理。
要說(shuō)延時(shí)原理，得從mysql的數(shù)據(jù)庫(kù)主從復(fù)制原理說(shuō)起，mysql的主從復(fù)制都是單線程的操作，
主庫(kù)對(duì)所有DDL和DML產(chǎn)生binlog，binlog是順序?qū)?，所以效率很高，slave的Slave_IO_Running線程到主庫(kù)取日志，效率很比較高，下一步，問(wèn)題來(lái)了，slave的Slave_SQL_Running線程將主庫(kù)的DDL和DML操作在slave實(shí)施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產(chǎn)生lock爭(zhēng)用，由于Slave_SQL_Running也是單線程的，所以一個(gè)DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會(huì)等待這個(gè)DDL執(zhí)行完才會(huì)繼續(xù)執(zhí)行，這就導(dǎo)致了延時(shí)。有朋友會(huì)問(wèn)：“主庫(kù)上那個(gè)相同的DDL也需要執(zhí)行10分，為什么slave會(huì)延時(shí)？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。
2. MySQL數(shù)據(jù)庫(kù)主從同步延遲是怎么產(chǎn)生的。
當(dāng)主庫(kù)的TPS并發(fā)較高時(shí)，產(chǎn)生的DDL數(shù)量超過(guò)slave一個(gè)sql線程所能承受的范圍，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語(yǔ)句產(chǎn)生了鎖等待。
3. MySQL數(shù)據(jù)庫(kù)主從同步延遲解決方案。
丁奇的transefer是一個(gè)不錯(cuò)的方案，不過(guò)一般公司受限于對(duì)mysql的代碼修改能力的限制和對(duì)mysql的掌控能力，還是不太適合。
最簡(jiǎn)單的減少slave同步延時(shí)的方案就是在架構(gòu)上做優(yōu)化，盡量讓主庫(kù)的DDL快速執(zhí)行。還有就是主庫(kù)是寫(xiě)，對(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í)行效率。另外就是使用比主庫(kù)更好的硬件設(shè)備作為slave。
mysql-5.6.3已經(jīng)支持了多線程的主從復(fù)制。原理和丁奇的類似，丁奇的是以表做多線程，oracle使用的是以數(shù)據(jù)庫(kù)(schema)為單位做多線程，不同的庫(kù)可以使用不同的復(fù)制線程。
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
默認(rèn)情況下，并不是每次寫(xiě)入時(shí)都將binlog與硬盤同步。因此如果操作系統(tǒng)或機(jī)器(不僅僅是MySQL服務(wù)器)崩潰，有可能binlog中最后的語(yǔ)句丟 失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫(xiě)入后與硬盤 同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時(shí)，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性。如果使用InnoDB表，MySQL服務(wù)器 處理COMMIT語(yǔ)句，它將整個(gè)事務(wù)寫(xiě)入binlog并將事務(wù)提交到InnoDB中。如果在兩次操作之間出現(xiàn)崩潰，重啟時(shí)，事務(wù)被InnoDB回滾，但仍 然存在binlog中?？梢杂?-innodb-safe-binlog選項(xiàng)來(lái)增加InnoDB表內(nèi)容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事務(wù)支持，該選項(xiàng)作廢了），該選項(xiàng)可以提供更大程度的安全，使每個(gè)事務(wù)的 binlog(sync_binlog =1)和(默認(rèn)情況為真)InnoDB日志與硬盤同步，該選項(xiàng)的效果是崩潰后重啟時(shí)，在滾回事務(wù)后，MySQL服務(wù)器從binlog剪切回滾的 InnoDB事務(wù)。這樣可以確保binlog反饋InnoDB表的確切數(shù)據(jù)等，并使從服務(wù)器保持與主服務(wù)器保持同步(不接收 回滾的語(yǔ)句)。
innodb_flush_log_at_trx_commit （這個(gè)很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調(diào)整這個(gè)值。默認(rèn)值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫(xiě)入（flush）硬盤，這是很費(fèi)時(shí)的。特別是使用電 池供電緩存（Battery backed up cache）時(shí)。設(shè)成2對(duì)于很多運(yùn)用，特別是從MyISAM表轉(zhuǎn)過(guò)來(lái)的是可以的，它的意思是不寫(xiě)入硬盤而是寫(xiě)入系統(tǒng)緩存。日志仍然會(huì)每秒flush到硬 盤，所以你一般不會(huì)丟失超過(guò)1-2秒的更新。設(shè)成0會(huì)更快一點(diǎn)，但安全方面比較差，即使MySQL掛了也可能會(huì)丟失事務(wù)的數(shù)據(jù)。而值2只會(huì)在整個(gè)操作系統(tǒng) 掛了時(shí)才可能丟數(shù)據(jù)。
要說(shuō)延時(shí)原理，得從mysql的數(shù)據(jù)庫(kù)主從復(fù)制原理說(shuō)起，mysql的主從復(fù)制都是單線程的操作，
主庫(kù)對(duì)所有DDL和DML產(chǎn)生binlog，binlog是順序?qū)?，所以效率很高，slave的Slave_IO_Running線程到主庫(kù)取日志，效率很比較高，下一步，問(wèn)題來(lái)了，slave的Slave_SQL_Running線程將主庫(kù)的DDL和DML操作在slave實(shí)施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產(chǎn)生lock爭(zhēng)用，由于Slave_SQL_Running也是單線程的，所以一個(gè)DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會(huì)等待這個(gè)DDL執(zhí)行完才會(huì)繼續(xù)執(zhí)行，這就導(dǎo)致了延時(shí)。有朋友會(huì)問(wèn)：“主庫(kù)上那個(gè)相同的DDL也需要執(zhí)行10分，為什么slave會(huì)延時(shí)？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。
2. MySQL數(shù)據(jù)庫(kù)主從同步延遲是怎么產(chǎn)生的。
當(dāng)主庫(kù)的TPS并發(fā)較高時(shí)，產(chǎn)生的DDL數(shù)量超過(guò)slave一個(gè)sql線程所能承受的范圍，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語(yǔ)句產(chǎn)生了鎖等待。
3. MySQL數(shù)據(jù)庫(kù)主從同步延遲解決方案。
丁奇的transefer是一個(gè)不錯(cuò)的方案，不過(guò)一般公司受限于對(duì)mysql的代碼修改能力的限制和對(duì)mysql的掌控能力，還是不太適合。
最簡(jiǎn)單的減少slave同步延時(shí)的方案就是在架構(gòu)上做優(yōu)化，盡量讓主庫(kù)的DDL快速執(zhí)行。還有就是主庫(kù)是寫(xiě)，對(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í)行效率。另外就是使用比主庫(kù)更好的硬件設(shè)備作為slave。
mysql-5.6.3已經(jīng)支持了多線程的主從復(fù)制。原理和丁奇的類似，丁奇的是以表做多線程，oracle使用的是以數(shù)據(jù)庫(kù)(schema)為單位做多線程，不同的庫(kù)可以使用不同的復(fù)制線程。
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
默認(rèn)情況下，并不是每次寫(xiě)入時(shí)都將binlog與硬盤同步。因此如果操作系統(tǒng)或機(jī)器(不僅僅是MySQL服務(wù)器)崩潰，有可能binlog中最后的語(yǔ)句丟 失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫(xiě)入后與硬盤 同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時(shí)，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性。如果使用InnoDB表，MySQL服務(wù)器 處理COMMIT語(yǔ)句，它將整個(gè)事務(wù)寫(xiě)入binlog并將事務(wù)提交到InnoDB中。如果在兩次操作之間出現(xiàn)崩潰，重啟時(shí)，事務(wù)被InnoDB回滾，但仍 然存在binlog中?？梢杂?-innodb-safe-binlog選項(xiàng)來(lái)增加InnoDB表內(nèi)容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事務(wù)支持，該選項(xiàng)作廢了），該選項(xiàng)可以提供更大程度的安全，使每個(gè)事務(wù)的 binlog(sync_binlog =1)和(默認(rèn)情況為真)InnoDB日志與硬盤同步，該選項(xiàng)的效果是崩潰后重啟時(shí)，在滾回事務(wù)后，MySQL服務(wù)器從binlog剪切回滾的 InnoDB事務(wù)。這樣可以確保binlog反饋InnoDB表的確切數(shù)據(jù)等，并使從服務(wù)器保持與主服務(wù)器保持同步(不接收 回滾的語(yǔ)句)。
innodb_flush_log_at_trx_commit （這個(gè)很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調(diào)整這個(gè)值。默認(rèn)值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫(xiě)入（flush）硬盤，這是很費(fèi)時(shí)的。特別是使用電 池供電緩存（Battery backed up cache）時(shí)。設(shè)成2對(duì)于很多運(yùn)用，特別是從MyISAM表轉(zhuǎn)過(guò)來(lái)的是可以的，它的意思是不寫(xiě)入硬盤而是寫(xiě)入系統(tǒng)緩存。日志仍然會(huì)每秒flush到硬 盤，所以你一般不會(huì)丟失超過(guò)1-2秒的更新。設(shè)成0會(huì)更快一點(diǎn)，但安全方面比較差，即使MySQL掛了也可能會(huì)丟失事務(wù)的數(shù)據(jù)。而值2只會(huì)在整個(gè)操作系統(tǒng) 掛了時(shí)才可能丟數(shù)據(jù)。
相關(guān)文章
在MySQL數(shù)據(jù)庫(kù)中表是一種很重要的數(shù)據(jù)庫(kù)對(duì)象,是組成數(shù)據(jù)庫(kù)的基本元素,由若干個(gè)字段組成,主要用來(lái)實(shí)現(xiàn)存儲(chǔ)數(shù)據(jù)記錄,這篇文章主要給大家介紹了關(guān)于MySQL庫(kù)操作和表操作的相關(guān)資料,需要的朋友可以參考下2023-12-12
Mysql中自定義函數(shù)的創(chuàng)建和執(zhí)行方式
這篇文章主要介紹了Mysql中自定義函數(shù)的創(chuàng)建和執(zhí)行方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-03-03
MySQL DISTINCT 的基本實(shí)現(xiàn)原理詳解
這篇文章主要介紹了MySQL DISTINCT 的基本實(shí)現(xiàn)原理詳解,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-07-07
關(guān)于MySQL數(shù)據(jù)庫(kù)死鎖的案例和解決方案
MySQL Update語(yǔ)句防止死鎖是指在修改MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)時(shí),為避免多個(gè)進(jìn)程同時(shí)修改同一數(shù)據(jù)行而造成死鎖的情況,引入了一些機(jī)制來(lái)防止死鎖的產(chǎn)生,本文介紹了一個(gè) MySQL 數(shù)據(jù)庫(kù)死鎖的案例和解決方案,需要的朋友可以參考下2023-09-09
mysql 8.0.18各版本安裝及安裝中出現(xiàn)的問(wèn)題（精華總結(jié)）
這篇文章主要介紹了mysql 8.0.18各版本安裝及安裝中出現(xiàn)的問(wèn)題,本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-12-12
MySql無(wú)法連接本地地址localhost問(wèn)題
這篇文章主要介紹了MySql無(wú)法連接本地地址localhost問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-06-06
這篇文章給大家介紹了MySql 8.0.11安裝配置教程，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，感興趣的朋友一起看看吧2018-05-05
全量備份是MySQL數(shù)據(jù)庫(kù)備份的常見(jiàn)方式,本文主要介紹了MySQL全量備份的實(shí)現(xiàn),全量備份和增量備份是 MySQL 數(shù)據(jù)庫(kù)備份的兩種常見(jiàn)方式2024-04-04
關(guān)于MySQL查詢語(yǔ)句的優(yōu)化詳解
這篇文章主要介紹了MySQL查詢語(yǔ)句的優(yōu)化方法，分別介紹了子查詢優(yōu)化，分頁(yè)查詢優(yōu)化以及排序查詢優(yōu)化，對(duì)學(xué)習(xí)有一定的幫助，需要的小伙伴可以參考一下2023-04-04
MySQL無(wú)法修改主鍵的問(wèn)題分析及解決方案
最近同事咨詢了一個(gè)問(wèn)題,TDSQL（for MySQL）中的某張表主鍵需要改為聯(lián)合主鍵,是否必須先刪除現(xiàn)有的主鍵？因?yàn)閯h除主鍵時(shí),提示這個(gè)錯(cuò)誤,所以本文給大家介紹了MySQL無(wú)法修改主鍵的問(wèn)題分析及解決方案,需要的朋友可以參考下2024-01-01