mysql Replication
-----------------------------
1. 兩台主機的 mysql 設定檔
my.cnf
加上
;;server-id = 2
server-d =1
log-bin
binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3
binlog-do-db = db4
;;master-host = 192.168.1.22
master-host = 192.168.1.30
master-user = ruser
master-password = rpass
master-port = 3306
master-connect-retry = 60
replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3
replicate-do-db = db4
2. 兩台主機新增帳號
***** For 4.0.x 以前版本
grant file on *.* to ruser@192.168.1.22 identified by 'rpass';
grant file on *.* to ruser@192.168.1.30 identified by 'rpass';
***** For 4.1.x 以後版本
grant replication slave on *.* to ruser@192.168.1.22 identified by 'rpass';
grant replication slave on *.* to ruser@192.168.1.30 identified by 'rpass';
3. 修改 /etc/hosts.allow
mysqld: 127.0.0.1 192.168.1.22 192.168.1.30
4. 清除 bin-log 檔
設定使用 ssh 直接登入其它伺服器 , 不需要認證
在 192.168.1.22主機上執行
ssh-keygen -t rsa
不要輸入密碼, 產生 RSA key
cat /root/.ssh/id_rsa.pub
將內容複製起來 , 然後在 192.168.1.30 主機上 , 加入 /root/.ssh/authorized_keys 存檔
同樣動作 192.168.1.30 也做一次
如此在 192.168.1.22 主機上執行 ssh 192.168.1.30 , 即可直接登錄
同理在 192.168.1.30 主機上執行 ssh 192.168.1.22 也可直接登錄
新增一檔案 purge_replication_log.sh , 內容如下:
#!/usr/bin/env bash
#######################################################
# Initialization: define variables in need
#######################################################
PATH=/usr/local/bin:/usr/bin:/bin
USER="root"
PASSWD="rootpassword"
CMDSLAVE="stop slave;reset slave;start slave;"
CMDMASTER="reset master;"
MASTER="192.168.1.22"
SLAVE="192.168.1.30"
#### clean master bin-log and slave restart slave
(mysql -u$USER -p$PASSWD -e "$CMDMASTER") 1>/dev/null
(ssh $SLAVE "mysql -u$USER -p$PASSWD -e \"$CMDSLAVE\"") 1>/dev/null
### claen slave bin-log and master restart slave
(ssh $SLAVE "mysql -u$USER -p$PASSWD -e \"$CMDMASTER\"") 1>/dev/null
(mysql -u$USER -p$PASSWD -e "$CMDSLAVE") 1>/dev/null
exit 0
如此 , chmod 755 purge_replication_log.sh
定期執行 purge_replication_log.sh 即可將 bin-log 刪除 , 以防 bin-log 過大 , 並保持雙向同步