Linux MySQL 雙主互為主從搭建實戰：CentOS 6.5 + MySQL 5.6.19 完整配置

Linux MySQL 雙主互為主從搭建實戰：CentOS 6.5 + MySQL 5.6.19 完整配置

雙主（Master-Master）架構提供寫入高可用與讀取負載分發，核心風險是自增 ID 衝突與循環復制。本文給出生產級雙主搭建全流程，含 auto_increment_offset/increment 衝突避免、GTID 預留、防火牆、服務註冊。

拓撲與版本

• Master1: 192.168.100.204
• Master2: 192.168.100.205
• OS: CentOS 6.5
• MySQL: 5.6.19（源碼編譯安裝）

基礎環境雙節點同步執行

# 1. 防火牆放行 3306
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
/etc/rc.d/init.d/iptables save

# 2. 依賴包
yum -y install gcc gcc-c++ ncurses ncurses-devel openssl openssl-devel cmake perl lsof bison

# 3. 移除系統自帶 MySQL
rpm -qa | grep mysql
rpm -e --allmatches --nodeps mysql-libs-5.1.71-1.el6.x86_64

# 4. 目錄規劃
mkdir -p /doiido/soft /doiido/mysql/data

# 5. mysql 用戶
groupadd mysql
useradd -s /sbin/nologin -M -g mysql mysql
chown -R mysql:mysql /doiido/mysql/

# 6. 編譯安裝 MySQL 5.6.19
cd /doiido/soft
tar -zxvf mysql-5.6.19.tar.gz
cd mysql-5.6.19
cmake -DCMAKE_INSTALL_PREFIX=/doiido/server/mysql \
  -DMYSQL_DATADIR=/doiido/mysql/data \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DWITH_MEMORY_STORAGE_ENGINE=1 \
  -DWITH_MYISAM_STORAGE_ENGINE=1 \
  -DSYSCONFDIR=/etc/ \
  -DWITH_SSL=yes \
  -DDEFAULT_CHARSET=utf8 \
  -DDEFAULT_COLLATION=utf8_general_ci \
  -DWITH_READLINE=on
make && make install

# 7. 權限與軟連接
chmod +w /doiido/server/mysql
chown -R mysql:mysql /doiido/server/mysql/
ln -s /doiido/server/mysql/lib/lib* /usr/lib/
ln -s /doiido/server/mysql/bin/mysql /usr/bin

# 8. 基礎配置 /etc/my.cnf
cat > /etc/my.cnf <<'EOF'
[mysqld]
character-set-server = utf8
default-storage-engine = MyISAM
basedir = /doiido/server/mysql
datadir = /doiido/mysql/data
log-error = /doiido/mysql/mysql_error.log
pid-file = /doiido/mysql/mysql.pid
max_allowed_packet = 32M
explicit_defaults_for_timestamp = true
EOF

# 9. 初始化數據字典
/doiido/server/mysql/scripts/mysql_install_db \
  --basedir=/doiido/server/mysql \
  --datadir=/doiido/mysql/data \
  --user=mysql

# 10. 服務註冊
cp /doiido/server/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --level 345 mysqld on

# 11. 修改啟動腳本 basedir/datadir
sed -i 's|^basedir=.*|basedir=/doiido/server/mysql|' /etc/init.d/mysqld
sed -i 's|^datadir=.*|datadir=/doiido/mysql/data|' /etc/init.d/mysqld

# 12. 啟動
service mysqld start

# 13. 創建業務庫與賬號
mysql -uroot -p -e "
CREATE DATABASE doiido;
GRANT ALL PRIVILEGES ON doiido.* TO 'doiido'@'%' IDENTIFIED BY 'doiido123';
"

雙主復制專用配置（關鍵差異化）

Master1 (192.168.100.204) 專屬配置

-- /etc/my.cnf 追加
[mysqld]
server-id = 1
log-bin = bin.log
binlog-do-db = doiido
replicate-do-db = doiido
# 關鍵：自增 ID 奇數
auto_increment_offset = 1
auto_increment_increment = 2
# GTID 預留（5.6 支持但非必須）
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

service mysqld restart

# 創建復制賬號（授權給 Master2 IP）
mysql -uroot -p -e "
GRANT REPLICATION SLAVE ON *.* TO 'dodo'@'192.168.100.205' IDENTIFIED BY 'jjjjjj';
FLUSH PRIVILEGES;
"

# 記錄 Master1 Binlog 位點
mysql -uroot -p -e "SHOW MASTER STATUS;"
-- 例：File=bin.000001, Position=120

Master2 (192.168.100.205) 專屬配置

-- /etc/my.cnf 追加
[mysqld]
server-id = 2
log-bin = bin.log
binlog-do-db = doiido
replicate-do-db = doiido
# 關鍵：自增 ID 偶數
auto_increment_offset = 2
auto_increment_increment = 2
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

service mysqld restart

# 創建業務庫（同步前需存在）
mysql -uroot -p -e "CREATE DATABASE doiido;"

# 配置 Master1 為上游
mysql -uroot -p -e "
CHANGE MASTER TO
  MASTER_HOST='192.168.100.204',
  MASTER_PORT=3306,
  MASTER_USER='dodo',
  MASTER_PASSWORD='jjjjjj',
  MASTER_LOG_FILE='bin.000001',
  MASTER_LOG_POS=120;
START SLAVE;
"

# 驗證
mysql -uroot -p -e "SHOW SLAVE STATUS\G"
-- Slave_IO_Running: Yes, Slave_SQL_Running: Yes

完成雙向：Master1 指向 Master2

# 在 Master2 記錄 Binlog 位點
mysql -uroot -p -e "SHOW MASTER STATUS;"
-- 例：File=bin.000001, Position=649

# 在 Master1 配置 Master2 為上游
mysql -uroot -p -e "
CHANGE MASTER TO
  MASTER_HOST='192.168.100.205',
  MASTER_PORT=3306,
  MASTER_USER='dodo',
  MASTER_PASSWORD='jjjjjj',
  MASTER_LOG_FILE='bin.000001',
  MASTER_LOG_POS=649;
START SLAVE;
"

# 雙向驗證
mysql -uroot -p -e "SHOW SLAVE STATUS\G"
-- 兩節點均：Slave_IO_Running: Yes, Slave_SQL_Running: Yes

關鍵風險控制點

遷移建議：MySQL 5.7+ Group Replication / 8.0 InnoDB Cluster

• 原生多主同步復制，基於 Paxos 一致性協議，自動故障轉移、無自增衝突、讀寫分離內建。
• 5.6 雙主屬「手工藝」，維護成本高，新架構優先選 Group Replication。

雙主搭建易，運行難。auto_increment_offset 是救命繩，log_slave_updates 是防循環盾，業務單寫是根本解。條件允許，直上 Group Replication。