前言

為了數(shù)據(jù)安全，數(shù)據(jù)庫需要定期備份，這個(gè)大家都懂，然而數(shù)據(jù)庫備份的時(shí)候，最怕寫操作，因?yàn)檫@個(gè)最容易導(dǎo)致數(shù)據(jù)的不一致，松哥舉一個(gè)簡單的例子大家來看下：

假設(shè)在數(shù)據(jù)庫備份期間，有用戶下單了，那么可能會(huì)出現(xiàn)如下問題：

• 庫存表扣庫存。
• 備份庫存表。
• 備份訂單表數(shù)據(jù)。
• 訂單表添加訂單。
• 用戶表扣除賬戶余額。
• 備份用戶表。

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

為了解決這個(gè)問題，MySQL 中提供了很多方案，我們來逐一進(jìn)行講解并分析其優(yōu)劣。

1. 全庫只讀

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

show variables like 'read_only';

可以看到，默認(rèn)情況下，read_only 是 OFF，即關(guān)閉狀態(tài)，我們先把它改為 ON，執(zhí)行如下 SQL：

set global read_only=1;

1 表示 ON，0 表示 OFF，執(zhí)行結(jié)果如下：

這個(gè) read_only 對 super 用戶無效，所以設(shè)置完成后，接下來我們退出來這個(gè)會(huì)話，然后創(chuàng)建一個(gè)不包含 super 權(quán)限的用戶，用新用戶登錄，登錄成功之后，執(zhí)行一個(gè)插入 SQL，結(jié)果如下：

可以看到，這個(gè)錯(cuò)誤信息中說，現(xiàn)在的 MySQL 是只讀的（只能查詢），不能執(zhí)行當(dāng)前 SQL。

加了只讀屬性，就不用擔(dān)心備份的時(shí)候發(fā)生數(shù)據(jù)不一致的問題了。

但是 read_only 我們通常用來標(biāo)識(shí)一個(gè) MySQL 實(shí)例是主庫還是從庫：

• read_only=0，表示該實(shí)例為主庫。數(shù)據(jù)庫管理員 DBA 可能每隔一段時(shí)間就會(huì)對該實(shí)例寫入一些業(yè)務(wù)無關(guān)的數(shù)據(jù)來判斷主庫是否可寫，是否可用，這就是常見的探測主庫實(shí)例是否活著的。
• read_only=1，表示該實(shí)例為從庫。每隔一段時(shí)間探活，往往只會(huì)對從庫進(jìn)行讀操作，比如select 1;這樣進(jìn)行探活從庫。

所以，read_only 這個(gè)屬性其實(shí)并不適合用來做備份，而且如果使用了 read_only 屬性將整個(gè)庫設(shè)置為 readonly 之后，如果客戶端發(fā)生異常，則數(shù)據(jù)庫就會(huì)一直保持 readonly 狀態(tài)，這樣會(huì)導(dǎo)致整個(gè)庫長時(shí)間處于不可寫狀態(tài)，風(fēng)險(xiǎn)很高。

因此這種方案不合格。

2. 全局鎖

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

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

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

和第一小節(jié)的方案相比，F(xiàn)TWRL 有一點(diǎn)進(jìn)步，即：執(zhí)行 FTWRL 命令之后如果客戶端發(fā)生異常斷開，那么 MySQL 會(huì)自動(dòng)釋放這個(gè)全局鎖，整個(gè)庫回到可以正常更新的狀態(tài)，而不會(huì)一直處于只讀狀態(tài)。

但是?。?！

加了全局鎖，就意味著整個(gè)數(shù)據(jù)庫在備份期間都是只讀狀態(tài)，那么在數(shù)據(jù)庫備份期間，業(yè)務(wù)就只能停擺了。

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

3. 事務(wù)

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

在這個(gè)隔離級(jí)別下，如果用戶在另外一個(gè)事務(wù)中執(zhí)行同條 SELECT 語句數(shù)次，結(jié)果總是相同的。（因?yàn)檎趫?zhí)行的事務(wù)所產(chǎn)生的數(shù)據(jù)變化不能被外部看到）。

換言之，在 InnoDB 這種支持事務(wù)的存儲(chǔ)引擎中，那么我們就可以在備份數(shù)據(jù)庫之前先開啟事務(wù)，此時(shí)會(huì)先創(chuàng)建一致性視圖，然后整個(gè)事務(wù)執(zhí)行期間都在用這個(gè)一致性視圖，而且由于 MVCC 的支持，備份期間業(yè)務(wù)依然可以對數(shù)據(jù)進(jìn)行更新操作，并且這些更新操作不會(huì)被當(dāng)前事務(wù)看到。

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

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

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

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

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

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

開啟之后，默認(rèn)日志的目錄是 mysql 的 data 目錄，文件名默認(rèn)為 主機(jī)名.log。

接下來，我們先來執(zhí)行一個(gè)不帶 -–single-transaction 參數(shù)的備份，如下：

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

大家注意默認(rèn)的 general_log 的位置。

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

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

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

4. 小結(jié)

總結(jié)一下，加事務(wù)備份似乎是一個(gè)不錯(cuò)的選擇，不過這個(gè)方案也有一個(gè)局限性，那就是只適用于支持事務(wù)的引擎如 InnoDB，對于 MyISAM 這樣的存儲(chǔ)引擎，如果要備份，還是乖乖的使用全局鎖吧。

到此這篇關(guān)于MySQL如何保證備份數(shù)據(jù)一致性的文章就介紹到這了,更多相關(guān)MySQL備份數(shù)據(jù)一致性內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論