最近在做MySQL主從數(shù)據(jù)庫(kù)同步測(cè)試,，發(fā)現(xiàn)了一些問(wèn)題，其中主從同步延遲問(wèn)題是其中之一,，下面內(nèi)容是從網(wǎng)上找到的一些講解,，記錄下來(lái)以便自己學(xué)習(xí)；

MySQL的主從同步是一個(gè)很成熟的架構(gòu),，優(yōu)點(diǎn)為：①在從服務(wù)器可以執(zhí)行查詢工作(即我們常說(shuō)的讀功能),，降低主服務(wù)器壓力;②在從主服務(wù)器進(jìn)行備份，避免備份期間影響主服務(wù)器服務(wù);③當(dāng)主服務(wù)器出現(xiàn)問(wèn)題時(shí),，可以切換到從服務(wù)器,。

MySQL主從同步故障－Slave_SQL_Running: No http://www./Linux/2014-02/96945.htm

MySQL主從同步搭建 http://www./Linux/2013-12/93934.htm

MySQL主從復(fù)制配置詳述 http://www./Linux/2014-02/97136.htm

MySQL Replication(主從服務(wù)器)配置實(shí)例 http://www./Linux/2013-12/94485.htm

在Linux系統(tǒng)中做MySQL數(shù)據(jù)庫(kù)主從服務(wù)器 http://www./Linux/2013-12/93986.htm

MySQL 安裝與主從配置 http://www./Linux/2013-12/93378.htm

相信大家對(duì)于這些好處已經(jīng)非常了解了，在項(xiàng)目的部署中也采用這種方案,。但是MySQL的主從同步一直有從庫(kù)延遲的問(wèn)題,，那么為什么會(huì)有這種問(wèn)題。這種問(wèn)題如何解決呢,？

1. MySQL數(shù)據(jù)庫(kù)主從同步延遲原理,。

2. MySQL數(shù)據(jù)庫(kù)主從同步延遲是怎么產(chǎn)生的。

3. MySQL數(shù)據(jù)庫(kù)主從同步延遲解決方案,。

1. MySQL數(shù)據(jù)庫(kù)主從同步延遲原理,。

答：談到MySQL數(shù)據(jù)庫(kù)主從同步延遲原理，得從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ù)主從同步延遲解決方案

答：最簡(jiǎn)單的減少slave同步延時(shí)的方案就是在架構(gòu)上做優(yōu)化,，盡量讓主庫(kù)的DDL快速執(zhí)行,。還有就是主庫(kù)是寫,，對(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

This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction

默認(rèn)情況下,，并不是每次寫入時(shí)都將binlog與硬盤同步。因此如果操作系統(tǒng)或機(jī)器(不僅僅是MySQL服務(wù)器)崩潰,，有可能binlog中最后的語(yǔ)句丟 失了,。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值,，但也是最慢的),，使binlog在每N次binlog寫入后與硬盤 同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時(shí),，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性,。如果使用InnoDB表，MySQL服務(wù)器 處理COMMIT語(yǔ)句,，它將整個(gè)事務(wù)寫入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ù)外的指令都需要把日志寫入（flush）硬盤，這是很費(fèi)時(shí)的,。特別是使用電 池供電緩存（Battery backed up cache）時(shí),。設(shè)成2對(duì)于很多運(yùn)用，特別是從MyISAM表轉(zhuǎn)過(guò)來(lái)的是可以的，它的意思是不寫入硬盤而是寫入系統(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ù),。

本文永久更新鏈接地址：http://www./Linux/2014-05/101450.htm