MySQL數據庫主從同步延遲原理
要說延時原理得從mysql的數據庫主從復制原理說起mysql的主從復制都是單線程的操作
主庫對所有DDL和DML產生binlogbinlog是順序寫所以效率很高slave的Slave_IO_Running線程到主庫取日志效率很比較高下一步問題來了slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施DML和DDL的IO操作是隨即的不是順序的成本高很多還可能可slave上的其他查詢產生lock爭用由於Slave_SQL_Running也是單線程的所以一個DDL卡主了需要執行分鐘那麼所有之後的DDL會等待這個DDL執行完才會繼續執行這就導致了延時有朋友會問主庫上那個相同的DDL也需要執行分為什麼slave會延時？答案是master可以並發Slave_SQL_Running線程卻不可以
MySQL數據庫主從同步延遲是怎麼產生的
當主庫的TPS並發較高時產生的DDL數量超過slave一個sql線程所能承受的范圍那麼延時就產生了當然還有就是可能與slave的大型query語句產生了鎖等待
MySQL數據庫主從同步延遲解決方案
丁奇的transefer是一個不錯的方案不過一般公司受限於對mysql的代碼修改能力的限制和對mysql的掌控能力還是不太適合
最簡單的減少slave同步延時的方案就是在架構上做優化盡量讓主庫的DDL快速執行還有就是主庫是寫對數據安全性較高比如sync_binlog=innodb_flush_log_at_trx_commit = 之類的設置而slave則不需要這麼高的數據安全完全可以講sync_binlog設置為或者關閉binloginnodb_flushlog也可以設置為來提高sql的執行效率另外就是使用比主庫更好的硬件設備作為slave
mysql已經支持了多線程的主從復制原理和丁奇的類似丁奇的是以表做多線程oracle使用的是以數據庫（schema）為單位做多線程不同的庫可以使用不同的復制線程
sync_binlog= o
This makes MySQL synchronize the binary logs contents to disk each time it commits a transaction
默認情況下並不是每次寫入時都將binlog與硬盤同步因此如果操作系統或機器（不僅僅是MySQL服務器）崩潰有可能binlog中最後的語句丟 失了要想防止這種情況你可以使用sync_binlog全局變量（是最安全的值但也是最慢的）使binlog在每N次binlog寫入後與硬盤 同步即使sync_binlog設置為出現崩潰時也有可能表內容和binlog內容之間存在不一致性如果使用InnoDB表MySQL服務器 處理COMMIT語句它將整個事務寫入binlog並將事務提交到InnoDB中如果在兩次操作之間出現崩潰重啟時事務被InnoDB回滾但仍 然存在binlog中可以用innodbsafebinlog選項來增加InnoDB表內容和binlog之間的一致性（注釋在MySQL 中不需要innodbsafebinlog;由於引入了XA事務支持該選項作廢了）該選項可以提供更大程度的安全使每個事務的 binlog（sync_binlog =）和（默認情況為真）InnoDB日志與硬盤同步該選項的效果是崩潰後重啟時在滾回事務後MySQL服務器從binlog剪切回滾的 InnoDB事務這樣可以確保binlog反饋InnoDB表的確切數據等並使從服務器保持與主服務器保持同步（不接收 回滾的語句）
innodb_flush_log_at_trx_commit （這個很管用）
抱怨Innodb比MyISAM慢 倍？那麼你大概是忘了調整這個值默認值的意思是每一次事務提交或事務外的指令都需要把日志寫入（flush）硬盤這是很費時的特別是使用電 池供電緩存（Battery backed up cache）時設成對於很多運用特別是從MyISAM表轉過來的是可以的它的意思是不寫入硬盤而是寫入系統緩存日志仍然會每秒flush到硬 盤所以你一般不會丟失超過秒的更新設成會更快一點但安全方面比較差即使MySQL掛了也可能會丟失事務的數據而值只會在整個操作系統 掛了時才可能丟數據
From:http://tw.wingwit.com/Article/program/MySQL/201311/29358.html
