MySQL 主從複製架構部署與故障排查全指南：配置、原理、常見錯誤碼 1146 解決

前言

MySQL 異步複製（Replication）是高可用、讀寫分離、備份卸載的基石。本文系統講解單向異步複製原理、三線程模型、部署步驟、狀態監控、典型故障（Last_SQL_Errno: 1146）根因與修復，並給出生產級運行維護清單。

一、複製原理與三線程模型

• Master Binlog Dump Thread：讀取 Binlog 發送給 Slave
• Slave I/O Thread：連接 Master，接收 Binlog 寫入本地 Relay Log
• Slave SQL Thread：讀取 Relay Log 重放事務，更新數據

關鍵文件：master.info（記錄 Master 連接信息、Binlog 位點）、relay-log.info（記錄 Relay Log 執行位點）、relay-bin.xxxxxx、relay-bin.index。

二、部署標準流程（GTID 模式推薦）

2.1 Master 配置

[mysqld]
server_id=101
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=1
expire_logs_days=7
max_binlog_size=1G

2.2 Slave 配置

[mysqld]
server_id=102
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=1
relay_log=relay-bin
relay_log_index=relay-bin.index
read_only=1
super_read_only=1

2.3 建立複製賬號

-- Master 上
CREATE USER 'repl'@'10.0.%.%' IDENTIFIED BY 'ReplPass#2024';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%.%';
FLUSH PRIVILEGES;

2.4 獲取同步起點（GTID 模式無需位點）

-- 非 GTID 時代需 SHOW MASTER STATUS 記 File/Position
-- GTID 模式直接用 AUTO_POSITION

2.5 啟動複製

-- Slave 上
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='10.0.1.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='ReplPass#2024',
  MASTER_AUTO_POSITION=1;  -- GTID 模式
START SLAVE;

2.6 驗證狀態

SHOW SLAVE STATUS\G
-- 關鍵指標
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Retrieved_Gtid_Set: xxxx
Executed_Gtid_Set: xxxx

三、常用管理命令

四、經典故障：Last_SQL_Errno: 1146 Table doesn't exist

4.1 現象

Last_SQL_Error: Error 'Table 'db.tbl' doesn't exist' on query.
Default database: 'db'. Query: 'ALTER TABLE tbl DISCARD TABLESPACE'
-- 或
Query: 'ALTER TABLE tbl IMPORT TABLESPACE'

4.2 根因

Transportable Tablespace（TTS）遷移過程中，Slave SQL Thread 重放 DISCARD TABLESPACE / IMPORT TABLESPACE 時，表定義（.frm/.ibd）在 Slave 上不存在或不匹配。

典型場景：

1. Master 執行 FLUSH TABLES ... FOR EXPORT、拷貝 .ibd/.cfg 到 Slave
2. Slave 執行 CREATE TABLE ...; ALTER TABLE ... DISCARD TABLESPACE（刪除 .ibd）
3. 拷貝 .ibd 到 Slave 數據目錄
4. Slave 執行 ALTER TABLE ... IMPORT TABLESPACE

若步驟 2/3/4 中斷、或 Slave 並行複製導致順序錯亂，SQL Thread 找不到表定義報 1146。

4.3 修復方案（按嚴重程度）

方案 A：補齊表定義 + 重放（輕量）

-- 1. 備份現有 .ibd（若存在）
mv /data/mysql/db/tbl.ibd /data/mysql/db/tbl.ibd.bak

-- 2. 從 Master 獲取建表語句
SHOW CREATE TABLE db.tbl\G

-- 3. Slave 重建表結構
CREATE TABLE `tbl` ( ... ) ENGINE=InnoDB;

-- 4. 執行 DISCARD TABLESPACE（清理剛建的 .ibd）
ALTER TABLE tbl DISCARD TABLESPACE;

-- 5. 恢復備份的 .ibd（或從 Master 重新拷貝）
mv /data/mysql/db/tbl.ibd.bak /data/mysql/db/tbl.ibd
-- 或 scp master:/data/mysql/db/tbl.ibd /data/mysql/db/

-- 6. 執行 IMPORT TABLESPACE
ALTER TABLE tbl IMPORT TABLESPACE;

-- 7. 啟動複製
START SLAVE SQL_THREAD;

方案 B：跳過錯誤事務（緊急止血，非 GTID）

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;  -- 或 2，視事務組大小
START SLAVE;
-- 事後必須校驗數據一致性！

方案 C：重建 Slave（徹底乾淨）

• Master 全量備份（xtrabackup / mysqldump --single-transaction --master-data=2）
• 恢復到 Slave，重新 CHANGE MASTER TO ... MASTER_AUTO_POSITION=1

五、生產級運行維護清單

1. 監控：Seconds_Behind_Master、Slave_IO_Running/SQL_Running、Relay_Log_Space、主從校驗和報警
2. 並行複製：slave_parallel_workers=8、slave_parallel_type=LOGICAL_CLOCK（5.7+）、slave_preserve_commit_order=1
3. 半同步複製：rpl_semi_sync_master_enabled=1、rpl_semi_sync_slave_enabled=1、rpl_semi_sync_master_timeout=1000
4. 定期校驗：pt-table-checksum + pt-table-sync 每週全量校驗
5. 備份策略：Slave 停止 SQL Thread 做物理備份，不影響 Master