MySQL 主從複制 (Master-Slave) 實戰部署全記錄：授權、Binlog、同步、故障處理

架構優勢

1. 主庫故障秒級切換從庫
2. 讀寫分離：從庫承擔查詢壓力
3. 從庫備份不影響主庫效能

僅適合「更新不頻繁、實時性要求不高」的查詢場景；核心交易仍走主庫。

環境假設

• Master: 192.168.10.1
• Slave: 192.168.10.2

1. 用戶授權 (Master 執行)

-- 通用帳號 (所有權限)
GRANT ALL ON *.* TO 'user1'@'192.168.10.2' IDENTIFIED BY '123456';

-- 或僅複制權限 (更安全)
GRANT REPLICATION SLAVE ON *.* TO 'user2'@'192.168.10.%' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

2. 啟用 Binlog (Master my.cnf)

[mysqld]
log-bin = /var/log/mysql/mysql-bin.log
server-id = 1
# 可選：慢查詢日誌
log_slow_queries = /var/log/mysql/mysql-slow.log

重啟後驗證：SHOW VARIABLES LIKE 'log_bin'; 應為 ON。

3. Binlog 常用管理

FLUSH LOGS;                    -- 產生新 binlog 文件
SHOW MASTER STATUS;            -- 當前文件與位置
SHOW MASTER LOGS;              -- 所有 binlog 列表
RESET MASTER;                  -- 清空所有 binlog (慎用)
mysqlbinlog /var/log/mysql/mysql-bin.000001 | more  -- 查看內容
mysqlbinlog --no-defaults binlog.000002 | mysql -uroot -p db  -- 恢復指定 binlog

4. 數據初始同步 (Master→Slave)

# Master
RESET MASTER;
mysqldump -uroot -p123123 test -l -F > /tmp/test.sql  # -l 鎖表, -F 產生新 binlog
scp /tmp/test.sql 192.168.10.2:/tmp/

# Slave
RESET MASTER;
mysql -uroot -p123123 test < /tmp/test.sql  # 或 source /tmp/test.sql

5. Slave 配置 (my.cnf)

[mysqld]
server-id = 2
master-host = 192.168.10.1
master-user = user1
master-password = 123456
master-port = 3306

重啟 Slave 服務。

6. 驗證複制狀態

SHOW SLAVE STATUS\G
-- 關鍵指標
Slave_IO_Running: Yes   -- IO 線程正常拉取 binlog
Slave_SQL_Running: Yes  -- SQL 線程正常回放
Seconds_Behind_Master: 0

7. 常見故障與修復

-- 手動重新定點同步
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.10.1',
  MASTER_USER='user1',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=286;
START SLAVE;

常用控制命令

START SLAVE;      -- 啟動複制
STOP SLAVE;       -- 暫停複制
SHOW PROCESSLIST; -- 查看複制線程狀態