MySQL如何保證備份數(shù)據(jù)的一致性詳解
前言
為了數(shù)據(jù)安全,數(shù)據(jù)庫(kù)需要定期備份,這個(gè)大家都懂,然而數(shù)據(jù)庫(kù)備份的時(shí)候,最怕寫(xiě)操作,因?yàn)檫@個(gè)最容易導(dǎo)致數(shù)據(jù)的不一致,松哥舉一個(gè)簡(jiǎn)單的例子大家來(lái)看下:
假設(shè)在數(shù)據(jù)庫(kù)備份期間,有用戶下單了,那么可能會(huì)出現(xiàn)如下問(wèn)題:
- 庫(kù)存表扣庫(kù)存。
- 備份庫(kù)存表。
- 備份訂單表數(shù)據(jù)。
- 訂單表添加訂單。
- 用戶表扣除賬戶余額。
- 備份用戶表。
如果按照上面這樣的邏輯執(zhí)行,備份文件中的訂單表就少了一條記錄。將來(lái)如果使用這個(gè)備份文件恢復(fù)數(shù)據(jù)的話,就少了一條記錄,造成數(shù)據(jù)不一致。
為了解決這個(gè)問(wèn)題,MySQL 中提供了很多方案,我們來(lái)逐一進(jìn)行講解并分析其優(yōu)劣。
1. 全庫(kù)只讀
要解決這個(gè)問(wèn)題,我們最容易想到的辦法就是在數(shù)據(jù)庫(kù)備份期間設(shè)置數(shù)據(jù)庫(kù)只讀,不能寫(xiě),這樣就不用擔(dān)心數(shù)據(jù)不一致了,設(shè)置全庫(kù)只讀的辦法也很簡(jiǎn)單,首先我們執(zhí)行如下 SQL 先看看對(duì)應(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
對(duì) super 用戶無(wú)效,所以設(shè)置完成后,接下來(lái)我們退出來(lái)這個(gè)會(huì)話,然后創(chuàng)建一個(gè)不包含 super 權(quán)限的用戶,用新用戶登錄,登錄成功之后,執(zhí)行一個(gè)插入 SQL,結(jié)果如下:
可以看到,這個(gè)錯(cuò)誤信息中說(shuō),現(xiàn)在的 MySQL 是只讀的(只能查詢(xún)),不能執(zhí)行當(dāng)前 SQL。
加了只讀屬性,就不用擔(dān)心備份的時(shí)候發(fā)生數(shù)據(jù)不一致的問(wèn)題了。
但是 read_only
我們通常用來(lái)標(biāo)識(shí)一個(gè) MySQL 實(shí)例是主庫(kù)還是從庫(kù):
- read_only=0,表示該實(shí)例為主庫(kù)。數(shù)據(jù)庫(kù)管理員 DBA 可能每隔一段時(shí)間就會(huì)對(duì)該實(shí)例寫(xiě)入一些業(yè)務(wù)無(wú)關(guān)的數(shù)據(jù)來(lái)判斷主庫(kù)是否可寫(xiě),是否可用,這就是常見(jiàn)的探測(cè)主庫(kù)實(shí)例是否活著的。
- read_only=1,表示該實(shí)例為從庫(kù)。每隔一段時(shí)間探活,往往只會(huì)對(duì)從庫(kù)進(jìn)行讀操作,比如select 1;這樣進(jìn)行探活從庫(kù)。
所以,read_only
這個(gè)屬性其實(shí)并不適合用來(lái)做備份,而且如果使用了 read_only
屬性將整個(gè)庫(kù)設(shè)置為 readonly 之后,如果客戶端發(fā)生異常,則數(shù)據(jù)庫(kù)就會(huì)一直保持 readonly 狀態(tài),這樣會(huì)導(dǎo)致整個(gè)庫(kù)長(zhǎng)時(shí)間處于不可寫(xiě)狀態(tài),風(fēng)險(xiǎn)很高。
因此這種方案不合格。
2. 全局鎖
全局鎖,顧名思義,就是把整個(gè)庫(kù)鎖起來(lái),鎖起來(lái)的庫(kù)就不能增刪改了,只能讀了。
那么我們看看怎么使用全局鎖。MySQL 提供了一個(gè)加全局讀鎖的方法,命令是 flush tables with read lock
(FTWRL)。當(dāng)你需要讓整個(gè)庫(kù)處于只讀狀態(tài)的時(shí)候,可以使用這個(gè)命令,之后其他線程的增刪改等操作就會(huì)被阻塞。
從圖中可以看到,使用 flush tables with read lock;
指令可以鎖定表;使用 unlock tables;
指令則可以完成解鎖操作(會(huì)話斷開(kāi)時(shí)也會(huì)自動(dòng)解鎖)。
和第一小節(jié)的方案相比,F(xiàn)TWRL 有一點(diǎn)進(jìn)步,即:執(zhí)行 FTWRL 命令之后如果客戶端發(fā)生異常斷開(kāi),那么 MySQL 會(huì)自動(dòng)釋放這個(gè)全局鎖,整個(gè)庫(kù)回到可以正常更新的狀態(tài),而不會(huì)一直處于只讀狀態(tài)。
但是?。。?/p>
加了全局鎖,就意味著整個(gè)數(shù)據(jù)庫(kù)在備份期間都是只讀狀態(tài),那么在數(shù)據(jù)庫(kù)備份期間,業(yè)務(wù)就只能停擺了。
所以這種方式也不是最佳方案。
3. 事務(wù)
不知道小伙伴們是否還記得松哥之前和大家分享的數(shù)據(jù)庫(kù)的隔離級(jí)別,四種隔離級(jí)別中有一個(gè)是可重復(fù)讀(REPEATABLE READ)
,這也是 MySQL 默認(rèn)的隔離級(jí)別。
在這個(gè)隔離級(jí)別下,如果用戶在另外一個(gè)事務(wù)中執(zhí)行同條 SELECT 語(yǔ)句數(shù)次,結(jié)果總是相同的。(因?yàn)檎趫?zhí)行的事務(wù)所產(chǎn)生的數(shù)據(jù)變化不能被外部看到)。
換言之,在 InnoDB 這種支持事務(wù)的存儲(chǔ)引擎中,那么我們就可以在備份數(shù)據(jù)庫(kù)之前先開(kāi)啟事務(wù),此時(shí)會(huì)先創(chuàng)建一致性視圖,然后整個(gè)事務(wù)執(zhí)行期間都在用這個(gè)一致性視圖,而且由于 MVCC 的支持,備份期間業(yè)務(wù)依然可以對(duì)數(shù)據(jù)進(jìn)行更新操作,并且這些更新操作不會(huì)被當(dāng)前事務(wù)看到。
在可重復(fù)讀的隔離級(jí)別下,即使其他事務(wù)更新了表數(shù)據(jù),也不會(huì)影響備份數(shù)據(jù)庫(kù)的事務(wù)讀取結(jié)果,這就是事務(wù)四大特性中的隔離性,這樣備份期間備份的數(shù)據(jù)一直是在開(kāi)啟事務(wù)時(shí)的數(shù)據(jù)。
具體操作也很簡(jiǎn)單,使用 mysqldump 備份數(shù)據(jù)庫(kù)的時(shí)候,加上 -–single-transaction
參數(shù)即可。
為了看到 -–single-transaction
參數(shù)的作用,我們可以先開(kāi)啟 general_log
,general_log
即 General Query Log,它記錄了 MySQL 服務(wù)器的操作。當(dāng)客戶端連接、斷開(kāi)連接、接收到客戶端的 SQL 語(yǔ)句時(shí),會(huì)向 general_log
中寫(xiě)入日志,開(kāi)啟 general_log
會(huì)損失一定的性能,但是在開(kāi)發(fā)、測(cè)試環(huán)境下開(kāi)啟日志,可以幫忙我們加快排查出現(xiàn)的問(wèn)題。
通過(guò)如下查詢(xún)我們可以看到,默認(rèn)情況下 general_log
并沒(méi)有開(kāi)啟:
我們可以通過(guò)修改配置文件 my.cnf(Linux)/my.ini(Windows)
,在 mysqld
下面增加或修改(如已存在配置項(xiàng))general_log
的值為1,修改后重啟 MySQL 服務(wù)即可生效。
也可以通過(guò)在 MySQL 終端執(zhí)行 set global general_log = ON
來(lái)開(kāi)啟 general log
,此方法可以不用重啟 MySQL
。
開(kāi)啟之后,默認(rèn)日志的目錄是 mysql 的 data 目錄,文件名默認(rèn)為 主機(jī)名.log
。
接下來(lái),我們先來(lái)執(zhí)行一個(gè)不帶 -–single-transaction
參數(shù)的備份,如下:
mysqldump -h localhost -uroot -p123 test08 > test08.sql
大家注意默認(rèn)的 general_log
的位置。
接下來(lái)我們?cè)賮?lái)加上 -–single-transaction
參數(shù)看看:
mysqldump -h localhost -uroot -p123 --single-transaction test08 > test08.sql
大家看我藍(lán)色選中的部分,可以看到,確實(shí)先開(kāi)啟了事務(wù),然后才開(kāi)始備份的,對(duì)比不加 -–single-transaction
參數(shù)的日志,多了開(kāi)啟事務(wù)這一部分。
4. 小結(jié)
總結(jié)一下,加事務(wù)備份似乎是一個(gè)不錯(cuò)的選擇,不過(guò)這個(gè)方案也有一個(gè)局限性,那就是只適用于支持事務(wù)的引擎如 InnoDB,對(duì)于 MyISAM 這樣的存儲(chǔ)引擎,如果要備份,還是乖乖的使用全局鎖吧。
到此這篇關(guān)于MySQL如何保證備份數(shù)據(jù)一致性的文章就介紹到這了,更多相關(guān)MySQL備份數(shù)據(jù)一致性?xún)?nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
如何利用Mysql計(jì)算地址經(jīng)緯度距離實(shí)時(shí)位置
最近工作中遇到了一個(gè)附近門(mén)店的功能,下面這篇文章主要給大家介紹了關(guān)于如何利用Mysql計(jì)算地址經(jīng)緯度距離實(shí)時(shí)位置的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04linux環(huán)境下安裝mysql數(shù)據(jù)庫(kù)的詳細(xì)教程
這篇文章主要介紹了linux環(huán)境下安裝mysql數(shù)據(jù)庫(kù)的詳細(xì)教程,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-06-06MYSQL讀寫(xiě)性能測(cè)試的簡(jiǎn)單記錄
本文主要介紹了MYSQL讀寫(xiě)性能測(cè)試的簡(jiǎn)單記錄,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-08-08詳解Mysql取前一天、前一周、后一天等時(shí)間函數(shù)
本文給大家介紹Mysql取前一天、前一周、后一天等時(shí)間函數(shù),本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧2023-11-11SQL數(shù)據(jù)分表Mybatis?Plus動(dòng)態(tài)表名優(yōu)方案
這篇文章主要介紹了SQL數(shù)據(jù)分表Mybatis?Plus動(dòng)態(tài)表名優(yōu)方案,文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹,具有一定的參考價(jià)值,需要的小伙伴可以參考一下2022-08-08MySQL實(shí)現(xiàn)批量插入測(cè)試數(shù)據(jù)的方式總結(jié)
在開(kāi)發(fā)過(guò)程中經(jīng)常需要一些測(cè)試數(shù)據(jù),?這個(gè)時(shí)候如果手敲的話,?十行二十行還好,?多了就很死亡了,?接下來(lái)介紹兩種常用的MySQL測(cè)試數(shù)據(jù)批量生成方式,希望對(duì)大家有所幫助2023-05-05