Replication 目的是備份DB的資料 當主DB死掉 資料不致失去太多 也可以立刻以SLAVE DB 代替
先準備兩台DB
先開放兩邊的port 3306
/sbin/iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
開始設定
1.停止mysql服務
Master#>services mysqld stop
2.修改/etc下的my.cnf
加入這兩行
server-id = 1
log-bin = mysql-bin
將server-id設為1
打開log-bin
其中server-id必須各自不同, 用於Replication關係中識別各Server.
3.重新啟動mysql
Master#>services mysqld start
進入Mysql
Master#>mysql -u root -p
設定Replication 的帳號 repl
mysql>GRANT REPLICATION SLAVE ON *.* TO repl@Slave的IP IDENTIFIED BY '密碼';
mysql>FLUSH PRIVILEGES;
將DB TABLE 資料先停住 不讓其寫入
mysql>FLUSH TABLES WITH READ LOCK;
show 出Position 設定Slave要用的參數
mysql> SHOW MASTER STATUS;
- +-----------------------+-----------+-------------------+-----------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +-----------------------+-----------+-------------------+-----------------------+
- | mysql-bin.000001 | 759 | | |
- +-----------------------+-----------+-------------------+-----------------------+
修改/etc下的my.cnf
加入這兩行
server-id = 2
log-bin = mysql-bin
5.進入Mysql設定
Slave#>services mysqld start
Slaver#>mysql -u root -p
mysql>CHANGE MASTER TO MASTER_HOST=Master-IP, MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS='759;
MASTER_HOST=Master的IP
MASTER_USER=設定的replication的帳號
MASTER_PASSWORD=replication的密碼
啟動Slave
mysql> Start Slave;
mysql> SHOW SLAVE STATUS\G
-
- mysql> SHOW SLAVE STATUS\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: xxx.xxx.xxx.xxx
- Master_User: repl
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000001
- Read_Master_Log_Pos: 759
- Relay_Log_File: mysqld-relay-bin.000042
- Relay_Log_Pos: 235
- Relay_Master_Log_File: mysql-bin.000001
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 759
- Relay_Log_Space: 235
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- 1 row in set (0.00 sec)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.回到Master上
重新打開剛剛鎖住的DB
mysql>UNLOCK TABLES;
Slave_IO_Running = No 或Connecting
代表replication未成功
原因有檢查 1.網路連線沒開通
2.pos設定沒同步
3.repl帳號密碼錯誤
留言列表
