MySQL 數(shù)據(jù)庫(kù)忽然斷電會(huì)丟數(shù)據(jù)嗎<轉(zhuǎn)載>

在上一章，我講解了 MySQL 鎖的相關(guān)內(nèi)容。主要談到了全局鎖、表鎖、行鎖以及死鎖等。通過(guò)這些學(xué)習(xí)，相信我們可以理解鎖的原理，并在工作中降低鎖沖突的概率。這也是優(yōu)化數(shù)據(jù)庫(kù)必須掌握的知識(shí)點(diǎn)。

從本節(jié)開(kāi)始，將進(jìn)入一個(gè)新的 MySQL 知識(shí)大類：MySQL 事務(wù)。

什么是事務(wù)？

根據(jù)《高性能 MySQL》第 3 版 1.3 事務(wù)一節(jié)中定義：

事務(wù)就是一組原子性的 SQL 查詢，或者說(shuō)一個(gè)獨(dú)立的工作單元。如果數(shù)據(jù)庫(kù)引擎能夠成功地對(duì)數(shù)據(jù)庫(kù)應(yīng)用該組查詢的全部語(yǔ)句，那么就執(zhí)行該組查詢。如果其中有任何一條語(yǔ)句因?yàn)楸罎⒒蚱渌驘o(wú)法執(zhí)行，那么所有的語(yǔ)句都不會(huì)執(zhí)行。也就是說(shuō)，事務(wù)內(nèi)的語(yǔ)句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。

看上面的文字可以稍微抽象了一點(diǎn)，可以結(jié)合生活中的一個(gè)例子：

比如你給朋友轉(zhuǎn)賬 100 元，其大致過(guò)程是：從你的賬戶扣除 100 元，然后再到你朋友的賬戶中增加 100 元，試想，如果在這中間，因?yàn)榫W(wǎng)絡(luò)問(wèn)題或者程序問(wèn)題，導(dǎo)致在你的賬戶中扣除了，但是沒(méi)有在你朋友的賬戶中增加，那豈不是亂套了。

所以，類似這種情況，就可以把這兩個(gè)步驟放到一個(gè)事務(wù)里面。要么全部成功，也就是從你的賬戶扣除之后，然后在你朋友賬戶中新增；要么全部失敗，比如在中間出現(xiàn)問(wèn)題，會(huì)回滾這中間所有的變更。大致操作步驟如下表：

一個(gè)良好的事務(wù)處理系統(tǒng)，必須具備 ACID 特性：

atomicity（原子性） ：要么全執(zhí)行，要么全都不執(zhí)行；consistency（一致性）：在事務(wù)開(kāi)始和完成時(shí)，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)；isolation（隔離性） ：事務(wù)處理過(guò)程中的中間狀態(tài)對(duì)外部是不可見(jiàn)的；durability（持久性） ：事務(wù)完成之后，它對(duì)于數(shù)據(jù)的修改是永久性的。InnoDB 采用 redo log 機(jī)制來(lái)保證事務(wù)更新的一致性和持久性。什么是 redo log？下面來(lái)一起看下：

2 Redo log

Redo log 稱為重做日志，用于記錄事務(wù)操作變化，記錄的是數(shù)據(jù)被修改之后的值。

Redo log 由兩部分組成：

內(nèi)存中的重做日志緩沖（redo log buffer）重做日志文件（redo log file）

每次數(shù)據(jù)更新會(huì)先更新 redo log buffer，然后根據(jù) innodb_flush_log_at_trx_commit 來(lái)控制 redo log buffer 更新到 redo log file 的時(shí)機(jī)。innodb_flush_log_at_trx_commit 有三個(gè)值可選：

0：事務(wù)提交時(shí)，在事務(wù)提交時(shí)，每秒觸發(fā)一次 redo log buffer 寫(xiě)磁盤(pán)操作，并調(diào)用操作系統(tǒng) fsync 刷新 IO 緩存。

1：事務(wù)提交時(shí)，InnoDB 立即將緩存中的 redo 日志寫(xiě)到日志文件中，并調(diào)用操作系統(tǒng) fsync 刷新 IO 緩存；

2：事務(wù)提交時(shí)，InnoDB 立即將緩存中的 redo 日志寫(xiě)到日志文件中，但不是馬上調(diào)用 fsync 刷新 IO 緩存，而是每秒只做一次磁盤(pán) IO 緩存刷新操作。

innodb_flush_log_at_trx_commit 參數(shù)的默認(rèn)值是 1，也就是每個(gè)事務(wù)提交的時(shí)候都會(huì)從 log buffer 寫(xiě)更新記錄到日志文件，而且會(huì)刷新磁盤(pán)緩存，這完全滿足事務(wù)持久化的要求，是最安全的，但是這樣會(huì)有比較大的性能損失。

將參數(shù)設(shè)置為 0 時(shí)，如果數(shù)據(jù)庫(kù)崩潰，最后 1秒鐘的 redo log 可能會(huì)由于未及時(shí)寫(xiě)入磁盤(pán)文件而丟失，這種方式盡管效率最高，但是最不安全。

將參數(shù)設(shè)置為 2 時(shí)，如果數(shù)據(jù)庫(kù)崩潰，由于已經(jīng)執(zhí)行了重做日志寫(xiě)入磁盤(pán)的操作，只是沒(méi)有做磁盤(pán) IO 刷新操作，因此，只要不發(fā)生操作系統(tǒng)崩潰，數(shù)據(jù)就不會(huì)丟失，這種方式是對(duì)性能和安全的一種折中處理。

3 Binlog

二進(jìn)制日志（binlog）記錄了所有的 DDL（數(shù)據(jù)定義語(yǔ)句）和 DML（數(shù)據(jù)操縱語(yǔ)句），但是不包括 select 和 show 這類操作。Binlog 有以下幾個(gè)作用：

恢復(fù)：數(shù)據(jù)恢復(fù)時(shí)可以使用二進(jìn)制日志。復(fù)制：通過(guò)傳輸二進(jìn)制日志到從庫(kù)，然后進(jìn)行恢復(fù)，以實(shí)現(xiàn)主從同步。審計(jì)：可以通過(guò)二進(jìn)制日志進(jìn)行審計(jì)數(shù)據(jù)的變更操作。

可以通過(guò)參數(shù) sync_binlog 來(lái)控制累積多少個(gè)事務(wù)后才將二進(jìn)制日志 fsync 到磁盤(pán)。

sync_binlog=0，表示每次提交事務(wù)都只write，不fsync。sync_binlog=1，表示每次提交事務(wù)都會(huì)執(zhí)行fsync。sync_binlog=N(N>1)，表示每次提交事務(wù)都write，累積N個(gè)事務(wù)后才fsync。比如要加快寫(xiě)入數(shù)據(jù)的速度或者機(jī)器磁盤(pán) IO 瓶頸時(shí)，可以將 sync_binlog 設(shè)置成大于 1 的值，但是如果設(shè)置為 N(N>1)時(shí)，如果數(shù)據(jù)庫(kù)崩潰，可能會(huì)丟失最近 N 個(gè)事務(wù)的 binlog。

4 怎樣確保數(shù)據(jù)庫(kù)突然斷電不丟數(shù)據(jù)？

通過(guò)上面的講解，只要 innodb_flush_log_at_trx_commit 和 sync_binlog 都為 1（通常稱為：雙一），就能確保 MySQL 機(jī)器斷電重啟后，數(shù)據(jù)不丟失。

因此建議在比較重要的庫(kù)，比如涉及到錢的庫(kù)，設(shè)置為雙一，而你的測(cè)試環(huán)境或者正式業(yè)務(wù)不那么重要的庫(kù)（比如日志庫(kù)）可以將 innodb_flush_log_at_trx_commit 設(shè)置為0，sync_binlog 設(shè)置成大于100 的數(shù)值，提高更新效率。

5 總結(jié)

本節(jié)講解了什么是事務(wù)？

所謂事務(wù)：是指一組原子性的 SQL 查詢，事務(wù)里的 SQL 要么全部執(zhí)行成功，要么全部執(zhí)行失敗。

一個(gè)良好的事務(wù)處理系統(tǒng)，必須具備 ACID 特性： atomicity（原子性）、consistency（一致性）、 isolation（隔離性）、 durability（持久性）。

另外講解了 Redo log 和 Binlog：

Redo log：稱為重做日志，用于記錄事務(wù)操作變化，記錄的是數(shù)據(jù)被修改之后的值。

Binlog：記錄了所有變更操作，其作用有：恢復(fù)、復(fù)制、審計(jì)等。

如果想要數(shù)據(jù)庫(kù)達(dá)到最安全的狀態(tài)，可以將 innodb_flush_log_at_trx_commit 和 sync_binlog 都設(shè)置為 1。

6 問(wèn)題

你工作中有遇到過(guò)丟數(shù)據(jù)的場(chǎng)景嗎？是什么原因?qū)е碌哪?？下一章我們統(tǒng)一作答。希望大家可以踴躍評(píng)論。謝謝大家的支持。

本文百家號(hào)首發(fā) 王者小哆啦原創(chuàng) 歡迎大家關(guān)注，收藏。