環境
- Master：Ubuntu 10.10，192.168.1.112，MySQL 5.1.49
- Slave：192.168.10.245
- 同步資料庫：
test、blog
1. Master 配置 /etc/mysql/my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-test-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = mixed
2. Slave 配置 /etc/mysql/my.cnf
[mysqld]
server-id = 2
replicate-do-db = test
replicate-do-db = blog
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = mixed
slave-net-timeout = 60
master-connect-retry = 10
3. Master 建立複制帳號
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO 'slave'@'192.168.10.245' IDENTIFIED BY '123456';
4. 初始化資料同步
-- Master
FLUSH TABLES WITH READ LOCK;
mysqldump -uroot -p test > /tmp/test.sql
-- 複製到 Slave
mysql -uroot -p test < /tmp/test.sql -- Slave 先建庫 test
-- Master 查看位點
SHOW MASTER STATUS;
-- 例：File=mysql-test-bin.000022, Position=624
5. Slave 指定同步起點並啟動
CHANGE MASTER TO
MASTER_HOST='192.168.1.112',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-test-bin.000022',
MASTER_LOG_POS=624;
START SLAVE;
6. 驗證同步狀態
SHOW SLAVE STATUS\G
-- 關鍵指標
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_Error: (空)
7. 解鎖 Master
UNLOCK TABLES;
常見坑點提醒
binlog_format=mixed 兼容性最好（STATEMENT/ROW 自動切換）。- Slave 需設
relay_log 避免主機名變更導致中斷。
- 初始同步必須在鎖表狀態下進行，確保位點對應的一致性快照。
