您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 主從復制實戰：機房遷移零停機遷移全流程（異構環境、版本升級、權限同步、數據校驗）

編輯：MySQL 綜合教學

項目背景：舊機房下線、OS 升級（SUSE→TLinux）、MySQL 5.5→5.6 跨版本遷移

核心約束：業務零感知、數據零丟失、回滾可控。採用「主從同步追平 → 業務切換 → 校驗收口」經典路徑。

環境對比與兼容性評估

維度	源端 (Old)	目標端 (New)	風險點
IP	A (已回收)	B	-
OS	SUSE 11 x64	TLinux 1.2 (CentOS 6.2 兼容)	glibc、系統參數差異
MySQL	5.5.3	5.6.25	GTID、優化器、默認值變更
啟動方式	`mysqld_safe &`	`systemctl/Init.d`	進程管理差異
配置文件	無標準路徑	/etc/my.cnf	參數遷移需逐項核對

結論：5.5→5.6 兼容性良好，但需關注 sql_mode、innodb_strict_mode、datetime 精度等變更。

新環境部署：源碼編譯標準化安裝


# 依賴
yum install -y cmake gcc gcc-c++ ncurses-devel bison openssl-devel

# 編譯參數（生產標準化）
cmake . \
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
  -DMYSQL_DATADIR=/data/dbdata \
  -DSYSCONFDIR=/etc \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DWITH_SSL=system -DWITH_ZLIB=system \
  -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci \
  -DENABLED_LOCAL_INFILE=1 -DWITH_DEBUG=0

make -j$(nproc) && make install

# 初始化
groupadd -r mysql; useradd -r -g mysql -s /sbin/nologin mysql
mkdir -p /data/dbdata; chown mysql:mysql /data/dbdata
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/dbdata

# 服務註冊
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld; chkconfig mysqld on

核心配置差異化管理


# 新機器 /etc/my.cnf 關鍵同步相關配置
[mysqld]
server-id = 20151109              # 全局唯一，避開舊機器 ID
log-bin = /data/dbdata/binlog/mysql-bin
binlog_format = MIXED
binlog_cache_size = 4M
max_binlog_size = 1G
expire_logs_days = 7
log-slave-updates = ON            # 關鍵：從庫收到的變更寫入自身 Binlog
sync_binlog = 1                   # 關鍵：每次提交刷盤
gtid_mode = ON                    # 5.6+ 推薦開啟 GTID
enforce_gtid_consistency = ON

舊機器動態開啟 Binlog（無配置文件場景）：


SET GLOBAL server_id=2;
SET GLOBAL binlog_format='MIXED';
SET SQL_LOG_BIN=1;

數據遷移：mysqldump + Single-Transaction + Master-Data


# 源端導出（InnoDB 表單事務一致性視圖，僅短暫全局讀鎖）
mysqldump -uroot -p \
  --default-character-set=utf8 \
  -Y -B --set-charset \
  --single-transaction --master-data=1 \
  --databases target_db > /data/backup/target_db_$(date +%F).sql

# 關鍵參數解析
# --master-data=1: 輸出 CHANGE MASTER TO 命令（不註釋），自動記錄 Binlog 位置
# --single-transaction: InnoDB 可重複讀隔離級別下一致性快照，不阻塞寫入
# -Y: 導出表空間定義（PXC/傳輸表空間需求）

主從建立與同步追平

目標端建庫：CREATE DATABASE target_db DEFAULT CHARSET utf8mb4;
目標端 STOP SLAVE; 導入全量 SQL。
解析備份文件中的 CHANGE MASTER 位置：grep CHANGE backup.sql。
目標端執行 CHANGE MASTER TO ...（使用源端 IP、同步賬號、解析出的 File/Position）。
START SLAVE; 監控 Seconds_Behind_Master 降為 0。

Binlog 運維命令速查（遷移期高頻使用）

操作	命令	場景
查看 Binlog 列表	`SHOW MASTER LOGS;`	確認文件編號、大小
查看主庫當前位置	`SHOW MASTER STATUS;`	獲取最新 File/Position
刷新生成新 Binlog	`FLUSH LOGS;`	切割日誌、定位追蹤
重置 Binlog（慎用）	`RESET MASTER;`	初始化新環境、清理舊日誌
按時間清理	`PURGE BINARY LOGS BEFORE '2024-01-01';`	定期清理釋放磁盤
按文件清理	`PURGE BINARY LOGS TO 'mysql-bin.000010';`	保留指定文件及之後

存儲過程與權限遷移（易遺漏環節）

存儲過程/函數


# 單獨導出（避免重複導出表結構）
mysqldump -uroot -p -n -d -t -R target_db > routines.sql
# 目標端導入
mysql target_db < routines.sql

賬號權限


# 源端生成授權腳本
mysql -uroot -p -e "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.session');" -s -N > grants.sql
# 手動執行 grants.sql 獲取完整授權語句，再在目標端執行（注意過濾不存在的庫/表）

數據校驗三層防線

層級	工具/方法	特點	建議頻率
L1 快速	`SHOW SLAVE STATUS` + 表行數抽樣	秒級、低開銷	同步追平期間每分鐘
L2 精準	`pt-table-checksum` (Percona Toolkit)	Chunk 分塊、主從對比、不阻塞業務	切換前全量、日常每日
L3 修復	`pt-table-sync --execute`	生成 REPLACE/INSERT 修復語句，謹慎執行	發現不一致時按需

常見坑點與應對

問題	根因	解決方案
MyISAM 鎖表導出阻塞業務	源端存在 MyISAM 表	遷移前全量轉 InnoDB、或架構從庫專職備份
pt-table-sync 修復後自增 ID 變化	REPLACE INTO 導致自增重算	業務禁用自增 ID 作為業務主鍵；修復前備份、驗證業務影響
版本升級導致 sql_mode 報錯	5.6 默認 `STRICT_TRANS_TABLES`	統一設定 `sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES`，測試驗證
字符集不一致導致亂碼	源 latin1、目標 utf8mb4	導出指定 `--default-character-set=utf8`，目標庫建表指定 utf8mb4