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