MySQL 權限管理與主從同步搭建：從賬號授權到 GTID 複制的工程化實踐

MySQL 權限管理與主從同步搭建：從賬號授權到 GTID 複制的工程化實踐

本文結合權限模型最小化原則與主從複制標準化流程，給出生產環境可直接落地的操作手冊，覆蓋 5.5+ 版本棄用參數的兼容方案。

一、賬號權限分配基線

核心命令

-- 唯讀業務賬號
GRANT SELECT ON wg_dp.* TO 'wgdp'@'%' IDENTIFIED BY 'strong_pass';

-- 開發/管理賬號 (受限)
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,INDEX ON db.* TO 'dev'@'10.0.%.%' IDENTIFIED BY 'strong_pass';

-- 查看權限
SHOW GRANTS FOR 'wgdp'@'%';

-- 回收權限
REVOKE ALL ON *.* FROM 'wgdp'@'%';

-- 刷新生效 (5.7+ 自動生效，舊版本需執行)
FLUSH PRIVILEGES;

注意：權限變更需目標賬號**重新連接**生效。

權限層級與範圍

• 數據操作：SELECT/INSERT/UPDATE/DELETE (表級)
• 結構變更：ALTER/CREATE/DROP/INDEX (庫/表級)
• 管理權限：GRANT OPTION/SHUTDOWN/PROCESS/SUPER/FILE/RELOAD/REPLICATION * (全局級，極度收緊)

二、主從同步搭建 (一主一從/一主多從)

環境

• Master: 10.209.112.58 (MySQL 5.5)
• Slave: 10.46.169.62 (MySQL 5.5)
• 同步賬號: wgdp_syc

1. Master 配置

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW          -- 推薦 ROW 格式
binlog-do-db = business_db   -- 僅同步業務庫
binlog-ignore-db = mysql     -- 忽略系統庫

2. 創建同步專用賬號

GRANT REPLICATION SLAVE ON *.* TO 'wgdp_syc'@'10.46.169.62' IDENTIFIED BY 'syc_strong_pass';
-- 若需 LOAD TABLE FROM MASTER (極少用)，額外授予 FILE, SELECT
FLUSH PRIVILEGES;

3. 獲取 Master Binlog 位點

FLUSH TABLES WITH READ LOCK;  -- 全局讀鎖，保證一致性位點
SHOW MASTER STATUS;
-- 記錄 File (如 mysql-bin.000001) 與 Position (如 107)
-- 此處可配合 mysqldump/xtrabackup 導出基線數據
UNLOCK TABLES;

4. Slave 配置 (5.5+ 棄用 my.cnf 參數，改用 SQL 配置)

[mysqld]
server-id = 2
relay-log = mysql-relay
# 以下參數 5.5+ 已棄用，勿寫入 my.cnf:
# master-host, master-user, master-password, master-port
replicate-do-db = business_db
replicate-ignore-db = mysql

5. Slave 建立同步連接 (核心步驟)

CHANGE MASTER TO
  MASTER_HOST = '10.209.112.58',
  MASTER_PORT = 3306,
  MASTER_USER = 'wgdp_syc',
  MASTER_PASSWORD = 'syc_strong_pass',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 107;
  -- GTID 模式 (5.6+) 替代位點:
  -- MASTER_AUTO_POSITION = 1;

6. 啟動並驗證

START SLAVE;
SHOW SLAVE STATUS\G;
-- 關鍵指標雙 Yes:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- 監控延遲: Seconds_Behind_Master

三、常見故障處理

四、GTID 複制升級建議 (MySQL 5.6+)

-- Master/Slave 同開啟:
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1

-- Slave 連接簡化為:
CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='...', MASTER_PASSWORD='...', MASTER_AUTO_POSITION=1;

優勢：自動定位位點、故障切換無需手動對齊 Binlog、多源複製原生支持。

五、監控與運維清單

• 關鍵指標納入 Prometheus/Grafana：Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master, Relay_Log_Space。
• 定期 pt-table-checksum + pt-table-sync 校驗主從一致性。
• Binlog 保留策略：expire_logs_days=7 或 binlog_expire_logs_seconds，防磁盤打滿。
• 演練主從切換：STOP SLAVE; RESET SLAVE ALL; CHANGE MASTER TO ...; START SLAVE; 全流程腳本化。

權限最小化是安全底線，標準化複制拓撲是可用性基石。將上述流程固化為 Ansible/Terraform 腳本，實現基礎設施即代碼。