[[424680]]

事情是這樣的，前幾天隔壁部門的哥們?cè)谏a(chǎn)環(huán)境的數(shù)據(jù)庫(kù)上，執(zhí)行了一下drop命令，好嘛，活生生的刪庫(kù)跑路的例子居然真的在我身邊發(fā)生了，好在運(yùn)維同學(xué)給力，后來(lái)恢復(fù)了數(shù)據(jù)。事后聽說(shuō)這哥們雖然沒被開除，但也吃了個(gè)公司的警告。

再然后，運(yùn)維那邊回收了所有環(huán)境下數(shù)據(jù)庫(kù)的drop命令的權(quán)限，甚至包括了開發(fā)環(huán)境，本來(lái)覺得對(duì)我們也沒啥影響，一般我們也沒有啥需要?jiǎng)h表的需求。但是隔了沒幾天，我在重命名一個(gè)表的時(shí)候，突然彈出了這樣一個(gè)報(bào)錯(cuò)：

仔細(xì)看了一眼報(bào)錯(cuò)：

1142 - DROP command denied to user 'hydra'@'localhost' for table 't_orders' 

什么情況，重命名表和drop命令還有什么關(guān)系?本著懷疑的態(tài)度，就想探究一下沒有drop權(quán)限后，對(duì)我們的日常數(shù)據(jù)庫(kù)操作都有什么影響，于是就有了后面一系列在本地進(jìn)行的測(cè)試。

首先需要一個(gè)沒有drop權(quán)限的mysql用戶，我們先在本地環(huán)境使用root用戶登錄mysql，取消用戶hydra的drop權(quán)限。和grant授權(quán)命令相對(duì)應(yīng)的，可以使用revoke命令取消對(duì)用戶的授權(quán)：

revoke drop on *.* from hydra@'localhost'; 

好了，準(zhǔn)備工作做完了，It's show time~

修改表名

前面直接使用navicat來(lái)修改表名失敗，那我們?cè)儆胹ql命令來(lái)嘗試一下：

上面測(cè)試了兩種重命名表的命令，無(wú)論是ALTER還是RENAME都不能正常使用，看來(lái)drop的權(quán)限確實(shí)會(huì)對(duì)修改表名造成影響。至于重命名失敗的原因，看一下官方文檔的說(shuō)明：

RENAME TABLE renames one or more tables. You must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.

簡(jiǎn)單來(lái)說(shuō)就是在重命名表時(shí)，必須有原始表的ALTER和DROP權(quán)限，以及新表的CREATE和INSERT權(quán)限。

truncate

當(dāng)我需要清空一張表、順帶把AUTO_INCREMENT的主鍵置為初始值時(shí)，突然發(fā)現(xiàn)truncate命令也無(wú)法執(zhí)行了：

有了上面的經(jīng)驗(yàn)，還是看一下官方文檔的說(shuō)明：

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

文檔給出的解釋是盡管truncate和delete的功能很像，但是truncate被歸類為DDL語(yǔ)言，而delete則是DML語(yǔ)言。相對(duì)于delete一行行刪除數(shù)據(jù)，truncate會(huì)刪除表后重新新建表，這一操作相對(duì)delete會(huì)快很多，尤其是對(duì)大表而言。

從分類也可以看出兩者之間的不同，DML(data manipulation language)作為數(shù)據(jù)操作語(yǔ)言，主要是針對(duì)數(shù)據(jù)進(jìn)行一些操作，例如常用的增刪改查。而DDL(data definition language)則是數(shù)據(jù)定義語(yǔ)言，主要應(yīng)用于定義或改變表的結(jié)構(gòu)等操作，并且這一操作過(guò)程是隱性提交的，不能回滾。

在truncate無(wú)法使用的情況下，來(lái)執(zhí)行一下delete試試：

雖然說(shuō)不帶where條件的delete刪除語(yǔ)句很不推薦使用，但是在功能上還是可以執(zhí)行成功的。那么再看看另一個(gè)問題，表中的自增id重置了嗎?

我們知道，如果執(zhí)行了truncate的話，那么自增列id的值會(huì)被重置為1。下面看看delete執(zhí)行后的情況，插入一條數(shù)據(jù)并查詢：

通過(guò)上面的結(jié)果，可以看到使用delete清表后，自增列的值還是在原先的基礎(chǔ)上進(jìn)行自增。如果需要重置這個(gè)值的話，需要我們手動(dòng)在表上執(zhí)行alter命令修改：

alter table t_orders auto_increment= 1; 

drop作用范圍

那么，是否存在即使在沒有權(quán)限的情況下，也可以執(zhí)行成功的drop指令?我們對(duì)不同對(duì)象分別進(jìn)行測(cè)試，首先嘗試對(duì)數(shù)據(jù)庫(kù)、表、視圖的drop操作：

drop DATABASE mall; 
> 1044 - Access denied for user 'hydra'@'localhost' to database 'mall' 
> 時(shí)間: 0.005s 
 
drop TABLE t_orders; 
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 't_orders' 
> 時(shí)間: 0s 
 
drop VIEW order_view; 
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 'order_view' 
> 時(shí)間: 0.001s 

上面這些命令理所當(dāng)然的沒有執(zhí)行成功，但是在嘗試到使用drop刪除存儲(chǔ)過(guò)程時(shí)，意料之外的結(jié)果出現(xiàn)了。在沒有drop權(quán)限的情況下，對(duì)存儲(chǔ)過(guò)程的drop操作，居然可以執(zhí)行成功：

翻到官方文檔中授權(quán)這一章節(jié)，看一下這張圖就明白了：

上面的表進(jìn)行了解釋，drop命令的作用范圍僅僅是數(shù)據(jù)庫(kù)、表以及視圖，而存儲(chǔ)過(guò)程的權(quán)限被單獨(dú)放在alter routine中了，因此即使沒有drop權(quán)限，我們?nèi)钥梢杂胐rop命令來(lái)刪除存儲(chǔ)過(guò)程。

delete后如何恢復(fù)數(shù)據(jù)

通過(guò)前面的實(shí)驗(yàn)可以看到，雖然在回收drop權(quán)限后不能使用truncate清空數(shù)據(jù)表了，但我們?nèi)匀豢梢允褂胐elete語(yǔ)句達(dá)到相同的效果，那么為什么delete就不害怕刪庫(kù)的風(fēng)險(xiǎn)呢?

前面我們提到過(guò)，delete語(yǔ)句屬于DDL語(yǔ)言，其實(shí)在實(shí)際的刪除過(guò)程中是一行行的進(jìn)行刪除的，并且會(huì)將每行數(shù)據(jù)的刪除日志記錄在日志中，下面我們就看看如何利用binlog來(lái)恢復(fù)刪除的數(shù)據(jù)。

首先要求數(shù)據(jù)庫(kù)開啟binlog，使用下面的語(yǔ)句來(lái)查詢是否開啟：

show variables like '%log_bin%'; 

在值為ON的情況下，表示開啟了binglog：

確保開啟了binlog后，我們使用delete來(lái)刪除表中的全部數(shù)據(jù)：

delete from t_orders; 

在恢復(fù)刪除的數(shù)據(jù)前，需要先找到存放數(shù)據(jù)文件的目錄：

在該目錄下，存在若干名稱為mysql-bin.*****的文件，我們需要根據(jù)刪除操作發(fā)生的時(shí)間找到臨近的binglog文件：

找到目標(biāo)binlog文件后，這里先將它拷貝到D:\tmp目錄下，然后到mysql安裝目錄的bin目錄下，執(zhí)行下面的指令：

mysqlbinlog --base64-output=decode-rows -v  
  --database=mall  
  --start-datetime="2021-09-17 20:50:00"  
  --stop-datetime="2021-09-17 21:30:00"  
  D:\tmp\mysql-bin.000001 > mysqllog.sql 

對(duì)參數(shù)進(jìn)行一下說(shuō)明：

• base64-output=decode-rows：基于行事件解析成sql語(yǔ)句，并將數(shù)據(jù)轉(zhuǎn)換正常的字符
• database：數(shù)據(jù)庫(kù)名
• start-datetime：從binlog中第一個(gè)等于或晚于該時(shí)間戳的事件開始讀取，也就是恢復(fù)數(shù)據(jù)的起始時(shí)間
• stop-datetime：與上面對(duì)應(yīng)的，是恢復(fù)數(shù)據(jù)的結(jié)束時(shí)間
• D:\tmp\mysql-bin.000001：恢復(fù)數(shù)據(jù)的日志文件
• mysqllog.sql：恢復(fù)數(shù)據(jù)的輸出文件

執(zhí)行完成后，在bin目錄下會(huì)生成一個(gè)mysqllog.sql的文件，打開文件看一下，可以找到刪除時(shí)執(zhí)行的delete語(yǔ)句：

從語(yǔ)句中可以拿到delete命令執(zhí)行時(shí)每一行數(shù)據(jù)的值，這樣就可以進(jìn)行數(shù)據(jù)的恢復(fù)了。如果需要恢復(fù)的數(shù)據(jù)量非常大的話，建議使用腳本批量將delete語(yǔ)句轉(zhuǎn)換為insert語(yǔ)句，減輕恢復(fù)數(shù)據(jù)的工作量。

好了，如果你堅(jiān)持看到這里，答應(yīng)我，以后刪庫(kù)前，先看一下有沒有開啟binlog好嗎?

官方文檔：https://dev.mysql.com/doc/refman/5.7/en

【編輯推薦】