MySQL 導出用戶權限遷移腳本：grants.sql 自動生成與跨實例同步

遷移場景

MySQL 升級、遷移、災備演練時，需將源端用戶與權限完整複製到目標端。手工重建極易遺漏，自動化腳本為標準做法。

自動生成腳本

#!/bin/bash
# exp_grants.sh
# 功能：導出所有用戶的 GRANT 語句至 grants.sql

PWD="your_root_password"  # 建議用 .my.cnf 或 mysql_config_editor 避免明文

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

expgrants > ./grants.sql

腳本解析

1. 第一個 mysql：查詢 mysql.user 產生 SHOW GRANTS FOR 'user'@'host'; 列表
2. 管道餵給第二個 mysql：執行每條 SHOW GRANTS 獲得原始輸出
3. sed 後處理：
   • s/\(GRANT .*\)/\1;/：GRANT 行補分號
   • s/^\(Grants for .*\)/-- \1 /：註釋標題行
   • /--/{x;p;x;}：在註釋行前插入空行，提升可讀性

輸出範例

-- Grants for root@127.0.0.1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*EB3EA...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;

-- Grants for app_user@192.168.1.%
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app_user'@'192.168.1.%' IDENTIFIED BY PASSWORD '*ABC123...';

目標端執行

mysql -uroot -p < grants.sql

注意事項與增強

• 目標端非空：已存在同名用戶會報錯或覆蓋，建議先備份 mysql.user 或用 pt-show-grants --separate --revoke 產生可回滾腳本
• 過濾 root：遷移業務帳號時加 WHERE user <> 'root'
• 密碼雜湊相容性：MySQL 8.0 使用 caching_sha2_password，5.7 用 mysql_native_password，跨大版本遷移需確認插件相容或重設密碼
• 全域權限 vs 庫級權限：腳本同時導出兩者，目標端無對應庫會報錯，預先建庫或修改腳本
• 更強工具：pt-show-grants (Percona Toolkit) 支援 --revoke、--flush、--only-users 等進階選項

小結

三行管道命令搞定權限導出：SELECT CONCAT('SHOW GRANTS...') | mysql | sed。生產遷移建議用 pt-show-grants 並配合 --revoke 產生冪等腳本。核心原則：自動化、可審計、可回滾、跨版本相容。