TA貢獻1777條經驗 獲得超10個贊
項目上 MySQL 還原 SQL 備份經常會碰到一個錯誤如下，且通常出現在導入視圖、函數、存儲過程、事件等對象時，其根本原因就是因為導入時所用賬號并不具有SUPER 權限，所以無法創建其他賬號的所屬對象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常見場景：1. 還原 RDS 時經常出現，因為 RDS 不提供 SUPER 權限；2. 由開發庫還原到項目現場，賬號權限等有所不同。
處理方式：
1. 在原庫中批量修改對象所有者為導入賬號或修改 SQL SECURITY 為 Invoker；2. 使用 mysqldump 導出備份，然后將 SQL 文件中的對象所有者替換為導入賬號。
二、問題原因我們先來看下為啥會出現這個報錯，那就得說下 MySQL 中一個很特別的權限控制機制，像視圖、函數、存儲過程、觸發器等這些數據對象會存在一個 DEFINER 和一個 SQL SECURITY 的屬性，如下所示：
--視圖定義CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test
--函數定義CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER
--存儲過程定義CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER
--觸發器定義CREATE DEFINER=`root`@`%` trigger t_test
--事件定義CREATE DEFINER=`root`@`%` EVENT `e_test`
DEFINER：對象定義者，在創建對象時可以手動指定用戶，不指定的話默認為當前連接用戶；
SQL SECURITY：指明以誰的權限來執行該對象，有兩個選項，一個為 DEFINER，一個為 INVOKER，默認情況下系統指定為 DEFINER；DEFINER：表示按定義者的權限來執行； INVOKER：表示按調用者的權限來執行。
如果導入賬號具有 SUPER 權限，即使對象的所有者賬號不存在，也可以導入成功，但是在查詢對象時，如果對象的 SQL SECURITY 為 DEFINER，則會報賬號不存在的報錯。ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist
改寫內容上述這個 DEFINER 問題，個人想到最簡單的解決方式就是 mysqldump 導出時直接摘除掉相關屬性，但是 mysqldump 本身并不提供對應參數，所以比較蛋疼，無論是原庫走腳本變更或是備份后修改 SQL 文件都不是非常方便，尤其是觸發器的 DEFINER，只能先 DROP 再 CREATE 才可以變更。只能看下是否可以從 mysqldump 源碼中去掉 DEFINER 定義。本次 mysqldump 改寫主要有 2 個目的：1. 摘取備份中視圖、函數、存儲過程、觸發器等對象的 DEFINER 定義；2. 嘗試加上比較簡單的備份進度顯示（原生 mysqldump 的 verbose 參數不是非常清晰，想要實現 navicate 備份時的那種行數顯示）。
改寫好處：1. 可以避免還原時遇到 DEFINER 報錯相關問題；2. 根據輸出信息知道備份是否正常進行，防止備份中遇到元數據鎖無法獲取然后一直卡住的情況。
舉報