MySQL 5.7 Master Master Replication 主主式複製 安裝與設置

上一篇文章講完設置主從式（Master Slave）機制，今次這篇文章教大家如何安裝與設置主主式（Master Master）機制的架構。

MySQL的主主式架構兩個節點都可以讀寫，好處是如果有一方出現問題不能正常運作，另一個節點都可以正常提供服務。當有問題的節點回復正常後，有問題的節點會把還沒有複製的資料複製過去。

以下是安裝與設置流程：

伺服器 A：
作業系統：CentOS 7
資料庫：MySQL 5.7
IP：192.168.1.10

伺服器 B：
作業系統：CentOS 7
資料庫：MySQL 5.7
IP：192.168.1.11

步驟一：
安裝與設置「伺服器 A」

1. 下載MySQL的Yum Repository RPM（可從官方網站下載其它版本 https://dev.mysql.com/downloads/repo/yum/）

   $ wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

2. 安裝MySQL的Yum Repository RPM

   $ sudo yum localinstall mysql57-community-release-el7-7.noarch.rpm

3. 安裝MySQL

   $ sudo yum install mysql-community-server

4. 修改MySQL的設置文檔my.cnf

   $ sudo vi /etc/my.cnf
   [mysqld]
   # 在最後加入以下參數
   server-id=1
   log-bin=mysql-bin
   binlog-do-db=exampledb

5. 啓動MySQL服務

   $ sudo systemctl start mysqld.service

6. 查看MySQL初始啓動時，默認的root密碼

   $ cat /var/log/mysqld.log | grep 'temporary password'
   2018-04-19T15:12:10.337245Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxxxxxx

7. 提供MySQL的安裝過程中的安全性，使用mysql_secure_installation

   $ mysql_secure_installation
   Securing the MySQL server deployment.
   Enter password for user root:
   The existing password for the user account root has expired. Please set a new password.
   New password:
   Re-enter new password:
   The 'validate_password' plugin is installed on the server.
   The subsequent steps will run with the existing configuration of the plugin.
   Using existing password for root.
   Estimated strength of the password: 100
   Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
   New password:
   Re-enter new password:
   Estimated strength of the password: 100
   Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
   By default, a MySQL installation has an anonymous user,
   allowing anyone to log into MySQL without having to have
   a user account created for them. This is intended only for testing, and to make the installation go a bit smoother.
   You should remove them before moving into a production environment.
   Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
   Success.
   Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.
   Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
   Success.
   By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.
   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
   - Dropping test database...
   Success.
   - Removing privileges on test database...
   Success.
   Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
   Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
   Success.

8. 使用root用戶登入MySQL

   $ mysql -u root -p

9. 新增與授權一個可以有覆製能力的用戶「replication」

   mysql> grant replication slave on *.* to 'replication'@'%' identified by 'password';
   mysql> flush privileges;
   mysql> exit

10. 如防火牆沒有讓端口3306放行，需要為防火牆設置

    $ sudo firewall-cmd --add-service=mysql --permanent
    $ sudo firewall-cmd --reload

11. 設置需要同步的主（Master）節點資料庫

    mysql> change master to
        -> master_host='192.168.1.12',
        -> master_user='replication',
        -> master_password='password';

12. 啓動開始進行同步（需要等待步驟二 1至10點 配置完成才可執行）

    start slave;

步驟二：
安裝與設置「伺服器 B」

1. 與「伺服器 A – 1」相同
2. 與「伺服器 A – 2」相同
3. 與「伺服器 A – 3」相同
4. 修改MySQL的設置文檔my.cnf

   $ sudo vi /etc/my.cnf
   [mysqld]
   # 在最後加入以下參數
   server-id=2
   log-bin=mysql-bin
   binlog-do-db=exampledb

5. 與「伺服器 A – 5」相同
6. 與「伺服器 A – 6」相同
7. 與「伺服器 A – 7」相同
8. 與「伺服器 A – 8」相同
9. 與「伺服器 A – 9」相同
10. 與「伺服器 A – 10」相同
11. 設置需要同步的主（Master）節點資料庫

    mysql> change master to
        -> master_host='192.168.1.11',
        -> master_user='replication',
        -> master_password='password';

12. 啓動開始進行同步（需要等待步驟一 1至10點 配置完成才可執行）

    start slave;

步驟三：
兩台伺服器已經設置完，可以進行測試

1. 使用root用戶登入伺服器 A的MySQL
2. MySQL新增一個exampledb資料庫和test資料表

   mysql> create database exampledb;
   mysql> use exampledb;
   mysql> create table test (number int);

3. 在伺服器 A上的資料表test新增一筆記錄

   mysql> insert into test values (1);

4. 使用root用戶登入伺服器 B的MySQL
5. 在伺服器 B上的查詢資料表test

   mysql> select * from test;

6. 如果查詢到結果如下代表設置成功

   +--------+
   | number |
   +--------+
   |      1 |
   +--------+