同事刪庫跑路後，我連表名都不能修改了？

碼農參上發表於2021-09-18

原創：碼農參上（微信公眾號ID：CODER_SANJYOU），歡迎分享，轉載請保留出處。

事情是這樣的，前幾天隔壁部門的哥們在生產環境的資料庫上，執行了一下drop命令，好嘛，活生生的刪庫跑路的例子居然真的在我身邊發生了，好在運維同學給力，後來恢復了資料。事後聽說這哥們雖然沒被開除，但也吃了個公司的警告。

再然後，運維那邊回收了所有環境下資料庫的drop命令的許可權，甚至包括了開發環境，本來覺得對我們也沒啥影響，一般我們也沒有啥需要刪表的需求。但是隔了沒幾天，我在重新命名一個表的時候，突然彈出了這樣一個報錯：

仔細看了一眼報錯：

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

什麼情況，重新命名錶和drop命令還有什麼關係？本著懷疑的態度，就想探究一下沒有drop許可權後，對我們的日常資料庫操作都有什麼影響，於是就有了後面一系列在本地進行的測試。

首先需要一個沒有drop許可權的mysql使用者，我們先在本地環境使用root使用者登入mysql，取消使用者hydra的drop許可權。和grant授權命令相對應的，可以使用revoke命令取消對使用者的授權：

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

好了，準備工作做完了，It's show time~

修改表名

前面直接使用navicat來修改表名失敗，那我們再用sql命令來嘗試一下：

上面測試了兩種重新命名錶的命令，無論是ALTER還是RENAME都不能正常使用，看來drop的許可權確實會對修改表名造成影響。至於重新命名失敗的原因，看一下官方文件的說明：

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.

簡單來說就是在重新命名錶時，必須有原始表的ALTER和DROP許可權，以及新表的CREATE和INSERT許可權。

truncate

當我需要清空一張表、順帶把AUTO_INCREMENT的主鍵置為初始值時，突然發現truncate命令也無法執行了：

有了上面的經驗，還是看一下官方文件的說明：

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語言，而delete則是DML語言。相對於delete一行行刪除資料，truncate會刪除表後重新新建表，這一操作相對delete會快很多，尤其是對大表而言。

從分類也可以看出兩者之間的不同，DML(data manipulation language)作為資料操作語言，主要是針對資料進行一些操作，例如常用的增刪改查。而DDL(data definition language)則是資料定義語言，主要應用於定義或改變表的結構等操作，並且這一操作過程是隱性提交的，不能回滾。

在truncate無法使用的情況下，來執行一下delete試試：

雖然說不帶where條件的delete刪除語句很不推薦使用，但是在功能上還是可以執行成功的。那麼再看看另一個問題，表中的自增id重置了嗎？

我們知道，如果執行了truncate的話，那麼自增列id的值會被重置為1。下面看看delete執行後的情況，插入一條資料並查詢：

通過上面的結果，可以看到使用delete清表後，自增列的值還是在原先的基礎上進行自增。如果需要重置這個值的話，需要我們手動在表上執行alter命令修改：

alter table t_orders auto_increment= 1;

drop作用範圍

那麼，是否存在即使在沒有許可權的情況下，也可以執行成功的drop指令？我們對不同物件分別進行測試，首先嚐試對資料庫、表、檢視的drop操作：

drop DATABASE mall;
> 1044 - Access denied for user 'hydra'@'localhost' to database 'mall'
> 時間: 0.005s

drop TABLE t_orders;
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 't_orders'
> 時間: 0s

drop VIEW order_view;
> 1142 - DROP command denied to user 'hydra'@'localhost' for table 'order_view'
> 時間: 0.001s

上面這些命令理所當然的沒有執行成功，但是在嘗試到使用drop刪除儲存過程時，意料之外的結果出現了。在沒有drop許可權的情況下，對儲存過程的drop操作，居然可以執行成功：

翻到官方文件中授權這一章節，看一下這張圖就明白了：

上面的表進行了解釋，drop命令的作用範圍僅僅是資料庫、表以及檢視，而儲存過程的許可權被單獨放在alter routine中了，因此即使沒有drop許可權，我們仍可以用drop命令來刪除儲存過程。

delete後如何恢復資料

通過前面的實驗可以看到，雖然在回收drop許可權後不能使用truncate清空資料表了，但我們仍然可以使用delete語句達到相同的效果，那麼為什麼delete就不害怕刪庫的風險呢？

前面我們提到過，delete語句屬於DDL語言，其實在實際的刪除過程中是一行行的進行刪除的，並且會將每行資料的刪除日誌記錄在日誌中，下面我們就看看如何利用binlog來恢復刪除的資料。

首先要求資料庫開啟binlog，使用下面的語句來查詢是否開啟：

show variables like '%log_bin%';

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

確保開啟了binlog後，我們使用delete來刪除表中的全部資料：

delete from t_orders;

在恢復刪除的資料前，需要先找到存放資料檔案的目錄：

在該目錄下，存在若干名稱為mysql-bin.*****的檔案，我們需要根據刪除操作發生的時間找到臨近的binglog檔案：

找到目標binlog檔案後，這裡先將它拷貝到D:\tmp目錄下，然後到mysql安裝目錄的bin目錄下，執行下面的指令：

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

對引數進行一下說明：

  • base64-output=decode-rows：基於行事件解析成sql語句，並將資料轉換正常的字元
  • database：資料庫名
  • start-datetime：從binlog中第一個等於或晚於該時間戳的事件開始讀取，也就是恢復資料的起始時間
  • stop-datetime：與上面對應的，是恢復資料的結束時間
  • D:\tmp\mysql-bin.000001：恢復資料的日誌檔案
  • mysqllog.sql：恢復資料的輸出檔案

執行完成後，在bin目錄下會生成一個mysqllog.sql的檔案，開啟檔案看一下，可以找到刪除時執行的delete語句：

從語句中可以拿到delete命令執行時每一行資料的值，這樣就可以進行資料的恢復了。如果需要恢復的資料量非常大的話，建議使用指令碼批量將delete語句轉換為insert語句，減輕恢復資料的工作量。

好了，如果你堅持看到這裡，答應我，以後刪庫前，先看一下有沒有開啟binlog好嗎？

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

作者簡介，碼農參上（CODER_SANJYOU），一個熱愛分享的公眾號，有趣、深入、直接，與你聊聊技術。個人微信DrHydra9，歡迎新增好友，進一步交流。

相關文章