面試官：如果 MySQL 資料庫中的資料丢失，有哪些補救的辦法呢？

以前去企業面試的時候，經常被問一些關于 MySQL 資料庫相關的問題，其中最典型的就是關于 MySQL 資料庫資料安全的問題。

例如：如何才能保證 MySQL 資料庫的資料安全？MySQL 資料庫如果發生資料丢失可能會發生在什麼地方？如果 MySQL 資料庫出現了資料丢失該如何挽救？

問這些問題的主要目的就是考驗求職者的生産經驗；但是就我面試的過程而言，能夠完整答出來的求職者微乎其微。

出現這種情況主要是因為大多數求職者對于 MySQL 資料庫底層資料存儲的運作機制了解得不夠清楚，那麼今天我們就來針對于上述的幾個問題跟大家詳細讨論一下 MySQL 資料庫的底層存儲運作機制。

MySQL 資料庫在什麼情況下可能會發生資料丢失

在介紹 MySQL 資料庫在什麼情況下可能會丢失資料之前，我們首先回顧一下寫入一條資料到 MySQL 資料庫中所經曆的子產品，具體如下。

• 第一個子產品：将修改的資料邏輯儲存在 change buffer 之中。
• 第二個子產品：将修改的資料儲存在 binlog cache 之中。
• 第三個子產品：将修改的資料儲存在 redo log 之中。

在這三個子產品中，change buffer 是用來儲存修改資料的邏輯的，在修改之後，通過 merge 的方式寫入磁盤。

在這個子產品中，有沒有可能發生資料丢失呢？其實微乎其微，這主要是因為 MySQL 資料庫為了防止資料丢失而增加了 redo log 這個子產品，其主要的作用就是防止資料丢失。那麼，我們就一起來聊一聊 redo log 是怎麼儲存資料的。

redo log 主要分為兩個部分，分别是 redo log 和 redo log buffer 兩個部分。redo log 和 redo log buffer 的功能我們之前多次說過，這裡不再贅述。下面我們主要介紹 redo log 是怎麼儲存資料的。

當一條資料寫入資料庫之前，為了防止資料丢失，首先會将該條資料儲存在 redo log buffer 之中，然後再儲存在 redo log 之中，以便當資料庫當機之時作資料恢複使用。

那麼這個時候我們就要問，在這個過程中 redo log 有沒有可能會發生資料丢失呢？

這是有可能的。假如一個事務在執行一半的時候突然 MySQL 資料庫當機，此時 redo log buffer 中的所有資料将會全部丢失，不過一般這種情況隻會發生在事務未送出的情況下，所有資料丢失也影響不大。

此時，我相信你會問，如果恰好在事務送出之時，MySQL 資料庫發生當機會不會丢失資料呢？

顯然，這也是有可能的。下面我們根據 MySQL 資料庫儲存 redo log buffer 中的資料來分析丢失資料的可能。

在 MySQL 資料庫中，redo log buffer 有三種儲存資料的狀态，分别是：

• redo log buffer 将資料儲存在 MySQL 資料庫的記憶體中，也就是 InnoDB 存儲引擎的 buffer pool 之中。這其實跟上述的情況一緻，儲存的是未送出的資料，此時如果 MySQL 發生當機，丢失的是未送出的事務資訊，對于 MySQL 資料庫整體而言，沒有大的影響。
• redo log buffer 将資料儲存在 page cache 之中，也就是磁盤緩存之中。此時 MySQL 中的事務已經送出，假設恰巧運作 MySQL 資料庫的伺服器在此時發生當機，那麼很顯然已經送出的事務資料就會發生丢失。在這種情況下發生的資料丢失是無法恢複的。
• redo log buffer 将資料儲存在磁盤之中，這種情況下一般隻有磁盤不發生異常，是不會發生資料丢失的。

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

MySQL 針對于redo log buffer儲存資料的三種狀态又提供了名為innodb_flush_log_at_trx_commit的參數，這個參數有三個值，最主要的功能是告訴 MySQL 該将 redo log buffer 中的資料儲存在哪裡，具體如下：

• 當該參數的值設定為 0 時，redo log buffer 将會把所有的資料儲存在 buffer pool 之中；也就是全部儲存在記憶體之中，此時性能是最好的，但是一旦資料庫發生了重新開機，redo log buffer 中的資料也就随即全部丢失。
• 當該參數的值設定為 1 時，redo log buffer 将會把所有的資料直接儲存在磁盤之中，此時資料是最安全的，但是性能卻是最差的。
• 當該參數的值設定為 2 時，redo log buffer 将會把所有的資料儲存在 page cache 之中；也就是說會将資料全部儲存在磁盤緩存之中，此時性能跟設定為 0 時的性能相差無幾，但是如果此時部署 MySQL 資料庫的伺服器發生了當機，資料也會随即丢失。

注意：在實際的應用中，也并非隻有事務發生了送出，才會将資料儲存到磁盤之中。還有如下兩種情況。

第一種情況：如果有多個事務并行之時，會将已經儲存在 redo log buffer 中的資料全部持久化。 第二種情況：如果 redo log buffer 占用 InnoDB 存儲引擎的 buffer pool 記憶體空間的一半，MySQL 也會将資料持久化。

上面我們介紹了 redo log 可能會發生資料丢失的場景，下面我們再來了解一下 binlog 中可能發生資料丢失的情況。

相對于 redo log 來說，binlog 寫資料相對簡單。

首先需要說明的是，binlog 每一次寫入都是将整個事務同時寫入 binlog 檔案中，這主要是因為 MySQL 資料庫中的事務具有原子性，是以在一個事務未執行完成之前，MySQL 資料庫是将其寫入 binlog cache 之中。

其中 binlog cache 是 MySQL 中的一塊記憶體空間，那麼此時就帶來了一個新的問題，就是如果 binlog cache 的空間不足以承載某一個事務所包含的所有資料時，MySQL 會将該事務中所有的資料全部暫存到磁盤中（此時就會不得已而産生磁盤IO，随即就會導緻一定的性能問題）。

為了解決這個問題，MySQL 資料庫為我們提供了一個 binlog_cache_size 參數，這個參數主要是用來設定 binlog cache 的空間大小的。如果當 binlog cache 中的資料大小超過了 binlog_cache_size 設定的大小時，MySQL 會将該事務中所有的資料全部暫存到磁盤中。

與 redo log 相同的是，binlog cache 也有儲存資料的三種狀态，并且 MySQL 提供了 sync_binlog 這個參數來控制這種狀态。這三種狀态分别是：

• 當該值等于 0 時，每次事務送出之後，儲存在 page cache；
• 當該值等于 1 時，每次事務送出之後，儲存到磁盤之中；
• 當該值等于 N（N > 1） 時，每次事務送出之後，都會儲存在 page cache 之中，并且累計 N 次之後寫入磁盤。

于是，我們不難看出，當 N 越大時，相關的性能就會越好；相反，如果在資料送出期間發生了資料庫當機，随即帶來的後果就是會丢失儲存在 binlog cache 中的資料。

總結

今天，我們主要介紹了在 MySQL 資料庫運作過程中可能會發生資料丢失的幾種情況。

首先是 redo log，在 redo log 中最可能丢失資料的情況就是當 redo log buffer 中的資料儲存在 MySQL 記憶體之中，也就是當 innodb_flush_log_at_trx_commit 設定成 0 時；是以，為了安全和性能兩個方面考慮，建議将其設定成 2， 一般 MySQL 重新開機，而部署 MySQL 的伺服器不會重新開機。

其次是 binlog，與 redo log相同的是 binlog 也有三種儲存資料的狀态，同樣為了安全和性能兩個方面考慮，我建議你将 sync_binlog 設定成 N。

在日常的生産環境之中，一般會将 sync_binlog 設定為：100 ~ 1000 之間，具體要看伺服器性能，如果伺服器的記憶體有空餘，可以将其按需調大。

作者：Mche

連結：https://juejin.cn/post/7188423924894400569

來源：稀土掘金