http://blog.csdn.net/woqutechteam/article/details/51178803

MySQL ?Binary log在MySQL 5.1版本后推出主要用于主備復(fù)制的搭建，我們回顧下MySQL 在開(kāi)啟/關(guān)閉 Binary Log功能時(shí)是如何工作的 。

MySQL沒(méi)有開(kāi)啟Binary log的情況下：?

• ?InnoDB存儲(chǔ)引擎通過(guò)redo和undo日志可以safe crash recovery數(shù)據(jù)庫(kù)，當(dāng)數(shù)據(jù)crash recovery時(shí)，通過(guò)redo日志將所有已經(jīng)在存儲(chǔ)引擎內(nèi)部提交的事務(wù)應(yīng)用redo log恢復(fù)，所有已經(jīng)prepared但是沒(méi)有commit的transactions將會(huì)應(yīng)用undo log做roll back。然后客戶端連接時(shí)就能看到已經(jīng)提交的數(shù)據(jù)存在數(shù)據(jù)庫(kù)內(nèi)，未提交被回滾地?cái)?shù)據(jù)需要重新執(zhí)行。

MySQL開(kāi)啟Binary log 的情況下：

• 為了保證存儲(chǔ)引擎和MySQL數(shù)據(jù)庫(kù)上層的二進(jìn)制日志保持一致（因?yàn)閭鋷?kù)通過(guò)二進(jìn)制日志重放主庫(kù)提交的事務(wù)，假設(shè)主庫(kù)存儲(chǔ)引擎已經(jīng)提交而二進(jìn)制日志沒(méi)有保持一致，則會(huì)使備庫(kù)數(shù)據(jù)丟失造成主備數(shù)據(jù)不一致），引入二階段提交（two phase commit or 2pc）?

圖1 二階段提交

? MySQL二階段提交流程：

????????????Storage Engine（InnoDB） transaction prepare階段：即sql語(yǔ)句已經(jīng)成功執(zhí)行并生成redo和undo的內(nèi)存日志

????????????Binary log日志提提交

• • • write()將binary log內(nèi)存日志數(shù)據(jù)寫入文件系統(tǒng)緩存
    • fsync()將binary log 文件系統(tǒng)緩存日志數(shù)據(jù)永久寫入磁盤

????????????Storage Engine(InnoDB)內(nèi)部提交

• • • commit階段在存儲(chǔ)引擎內(nèi)提交( innodb_flush_log_at_trx_commit控制)使undo和redo永久寫入磁盤

????開(kāi)啟Binary log的MySQL在crash recovery時(shí)：

• 當(dāng)事務(wù)在prepare階段crash，數(shù)據(jù)庫(kù)recovery的時(shí)候該事務(wù)未寫入Binary log并且存儲(chǔ)引擎未提交，將該事務(wù)roll back。
• 當(dāng)事務(wù)在Binary log日志已經(jīng)fsync()永久寫入二進(jìn)制日志時(shí)crash，但是存儲(chǔ)引擎未來(lái)得及commit,此時(shí)MySQL數(shù)據(jù)庫(kù)recovery的時(shí)候?qū)?huì)從二進(jìn)制日志的Xid（MySQL數(shù)據(jù)庫(kù)內(nèi)部分布式事務(wù)XA）中獲取提交的信息重新將該事務(wù)重做并commit使存儲(chǔ)引擎和二進(jìn)制日志始終保持一致。

?

以上提到單個(gè)事務(wù)的二階段提交過(guò)程，能夠保證存儲(chǔ)引擎和binary log日志保持一致，但是在并發(fā)的情況下怎么保證存儲(chǔ)引擎和Binary Log提交的順序一致？當(dāng)多個(gè)事務(wù)并發(fā)提交的情況，如果Binary Log和存儲(chǔ)引擎順序不一致會(huì)造成什么影響？

圖2 InnoDB存儲(chǔ)引擎提交的順序與MySQL上層的二進(jìn)制日志順序不同

如上圖：事務(wù)按照T1、T2、T3順序開(kāi)始執(zhí)行，將二進(jìn)制日志（按照T1、T2、T3順序）寫入日志文件系統(tǒng)緩存，調(diào)用fsync()進(jìn)行一次group commit將日志文件永久寫入磁盤，但是存儲(chǔ)引擎提交的順序?yàn)?strong>T2、T3、T1。當(dāng)T2、T3提交事務(wù)之后做了一個(gè)On-line的backup程序新建一個(gè)slave來(lái)做replication，那么事務(wù)T1在slave機(jī)器restore MySQL數(shù)據(jù)庫(kù)的時(shí)候發(fā)現(xiàn)未在存儲(chǔ)引擎內(nèi)提交，T1事務(wù)被roll back，此時(shí)主備數(shù)據(jù)不一致(搭建Slave時(shí)，change master to的日志偏移量記錄T3在事務(wù)位置之后)。

結(jié)論：MySQL數(shù)據(jù)庫(kù)上層二進(jìn)制日志的寫入順序和存儲(chǔ)引擎InnoDB層的事務(wù)提交順序一致，用于備份及恢復(fù)需要，如xtrabackup和innobackpex工具。
? ? ??為了解決以上問(wèn)題，在早期的MySQL版本，通過(guò)prepare_commit_mutex 鎖保證MySQ數(shù)據(jù)庫(kù)上層二進(jìn)制日志和Innodb存儲(chǔ)引擎層的事務(wù)提交順序一致。

圖3 通過(guò)prepare_commit_mutex保證存儲(chǔ)引擎和二進(jìn)制日志順序提交順序一致

圖3可以看出在prepare_commit_mutex，只有當(dāng)上一個(gè)事務(wù)commit后釋放鎖，下一個(gè)事務(wù)才可以進(jìn)行prepara操作，并且在每個(gè)transaction過(guò)程中Binary log沒(méi)有fsync()的調(diào)用。由于內(nèi)存數(shù)據(jù)寫入磁盤的開(kāi)銷很大，如果頻繁fsync()把日志數(shù)據(jù)永久寫入磁盤數(shù)據(jù)庫(kù)的性能將會(huì)急劇下降。此時(shí)MySQL 數(shù)據(jù)庫(kù)提供sync_binlog參數(shù)來(lái)設(shè)置多少個(gè)binlog日志產(chǎn)生的時(shí)候調(diào)用一次fsync()把二進(jìn)制日志刷入磁盤來(lái)提高整體性能，該參數(shù)的設(shè)置作用：

• sync_binlog=0，二進(jìn)制日志fsync()的操作基于操作系統(tǒng)。
• sync_binlog=1，每一個(gè)transaction commit都會(huì)調(diào)用一次fsync()，此時(shí)能保證數(shù)據(jù)最安全但是性能影響較大。
• sync_binlog=N，當(dāng)數(shù)據(jù)庫(kù)crash的時(shí)候至少會(huì)丟失N-1個(gè)transactions。

圖3 所示MySQL開(kāi)啟Binary log時(shí)使用prepare_commit_mutex和sync_log保證二進(jìn)制日志和存儲(chǔ)引擎順序保持一致（通過(guò)sync_binlog來(lái)控制日志的刷新頻率），prepare_commit_mutex的鎖機(jī)制造成高并發(fā)提交事務(wù)的時(shí)候性能非常差而且二進(jìn)制日志也無(wú)法group commit。

那么如何保證MySQL開(kāi)啟Binary Log日志后使二進(jìn)制日志寫入順序和存儲(chǔ)引擎提交順序保持一致并且能夠進(jìn)行二進(jìn)制日志的Group Commit？

MySQL 5.6 引入BLGC（Binary Log Group Commit）,二進(jìn)制日志的提交過(guò)程分成三個(gè)階段，Flush stage、Sync stage、Commit stage。

那么事務(wù)提交過(guò)程簡(jiǎn)化為：

存儲(chǔ)引擎（InnoDB) Prepare????---->????數(shù)據(jù)庫(kù)上層(Binary Log) ? Flush Stage????---->????Sync Stage????---->????調(diào)存儲(chǔ)引擎（InnoDB）Commit stage.

每個(gè)stage階段都有各自的隊(duì)列，使每個(gè)session的事務(wù)進(jìn)行排隊(duì)。當(dāng)一個(gè)線程注冊(cè)了一個(gè)空隊(duì)列，該線程就視為該隊(duì)列的leader，后注冊(cè)到該隊(duì)列的線程為follower，leader控制隊(duì)列中follower的行為。leader同時(shí)帶領(lǐng)當(dāng)前隊(duì)列的所有follower到下一個(gè)stage去執(zhí)行，當(dāng)遇到下一個(gè)stage并非空隊(duì)列，此時(shí)leader可以變成follower到此隊(duì)列中（注：follower的線程不可能變成leader）

?圖4: 二進(jìn)制日志三階段提交過(guò)程

在 Flush stage：所有已經(jīng)注冊(cè)線程都將寫入binary log緩存

在Sync stage ：binary log緩存的數(shù)據(jù)將會(huì)sync到磁盤，當(dāng)sync_binlog=1時(shí)所有該隊(duì)列事務(wù)的二進(jìn)制日志緩存永久寫入磁盤

在 Commit stage:leader根據(jù)順序調(diào)用存儲(chǔ)引擎提交事務(wù)。

當(dāng)一組事務(wù)在進(jìn)行Commit階段時(shí)，其他新的事務(wù)可以進(jìn)行Flush階段，從而使group commit不斷生效。那么為了提高group commit中一組隊(duì)列的事務(wù)數(shù)量，MySQL用binlog_max_flush_queue_time來(lái)控制在Flush stage中的等待時(shí)間，讓Flush隊(duì)列在此階段多等待一些時(shí)間來(lái)增加這一組事務(wù)隊(duì)列的數(shù)量使該隊(duì)列到Sync階段可以一次fysn()更多的事務(wù)。

?

MySQL 5.7 Parallel replication實(shí)現(xiàn)主備多線程復(fù)制基于主庫(kù)Binary Log Group Commit, 并在Binary log日志中標(biāo)識(shí)同一組事務(wù)的last_commited=N和該組事務(wù)內(nèi)所有的事務(wù)提交順序。為了增加一組事務(wù)內(nèi)的事務(wù)數(shù)量提高備庫(kù)組提交時(shí)的并發(fā)量引入了binlog_group_commit_sync_delay=N 和binlog_group_commit_sync_no_delay_count=N (注：binlog_max_flush_queue_time?在MySQL的5.7.9及之后版本不再生效)參數(shù)，MySQL等待binlog_group_commit_sync_delay毫秒直到達(dá)到binlog_group_commit_sync_no_delay_count事務(wù)個(gè)數(shù)時(shí)，將進(jìn)行一次組提交。

?

Reference:http://mysqlmusings.blogspot.kr/2012/06/binary-log-group-commit-in-mysql-56.html

轉(zhuǎn)載于:https://www.cnblogs.com/devilwind/p/8058248.html

總結(jié)

以上是生活随笔為你收集整理的MySQL并发复制系列一：binlog组提交 (转载)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。