背景
排查原因
錯誤提示一的原因
- definer
- invoker
- 錯誤提示二的原因
  - log_bin_trust_function_creators
解決方案
- 錯誤提示一的方案
  - 方案一
  - 方案二
- 錯誤提示二的方案
總結

背景

在AWS RDS環境下，使用mysqldump備份了一個MySQL資料庫資料庫，然後想把它用mysql的命令還原到另外一個資料庫下面，結果在還原的過程中遇到的下面的錯誤提示信息，此時的MySQL實例是沒有開啟binlog。

ERROR 1227 (42000) at line 1163: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

如果在開啟了binlog的情況下，把mysqldump命令備份的SQL文件導入到新的schema下面，則會出現如下的錯誤提示：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

下面研究了一下出現上面兩個錯誤的原因分別是什麼。

排查原因

錯誤提示一的原因

先看第一個錯誤是什麼原因導致的

根據第一個錯誤提示信息，我找到了導出來的SQL文件的第1163行，發現這一行的代碼如下所示，在這一行定義了一個trigger觸發器。

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`app_user`@`10.10.%`*/ /*!50003 trigger SEC_RESOURCE_ROLE_DELETE_TS_NN_UPDATE_TRIGGER before update on SEC_RESOURCE_ROLE
            for each row
            begin
                    if not(NEW.DELETE_TS <=> OLD.DELETE_TS) then
                            set NEW.DELETE_TS_NN = if (NEW.DELETE_TS is null, '1000-01-01 00:00:00.000', NEW.DELETE_TS);
                    end if;
            end */;;
DELIMITER ;

根據錯誤提示信息，再加上定位到的SQL語句，我猜測是當前我執行導入操作的用戶，沒有許可權嗎？

為了驗證我的猜測，我把同樣的SQL文件，在本地的MySQL資料庫中，使用root用戶導入了一次，結果成功了。而使用了AWS RDS for MySQL的管理員用戶想RDS中導入卻失敗，確實是這個許可權的問題。

我們都知道AWS RDS中的用戶是沒有root許可權的，但是沒有想到它的許可權是這麼低。RDS中許可權最大的用戶就是這個了，如果這個許可權都不夠的話，那就沒有任何其他用戶可以用了。只能繼續想辦法看下具體是需要什麼樣的許可權才可以執行上面的創建觸發器的語句。

我把上面創建觸發器的SQL語句，稍做修改，發現把裡面的DEFINER選項/*!50017 DEFINER=app_user@10.10.%*/給去掉就可以創建成功。改為如下的語句來執行是OK的：

DELIMITER ;;
/*!50003 CREATE*/ /*!50003 trigger SEC_RESOURCE_ROLE_DELETE_TS_NN_UPDATE_TRIGGER before update on SEC_RESOURCE_ROLE
            for each row
            begin
                    if not(NEW.DELETE_TS <=> OLD.DELETE_TS) then
                            set NEW.DELETE_TS_NN = if (NEW.DELETE_TS is null, '1000-01-01 00:00:00.000', NEW.DELETE_TS);
                    end if;
            end */;;
DELIMITER ;

這說明是definer的語句導致了上面的觸發器不能正常在MySQL版本的RDS中執行。

在MySQL中，創建函數、存儲過程、視圖、事件、觸發器的時候，可以為其指定definer屬性，但是只能指定執行當前創建函數、存儲過程、視圖、事件、觸發器對象DDL語句的用戶才可以，如果要指定為其他用戶作為definer，則需要使用超級用戶才可以。

definer

這裡說明一下definer關鍵字的作用。

definer關鍵字的作用是用來指定當前的函數、存儲過程、視圖、事件、觸發器等資料庫對象是由哪個用戶創建的。這裡在指定的時候可以為某一個資料庫對象指定其他用戶作為definer，但前提是擁有super許可權的用戶才可以在創建資料庫對象的時候指定其他用戶作為definer，非super許可權的用戶，不可以這麼做，只能指定它自己作為definer。如果在創建對象的時候沒有顯示的聲明definer，會用當前執行創建對象DDL語句的用戶來作為默認的definer。

如下是定義了definer的存儲過程p1的示例。它使用了SQL SECURITY DEFINER屬性，並且指定了definer為u3@%這個用戶，存儲過程裡面是對資料庫procedure_test下面的表t1的counter欄位執行加1的一個update操作。也就是說，這個存儲過程p1每被調用一次，資料庫procedure_test下面的表t1的counter欄位的值就會被加1。

use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 1;
END;;
delimiter ;

/*上面的存儲過程，等價於下面的存儲過程*/
use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p1()
-- SQL SECURITY DEFINER /*可以省略該行，不指定的時候，默認就是使用sql security definer*/
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 1;
END;;
delimiter ;

對於上面的存儲過程p1，不管調用這個存儲過程的用戶是否對procedure_test資料庫下面的表t1是否有select、update的許可權(注意：修改一個表，前提是可以查詢表中的數據，查詢出來後才可以修改，所以不僅僅需要update的許可權，還需要select的許可權)，只要這個用戶在procedure_test資料庫下面有EXECUTE的許可權，這個用戶就可以調用procedure_test資料庫下面的p1這個存儲過程。

它是以definer中指定的用戶u3@%來執行這個存儲過程中的命令的。如果definer中定義的用戶u3@%對存儲過程中使用的資料庫對象procedure_test.t1沒有對應的select、update的許可權，那麼這個調用存儲過程p1的用戶在以definer用戶u3@%去調用該存儲過程的時候，也會失敗。

invoker

說道了definer屬性，就要提一下invoker屬性。

在定義函數、存儲過程、視圖對象的時候，除了可以指定definer屬性之外，還可以為其指定invoker屬性。invoker屬性的含義是，哪個用戶可以調用這個資料庫對象。

當前一個對象沒有在begin前面顯示的聲明SQL SECURITY DEFINER或SQL SECURITY INVOKER的時候，默認是使用SQL SECURITY DEFINER。當一個對象顯示的聲明了SQL SECURITY INVOKER則會覆蓋definer屬性的定義，真正在執行對應的對象的時候，會按照invoker的許可權去判斷是否可以執行對應的命令。

如下是一個定義了invoker的存儲過程p2。這個存儲過程，和前面不同的是，這裡使用了SQL SECURITY INVOKER屬性。

use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 100;
END;;
delimiter ;

對於上面的存儲過程p2，雖然定義的時候指定了definer為u3@%，但是由於還指定了SQL SECURITY INVOKER，所以在這個存儲過程被調用的時候，會根據invoker的屬性去判斷是否可以成功調用該存儲過程，忽略definer屬性的約束。這個存儲過程p2是否可以調用成功，取決於調用者是否對資料庫procedure_test下面的t1表擁有select、update的許可權。不會判斷調用者對資料庫procedure_test是否有execute許可權。

**注意：**觸發器、事件這兩個對象是沒有invoker屬性，不能為其指定哪些用戶可以調用執行它們，它們的調用執行由MySQL自己決定什麼時候調用，最多只能為其指定definer屬性，標識是哪個用戶創建的觸發器、事件。其他的幾個像：存儲過程、函數、視圖是可以為其指定invoker屬性的，標識哪個用戶可以調用該對象。

錯誤提示二的原因

接下來我們再看第二個錯誤是什麼原因導致的。

根據錯誤二的提示信息，我嘗試使用root用戶在我本地的MySQL中執行導入操作，在開啟binlog的情況下，是可以導入成功的。但是如果使用一個非root用戶，在本地MySQL開啟binlog的情況下，導入確實會出現下面的錯誤提示：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

錯誤提示給出了關鍵的解決方式：那就通過參數
log_bin_trust_function_creators的配置可以解決這個問題。

log_bin_trust_function_creators

該參數只有在binlog開啟的情況下才會生效，如果binlog沒有開啟，這個參數不管配置成什麼值，都不會生效，不起任何作用。所以，接下來我們討論的這個參數配置為off或者on的前提是：binlog已經是開啟的狀態。

參數
log_bin_trust_function_creators的取值範圍是0或1，對應著off或者on，其默認值為off。它是用來控制MySQL是否信任函數、存儲過程、觸發器的創建者所創建的這些資料庫對象。

off，表示不信任。在創建函數、存儲過程、觸發器之前，MySQL會驗證這些對象是否可以被創建。
on，表示信任。在創建函數、存儲過程、觸發器之前，MySQL不會去驗證這些對象是否可以被創建，待創建的對象只要語法上沒有問題，就可以創建成功。

那麼參數
log_bin_trust_function_creators到底是對待創建的資料庫對象(function、procedure、trigger)做什麼驗證呢？

如果待創建的資料庫對象，在定義過程中引用了非確定性因素的函數事件，比如在SQL語句中引用了rand()、uuid()、now()等MySQL內置的函數，就認為這個待創建的資料庫對象是不安全的。因為每次調用或執行這個資料庫對象後，它所產生的結果是不確定的。此時MySQL就認為這個對象是安全的資料庫對象。

如果參數log_bin_trust_function_creators=off的時候，MySQL就會對待創建的資料庫對象進行上面我們描述的檢查和驗證。是安全的對象，則可以創建成功，如果是不安全的對象則不能創建成功(擁有超級許可權的用戶除外，超級許可權的用戶在log_bin_trust_function_creators=off的情況下，即便是非安全的資料庫對象，也可以創建成功)。會拋出一個異常信息如下：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

如果參數log_bin_trust_function_creators=on的時候，MySQL就不會對待創建的資料庫對象進行安全性的檢查，在語法沒有錯誤的前提下，可以直接創建成功。

題外話：為什麼說引用rand()、uuid()、now()等不確定結果函數的資料庫對象就是不安全的呢？

這要從MySQL資料庫binlog和主從複製說起。

我們知道在binlog開啟的情況下，我們對資料庫中數據的任何變更操作都會被記錄在binlog中，從庫在同步主庫的數據的時候，就是讀取主庫中binlog的記錄，然後再從庫在執行一遍，來達到從庫資料庫和主庫數據一致的要求。

但是如果我們調用或執行一些資料庫對象的時候，它們引用了MySQL內置的不確定性的函數如uuid()。那麼binlog中記錄的這個操作的SQL語句在不同的MySQL實例上重放的時候，將會得到不同的結果。

例如一個存儲過程中對某個表的某一行進行了修改，其語句為update t set a=uuid() where id = 1;。那麼在binlog的記錄格式是statement格式的時候，binlog中就會記錄這樣的一個SQL語句，並不是把調用uuid()之後的得到的數據行修改後的內容記錄在binlog中。那麼這樣的binlog在同步到從庫之後，從庫重放binlog中的這樣的記錄時，在從庫上執行的update t set a=uuid() where id = 1;這個SQL語句後的結果得到的uuid()的值，和主庫上的值是相同的可能性幾乎為零。這就導致了從庫上面id=1的數據行，和主庫上面id=1的數據行的內容不一致，進而可能導致主從同步中斷。這就是為什麼引用不確定性內置函數的資料庫對象被視為不安全的原因。

如果在binlog格式是row格式的情況下，就不會出現上面我們描述這樣因為不安全的內置函數引用而導致的主從數據不一致的情況，因為row格式的binlog記錄的是id=1這一行數據修改後的數據內容，這樣的binlog在從庫中重放的時候，就直接把修改後的數據內容應用到從庫上，而不是把在主庫上執行是SQL語句在從庫上重新執行一遍。所以，在binlog開啟並且格式為row的前提下，即便是把參數
log_bin_trust_function_creators=on，表示在創建資料庫對象的時候不做驗證，也不會導致主從數據不一致的問題。

如果開啟了binlog之後並且搭建了主從，同時binlog的格式為statement，參數
log_bin_trust_function_creators改為on之後，表示不會對函數、存儲過程、觸發器等對象在創建的時候，進行驗證它們的安全性。就很有可能導致主從數據不一致。

當然，如果沒有開啟binlog，或者開啟了binlog但沒有配置主從同步，只是一個單實例的MySQL服務，設置這個參數為on，不會導致主從不同步。但是如果開啟了binlog，並且格式為statement，在使用binlog做數據恢復還原的時候，也是有可能導致還原後的數據和原先的數據不一致。所以，只要開啟了binlog，格式請一定設置為row格式。

對於binlog開啟與否，以及binlog的格式，主從鏈路的狀態和
log_bin_trust_function_creators參數之間的影響關係如下：

序號	binlog狀態	binlog格式	主從鏈路狀態	log_bin_trust_function_creators狀態	對主從或使用binlog恢複數的影響
1	ON	row	ON	ON/OFF都可以	無
2	ON	row	OFF	ON/OFF都可以	無
3	ON	mixed	ON	ON/OFF都可以	無
4	ON	mixed	OFF	ON/OFF都可以	無
5	ON	statement	ON	ON	有
6	ON	statement	OFF	ON/OFF都可以	無
7	OFF	N/A	N/A	ON/OFF都可以	無

解決方案

錯誤提示一的方案

了解了definer和revoker的作用之後，根據MySQL給拋出來的錯誤信息，結合AWS RDS版本的MySQL用戶是沒有超級用戶的許可權的，我們就可以理解為什麼使用mysqldump導出來的創建觸發器的語句會帶有definer，並且也能理解為什麼導入到新的schema下面就不能導入的原因了。

理解了為什麼，我們就可以針對性的進行解決這個問題了。目前的解決方案有以下幾種：

方案一

因為AWS RDS版本的MySQL用戶沒有超級許可權，不能指定definer為其他用戶，所以我們可以把這個definer定義給刪除掉。這樣在導入的時候，就會默認使用當前執行導入的用戶來作為該觸發器的definer。

如果你導出來的SQL文件比較小，可以直接雙擊打開編輯或者使用vi命令來編輯，找到對應的definer定義，將其刪除。

如果你導出來的SQL文件比較大，雙擊打開或者使用vi命令編輯基本不可能，那麼就使用如下的sed語句可以將函數、存儲過程、視圖、事件、觸發器中的definer屬性給刪除掉。在使用sed命令之前，請先備份你的原來的SQL文件，避免刪除失敗後，不能恢復。

cp your_mysqldump_file.sql your_mysqldump_file.sql.bak

sed -i -e 's/DEFINER=`app_user`@`10.10.\%`//g' your_mysqldump_file.sql

方案二

由於AWS RDS版本的MySQL提供的用戶，沒有超級許可權，所以它不能創建入觸發器的時候，指定其他用戶作為definer，那麼我們可以將dump出來的SQL文件中的definer改為當前RDS提供的用戶。同樣需要備份源SQL文件，給自己留個後悔葯吃。

cp your_mysqldump_file.sql your_mysqldump_file.sql.bak

sed -i -e 's/DEFINER=`app_user`@`10.10.\%`/DEFINER=`your_rds_admin_user`@`\%`/g' your_mysqldump_file.sql

這樣修改後的SQL文件，就可以使用RDS提供的用戶導入到新的schema下面了。

這裡在替換為RDS admin的用戶的時候，需要注意，用戶名稱後面是需要跟上%還是跟上具體的某一個網段，則需要根據你的mysql.user表中定義的RDS admin用戶對應的host列中的值是什麼。一般情況下面，RDS提供的admin用戶他們的網段都是%，而不是具體的某一個網段。

錯誤提示二的方案

目前我的RDS版本的MySQL是開啟了binlog，並且binlog的格式是row格式，但是我沒有配置主從同步的鏈路。所以，我們要修復前面開始遇到的問題，只需要把參數
log_bin_trust_function_creators由原先默認的off改為on，來開啟信任函數的創建者就可以避免創建觸發器、存儲過程等資料庫對象的驗證了。

修改參數的操作如下：

mysql> set global log_bin_trust_function_creators = on;
Query OK, 0 rows affected (0.00 sec)

修改完成上面的參數後，再重新導入SQL文件，即便是裡面有觸發器、函數的創建，也可以創建成功了。不會對這些將要創建的觸發器、函數、存儲過程進行安全性的檢查了。

但是如果需要這個參數長時間生效，需要在MySQL的參數配置文件中增加這個參數的配置。自己安裝部署的MySQL服務，可以修改my.cnf配置文件，如果是RDS版本的MySQL，則需要修改參數組中的這個參數，找到對應的參數，修改後，保存既可以，如果是多個RDS實例，使用同一個參數組，則需要複製出來一份新的參數組來給當前需要修改參數的RDS來使用。為了讓新的參數組生效，則需要重啟MySQL的RDS服務。