MySQL 5.5 主從復制配置實戰：一主一從完整步驟與驗證

環境

• Master：CentOS 5.6，MySQL 5.5.8，IP 192.168.1.2
• Slave：CentOS 5.6，MySQL 5.5.8，IP 192.168.1.3

Master 配置 /etc/my.cnf

[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Master 建立復制用戶 & 記錄坐標

CREATE USER 'repl_user'@'192.168.1.3';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.3' IDENTIFIED BY '123456';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File、Position
-- 例：mysql-bin.000013, 7863951

同步數據到 Slave

mysqldump test -uroot -padmin --opt | mysql test -uroot -padmin -h 192.168.1.3

Master 解鎖

UNLOCK TABLES;

Slave 配置 /etc/my.cnf

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
replicate-do-db=test
datadir=/var/lib/mysql

Slave 初始化復制

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.2',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000013',
  MASTER_LOG_POS=7863951;
START SLAVE;

驗證

SHOW SLAVE STATUS\G
-- 關鍵：Slave_IO_Running=Yes, Slave_SQL_Running=Yes

測試

Master 對 test 庫寫入，Slave 應同步出現。