主-主複製（192.168.0.214---192.168.0.53）

配置同步賬號

主1（192.168.0.214）

GRANT all privileges ON *.* TO mmuser@'192.168.0.53' IDENTIFIED BY 'mmpass';

修改 /etc/my.cnf

[mysql@dgryxrdb ~]$less /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

server-id = 1

log-bin = mysql-bin

binlog_format = mixed

binlog-do-db=mydb --需要記錄二進位制日誌的資料庫，如果有多個資料庫，可用“，”分割，或者使用多個binlog-do-db選項 --應該是主庫的引數

replicate-do-db=mydb --需要進行同步的資料庫，如果有多個資料庫，可用“，”分給，或者使用多個replicate-do-db選項 --應該是在從庫的引數，解釋需要同步的庫

#同步引數

#保證slave掛在任何一個master上都會接收到另一個master的寫入資訊

log-slave-updates --將執行的複製sql記錄到二進位制日誌（主-主應該加，是針對與主庫更新後不會迴圈的引數？？）

#master-host=192.168.0.53

#master-user=mmuser

#master-password=mmpass

#master-connect-retry=10

sync_binlog=1 --當有二進位制日誌寫入binlog檔案的時候，mysql伺服器將它同步至磁碟。

auto_increment_increment=2 --應設定為整個結構中伺服器的總數(自增量)

auto_increment_offset=1 --用來設定資料庫中自動增長的起點，因為兩臺伺服器都設定了一次自動增長值2，因此起點必須不同，這樣才能避免兩臺伺服器資料同步時出現主鍵衝突

#slave-skip-errors=all --過濾掉一些錯誤，跳過錯誤，繼續執行復制操作，如果不加此引數，出現任何錯誤都將停止複製

主2（192.168.0.53）

GRANT all privileges ON *.* TO mmuser@'192.168.0.214' IDENTIFIED BY 'mmpass';

修改/etc/my.cnf

[root@dns ~]# less /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

server-id = 53

log-bin = mysql-bin

binlog_format = mixed

binlog-do-db=mydb

replicate-do-db=mydb

sync_binlog=1

log-slave-updates

auto_increment_increment=2

auto_increment_offset=2

主1

[mysql@dgryxrdb ~]$mysql -u root -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.7-rc-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush tables with read lock\G; --防止進入新的資料

Query OK, 0 rows affected (0.00 sec)

ERROR:

No query specified

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000008

Position: 154

Binlog_Do_DB: mydb

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

主2

[mysql@dns ~]$mysql -u root -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.7-rc-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000006

Position: 154

Binlog_Do_DB: mydb

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

主1

mysql> stop slave

-> ;

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

mysql> show slave status

-> ;

Empty set (0.00 sec)

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.0.53',master_user='mmuser',master_password='mmpass',master_log_file='mysql-bin.000006',master_log_pos=154;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

主2

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.0.214',master_user='mmuser',master_password='mmpass',master_log_file='mysql-bin.000008',master_log_pos=154;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

在兩臺伺服器檢視：

mysql> show slave status\G;

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

注意：LOAD DATA FROM MASTER（手動執行資料同步）目前只在所有表使用MyISAM儲存引擎的資料庫上有效。

Mysql 主-主簡單配置 --centos6.4

相關文章