MySQL主主複製結構區別於主從複製結構。在主主複製結構中,兩臺伺服器的任何一臺上面的資料庫存發生了改變都會同步到另一臺伺服器上,這樣兩臺伺服器互為主從,並且都能向外提供服務。
有了上一節的主從複製,那麼主主複製就很容易了。
一、先修改配置檔案
伺服器A(192.168.1.254)配置如下
log-bin = mysql-bin
server-id = 1
expire-logs-days = 100
replicate-do-db = test
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
auto-increment-increment = 2
auto-increment-offset = 1
伺服器B(192.168.1.252)配置
log-bin = mysql-bin
server-id = 2
expire-logs-days = 100
replicate-do-db = test
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
auto-increment-increment = 2
auto-increment-offset = 2
兩臺伺服器都重啟
mysql> service mysqld restart
注:二都只有server-id不同和 auto-increment- offset不同
auto-increment-offset是用來設定資料庫中自動增長的起點的,回為這兩能伺服器都設定了一次自動增長值2,所以它們的起點必須得不同,這樣才能避免兩臺伺服器資料同步時出現主鍵衝突
replicate-do-db 指定同步的資料庫,我們只在兩臺伺服器間同步test資料庫
另:auto-increment-increment的值應設為整個結構中伺服器的總數,本案例用到兩臺伺服器,所以值設為2
二、同步資料
本文是用test做的實驗,匯出將test.sql檔案從254伺服器拷貝到252伺服器
備份資料前先鎖表,保證資料一致性
mysql> FLUSH TABLES WITH READ LOCK;
# mysqldump -uroot -p123456 test> /tmp/test.sql;
mysql> UNLOCK TABLES;
scp /tmp/test.sql root@192.168.1.252:/tmp
三、相互授權使用者(在A伺服器授權一個允許B訪問的使用者,反之亦然)
在伺服器A(192.168.1.254)上
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.252' IDENTIFIED BY PASSWORD '123456';
mysql> flush privileges;
在伺服器B(192.168.1.252)上
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.254' IDENTIFIED BY PASSWORD '123456';
mysql> flush privileges;
四、互告bin-log資訊
在伺服器A(192.168.1.254)
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000006 | 106 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
在伺服器A(192.168.1.252)
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000008 | 192 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
在A伺服器(192.168.1.254)上執行
mysql> change master to master_host='192.168.1.252',master_user='mysync',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=192;
在B伺服器(192.168.1.252)上執行
mysql> change master to master_host='192.168.1.254',master_user='mysync',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=106;
五、在兩伺服器都執行以下命令
mysql> start slave;
六、檢視狀態
mysql> show slave status\G
A伺服器(192.168.1.254)狀態如下:
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.252
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 192
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 337
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
B伺服器(192.168.1.252)狀態如下:
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.254
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000014
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
當看到了兩個yes,即:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
說明已經配置成功了
接下來看可以做一下實驗,測試一下是否同步
PS:
在測試的過程當中,我也遇到一些問題主要是兩臺機器互相通訊的問題
請注意,一定要保持兩臺的伺服器的mysql埠都向對方開啟,要不然是不能成功同步的。