內(nèi)部群炸了鍋，隔壁同事真刪庫了啊...
源?/?? ? ? ??文/?
事件起因
我們的系統(tǒng)中有數(shù)據(jù)導入的功能，可以把特定的格式的 excel 數(shù)據(jù)導入到系統(tǒng)中來。
由于客戶電腦的文件比較多，很多文件的名字也比較相近，客戶在導入 excel 時選錯了文件。
這個錯誤的 excel 文件的格式恰好能被系統(tǒng)解析，客戶也沒及時發(fā)現(xiàn)導錯了文件，所以就將 6 萬多條沒用的數(shù)據(jù)導入到了系統(tǒng)中。
這 6 萬多條數(shù)據(jù)對系統(tǒng)來說就是無用的數(shù)據(jù)，不會影響系統(tǒng)的運行，最多也就是占用一點數(shù)據(jù)庫空間而已。
客戶只需要把正確的 excel 重新導入，就可以繼續(xù)完成他的業(yè)務了。
但是，客戶是一個重度強迫癥患者，他覺得在管理平臺看到這 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ù)恢復的思路。所以我在測試環(huán)境新建了用戶表，導入了一些測試數(shù)據(jù)，當作是生產(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ù)確實是錯誤導入的數(shù)據(jù)后就準備開始刪除。由于表里面沒有邏輯刪除字段，所以只能進行物理刪除。
需要刪除的數(shù)據(jù)已經(jīng)確定，通常情況下把 sql 中的select *替換為delete去執(zhí)行，出錯的機率會小一點。
但是，研發(fā)人員并沒有去改原來的 sql，而是重新寫了一個刪除語句并且執(zhí)行。
delete?from?t_user?where?age>18;
問題就這樣出現(xiàn)了，在新寫的刪除語句中缺少了deptid=100的條件
不要問我為什么刪除之前沒有備份，這都是血淚的教訓
重新查表后發(fā)現(xiàn)誤刪了 10 多萬條數(shù)據(jù)。
生產(chǎn)環(huán)境中，很多業(yè)務都依賴這個表，算是系統(tǒng)的核心表。雖然是只刪除了 10 萬條數(shù)據(jù)，但系統(tǒng)的很多功能無法正常使用，其實和刪庫沒啥區(qū)別了。
研發(fā)人員發(fā)現(xiàn)刪庫后，第一時間報告給了領導（居然沒有第一時間跑路）。
領導當機立斷，要求系統(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ù)恢復的第三方公司了。
登錄生產(chǎn)環(huán)境數(shù)據(jù)庫，查看 binlog 是否開啟。
SHOW?VARIABLES?LIKE?'LOG_BIN%';
從圖中可以看到log_bin是處于ON的狀態(tài)，說明 binlog 是開啟的。
懸著的心終于放下了一大半，接下來就是想辦法從 binlog 中把數(shù)據(jù)恢復就行了。
從上圖中也可以看到log_bin_basename是/var/lib/mysql/bin-log，說明 binlog 是存放在 mysql 所在的服務器的/var/lib/mysql目錄下，文件是以bin-log開頭，比如：bin-log.000001。
登錄 mysql 所在的服務器，進入到 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ù)的日志。
接下來我們就需要把這些日志整理一下，然后想辦法恢復到數(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ā)一起，把客戶要求刪除的 6 萬多條數(shù)據(jù)重新給刪除，算是完成了客戶的要求。
至此，刪庫事件就暫時告一段落。不要問刪庫的研發(fā)受到了什么處分，問就是什么處分都沒有。
幾點建議
刪庫跑路真的不只是一句玩笑話，如果真的不小心刪庫了而又無法找回數(shù)據(jù)的話，不僅僅是簡單的罰款、扣績效就完事了，甚至有可能會面臨牢獄之災。
對于公司來說，一個不小心的刪庫操作，就有可能把公司刪沒了。畢竟刪庫造成的數(shù)據(jù)損失、經(jīng)濟損失不是所有公司都有能力承擔的。
所以，生產(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ā)就不應該具有生產(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 的事務是自動提交的，在連接工具中，正在修改的當前行失去光標后就會自動提交事務，極其容易操作失誤。
所以，如果確實的需要登錄生產(chǎn)庫，盡量使用具有只讀權(quán)限的賬號登錄。
「3、關閉 autocomit、多人復核」
如果確實需要在生產(chǎn)庫進行數(shù)據(jù)的增加、修改或刪除，在執(zhí)行 sql 之前最好先關閉事務的自動提交。
在需要登錄生產(chǎn)庫修改數(shù)據(jù)的情況下，想必問題也比較復雜，一條 sql 語句應該是完成不了，可能需要寫 N 多個 sql 才能完成數(shù)據(jù)的修改。
這么多的 sql，很有可能在執(zhí)行的時候會選錯。有時你只是想執(zhí)行一個 select 語句，結(jié)果發(fā)現(xiàn)執(zhí)行的是 delete。
更坑爹的是，大部分的數(shù)據(jù)庫連接工具有執(zhí)行當前選中內(nèi)容的功能。有時候你只想執(zhí)行當前選中的內(nèi)容，結(jié)果發(fā)現(xiàn)執(zhí)行的是全部內(nèi)容。
如果關閉了自動提交，就算出現(xiàn)上面的情況，也還有機會挽回。比如下面這樣：
--?關閉事務自動提交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之前需要至少再找一個同事進行確認。所謂當局者迷，自己有時可能處于一個錯誤的思路上，就想當然的認為結(jié)果沒問題，這時就需要一個旁觀者來指點迷津。
兩個人都確認沒問題之后再提交，出錯的機率也會小很多。
「4、修改數(shù)據(jù)之前先備份」
備份、備份、備份，重要的事情說三遍。
備份雖然會麻煩一點，但它是保證數(shù)據(jù)準確性最有效的手段。
況且，掌握一些技巧后，備份也不是很麻煩的事情。
比如，我們刪除數(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ù)誤刪了，甚至都不用恢復數(shù)據(jù)，只需要把備份表的名字改成原表的名字直接使用就可以了。
在生產(chǎn)庫修改數(shù)據(jù)之前，一定要記得備份，一旦數(shù)據(jù)修改出錯，這是成本最低并且最有效的恢復途徑。
「5、設置數(shù)據(jù)庫定期備份」
生產(chǎn)環(huán)境，運維人員一定要設置數(shù)據(jù)庫定期備份。研發(fā)人員也有義務提醒運維同事編寫自動備份腳本，因為生產(chǎn)庫一旦出現(xiàn)問題需要恢復數(shù)據(jù)，沒有定期備份的話，麻煩的不只是運維人員，研發(fā)人員也要跟著麻煩。
備份周期可以根據(jù)業(yè)務需要來決定。如果業(yè)務對數(shù)據(jù)要求的實時性比較高，備份周期相對短一點，恢復數(shù)據(jù)時可以最大程度的避免數(shù)據(jù)丟失；反之，備份周期可以長一點，節(jié)省磁盤空間。
如果有必要，可以定期把備份文件拷貝到異地服務器，避免由于一些不可抗力因素導致的當前服務器磁盤損壞，如地震、臺風等。
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 是關閉狀態(tài)，需要手動開啟。
開啟 binlog 需要修改數(shù)據(jù)庫的my.cnf配置文件，my.cnf文件通常在服務器的/etc目錄下。
打開/etc/my.cnf文件，配置 binlog 的相關參數(shù)，下文配置 binlog 的常用參數(shù)。
#?啟用binlog并設置binlog日志的存儲目錄log_bin =?/var/lib/mysql/bin-log#?設置binlog索引存儲目錄log_bin_index =?/var/lib/mysql/mysql-bin.index# 30天之前的日志自動刪除expire_logs_days = 30#?設置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，占用較多的磁盤空間。
尤其是當進行 alter 操作時，每條數(shù)據(jù)都發(fā)生變化，日志文件中就會有每一條的數(shù)據(jù)的日志。此時，如果表中的數(shù)據(jù)量很大的話，日志文件也會非常大。
在 mysql5.6 版本之后，針對 ROW 格式的日志，新增了binlog_row_image參數(shù)。
當binlog_row_image設置為minimal時，日志中只會記錄發(fā)生改變的列，而不是全部的列，這在一定程度上能減少 binlog 日志的大小。
雖然記錄每行數(shù)據(jù)的變化會造成日志文件過大，但這也是它的優(yōu)點所在。
因為它記錄了每條數(shù)據(jù)修改細節(jié)，所以在一些極端情況下也不會出現(xiàn)數(shù)據(jù)錯亂的問題。在做數(shù)據(jù)恢復或主從同步時能很好的保證數(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ù)同步時會有一些不可預估的情況出現(xiàn)，導致數(shù)據(jù)錯亂。比如 sleep()、last_insert_id() 等函數(shù)會出現(xiàn)問題。
MIXED 格式
MIXED 格式是 STATEMENT 和 ROW 的結(jié)合，mysql 會根據(jù)具體執(zhí)行的 sql 語句，來選擇合適的日志格式進行記錄。
MIXED 格式下，在執(zhí)行普通的 sql 語句時會選 STATEMENT 來記錄日志，在遇到復雜的語句或函數(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
恢復數(shù)據(jù)，事件的開始位置是 4300，結(jié)束位置是 10345
mysqlbinlog?--start-position?4300?--stop-position?10345?bin-log.000001?|?mysql?-uroot?-p123456?fusion
?推薦閱讀
華為最美小姐姐被外派墨西哥后...
國內(nèi)有程序員電視劇了，結(jié)果看了一分鐘，就吐了...
END
頂級程序員：topcoding
做最好的程序員社區(qū)：Java后端開發(fā)、Python、大數(shù)據(jù)、AI
一鍵三連「分享」、「點贊」和「在看」