MySQL 5.6 基於 Binlog 的主從復制（讀寫分離）完整版：從安裝到驗證的企業級標準交付

前言：為什麼選 Binlog 而非 GTID？

MySQL 5.6 支持兩種復制模式：

• Binlog（傳統）： 成熟穩定，**支持臨時表**，生產首選
• GTID（全局事務 ID）： 易於故障轉移，**但 5.6 不支持臨時表**，業務若用臨時表則不可用

本文採用 **Binlog 模式**，構建企業級讀寫分離架構。

架構規劃

標準化交付 14 步法

Step 1-2：雙機安裝 MySQL 5.6.18（RPM）並部署優化配置

• 刪除默認 /usr/my.cnf，部署優化版 /etc/my.cnf
• Master/Slave 配置差異僅 server-id、log-bin 前綴

Step 3：重啟雙機 MySQL

service mysql restart

Step 4：Master 創建復制賬號（綁定 Slave IP）

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY '111111';

Step 5：Master 鎖表獲取 Binlog 坐標

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 記錄：File: mysqlmaster-bin.000004, Position: 327

Step 6：Master 創建全量快照（共享目錄/SCP 傳輸）

cd /usr/bin
./mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases --triggers --routines --events > /mnt/windows/all.sql

Step 7：Master 解鎖

UNLOCK TABLES;

Step 8：Slave 導入快照

mysql -uroot -p -h127.0.0.1 -P3306 < /mnt/windows/all.sql

Step 9：Slave 配置主庫連接並啟動復制

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='111111',
  MASTER_LOG_FILE='mysqlmaster-bin.000004',
  MASTER_LOG_POS=327;

START SLAVE;

Step 10：驗證復制狀態

SHOW SLAVE STATUS\G;
-- 核心：Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0

Step 11：業務驗證

-- Master 寫入
USE test; CREATE TABLE t1 (id INT PRIMARY KEY); INSERT INTO t1 VALUES (1);
-- Slave 讀取
SELECT * FROM test.t1;  -- 可見

Step 12：讀寫分離接入（應用層/中間件）

• 寫操作 → Master
• 讀操作 → Slave（可掛多 Slave 做負載均衡）
• 大查詢/報表 → 專用 Slave（隔離影響）

Step 13：運維策略

• 大查詢高峰可 STOP SLAVE 暫停復制，低峰 START SLAVE 追趕，**不丟數據**
• 監控：Seconds_Behind_Master、Slave_*_Running、Last_*_Error

Step 14：附贈企業級優化配置模板（Master/Slave 通用，差異化 server-id/log-bin）

[client]
port=3306
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8

[mysqld]
sync_binlog=1
server-id=1  # Slave 改 2
port=3306
socket=/usr/local/mysql/mysql.sock
pid-file=/home/mysql/temp/my3306.pid
user=mysql
datadir=/home/mysql/data
tmpdir=/home/mysql/temp/
log-bin=/home/mysql/data/mysqlmaster-bin  # Slave 改 mysqlslave-bin
log-error=/home/mysql/logs/error.log
slow_query_log_file=/home/mysql/logs/slow.log
binlog_format=mixed
slow_query_log
long_query_time=10
wait_timeout=31536000
interactive_timeout=31536000
max_connections=500
max_user_connections=490
max_connect_errors=2
character_set_server=utf8
skip-external-locking
key_buffer_size=128M
max_allowed_packet=5M
table_open_cache=512
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=64M
thread_cache_size=8
query_cache_size=32M
thread_concurrency=4
binlog-ignore-db=mysql,information_schema
replicate_ignore_db=mysql,information_schema
expire-logs-days=10
skip-slave-start  # Master 保留，Slave 註釋
skip-name-resolve
lower_case_table_names=1
log_bin_trust_function_creators=1

# InnoDB
innodb_data_home_dir=/home/mysql/data
innodb_log_group_home_dir=/home/mysql/logs
innodb_data_file_path=ibdata1:128M:autoextend
innodb_buffer_pool_size=2G
innodb_log_file_size=10M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

總結

**14 步法 = 環境統一 + 配置模板化 + 鎖表快照精確坐標 + 標準化驗證 + 讀寫分離接入 + 運維策略固化**。將此流程封裝為 Ansible Playbook / Shell 腳本庫，新環境交付「一鍵部署、自動驗證、開箱即用」，是 DBA 團隊交付能力的核心資產。