Mysql Master-Master Replication 設置

db1：172.30.8.201
db2：172.30.8.203
先設定 db1：
一、修改 my.cnf
/etc/my.cnf 每個版本的linux放置的位置可能都不一樣
自己注意一下
修改：
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
db2也一樣修改my.cnf
注意，server-id不能一樣
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
設定完重新啟動mysql



二、設定replication權限
# mysql -u root -p
先建立一個帳號為replication密碼為slave
mysql> GRANT replication slave ON *.* TO 'replication'@'%' IDENTIFIED BY 'slave';

底下是 Master 機器的 bin log file
利用 SHOW MASTER STATUS; 來取得下面這兩個的值
master_log_file='mysql-bin.000004',
master_log_pos=106;
變更master為db2
mysql> CHANGE master TO master_host='172.30.8.203', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=106;
到 db2 執行 SHOW MASTER STATUS; 會得到底下結果


+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      321 |              |                  |
+------------------+----------+--------------+------------------+


那這樣就可以知道
master_log_file=’mysql-bin.000001′,
master_log_pos=321;

三、mysql slave
重新啟動 mysql
/etc/init.d/mysql restart

啟動 slave
mysql> START slave;

觀看 slave 狀態
mysql> show slave status \G;

請注意下面這兩行必須為 YES
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
這樣才算成功

設定 db2：
一、修改 my.cnf
/etc/my.cnf

修改：

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log

二、設定 mysql 權限
mysql -u root -p

mysql> GRANT replication slave ON *.* TO 'replication'@'%' IDENTIFIED BY 'slave';

底下是 Master 機器的 bin log file
master_log_file='mysql-bin.000001',
master_log_pos=321;
可以利用 SHOW MASTER STATUS; 來取得這兩個的值
mysql> CHANGE master TO master_host='172.30.8.201', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000001', master_log_pos=321;

三、測試觀看 mysql slave
重新啟動 mysql
/etc/init.d/mysql restart

啟動 slave
mysql> START slave;

觀看 slave 狀態
mysql> show slave status \G;

如果遇到
Slave_IO_Running: no
Slave_SQL_Running: no
這樣的話請依照下面步驟：

停止跟清除 SLAVE

mysql> STOP SLAVE;
mysql> RESET SLAVE;

停止 mysql
/etc/init.d/mysql stop

刪除 bin log 檔案
rm -rf /var/log/mysql/mysql-bin.*

啟動 mysql
/etc/init.d/mysql start

再設定一次master ip
mysql> change master to master_host='172.30.8.201', master_port=3306, master_user='replication', master_password='slave';

啟動 slave
mysql> START SLAVE;

參考網站:http://blog.wu-boy.com/2008/12/30/667/