改寫 mysqldump 解決 DEFINER 問題

作者：xuty

本文來源：原創投稿 *愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。

---

一、背景

項目上 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 (&39;@&39;) does not exist

三、改寫內容

上述這個 DEFINER 問題，個人想到最簡單的解決方式就是 mysqldump 導出時直接摘除掉相關屬性，但是 mysqldump 本身並不提供對應參數，所以比較蛋疼，無論是原庫走腳本變更或是備份後修改 SQL 文件都不是非常方便，尤其是觸發器的 DEFINER，只能先 DROP 再 CREATE 才可以變更。只能看下是否可以從 mysqldump 源碼中去掉 DEFINER 定義。

本次 mysqldump 改寫主要有 2 個目的：

1. 摘取備份中視圖、函數、存儲過程、觸發器等對象的 DEFINER 定義；

2. 嘗試加上比較簡單的備份進度顯示（原生 mysqldump 的 verbose 參數不是非常清晰，想要實現 navicate 備份時的那種行數顯示）。

改寫好處：

1. 可以避免還原時遇到 DEFINER 報錯相關問題；

2. 根據輸出信息知道備份是否正常進行，防止備份中遇到元數據鎖無法獲取然後一直卡住的情況。

四、版本選擇

改之前需要先選個 MySQL 版本，對比了下幾個 MySQL 5.7.27 以上的版本，發現其 mysqldump.c 的源碼是一樣的，而從 MySQL 8.0 之後則完全變了。

因此選擇 MySQL 5.7.27 版本的 mysqldump 源文件來改寫，所以本次改寫只適用於MySQL 5.7 版本 。

五、備份順序

如下是 mysqldump 備份對象時的順序，值得注意的是 mysqldump 在備份表的時候會連帶視圖也一起備份，但是只是臨時視圖（常量別名替換實際列），主要是為了防止後續其他視圖、函數與存儲過程中用到該視圖，所以通過臨時視圖來解決依賴問題，在最後才真正備份視圖，這招非常精妙！

六、源碼改動

6.1 列印函數

因為要在會話窗口下模擬 verbose 一樣輸出備份信息，所以就照搬了原生的 verbose_msg() 函數，新建了個 print_dump_msg() 函數用於備份信息輸出。

6.2 行數顯示

dump_table 函數中增加備份行數輸出，本身源碼就是一行一行循環讀取的，所以非常方便列印輸出。

6.3 DEFINER 摘除

trigger 與 event 的 DEFINER 是在 dump_trigger |dump_events_for_db->cover_definer_clause 函數中通過 my_case_str 方法摘走的。

routines 的 DEFINER 是在 dump_routines_for_db 函數中通過 fprint 方法重新拼接定義摘走的。

views 的 DEFINER 是在 get_view_structure 函數中通過 my_case_str 方法摘走的。

七、改寫效果

1. 備份過程中列印具體信息

2. SQL 備份中摘除了 DEFINER 屬性

八、性能測試

通過 sysbench 造測試數據後，分別使用改寫後的 mysqldump 與原生的 mysqldump 進行多次遠程備份，查看平均耗時。

這裡選擇遠程備份測試是因為很多實際使用場景也是遠程備份，而且遠程備份更能體現出頻繁列印信息對備份性能的影響。

實際壓測發現，如果每一行都列印一次，會嚴重影響性能，所以改成 1W 行列印一次，影響會比較小。

另外踩了個坑，一開始測試的時候是使用開啟 debug 編譯的 mysqldump，所以會執行很多多餘的 debug 代碼，備份速度非常慢，關閉 debug 重新編譯後，速度就比較正常了。

通過測試，如果是 1W 行列印一次，對於備份的性能損耗是比較低的，在可接受範圍之內。

九、小結

本次通過直接修改 mysqldump 源碼的方式，比較好的解決了 DEFINER 問題，另外將改寫後的 mysqldump 源碼拿到 Windows 環境下編譯後是可以直接使用的，親測有效，不過 Windows 編譯環境搭建繁瑣多了。

其實也可以通過類似的方式來實現異構資料庫的遷移，比如從 MySQL 遷移到其他資料庫時，可以通過修改 mysqldump 源碼來完成欄位類型映射與語法轉換，也是一種思路。