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

目前大部份的應該程式,都需要架設資料庫,所以說資料庫是應用程式的核心組件。這篇文章主要是教大家如何安裝與設置主從式(Master Slave)機制的架構,而不是探討這個機制可以應用在甚麼情境的應用程式下。

資料庫在擴展上最常使用的技術就是複製(Replication)技術,這個技術主要是以資料複製的方式把一個MySQL裹資料完整的複製到另一個MySQL節點上。因為大部分的資料庫應用程式都是SELECT為主,所以最常用到的就是讀寫分離的「主從式架構」。

MySQL的主(Master)節點負責「讀寫」,寫資料會透過Binlog Relay機制,以非同步模式複製Binlog到每一台從(Slave)節點執行。而從(Slave)節點只提供讀功能,不提供寫入功能,為了分散SELECT的運算。這種架構在實作時,必須自行在程式上實作「讀寫分離」的機制,即是需要程式邏輯上將SELECT Query 查詢分散到Slave節點執行。如果不想由程式上實作,也可以使用一些Proxy的軟體進行分配工作,如HAProxy。

廢話不多說,以下是安裝與設置流程:

伺服器 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

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

  1. 與「伺服器 A – 1」相同
  2. 與「伺服器 A – 2」相同
  3. 與「伺服器 A – 3」相同
  4. 修改MySQL的設置文檔my.cnf
    $ sudo vi /etc/my.cnf
    [mysqld]
    # 在最後加入以下參數
    server-id=2
  5. 與「伺服器 A – 5」相同
  6. 與「伺服器 A – 6」相同
  7. 與「伺服器 A – 7」相同
  8. 與「伺服器 A – 8」相同
  9. 設置需要同步的主(Master)節點資料庫
    mysql> change master to
        -> master_host='192.168.1.11',
        -> master_user='replication',
        -> master_password='password';
  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 |
    +--------+

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

*

驗證碼 * Time limit is exhausted. Please reload CAPTCHA.