MySQL怎么保證備份數據的一致性

這篇文章主要講解了“MySQL怎么保證備份數據的一致性”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL怎么保證備份數據的一致性”吧！

前言

為了數據安全，數據庫需要定期備份，這個大家都懂，然而數據庫備份的時候，最怕寫操作，因為這個最容易導致數據的不一致，松哥舉一個簡單的例子大家來看下：

假設在數據庫備份期間，有用戶下單了，那么可能會出現如下問題：

• 庫存表扣庫存。

• 備份庫存表。

• 備份訂單表數據。

• 訂單表添加訂單。

• 用戶表扣除賬戶余額。

• 備份用戶表。

如果按照上面這樣的邏輯執行，備份文件中的訂單表就少了一條記錄。將來如果使用這個備份文件恢復數據的話，就少了一條記錄，造成數據不一致。

為了解決這個問題，MySQL 中提供了很多方案，我們來逐一進行講解并分析其優劣。

1. 全庫只讀

要解決這個問題，我們最容易想到的辦法就是在數據庫備份期間設置數據庫只讀，不能寫，這樣就不用擔心數據不一致了，設置全庫只讀的辦法也很簡單，首先我們執行如下 SQL 先看看對應變量的值：

show variables like 'read_only';

可以看到，默認情況下，read_only 是 OFF，即關閉狀態，我們先把它改為 ON，執行如下 SQL：

set global read_only=1;

1 表示 ON，0 表示 OFF，執行結果如下：

這個 read_only 對 super 用戶無效，所以設置完成后，接下來我們退出來這個會話，然后創建一個不包含 super 權限的用戶，用新用戶登錄，登錄成功之后，執行一個插入 SQL，結果如下：

可以看到，這個錯誤信息中說，現在的 MySQL 是只讀的（只能查詢），不能執行當前 SQL。

加了只讀屬性，就不用擔心備份的時候發生數據不一致的問題了。

但是 read_only 我們通常用來標識一個 MySQL 實例是主庫還是從庫：

• read_only=0，表示該實例為主庫。數據庫管理員 DBA 可能每隔一段時間就會對該實例寫入一些業務無關的數據來判斷主庫是否可寫，是否可用，這就是常見的探測主庫實例是否活著的。

• read_only=1，表示該實例為從庫。每隔一段時間探活，往往只會對從庫進行讀操作，比如select 1;這樣進行探活從庫。

所以，read_only 這個屬性其實并不適合用來做備份，而且如果使用了 read_only 屬性將整個庫設置為 readonly 之后，如果客戶端發生異常，則數據庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處于不可寫狀態，風險很高。

因此這種方案不合格。

2. 全局鎖

全局鎖，顧名思義，就是把整個庫鎖起來，鎖起來的庫就不能增刪改了，只能讀了。

那么我們看看怎么使用全局鎖。MySQL 提供了一個加全局讀鎖的方法，命令是 flush tables with read lock (FTWRL)。當你需要讓整個庫處于只讀狀態的時候，可以使用這個命令，之后其他線程的增刪改等操作就會被阻塞。

從圖中可以看到，使用 flush tables with read lock; 指令可以鎖定表；使用 unlock tables; 指令則可以完成解鎖操作（會話斷開時也會自動解鎖）。

和第一小節的方案相比，FTWRL 有一點進步，即：執行 FTWRL 命令之后如果客戶端發生異常斷開，那么 MySQL 會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態，而不會一直處于只讀狀態。

但是！！！

加了全局鎖，就意味著整個數據庫在備份期間都是只讀狀態，那么在數據庫備份期間，業務就只能停擺了。

所以這種方式也不是最佳方案。

3. 事務

不知道小伙伴們是否還記得松哥之前和大家分享的數據庫的隔離級別，四種隔離級別中有一個是可重復讀（REPEATABLE READ），這也是 MySQL 默認的隔離級別。

在這個隔離級別下，如果用戶在另外一個事務中執行同條 SELECT 語句數次，結果總是相同的。（因為正在執行的事務所產生的數據變化不能被外部看到）。

換言之，在 InnoDB 這種支持事務的存儲引擎中，那么我們就可以在備份數據庫之前先開啟事務，此時會先創建一致性視圖，然后整個事務執行期間都在用這個一致性視圖，而且由于 MVCC 的支持，備份期間業務依然可以對數據進行更新操作，并且這些更新操作不會被當前事務看到。

在可重復讀的隔離級別下，即使其他事務更新了表數據，也不會影響備份數據庫的事務讀取結果，這就是事務四大特性中的隔離性，這樣備份期間備份的數據一直是在開啟事務時的數據。

具體操作也很簡單，使用 mysqldump 備份數據庫的時候，加上 -–single-transaction 參數即可。

為了看到 -–single-transaction 參數的作用，我們可以先開啟 general_log，general_log 即 General Query Log，它記錄了 MySQL 服務器的操作。當客戶端連接、斷開連接、接收到客戶端的 SQL 語句時，會向 general_log 中寫入日志，開啟 general_log 會損失一定的性能，但是在開發、測試環境下開啟日志，可以幫忙我們加快排查出現的問題。

通過如下查詢我們可以看到，默認情況下 general_log 并沒有開啟：

我們可以通過修改配置文件 my.cnf(Linux)/my.ini（Windows），在 mysqld 下面增加或修改（如已存在配置項）general_log 的值為1，修改后重啟 MySQL 服務即可生效。

也可以通過在 MySQL 終端執行 set global general_log = ON 來開啟 general log，此方法可以不用重啟 MySQL。

開啟之后，默認日志的目錄是 mysql 的 data 目錄，文件名默認為 主機名.log。

接下來，我們先來執行一個不帶 -–single-transaction 參數的備份，如下：

mysqldump -h localhost -uroot -p123 test08 > test08.sql

大家注意默認的 general_log 的位置。

接下來我們再來加上 -–single-transaction 參數看看：

mysqldump -h localhost -uroot -p123 --single-transaction test08 > test08.sql

大家看我藍色選中的部分，可以看到，確實先開啟了事務，然后才開始備份的，對比不加 -–single-transaction 參數的日志，多了開啟事務這一部分。

感謝各位的閱讀，以上就是“MySQL怎么保證備份數據的一致性”的內容了，經過本文的學習后，相信大家對MySQL怎么保證備份數據的一致性這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！