面試官：如果 MySQL 數(shù)據(jù)庫(kù)中的數(shù)據(jù)丟失，有哪些補(bǔ)救的辦法呢？

　　以前去企業(yè)面試的時(shí)候，經(jīng)常被問(wèn)一些關(guān)于 MySQL 數(shù)據(jù)庫(kù)相關(guān)的問(wèn)題，其中最典型的就是關(guān)于 MySQL 數(shù)據(jù)庫(kù)數(shù)據(jù)安全的問(wèn)題。

　　例如：如何才能保證 MySQL 數(shù)據(jù)庫(kù)的數(shù)據(jù)安全？MySQL 數(shù)據(jù)庫(kù)如果發(fā)生數(shù)據(jù)丟失可能會(huì)發(fā)生在什么地方？如果 MySQL 數(shù)據(jù)庫(kù)出現(xiàn)了數(shù)據(jù)丟失該如何挽救？

　　問(wèn)這些問(wèn)題的主要目的就是考驗(yàn)求職者的生產(chǎn)經(jīng)驗(yàn)；但是就我面試的過(guò)程而言，能夠完整答出來(lái)的求職者微乎其微。

　　出現(xiàn)這種情況主要是因?yàn)榇蠖鄶?shù)求職者對(duì)于 MySQL 數(shù)據(jù)庫(kù)底層數(shù)據(jù)存儲(chǔ)的運(yùn)行機(jī)制了解得不夠清楚，那么今天我們就來(lái)針對(duì)于上述的幾個(gè)問(wèn)題跟大家詳細(xì)討論一下 MySQL 數(shù)據(jù)庫(kù)的底層存儲(chǔ)運(yùn)行機(jī)制。

　　在介紹 MySQL 數(shù)據(jù)庫(kù)在什么情況下可能會(huì)丟失數(shù)據(jù)之前，我們首先回顧一下寫入一條數(shù)據(jù)到 MySQL 數(shù)據(jù)庫(kù)中所經(jīng)歷的模塊，具體如下。第一個(gè)模塊：將修改的數(shù)據(jù)邏輯保存在 change buffer 之中。第二個(gè)模塊：將修改的數(shù)據(jù)保存在 binlog cache 之中。第三個(gè)模塊：將修改的數(shù)據(jù)保存在 redo log 之中。

　　在這三個(gè)模塊中，change buffer 是用來(lái)保存修改數(shù)據(jù)的邏輯的，在修改之后，通過(guò) merge 的方式寫入磁盤。

　　在這個(gè)模塊中，有沒(méi)有可能發(fā)生數(shù)據(jù)丟失呢？其實(shí)微乎其微，這主要是因?yàn)?MySQL 數(shù)據(jù)庫(kù)為了防止數(shù)據(jù)丟失而增加了 redo log 這個(gè)模塊，其主要的作用就是防止數(shù)據(jù)丟失。那么，我們就一起來(lái)聊一聊 redo log 是怎么保存數(shù)據(jù)的。

　　redo log 主要分為兩個(gè)部分，分別是 redo log 和 redo log buffer 兩個(gè)部分。redo log 和 redo log buffer 的功能我們之前多次說(shuō)過(guò)，這里不再贅述。下面我們主要介紹 redo log 是怎么保存數(shù)據(jù)的。

　　當(dāng)一條數(shù)據(jù)寫入數(shù)據(jù)庫(kù)之前，為了防止數(shù)據(jù)丟失，首先會(huì)將該條數(shù)據(jù)保存在 redo log buffer 之中，然后再保存在 redo log 之中，以便當(dāng)數(shù)據(jù)庫(kù)宕機(jī)之時(shí)作數(shù)據(jù)恢復(fù)使用。

　　那么這個(gè)時(shí)候我們就要問(wèn)，在這個(gè)過(guò)程中 redo log 有沒(méi)有可能會(huì)發(fā)生數(shù)據(jù)丟失呢？

　　這是有可能的。假如一個(gè)事務(wù)在執(zhí)行一半的時(shí)候突然 MySQL 數(shù)據(jù)庫(kù)宕機(jī)，此時(shí) redo log buffer 中的所有數(shù)據(jù)將會(huì)全部丟失，不過(guò)一般這種情況只會(huì)發(fā)生在事務(wù)未提交的情況下，所有數(shù)據(jù)丟失也影響不大。

　　此時(shí)，我相信你會(huì)問(wèn)，如果恰好在事務(wù)提交之時(shí)，MySQL 數(shù)據(jù)庫(kù)發(fā)生宕機(jī)會(huì)不會(huì)丟失數(shù)據(jù)呢？

上海數(shù)據(jù)恢復(fù)

　　顯然，這也是有可能的。下面我們根據(jù) MySQL 數(shù)據(jù)庫(kù)保存 redo log buffer 中的數(shù)據(jù)來(lái)分析丟失數(shù)據(jù)的可能。

　　在 MySQL 數(shù)據(jù)庫(kù)中，redo log buffer 有三種保存數(shù)據(jù)的狀態(tài)，分別是：redo log buffer 將數(shù)據(jù)保存在 MySQL 數(shù)據(jù)庫(kù)的內(nèi)存中，也就是 InnoDB 存儲(chǔ)引擎的 buffer pool 之中。這其實(shí)跟上述的情況一致，保存的是未提交的數(shù)據(jù)，此時(shí)如果 MySQL 發(fā)生宕機(jī)，丟失的是未提交的事務(wù)信息，對(duì)于 MySQL 數(shù)據(jù)庫(kù)整體而言，沒(méi)有大的影響。redo log buffer 將數(shù)據(jù)保存在 page cache 之中，也就是磁盤緩存之中。此時(shí) MySQL 中的事務(wù)已經(jīng)提交，假設(shè)恰巧運(yùn)行 MySQL 數(shù)據(jù)庫(kù)的服務(wù)器在此時(shí)發(fā)生宕機(jī)，那么很顯然已經(jīng)提交的事務(wù)數(shù)據(jù)就會(huì)發(fā)生丟失。在這種情況下發(fā)生的數(shù)據(jù)丟失是無(wú)法恢復(fù)的。redo log buffer 將數(shù)據(jù)保存在磁盤之中，這種情況下一般只有磁盤不發(fā)生異常，是不會(huì)發(fā)生數(shù)據(jù)丟失的。

揚(yáng)州數(shù)據(jù)恢復(fù)

　　注意：系統(tǒng)磁盤也是有緩存的，通常我們稱之為：page cacge。

　　MySQL 針對(duì)于redo log buffer保存數(shù)據(jù)的三種狀態(tài)又提供了名為innodb_flush_log_at_trx_commit的參數(shù)，這個(gè)參數(shù)有三個(gè)值，最主要的功能是告訴 MySQL 該將 redo log buffer 中的數(shù)據(jù)保存在哪里，具體如下：當(dāng)該參數(shù)的值設(shè)置為 0 時(shí)，redo log buffer 將會(huì)把所有的數(shù)據(jù)保存在 buffer pool 之中；也就是全部保存在內(nèi)存之中，此時(shí)性能是最好的，但是一旦數(shù)據(jù)庫(kù)發(fā)生了重啟，redo log buffer 中的數(shù)據(jù)也就隨即全部丟失。當(dāng)該參數(shù)的值設(shè)置為 1 時(shí)，redo log buffer 將會(huì)把所有的數(shù)據(jù)直接保存在磁盤之中，此時(shí)數(shù)據(jù)是最安全的，但是性能卻是最差的。當(dāng)該參數(shù)的值設(shè)置為 2 時(shí)，redo log buffer 將會(huì)把所有的數(shù)據(jù)保存在 page cache 之中；也就是說(shuō)會(huì)將數(shù)據(jù)全部保存在磁盤緩存之中，此時(shí)性能跟設(shè)置為 0 時(shí)的性能相差無(wú)幾，但是如果此時(shí)部署 MySQL 數(shù)據(jù)庫(kù)的服務(wù)器發(fā)生了宕機(jī)，數(shù)據(jù)也會(huì)隨即丟失。

　　注意：在實(shí)際的應(yīng)用中，也并非只有事務(wù)發(fā)生了提交，才會(huì)將數(shù)據(jù)保存到磁盤之中。還有如下兩種情況。

　　第一種情況：如果有多個(gè)事務(wù)并行之時(shí)，會(huì)將已經(jīng)保存在 redo log buffer 中的數(shù)據(jù)全部持久化。 第二種情況：如果 redo log buffer 占用 InnoDB 存儲(chǔ)引擎的 buffer pool 內(nèi)存空間的一半，MySQL 也會(huì)將數(shù)據(jù)持久化。

　　上面我們介紹了 redo log 可能會(huì)發(fā)生數(shù)據(jù)丟失的場(chǎng)景，下面我們?cè)賮?lái)了解一下 binlog 中可能發(fā)生數(shù)據(jù)丟失的情況。

　　相對(duì)于 redo log 來(lái)說(shuō)，binlog 寫數(shù)據(jù)相對(duì)簡(jiǎn)單。

　　首先需要說(shuō)明的是，binlog 每一次寫入都是將整個(gè)事務(wù)同時(shí)寫入 binlog 文件中，這主要是因?yàn)?MySQL 數(shù)據(jù)庫(kù)中的事務(wù)具有原子性，所以在一個(gè)事務(wù)未執(zhí)行完成之前，MySQL 數(shù)據(jù)庫(kù)是將其寫入 binlog cache 之中。

　　其中 binlog cache 是 MySQL 中的一塊內(nèi)存空間，那么此時(shí)就帶來(lái)了一個(gè)新的問(wèn)題，就是如果 binlog cache 的空間不足以承載某一個(gè)事務(wù)所包含的所有數(shù)據(jù)時(shí)，MySQL 會(huì)將該事務(wù)中所有的數(shù)據(jù)全部暫存到磁盤中（此時(shí)就會(huì)不得已而產(chǎn)生磁盤IO，隨即就會(huì)導(dǎo)致一定的性能問(wèn)題）。

　　為了解決這個(gè)問(wèn)題，MySQL 數(shù)據(jù)庫(kù)為我們提供了一個(gè) binlog_cache_size 參數(shù)，這個(gè)參數(shù)主要是用來(lái)設(shè)置 binlog cache 的空間大小的。如果當(dāng) binlog cache 中的數(shù)據(jù)大小超過(guò)了 binlog_cache_size 設(shè)置的大小時(shí)，MySQL 會(huì)將該事務(wù)中所有的數(shù)據(jù)全部暫存到磁盤中。蘇州數(shù)據(jù)恢復(fù)

　　與 redo log 相同的是，binlog cache 也有保存數(shù)據(jù)的三種狀態(tài)，并且 MySQL 提供了 sync_binlog 這個(gè)參數(shù)來(lái)控制這種狀態(tài)。這三種狀態(tài)分別是：當(dāng)該值等于 0 時(shí)，每次事務(wù)提交之后，保存在 page cache；當(dāng)該值等于 1 時(shí)，每次事務(wù)提交之后，保存到磁盤之中；當(dāng)該值等于 N（N > 1） 時(shí)，每次事務(wù)提交之后，都會(huì)保存在 page cache 之中，并且累計(jì) N 次之后寫入磁盤。

　　于是，我們不難看出，當(dāng) N 越大時(shí)，相關(guān)的性能就會(huì)越好；相反，如果在數(shù)據(jù)提交期間發(fā)生了數(shù)據(jù)庫(kù)宕機(jī)，隨即帶來(lái)的后果就是會(huì)丟失保存在 binlog cache 中的數(shù)據(jù)。

　　今天，我們主要介紹了在 MySQL 數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中可能會(huì)發(fā)生數(shù)據(jù)丟失的幾種情況。

　　首先是 redo log，在 redo log 中最可能丟失數(shù)據(jù)的情況就是當(dāng) redo log buffer 中的數(shù)據(jù)保存在 MySQL 內(nèi)存之中，也就是當(dāng)

　　innodb_flush_log_at_trx_commit 設(shè)置成 0 時(shí)；所以，為了安全和性能兩個(gè)方面考慮，建議將其設(shè)置成 2， 一般 MySQL 重啟，而部署 MySQL 的服務(wù)器不會(huì)重啟。

　　其次是 binlog，與 redo log相同的是 binlog 也有三種保存數(shù)據(jù)的狀態(tài)，同樣為了安全和性能兩個(gè)方面考慮，我建議你將 sync_binlog 設(shè)置成 N。

　　在日常的生產(chǎn)環(huán)境之中，一般會(huì)將 sync_binlog 設(shè)置為：100 ~ 1000 之間，具體要看服務(wù)器性能，如果服務(wù)器的內(nèi)存有空余，可以將其按需調(diào)大。

　　作者：Mche鏈接：

　　https://juejin.cn/post/7188423924894400569來(lái)源：稀土掘金