當(dāng)前位置:首頁 > 公眾號精選 > 小林coding
[導(dǎo)讀]事件起因我們的系統(tǒng)中有數(shù)據(jù)導(dǎo)入的功能,可以把特定的格式的excel數(shù)據(jù)導(dǎo)入到系統(tǒng)中來由于客戶電腦的文件比較多,很多文件的名字也比較相近,客戶在導(dǎo)入excel時選錯了文件這個錯誤的excel文件的格式恰好能被系統(tǒng)解析,客戶也沒及時發(fā)現(xiàn)導(dǎo)錯了文件,所以就將6萬多條沒用的數(shù)據(jù)導(dǎo)入到了系統(tǒng)中這6萬多條數(shù)據(jù)對系統(tǒng)來說就是無用的數(shù)據(jù),不會影響系統(tǒng)的運行,最多也就是占用一點數(shù)據(jù)庫空間而已客戶只需要把正確的excel重新導(dǎo)入,就可以繼續(xù)完成他的業(yè)務(wù)了但是,客戶是一個重度強迫癥患者,他覺得在管理平臺看到這6萬多條沒用的數(shù)據(jù)令他抓狂客戶想要把這些數(shù)據(jù)刪除,我們系統(tǒng)又沒有提供批量刪除功能,只能單個刪除,這無疑是一個...

事件起因

我們的系統(tǒng)中有數(shù)據(jù)導(dǎo)入的功能,可以把特定的格式的excel數(shù)據(jù)導(dǎo)入到系統(tǒng)中來

由于客戶電腦的文件比較多,很多文件的名字也比較相近,客戶在導(dǎo)入excel時選錯了文件

這個錯誤的excel文件的格式恰好能被系統(tǒng)解析,客戶也沒及時發(fā)現(xiàn)導(dǎo)錯了文件,所以就將6萬多條沒用的數(shù)據(jù)導(dǎo)入到了系統(tǒng)中

這6萬多條數(shù)據(jù)對系統(tǒng)來說就是無用的數(shù)據(jù),不會影響系統(tǒng)的運行,最多也就是占用一點數(shù)據(jù)庫空間而已

客戶只需要把正確的excel重新導(dǎo)入,就可以繼續(xù)完成他的業(yè)務(wù)了

但是,客戶是一個重度強迫癥患者,他覺得在管理平臺看到這6萬多條沒用的數(shù)據(jù)令他抓狂

客戶想要把這些數(shù)據(jù)刪除,我們系統(tǒng)又沒有提供批量刪除功能,只能單個刪除,這無疑是一個巨大的工作量

客戶就通過客服部門找到了研發(fā)團隊,想讓我們研發(fā)人員從數(shù)據(jù)庫中直接刪除

刪庫經(jīng)過

雖然在生產(chǎn)環(huán)境直接操作數(shù)據(jù)庫明顯是違規(guī)操作,但客戶的要求又不得不滿足,誰讓人家是爸爸呢

金主爸爸
由于生產(chǎn)環(huán)境的數(shù)據(jù)和表結(jié)構(gòu)屬于商業(yè)機密,我們討論的重點也不在于數(shù)據(jù)和表結(jié)構(gòu),而是數(shù)據(jù)恢復(fù)的思路。所以我在測試環(huán)境新建了用戶表,導(dǎo)入了一些測試數(shù)據(jù),當(dāng)作是生產(chǎn)環(huán)境進行操作

研發(fā)人員登錄生產(chǎn)數(shù)據(jù)庫,執(zhí)行如下sql,找到了這6萬多條錯誤數(shù)據(jù)。

select?*?from?t_user?where?age>18?and?deptid=100;
在確認這6萬多條數(shù)據(jù)確實是錯誤導(dǎo)入的數(shù)據(jù)后就準(zhǔn)備開始刪除。由于表里面沒有邏輯刪除字段,所以只能進行物理刪除

需要刪除的數(shù)據(jù)已經(jīng)確定,通常情況下把sql中的select *替換為delete去執(zhí)行,出錯的機率會小一點

但是,研發(fā)人員并沒有去改原來的sql,而是重新寫了一個刪除語句并且執(zhí)行

delete?from?t_user?where?age>18;
問題就這樣出現(xiàn)了,在新寫的刪除語句中缺少了deptid=100的條件

不要問我為什么刪除之前沒有備份,這都是血淚的教訓(xùn)

重新查表后發(fā)現(xiàn)誤刪了10多萬條數(shù)據(jù)

生產(chǎn)環(huán)境中,很多業(yè)務(wù)都依賴這個表,算是系統(tǒng)的核心表。雖然是只刪除了10萬條數(shù)據(jù),但系統(tǒng)的很多功能無法正常使用,其實和刪庫沒啥區(qū)別了

研發(fā)人員發(fā)現(xiàn)刪庫后,第一時間報告給了領(lǐng)導(dǎo)(居然沒有第一時間跑路)

領(lǐng)導(dǎo)當(dāng)機立斷,要求系統(tǒng)停止運行,給所有客戶發(fā)送停服通知,打開所有客服通道,處理客戶投訴和答疑

同時,也安排研發(fā)人員進行數(shù)據(jù)找回,要求盡快搞定

數(shù)據(jù)找回

我們找到刪庫的研發(fā)人員詢問他有沒有備份,他的回答是沒有

我們又去咨詢運維的同事,看看生產(chǎn)環(huán)境有沒有開啟數(shù)據(jù)庫定期自動備份,運維的回答也是沒有

事情比較難辦了,只能把希望寄托于mysql的binlog了。

binlog二進制日志文件,數(shù)據(jù)庫的insert、delete、update、create、alter、drop等寫入操作都會被binlog記錄(下文對binlog有詳細介紹

binlog記錄日志是需要開啟配置的,希望生產(chǎn)環(huán)境的mysql數(shù)據(jù)庫開啟了binlog日志,否則只能找專業(yè)的磁盤數(shù)據(jù)恢復(fù)的第三方公司了

登錄生產(chǎn)環(huán)境數(shù)據(jù)庫,查看binlog是否開啟

SHOW?VARIABLES?LIKE?'LOG_BIN%';
從圖中可以看到log_bin是處于ON的狀態(tài),說明binlog是開啟的

懸著的心終于放下了一大半,接下來就是想辦法從binlog中把數(shù)據(jù)恢復(fù)就行了

從上圖中也可以看到log_bin_basename/var/lib/mysql/bin-log,說明binlog是存放在mysql所在的服務(wù)器的/var/lib/mysql目錄下,文件是以bin-log開頭,比如:bin-log.000001

登錄mysql所在的服務(wù)器,進入到binlog所在的目錄

cd?/var/lib/mysql
查看binlog日志文件

binlog日志文件是滾動生成的,從圖中看到現(xiàn)在已經(jīng)有4個文件了。

通常情況下,生產(chǎn)環(huán)境的binlog會有成百上千個,這時候就需要確認我們需要的數(shù)據(jù)是在第幾個binlog中了,下文也會講怎么確定我們需要的是第幾個

因為我們刪庫是剛剛發(fā)生的事情,所以我們需要的數(shù)據(jù)大概率是在第4個文件中

直接去查看第4個binlog文件,看到的全都是亂碼,就像下面這樣,這是因為binlog文件是二進制的

我們需要借助mysql官方提供的mysqlbinlog命令去才能正確解析binlog文件

用mysqlbinlog命令可以打開binlog文件,但是一個binlog文件的大小可能有幾百兆,要從幾百兆日志中找到我們需要的日志,還是比較麻煩的

還好mysqlbinlog命令提供一些參數(shù)選項可以讓我們對binlog文件進行篩選,最常用的參數(shù)就是時間參數(shù)(下文也會對mysqlbinlog的詳細用法進行說明)

經(jīng)過和刪庫的研發(fā)人員確定,刪庫的時間大概是「10:40」,那我們就以這個時間點為參考,找前后5分鐘的日志

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 | grep t_user
從圖中可以看到,這個時間點的日志確實包含我們刪除數(shù)據(jù)的日志

接下來我們就需要把這些日志整理一下,然后想辦法恢復(fù)到數(shù)據(jù)庫就可以了。

首先,把我們需要的日志單獨保存到tmp.log文件中,方便下載到本地

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 > tmp.log
把tmp.log下載到本地,用文本編輯工具打開看一下,可以看到一堆偽sql

在上圖的偽sql中
@1表示第一個字段
@2表示第二個字段
其他的以此類推

日志中包含的sql是一些偽sql,并不能直接在數(shù)據(jù)庫執(zhí)行,我們需要想辦法把這些偽sql處理成可在數(shù)據(jù)庫執(zhí)行的真正的sql

我們使用的文本編輯工具的批量替換功能,就像下面這樣

最終處理好的sql就像是這樣

把處理好的sql在測試數(shù)據(jù)庫驗證一下沒問題后直接在生產(chǎn)庫執(zhí)行

sql執(zhí)行完以后,被誤刪除的數(shù)據(jù)就恢復(fù)回來了。

我們和刪庫的研發(fā)一起,把客戶要求刪除的6萬多條數(shù)據(jù)重新給刪除,算是完成了客戶的要求

至此,刪庫事件就暫時告一段落。不要問刪庫的研發(fā)受到了什么處分,問就是什么處分都沒有

幾點建議

刪庫跑路真的不只是一句玩笑話,如果真的不小心刪庫了而又無法找回數(shù)據(jù)的話,不僅僅是簡單的罰款、扣績效就完事了,甚至有可能會面臨牢獄之災(zāi)

對于公司來說,一個不小心的刪庫操作,就有可能把公司刪沒了。畢竟刪庫造成的數(shù)據(jù)損失、經(jīng)濟損失不是所有公司都有能力承擔(dān)的

所以,生產(chǎn)環(huán)境的數(shù)據(jù)安全一定是重中之重。根據(jù)我多年的刪庫經(jīng)歷,也總結(jié)了一些經(jīng)驗分享給你們,希望對你們有所幫助

「1、研發(fā)人員不能直連生產(chǎn)庫」

生產(chǎn)庫一般由DBA或者運維來維護,研發(fā)人員很少有需要登錄生產(chǎn)數(shù)據(jù)庫查看數(shù)據(jù)的需求,就算數(shù)據(jù)真的有問題,一般情況下DBA或運維人員也能解決

如果一個系統(tǒng)需要研發(fā)人員頻繁的登錄數(shù)據(jù)庫去維護數(shù)據(jù),這時就該考慮在系統(tǒng)中增加一個管理功能來使用,而不是頻繁登錄數(shù)據(jù)庫

所以,研發(fā)就不應(yīng)該具有生產(chǎn)庫的登錄權(quán)限。如果偶爾的需要登錄生產(chǎn)庫查看數(shù)據(jù),可以找DBA開一個臨時賬號

「2、登錄生產(chǎn)庫使用只讀賬號」

大部分人使用數(shù)據(jù)庫都會使用連接工具,比如Navicat、SQLyog等

每個人的電腦上,大概率也只有一個連接工具。開發(fā)庫、測試庫、生產(chǎn)庫都在同一個連接工具中打開,有時只是想在開發(fā)庫中修改一條數(shù)據(jù),卻不小心修改了生產(chǎn)庫

而MySql的事務(wù)是自動提交的,在連接工具中,正在修改的當(dāng)前行失去光標(biāo)后就會自動提交事務(wù),極其容易操作失誤

所以,如果確實的需要登錄生產(chǎn)庫,盡量使用具有只讀權(quán)限的賬號登錄

「3、關(guān)閉autocomit、多人復(fù)核」

如果確實需要在生產(chǎn)庫進行數(shù)據(jù)的增加、修改或刪除,在執(zhí)行sql之前最好先關(guān)閉事務(wù)的自動提交

在需要登錄生產(chǎn)庫修改數(shù)據(jù)的情況下,想必問題也比較復(fù)雜,一條sql語句應(yīng)該是完成不了,可能需要寫N多個sql才能完成數(shù)據(jù)的修改

這么多的sql,很有可能在執(zhí)行的時候會選錯。有時你只是想執(zhí)行一個select語句,結(jié)果發(fā)現(xiàn)執(zhí)行的是delete

更坑爹的是,大部分的數(shù)據(jù)庫連接工具有執(zhí)行當(dāng)前選中內(nèi)容的功能。有時候你只想執(zhí)行當(dāng)前選中的內(nèi)容,結(jié)果發(fā)現(xiàn)執(zhí)行的是全部內(nèi)容

如果關(guān)閉了自動提交,就算出現(xiàn)上面的情況,也還有機會挽回。比如下面這樣

--?關(guān)閉事務(wù)自動提交
set?@@autocommit=0;


--?查看需要刪除的數(shù)據(jù),共65600條
select?*?from?t_user?where?age>18?and?deptid=100;
--?刪除
delete?from?t_user?where?age>18;


--?發(fā)現(xiàn)有問題,回滾
select?*?from?t_user?where?age>18?and?deptid=100;
rollback?;

--?確認沒問題,提交
--?commit;
另外,在commit之前需要至少再找一個同事進行確認。所謂當(dāng)局者迷,自己有時可能處于一個錯誤的思路上,就想當(dāng)然的認為結(jié)果沒問題,這時就需要一個旁觀者來指點迷津

兩個人都確認沒問題之后再提交,出錯的機率也會小很多

「4、修改數(shù)據(jù)之前先備份」

備份、備份、備份,重要的事情說三遍

備份雖然會麻煩一點,但它是保證數(shù)據(jù)準(zhǔn)確性最有效的手段

況且,掌握一些技巧后,備份也不是很麻煩的事情

比如,我們刪除數(shù)據(jù)之前可以先這樣備份

--?創(chuàng)建一個和原表一樣的備份表(包含索引)
create?table?t_user_bak?like?t_user;

--?拷貝數(shù)據(jù)到備份表
INSERT?into?t_user_bak?select?*?from?t_user;

--?確認數(shù)據(jù)拷貝完成
select?*?from?t_user_bak;
這樣備份的數(shù)據(jù),就算原表數(shù)據(jù)誤刪了,甚至都不用恢復(fù)數(shù)據(jù),只需要把備份表的名字改成原表的名字直接使用就可以了

在生產(chǎn)庫修改數(shù)據(jù)之前,一定要記得備份,一旦數(shù)據(jù)修改出錯,這是成本最低并且最有效的恢復(fù)途徑

「5、設(shè)置數(shù)據(jù)庫定期備份」

生產(chǎn)環(huán)境,運維人員一定要設(shè)置數(shù)據(jù)庫定期備份。研發(fā)人員也有義務(wù)提醒運維同事編寫自動備份腳本,因為生產(chǎn)庫一旦出現(xiàn)問題需要恢復(fù)數(shù)據(jù),沒有定期備份的話,麻煩的不只是運維人員,研發(fā)人員也要跟著麻煩

備份周期可以根據(jù)業(yè)務(wù)需要來決定。如果業(yè)務(wù)對數(shù)據(jù)要求的實時性比較高,備份周期相對短一點,恢復(fù)數(shù)據(jù)時可以最大程度的避免數(shù)據(jù)丟失;反之,備份周期可以長一點,節(jié)省磁盤空間

如果有必要,可以定期把備份文件拷貝到異地服務(wù)器,避免由于一些不可抗力因素導(dǎo)致的當(dāng)前服務(wù)器磁盤損壞,如地震、臺風(fēng)等

binglog日志

binlog即Binary Log,它是二進制文件,用來記錄數(shù)據(jù)庫寫操作的日志

數(shù)據(jù)庫的insert、delete、update、create、alter、drop等寫入操作都會被binlog記錄

因此,數(shù)據(jù)庫的主從數(shù)據(jù)同步通常也是基于binlog完成的,本文只對binlog做一些簡單介紹,后期會單獨寫一篇文章講基于binlog的主從數(shù)據(jù)同步

binlog日志需要配置開啟,可以通過腳本查看binlog是否開啟

SHOW?VARIABLES?LIKE?'LOG_BIN%';
如果log_bin參數(shù)顯示的是OFF說明binlog是關(guān)閉狀態(tài),需要手動開啟

開啟binlog需要修改數(shù)據(jù)庫的my.cnf配置文件,my.cnf文件通常在服務(wù)器的/etc目錄下

打開/etc/my.cnf文件,配置binlog的相關(guān)參數(shù),下文配置binlog的常用參數(shù)

# 啟用binlog并設(shè)置binlog日志的存儲目錄
log_bin = /var/lib/mysql/bin-log
# 設(shè)置binlog索引存儲目錄
log_bin_index = /var/lib/mysql/mysql-bin.index
# 30天之前的日志自動刪除
expire_logs_days = 30
# 設(shè)置binlog日志模式,共有3種模式:STATMENT、ROW、MIXED
binlog_format = row
binlog的日志有三種格式,分別是STATEMENT、ROW、MIXED。在mysql5.7.7版本之前默認使用的是STATEMENT,之后的版本默認使用的是ROW

ROW格式

ROW格式下,binlog記錄的是每一條數(shù)據(jù)被修改的詳細細節(jié)。

比如,執(zhí)行delete語句,刪除的數(shù)據(jù)有多少條,binlog中就記錄有多少條偽sql

delete?from?t_user?where?age>18;
那么row格式的日志的缺點就很明顯,在發(fā)生批量操作時,日志文件中會記錄大量的偽sql,占用較多的磁盤空間

尤其是當(dāng)進行alter操作時,每條數(shù)據(jù)都發(fā)生變化,日志文件中就會有每一條的數(shù)據(jù)的日志。此時,如果表中的數(shù)據(jù)量很大的話,日志文件也會非常大

在mysql5.6版本之后,針對ROW格式的日志,新增了binlog_row_image參數(shù)。

當(dāng)binlog_row_image設(shè)置為minimal時,日志中只會記錄發(fā)生改變的列,而不是全部的列,這在一定程度上能減少binlog日志的大小

雖然記錄每行數(shù)據(jù)的變化會造成日志文件過大,但這也是它的優(yōu)點所在

因為它記錄了每條數(shù)據(jù)修改細節(jié),所以在一些極端情況下也不會出現(xiàn)數(shù)據(jù)錯亂的問題。在做數(shù)據(jù)恢復(fù)或主從同步時能很好的保證數(shù)據(jù)的真實性和一致性

STATEMENT格式

STATEMENT格式下,日志中記錄的是真正的sql語句,就像是這樣

日志中的sql是直接可以拿到數(shù)據(jù)庫運行的

STATEMENT格式的日志的優(yōu)缺點和ROW格式的正好相反,它記錄的是sql語句和執(zhí)行語句時的上下文環(huán)境,而不是每一條數(shù)據(jù)。所以它的日志文件會比ROW格式的日志文件小一些

由于記錄的只是sql語句和上下文的環(huán)境,STATEMENT格式的日志在進行主從數(shù)據(jù)同步時會有一些不可預(yù)估的情況出現(xiàn),導(dǎo)致數(shù)據(jù)錯亂。比如sleep()、last_insert_id()等函數(shù)會出現(xiàn)問題

MIXED格式

MIXED格式是STATEMENT和ROW的結(jié)合,mysql會根據(jù)具體執(zhí)行的sql語句,來選擇合適的日志格式進行記錄

MIXED格式下,在執(zhí)行普通的sql語句時會選STATEMENT來記錄日志,在遇到復(fù)雜的語句或函數(shù)操作時會選擇ROW來記錄日志

mysqlbinlog命令

mysql數(shù)據(jù)庫的binlog文件是二進制的,基本看不懂,使用數(shù)據(jù)庫自帶的mysqlbinlog命令可以把二進制文件轉(zhuǎn)換成能看懂的十進制文件

由于數(shù)據(jù)庫的binlog文件可能會很大,查看起來會很麻煩,所以mysqlbinlog命令也提供了一些參數(shù)可以用來篩選日志

「mysqlbinlog語法」

mysqlbinlog?[options]?log-files
options:可選參數(shù)
log-files:文件名稱

「options的常用值」

-d: 根據(jù)數(shù)據(jù)庫的名稱篩選日志
-o:跳過前N行日志
-r, --result-fil: 把日志輸出到指定文件
--start-datetime: 讀取指定時間之后的日志,時間格式:yyyy-MM-dd HH:mm:ss
--stop-datetime: 讀取指定時間之前的日志,時間格式:yyyy-MM-dd HH:mm:ss
--start-position: 從指定位置開始讀取日志
--stop-position: 讀取到指定位置停止
--base64-output:在row格式下,顯示偽sql語句
-v, --verbose:顯示偽sql語句,-vv可以為sql語句添加備注

「常用寫法」
查看fusion數(shù)據(jù)庫的日志

mysqlbinlog?-d=fusion?bin-log.000001
查看某個時間段內(nèi)的日志

mysqlbinlog??--start-datetime='2021-06-09?19:30:00'?--stop-datetime='2021-06-09?19:50:00'?bin-log.000001
恢復(fù)數(shù)據(jù),事件的開始位置是4300,結(jié)束位置是10345

mysqlbinlog?--start-position?4300?--stop-position?10345?bin-log.000001?|?mysql?-uroot?-p123456?fusion

本站聲明: 本文章由作者或相關(guān)機構(gòu)授權(quán)發(fā)布,目的在于傳遞更多信息,并不代表本站贊同其觀點,本站亦不保證或承諾內(nèi)容真實性等。需要轉(zhuǎn)載請聯(lián)系該專欄作者,如若文章內(nèi)容侵犯您的權(quán)益,請及時聯(lián)系本站刪除。
關(guān)閉
關(guān)閉