MySQL 用戶權限導出遷移腳本實戰分享

MySQL 用戶權限導出遷移腳本實戰分享

在 MySQL 資料庫遷移專案中，除了資料本身，用戶帳號與權限的遷移同樣關鍵。本文提供一個實用的 Shell 腳本，可從 mysql.user 系統表提取所有用戶權限並生成標準 SQL 語句，支援在目標伺服器直接執行還原，大幅簡化遷移作業。

一、權限導出腳本核心邏輯

腳本採用雙層 MySQL 查詢巢狀結構：

1. 第一層：查詢 mysql.user 表，動態生成 SHOW GRANTS FOR 'user'@'host' 語句
2. 第二層：執行上述生成的 SHOW GRANTS 語句，獲取實際授權語句
3. 後處理：使用 sed 格式化輸出，添加分號結尾、註釋用戶資訊

二、完整腳本內容 (exp_grant.sh)

#!/bin/bash
# Function: export user privileges
# Author: Leshami
# Blog: https://blog.csdn.net/leshami

pwd=123456  # 建議改為環境變數或配置檔讀取

expgrants() {
  mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
  ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}

expgrants > ./grants.sql

關鍵參數說明：

• -B：批次模式輸出（表格格式，無框線）
• -N：不輸出欄位名稱
• sed 處理邏輯：為 GRANT 語句加分號、將 "Grants for..." 行轉為註釋

三、執行腳本生成權限 SQL

[root@HKBO ~]# ./exp_grant.sh
[root@HKBO ~]# head grants.sql
-- Grants for root@127.0.0.1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*EB3EA446C759C9DA93F84FCB56430DBEF051A9DD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `CNBO0815`.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;

-- Grants for root@172.16.10.%
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.10.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

-- Grants for CNBO@192.168.1.%
GRANT USAGE ON *.* TO 'CNBO'@'192.168.1.%' IDENTIFIED BY PASSWORD '*ABD91BAD4A3448428563952E281015B237310EA8';
...........................

四、目標伺服器執行還原

# 直接導入執行
mysql -uname -ppwd < grants.sql

五、遷移注意事項與優化建議

進階過濾技巧：

• 僅遷移非 root 用戶：在腳本 SQL 中添加 WHERE user <> 'root'
• 排除系統帳號：添加 WHERE user NOT IN ('root','mysql.sys','mysql.session')
• 指定資料庫權限：修改查詢條件限制特定資料庫授權

六、MySQL 權限管理補充知識

常用授權語法：

-- 全庫全表最高權限（含授權權限）
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 特定資料庫權限
GRANT SELECT, INSERT, UPDATE ON dbname.* TO 'user'@'%' IDENTIFIED BY 'password';

-- 特定表權限（需逐表授權，MySQL 不支援單條語句多表）
GRANT ALL ON dbname.table1 TO 'user'@'%' IDENTIFIED BY 'password';
GRANT ALL ON dbname.table2 TO 'user'@'%' IDENTIFIED BY 'password';

-- 生效權限
FLUSH PRIVILEGES;

本腳本經過多個生產環境遷移專案驗證，可靠性較高。建議在正式遷移前於測試環境完整演練，確認權限還原無誤後再投入生產使用。