這老哥的刪庫水平，和我有的一拼

　　大家好，我是魚皮，今天分享一個真實案例，看看哪個小傻瓜又刪庫跑路了？

　　事件起因

　　我們的系統中有數據導入的功能，可以把特定的格式的excel數據導入到系統中來

　　由于客戶電腦的文件比較多，很多文件的名字也比較相近，客戶在導入excel時選錯了文件

　　這個錯誤的excel文件的格式恰好能被系統解析，客戶也沒及時發現導錯了文件，所以就將6萬多條沒用的數據導入到了系統中

　　這6萬多條數據對系統來說就是無用的數據，不會影響系統的運行，最多也就是占用一點數據庫空間而已

　　客戶只需要把正確的excel重新導入，就可以繼續完成他的業務了

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

　　客戶想要把這些數據刪除，我們系統又沒有提供批量刪除功能，只能單個刪除，這無疑是一個巨大的工作量

　　客戶就通過客服部門找到了研發團隊，想讓我們研發人員從數據庫中直接刪除

　　雖然在生產環境直接操作數據庫明顯是違規操作，但客戶的要求又不得不滿足，誰讓人家是爸爸呢金主爸爸

　　由于生產環境的數據和表結構屬于商業機密，我們討論的重點也不在于數據和表結構，而是數據恢復的思路。所以我在測試環境新建了用戶表，導入了一些測試數據，當作是生產環境進行操作

　　研發人員登錄生產數據庫，執行如下sql，找到了這6萬多條錯誤數據。

　　在確認這6萬多條數據確實是錯誤導入的數據后就準備開始刪除。由于表里面沒有邏輯刪除字段，所以只能進行物理刪除

　　需要刪除的數據已經確定，通常情況下把sql中的替換為去執行，出錯的機率會小一點

　　但是，研發人員并沒有去改原來的sql，而是重新寫了一個刪除語句并且執行

　　問題就這樣出現了，在新寫的刪除語句中缺少了的條件

　　不要問我為什么刪除之前沒有備份，這都是血淚的教訓

　　重新查表后發現誤刪了10多萬條數據

　　生產環境中，很多業務都依賴這個表，算是系統的核心表。雖然是只刪除了10萬條數據，但系統的很多功能無法正常使用，其實和刪庫沒啥區別了

　　研發人員發現刪庫后，第一時間報告給了領導（居然沒有第一時間跑路）

　　領導當機立斷，要求系統停止運行，給所有客戶發送停服通知，打開所有客服通道，處理客戶投訴和答疑

　　同時，也安排研發人員進行數據找回，要求盡快搞定

　　我們找到刪庫的研發人員詢問他有沒有備份，他的回答是沒有

　　我們又去咨詢運維的同事，看看生產環境有沒有開啟數據庫定期自動備份，運維的回答也是沒有

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

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

　　binlog記錄日志是需要開啟配置的，希望生產環境的mysql數據庫開啟了binlog日志，否則只能找專業的磁盤數據恢復的第三方公司了

　　登錄生產環境數據庫，查看binlog是否開啟

　　從圖中可以看到是處于的狀態，說明binlog是開啟的

　　懸著的心終于放下了一大半，接下來就是想辦法從binlog中把數據恢復就行了

　　從上圖中也可以看到是，說明binlog是存放在mysql所在的服務器的目錄下，文件是以開頭，比如：bin-log.000001

　　登錄mysql所在的服務器，進入到binlog所在的目錄

　　查看binlog日志文件

　　binlog日志文件是滾動生成的，從圖中看到現在已經有4個文件了。

　　通常情況下，生產環境的binlog會有成百上千個，這時候就需要確認我們需要的數據是在第幾個binlog中了，下文也會講怎么確定我們需要的是第幾個

　　因為我們刪庫是剛剛發生的事情，所以我們需要的數據大概率是在第4個文件中

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

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

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

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

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

　　從圖中可以看到，這個時間點的日志確實包含我們刪除數據的日志

　　接下來我們就需要把這些日志整理一下，然后想辦法恢復到數據庫就可以了。

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

　　把tmp.log下載到本地，用文本編輯工具打開看一下，可以看到一堆偽sql

　　在上圖的偽sql中

　　@1表示第一個字段

　　@2表示第二個字段

　　其他的以此類推

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

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

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

　　把處理好的sql在測試數據庫驗證一下沒問題后直接在生產庫執行

　　sql執行完以后，被誤刪除的數據就恢復回來了。

　　我們和刪庫的研發一起，把客戶要求刪除的6萬多條數據重新給刪除，算是完成了客戶的要求

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

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

　　對于公司來說，一個不小心的刪庫操作，就有可能把公司刪沒了。畢竟刪庫造成的數據損失、經濟損失不是所有公司都有能力承擔的

　　所以，生產環境的數據安全一定是重中之重。根據我多年的刪庫經歷，也總結了一些經驗分享給你們，希望對你們有所幫助

　　「1、研發人員不能直連生產庫」

　　生產庫一般由DBA或者運維來維護，研發人員很少有需要登錄生產數據庫查看數據的需求，就算數據真的有問題，一般情況下DBA或運維人員也能解決

　　如果一個系統需要研發人員頻繁的登錄數據庫去維護數據，這時就該考慮在系統中增加一個管理功能來使用，而不是頻繁登錄數據庫

　　所以，研發就不應該具有生產庫的登錄權限。如果偶爾的需要登錄生產庫查看數據，可以找DBA開一個臨時賬號

　　「2、登錄生產庫使用只讀賬號」

　　大部分人使用數據庫都會使用連接工具，比如Navicat、SQLyog等

　　每個人的電腦上，大概率也只有一個連接工具。開發庫、測試庫、生產庫都在同一個連接工具中打開，有時只是想在開發庫中修改一條數據，卻不小心修改了生產庫

　　而MySql的事務是自動提交的，在連接工具中，正在修改的當前行失去光標后就會自動提交事務，極其容易操作失誤

　　所以，如果確實的需要登錄生產庫，盡量使用具有只讀權限的賬號登錄

　　「3、關閉autocomit、多人復核」

　　如果確實需要在生產庫進行數據的增加、修改或刪除，在執行sql之前最好先關閉事務的自動提交

　　在需要登錄生產庫修改數據的情況下，想必問題也比較復雜，一條sql語句應該是完成不了，可能需要寫N多個sql才能完成數據的修改

　　這么多的sql，很有可能在執行的時候會選錯。有時你只是想執行一個select語句，結果發現執行的是delete

　　更坑爹的是，大部分的數據庫連接工具有的功能。有時候你只想執行當前選中的內容，結果發現執行的是全部內容

　　如果關閉了自動提交，就算出現上面的情況，也還有機會挽回。比如下面這樣

　　另外，在之前需要至少再找一個同事進行確認。所謂當局者迷，自己有時可能處于一個錯誤的思路上，就想當然的認為結果沒問題，這時就需要一個旁觀者來指點迷津

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

　　「4、修改數據之前先備份」

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

　　備份雖然會麻煩一點，但它是保證數據準確性最有效的手段

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

　　比如，我們刪除數據之前可以先這樣備份

　　這樣備份的數據，就算原表數據誤刪了，甚至都不用恢復數據，只需要把備份表的名字改成原表的名字直接使用就可以了

　　在生產庫修改數據之前，一定要記得備份，一旦數據修改出錯，這是成本最低并且最有效的恢復途徑

　　「5、設置數據庫定期備份」

　　生產環境，運維人員一定要設置數據庫定期備份。研發人員也有義務提醒運維同事編寫自動備份腳本，因為生產庫一旦出現問題需要恢復數據，沒有定期備份的話，麻煩的不只是運維人員，研發人員也要跟著麻煩

　　備份周期可以根據業務需要來決定。如果業務對數據要求的實時性比較高，備份周期相對短一點，恢復數據時可以最大程度的避免數據丟失；反之，備份周期可以長一點，節省磁盤空間

　　如果有必要，可以定期把備份文件拷貝到異地服務器，避免由于一些不可抗力因素導致的當前服務器磁盤損壞，如地震、臺風等

　　binlog即Binary Log，它是二進制文件，用來記錄數據庫寫操作的日志

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

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

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

　　如果參數顯示的是說明binlog是關閉狀態，需要手動開啟

　　開啟binlog需要修改數據庫的配置文件，my.cnf文件通常在服務器的目錄下

　　打開文件，配置binlog的相關參數，下文配置binlog的常用參數

　　binlog的日志有三種格式，分別是STATEMENT、ROW、MIXED。在mysql5.7.7版本之前默認使用的是STATEMENT，之后的版本默認使用的是ROW

　　ROW格式下，binlog記錄的是每一條數據被修改的詳細細節。

　　比如，執行delete語句，刪除的數據有多少條，binlog中就記錄有多少條偽sql

　　那么row格式的日志的缺點就很明顯，在發生批量操作時，日志文件中會記錄大量的偽sql，占用較多的磁盤空間

　　尤其是當進行alter操作時，每條數據都發生變化，日志文件中就會有每一條的數據的日志。此時，如果表中的數據量很大的話，日志文件也會非常大

　　在mysql5.6版本之后，針對ROW格式的日志，新增了參數。

　　當設置為時，日志中只會記錄發生改變的列，而不是全部的列，這在一定程度上能減少binlog日志的大小

　　雖然記錄每行數據的變化會造成日志文件過大，但這也是它的優點所在

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

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

　　日志中的sql是直接可以拿到數據庫運行的

　　STATEMENT格式的日志的優缺點和ROW格式的正好相反，它記錄的是sql語句和執行語句時的上下文環境，而不是每一條數據。所以它的日志文件會比ROW格式的日志文件小一些

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

　　MIXED格式是STATEMENT和ROW的結合，mysql會根據具體執行的sql語句，來選擇合適的日志格式進行記錄

　　MIXED格式下，在執行普通的sql語句時會選STATEMENT來記錄日志，在遇到復雜的語句或函數操作時會選擇ROW來記錄日志

　　mysql數據庫的binlog文件是二進制的，基本看不懂，使用數據庫自帶的命令可以把二進制文件轉換成能看懂的十進制文件

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

　　「mysqlbinlog語法」

　　：可選參數

　　：文件名稱

　　「options的常用值」

　　: 根據數據庫的名稱篩選日志

　　：跳過前N行日志

　　: 把日志輸出到指定文件

　　: 讀取指定時間之后的日志，時間格式：yyyy-MM-dd HH:mm:ss

　　: 讀取指定時間之前的日志，時間格式：yyyy-MM-dd HH:mm:ss

　　: 從指定位置開始讀取日志

　　: 讀取到指定位置停止

　　：在row格式下，顯示偽sql語句

　　：顯示偽sql語句，-vv可以為sql語句添加備注

　　「常用寫法」

　　查看fusion數據庫的日志

　　查看某個時間段內的日志

　　恢復數據，事件的開始位置是4300，結束位置是10345

　　往期推薦

　　前后端分離，千萬別再搞錯了！

　　哦，原來大廠是這樣發布應用的!

　　迷的一批，上線了新功能，老功能就廢了！

　　網站又被攻擊，我心態崩了