設定 MySQL Master-Master 複製

本文簡單說明如何設定兩台MySQL server，讓它們彼此互相備份資料(Master-master 模式)。雖然這樣的設定通常是支援高可用性(high availability) 平台的一部分，但本文不包含如何完成其他HA的工作。設定兩台MySQL伺服器互相備份，主要目的就是要確認資料的安全性；同時也提高可用性。

底下假設兩台伺服器的名稱跟IP, 分別為 host1(192.168.0.1) 跟 host2(192.168.0.2)。

• OS: CentOS 6.2
• MySQL 版本： 5.1.61

步驟1: 在兩台機器完成MySQL軟體基本的安裝

在CentOS/RHEL下，只需要以root執行:

# yum -y install mysql-server

步驟2：修改host1 的/etc/my.cnf 內容

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

log-bin=/var/log/mysql/mysql-bin.log
#binlog-do-db=<database name> # input the database which should be replicated
binlog-ignore-db=mysql         # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

auto_increment_increment=2
auto_increment_offset=1

[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

步驟 3:在host1建立MySQL log 的專屬目錄

root@host1# mkdir -p /var/log/mysql
root@host1# chown mysql:mysql /var/log/mysql<

步驟4 ： 重開host1 上的MySQL伺服器

root@host1# /etc/init.d/mysqld restart

步驟5：建立給host2使用的MySQL帳戶

使用MySQL的命令列客戶端，執行下列指令：

root@host1#  mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica2'@'192.168.0.%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000004 | 106       |              | mysql,test       |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> quit

步驟6：修改host2的/etc/my.cnf 內容

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=2

auto_increment_increment=2
auto_increment_offset=2

[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

步驟7:在host2建立MySQL log 的專屬目錄

root@host2# mkdir -p /var/log/mysql
root@host2# chown mysql:mysql /var/log/mysql

步驟8：重啟 host2 上的MySQL

root@host2# /etc/init.d/mysqld restart

步驟9：指定host2 上的MySQL的master為host1 

注意’mysql-bin.000004′跟106是來自步驟 X。

root@host2# mysql -u root
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='replica1', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106;

mysql> START SLAVE;
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 106       |              | mysql,test       |
+------------------+-----------+--------------+------------------+
1 row in set (0.01 sec)

步驟10: 建立給host1使用的MySQL同步用帳戶

root@host1#  mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica2'@'192.168.0.%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 106       |              | mysql,test       |
+------------------+-----------+--------------+------------------+
1 row in set (0.01 sec)
mysql> quit

步驟11：指定host1 上的MySQL的master為host2

root@host2# mysql -u root

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='replica2', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;

mysql> START SLAVE;