環境
- MySQL 4.1，Master: 192.168.0.205，Slave: 192.168.0.206。
- 安裝路徑：
/opt/mysql。
Master 配置
# /etc/my.cnf [mysqld]
server-id = 1
log-bin = binlog_name
創建復制賬號
GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO 'slave'@'192.168.0.206' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
Slave 連接測試
/opt/mysql/bin/mysql -u slave -p -h 192.168.0.205
# 確認網絡/權限通暢
Slave 配置
# /etc/my.cnf [mysqld]
server-id = 2
master-host = 192.168.0.205
master-user = slave
master-password = 123456
啟動同步
/opt/mysql/share/mysql/mysql start
/opt/mysql/bin/mysql -u root -p
mysql> LOAD DATA FROM MASTER;
LOAD DATA FROM MASTER 會鎖表導入，大庫建議改用 mysqldump --master-data=2 導入或直接複製數據目錄（需停庫）。
驗證測試
-- Master
CREATE DATABASE sampdb;
USE sampdb;
CREATE TABLE new (name CHAR(20), phone CHAR(20));
INSERT INTO new VALUES ('abc', '0532555555');
-- Slave
SHOW DATABASES; -- 見 sampdb
USE sampdb;
SHOW TABLES; -- 見 new
SELECT * FROM new; -- 見數據
常用管理命令
| 命令 | 作用 |
|---|
SLAVE STOP / START | 暫停/恢復復制線程 |
SHOW SLAVE STATUS\G | 查看復制坐標、延遲、錯誤 |
PURGE MASTER LOGS TO 'binlog.000004' | 清理已無用 Binlog，謹慎操作 |
CHANGE MASTER TO ... | 動態調整主庫參數 |
避坑提醒
server-id 唯一非 0。- Master 必須開
log-bin。
PURGE MASTER LOGS 會導致 Slave 斷點丟失，確認所有 Slave 已處理該 Binlog 再執行。- MySQL 5.6+ 建議用
CHANGE MASTER TO MASTER_AUTO_POSITION=1 (GTID) 替代文件/位置定位。
