我們存取遠端的 MySQL 伺服器時, 通常為了安全的緣故, 遠端的 MySQL 管理人員不太可能幫我們建立 Host 是 % 的帳號, 又因為負擔得起 HiNet 固定制的人畢竟是少數, 所以在我們多數人是使用是非固定制的 HiNet 的情況下, MySQL 的管理人員會建議我們去跟 HiNet 申請固定IP (固1) ; 不過 HiNet 的服務條款中, 也載明了 HiNet 會不定期進行電路優化作業, 導致固定 IP 三不五時就會變動, 這時候對於我們存取遠端的 MySQL 伺服器就會造成麻煩。 有時候 MySQL 的管理人員會抱怨帳號要重新建立並授權這種是很麻煩, 如果我們知道 MySQL 伺服器提供的方法, 來協助 MySQL 管理人員處理自己的固定 IP 變動, 會比較容易讓 MySQL 的管理人員心平氣和地幫我們進行變更, 而不至於遭致 MySQL 管理人員的怨言以對。
RENAME USER 官方工具
如果 MySQL 管理人員有保留帳號建立與歷次授權的 SQL 檔案, 那麼透過刪除舊帳號, 並將 SQL 檔案中的舊 IP 位址替換成新 IP 位址之後再執行, 就可以將舊帳號更換為新帳號, 只是這樣會增加 MySQL 管理人員的工作, 多做幾次肯定會收到抱怨。
MySQL 提供的 RENAME 工具, 可以讓我們將帳號使用的舊 IP 更換為新 IP , 同時也將帳號的授權一併做更動:
RENAME USER 'my_account'@'old_IP' TO 'my_account'@'new_IP'
額外需要處理的部分是 stored programs (fuctions, procedures, triggers) 與 views 的 definer 定義並不會隨著憶起被修正,因此若是執行時使用 definer security context 執行, 會出現錯誤訊息。
修正 Stored Procedures 的 definer
我們可以使用下列 SQL 敘述檢視以舊帳號為 definer 的預儲程序:
SHOW PROCEDURE STATUS WHERE Db = 'my_DB';
SELECT * FROM mysql.proc p WHERE p.Definer = 'my_account@old_IP';
確認無誤之後,可以使用下列 SQL 敘述批次更新預儲程序的 definer 為新帳號:
SET SQL_SAFE_UPDATES = 0;
UPDATE mysql.proc p SET p.Definer = 'my_account@new_IP' WHERE p.Definer = 'my_account@old_IP';
SET SQL_SAFE_UPDATES = 1;
修正 Triggers 的 definer
很不幸地, 目前並沒有簡單的方式來變更激發程序的 definer , 只能透過重新建立的方式來變更 definer 。
我們可以使用下列 SQL 敘述, 檢視以舊帳號為 definer 的激發程序:
USE my_DB;
SHOW TRIGGERS WHERE Definer = 'my_account@old_IP';
確認無誤之後, 可以使用下列 SQL 敘述產生重新建立激發程序的 SQL 敘述,再登入新帳號並使用 MySQL 提供的 Dynamic SQL 執行即可:
SELECT CONCAT('DROP TRIGGER ', t.trigger_name, ';', ' CREATE TRIGGER ', t.TRIGGER_NAME, ' AFTER ', t.EVENT_MANIPULATION, ' ON ', t.EVENT_OBJECT_SCHEMA, '.', t.EVENT_OBJECT_TABLE, ' FOR EACH ROW ', t.ACTION_STATEMENT, ';') AS sqlCommand FROM information_schema.triggers t WHERE t.EVENT_OBJECT_SCHEMA = 'my_DB' AND t.DEFINER = 'my_account@old_IP';
修正 Views 的 definer
同樣很不幸地, 目前也沒有簡單的方式, 來變更檢視的 definer , 只能透過重新建立的方式來變更 definer 。
我們可以使用下列 SQL 敘述, 檢視資料庫中所有的檢視表:
SHOW FULL TABLES IN my_DB WHERE TABLE_TYPE LIKE 'VIEW';
SELECT * FROM information_schema.TABLES WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'my_DB';
確認無誤之後, 可以使用下列 SQL 敘述產生重新建立檢視表的 SQL 敘述, 再登入新帳號並使用 MySQL 提供的 Dynamic SQL 執行即可:
SELECT CONCAT("ALTER DEFINER='my_account'@'new_IP' VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema = 'my_DB';