架構規劃

角色	IP	Server ID	關鍵配置
Master	192.168.10.151	1	log-bin, binlog_format=mixed
Slave	192.168.10.152	2	relay-log, read_only=1

前置準備（雙節點同步執行）

# 安裝編譯依賴
yum -y install gcc gcc-c++ cmake ncurses-devel libaio-devel openssl-devel bison

# 建立用戶組/用戶
groupadd mysql && useradd -r -g mysql -s /sbin/nologin mysql

# 下載源碼編譯安裝（或用二進制包更快）
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.33.tar.gz
tar zxf mysql-5.5.33.tar.gz && cd mysql-5.5.33
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
      -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
      -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \
      -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1
make && make install

# 初始化
chown -R mysql:mysql /usr/local/mysql
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql
cp support-files/my-medium.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld && chkconfig mysqld on
service mysqld start
/usr/local/mysql/bin/mysqladmin -u root password 'StrongPass123'

Master 配置 /etc/my.cnf

[mysqld]
server-id = 1
log-bin = master-bin
log-bin-index = master-bin.index
binlog_format = mixed
innodb_file_per_table = 1
expire_logs_days = 7
max_binlog_size = 100M

service mysqld restart
mysql -uroot -p -e "GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'ReplPass123'; FLUSH PRIVILEGES;"
# 記錄下一步需要的 File/Position
mysql -uroot -p -e "SHOW MASTER STATUS;"

Slave 配置 /etc/my.cnf

[mysqld]
server-id = 2
relay-log = relay-bin
relay-log-index = relay-bin.index
read_only = 1
innodb_file_per_table = 1
binlog_format = mixed

service mysqld restart
mysql -uroot -p -e "
CHANGE MASTER TO
  MASTER_HOST='192.168.10.151',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='ReplPass123',
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G
"

驗證關鍵指標

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error: (空)
Last_SQL_Error: (空)

• 若 Slave_IO_Running: Connecting 檢查防火牆 3306、網路通訊、帳號密碼。
• 若 Slave_SQL_Running: No 檢查 Last_SQL_Error，常見於主從表結構不一致、主鍵衝突。

防火牆/SELinux

# CentOS 6/7
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
service iptables save
# SELinux
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

常見坑點與避坑

1. server-id 必須唯一，不可重複。
2. Master 必須開啟 log-bin，Slave 建議開啟 log-slave-updates（鏈式複制）。
3. 建立複制帳號只授 REPLICATION SLAVE 權限，最小權限原則。
4. 初始同步大表可用 mysqldump --master-data=2 --single-transaction 導出並導入 Slave，再 CHANGE MASTER TO 指定位置。
5. 監控：Seconds_Behind_Master 持續 > 0 需告警，排查慢 SQL、網路延遲、Slave 硬體瓶頸。