MariaDB – Master Slave Replication

為了避免硬碟損毀進而造成資料庫遺失,可以使用主從式資料庫 (Master Slave Replication) 的技術. Master 的資料庫會透過 Binlog 的機制將資料同步到 Slave .

Master 不是真的會把資料複製一份到 Slave,而是 Master 會把所有會變動到資料的指令儲存到 Binlog ,其中包含 CREATE , ALTER TABLE , INSERT , UPDATE , DELETE … (不包含 SELECT 和 SHOW 這一類不影響資料的) .

Slave 透過 IO Thread 的方式 ( IO Thread 會請 Master 發送 Binlog 有更新的部分,並儲存至 Slave 的 Relay Log )
Slave 再透過 SQL Thread 讀取剛剛 IO Thread 儲存的 Relay Log 來執行有更新的部分(指令), 讓兩邊透過非同步的方式來同步資料.


圖片出自於 – https://gilbertasm.github.io/mysql/2018/04/15/understanding-mysql-replication-coordinates.html

Master Slave Replication 主要也只是避免資料庫遺失 (或是分散 SELECT 等讀取資料的指令到 Slave 來處理),如果想要做到 分散 處理,這時候需要透過 MariaDB Galera Cluster + HAProxy ,請參考 – http://benjr.tw/95536

下面來安裝與設置 MariaDB 主從式資料庫 (Master Slave Replication)

測試環境為 CentOS 7 / CentOS 8 x86_64 (虛擬機)

  • Master IP : 192.168.111.129
  • Slave IP : 192.168.111.157

安裝/設定 MariaDB + Replication

  • Master
    安裝 Mariadb 資料庫所需套件.

    [root@localhost ~]# yum install -y mariadb-server mariadb
    [root@localhost ~]# systemctl start mariadb
    [root@localhost ~]# systemctl enable mariadb
    

    初始化設定 MAriadb 資料庫,主要設定 MariaDB 的 root 帳號 (不同於 CentOS 系統 root 帳號) 以及其他設定.

    [root@localhost ~]# mysql_secure_installation
    
    NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
          SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
    
    In order to log into MariaDB to secure it, we'll need the current
    password for the root user.  If you've just installed MariaDB, and
    you haven't set the root password yet, the password will be blank,
    so you should just press enter here.
    
    Enter current password for root (enter for none): 
    OK, successfully used password, moving on...
    
    Setting the root password ensures that nobody can log into the MariaDB
    root user without the proper authorisation.
    
    Set root password? [Y/n] 
    New password: 
    Re-enter new password: 
    Password updated successfully!
    Reloading privilege tables..
     ... Success!
    
    
    By default, a MariaDB installation has an anonymous user, allowing anyone
    to log into MariaDB 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? [Y/n] 
     ... 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? [Y/n] 
     ... Success!
    
    By default, MariaDB 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? [Y/n] 
     - 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? [Y/n] 
     ... Success!
    
    Cleaning up...
    
    All done!  If you've completed all of the above steps, your MariaDB
    installation should now be secure.
    
    Thanks for using MariaDB!
    

    在 my.cnf ( CentOS 7 檔案位於 /etc/my.cnf , CentOS 8 檔案位於 /etc/my.cnf.d/mariadb-server.cnf ) 設定檔 [mysqld] 區塊加入以下與 Master replication 相關設定.

    [root@localhost ~]# vim /etc/my.cnf
    [mysqld]
    server_id=1
    log-basename=master
    log-bin
    binlog-format=row
    binlog-do-db=testdb
    
    • server_id=1
      主資料庫 Master 需設定為 1.
    • log-basename=master
    • log-bin
      Master Slave Replication 主要是透過 Master 儲存會變動到資料的指令,包含 CREATE , ALTER TABLE , INSERT , UPDATE , DELETE … 到 Binlog 並與 Slave 的 Binlog 同步,Slave 再依據此 Binlog 執行指令來確保兩邊資料的同步.
      這個 Binlog 可以透過 Linux 下的指令 mysqlbinlog 來查看內容.

      [root@localhost ~]# mysqlbinlog /var/lib/mysql/mariadb-bin.000001 
      

      或是在 Mariadb 資料庫操作介面下指令.

      MariaDB [(none)]> show binlog events \G
      
    • binlog-format=row
      記錄模式有三種 statement , row , mixed (沒特別研究).
    • binlog-do-db=testdb
      指定哪一個資料庫需要被同步,當有多個資料庫時就依序寫下資料庫名稱,如下所示.

      binlog-do-db=testdb1
      binlog-do-db=testdb2
      

      沒設定時就會同步所有資料庫,或是使用下面忽略的方式來設定.

      • replicate_ignore_db=testdb1 (忽略指定資料庫)
      • replicate_ignore_table=test1.table1 (忽略指定資料表)

    重啟資料庫服務.

    [root@localhost ~]# systemctl restart mariadb
    

    登入資料庫系統,並新增剛剛要同步的資料庫 testdb (如資料庫已存在請跳過這個步驟).

    [root@localhost ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 2
    Server version: 5.5.64-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> CREATE DATABASE testdb;
    Query OK, 1 row affected (0.00 sec)
    

    新增需要同步資料的使用者 slaveuser 與其密碼.

    MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY '111111';
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    

    一開始 Master 的資料需要手動同步到 Slave ,需先把資料庫設定成為唯讀 (Read lock).

    MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
    Query OK, 0 rows affected (0.00 sec)
    

    這邊需要新開一個 Linux command line (離開 mysql command line 後會讓唯讀 Read lock 失效),透過 mysqldump 把所有資料備份下來並傳給 Slave (IP: 192.168.111.157)

    [root@localhost ~]# mysqldump --all-databases --user=root --password --master-data > masterdatabase.sql
    [root@localhost ~]# scp masterdatabase.sql 192.168.111.157:/root/
    root@192.168.111.157's password: 
    masterdatabase.sql                                                             100%  503KB  66.5MB/s   00:00  
    

    檢視一下 Master 的狀態 ,需把 File (指令同步的檔案名稱) 與 Position (同步檔案目前位於哪一行) 記錄起來,後續 Slave 設定需要使用該參數.

    MariaDB [(none)]> SHOW MASTER STATUS;
    +--------------------+----------+--------------+------------------+
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+----------+--------------+------------------+
    | mariadb-bin.000001 |      554 | testdb       |                  |
    +--------------------+----------+--------------+------------------+
    1 row in set (0.01 sec)
    

    這邊就可以取消唯讀了,因為已經紀錄 File (指令同步的檔案名稱) 與 Position (同步檔案目前位於哪一行),之後 Master 做了什麼改變,Slave 都知道可以從哪一行指令 (Position) 還原做起.

    MariaDB [(none)]> UNLOCK TABLES;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [testdb]> exit
    Bye
    
  • Slave
    安裝 Mariadb 資料庫所需套件.

    [root@localhost ~]# yum install -y mariadb-server mariadb
    [root@localhost ~]# systemctl start mariadb
    [root@localhost ~]# systemctl enable mariadb
    

    初始化設定 MAriadb 資料庫,主要設定 MariaDB 的 root 帳號 (不同於 CentOS 系統 root 帳號) 以及其他設定.

    [root@localhost ~]# mysql_secure_installation
    
    NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
          SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
    
    In order to log into MariaDB to secure it, we'll need the current
    password for the root user.  If you've just installed MariaDB, and
    you haven't set the root password yet, the password will be blank,
    so you should just press enter here.
    
    Enter current password for root (enter for none): 
    OK, successfully used password, moving on...
    
    Setting the root password ensures that nobody can log into the MariaDB
    root user without the proper authorisation.
    
    Set root password? [Y/n] 
    New password: 
    Re-enter new password: 
    Password updated successfully!
    Reloading privilege tables..
     ... Success!
    
    
    By default, a MariaDB installation has an anonymous user, allowing anyone
    to log into MariaDB 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? [Y/n] 
     ... 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? [Y/n] 
     ... Success!
    
    By default, MariaDB 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? [Y/n] 
     - 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? [Y/n] 
     ... Success!
    
    Cleaning up...
    
    All done!  If you've completed all of the above steps, your MariaDB
    installation should now be secure.
    
    Thanks for using MariaDB!
    

    在 my.cnf ( CentOS 7 檔案位於 /etc/my.cnf , CentOS 8 檔案位於 /etc/my.cnf.d/mariadb-server.cnf ) 設定檔 [mysqld] 區塊加入以下與 Slave replication 相關設定.

    [root@localhost ~]# vim /etc/my.cnf
    [mysqld]
    server-id = 2
    replicate-do-db=testdb
    
    • server_id=2
      Slave 資料庫設定為 2 與 Master (為 1) 不相同即可.
    • binlog-do-db=testdb
      指定哪一個資料庫需要被同步,當有多個資料庫時就依序寫下資料庫名稱,如下所示.

      binlog-do-db=testdb1
      binlog-do-db=testdb2
      

    重啟資料庫服務.

    [root@localhost ~]# systemctl restart mariadb
    

    匯入剛剛從 Master 得到所有資料庫的備份檔案並重啟資料庫服務.

    [root@localhost ~]# mysql -u root -p < masterdatabase.sql 
    Enter password: 
    

    回到 MariaDB 指令介面來完成與 Master 同步資料的指令.

    [root@localhost ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 5.5.64-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.111.129', MASTER_USER='slaveuser', MASTER_PASSWORD='111111', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=554;
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [(none)]> START SLAVE;
    Query OK, 0 rows affected (0.00 sec)
    
    • MASTER_HOST=’192.168.111.129′
      Master 的 IP.
    • MASTER_USER=’slaveuser’
    • MASTER_PASSWORD=’111111′
      Master 有權限同步資料的使用者 slaveuser 與其密碼.
    • MASTER_LOG_FILE=’mariadb-bin.000001′
    • MASTER_LOG_POS=554;
      還記得剛剛在 Master 記錄起來 File 與 Position 值 (沒有記錄起來,請從備份 Master 資料庫重新做起,從前面紀錄的階段到現階段 Master 的資料庫有可能已有變化,其 Position 會不同,這會讓 Slave 與 Master 有一段時間的資料不同步的問題).

      +--------------------+----------+--------------+------------------+
      | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +--------------------+----------+--------------+------------------+
      | mariadb-bin.000001 |      554 | testdb       |                  |
      +--------------------+----------+--------------+------------------+
      

    檢視一下 Slave 的狀態, Slave_IO_Running 與 Slave_SQL_Running 須為 Yes,才代表成功.

    MariaDB [(none)]> SHOW SLAVE STATUS\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.111.129
                      Master_User: slaveuser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mariadb-bin.000001
              Read_Master_Log_Pos: 554
                   Relay_Log_File: mariadb-relay-bin.000002
                    Relay_Log_Pos: 531
            Relay_Master_Log_File: mariadb-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: testdb
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 554
                  Relay_Log_Space: 827
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
    1 row in set (0.00 sec)
    

測試 MariaDB Replication

  • Master
    在 Master testdb 資料庫新增 test 資料表,並新增一筆資料.

    [root@localhost ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 6
    Server version: 5.5.64-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> USE testdb;
    Database changed
    MariaDB [testdb]> CREATE TABLE test (ID INT);
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO test VALUES(1);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> SELECT * FROM test;
    +------+
    | ID   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
    
  • Slave
    在 Slave 也會看到剛剛在 Master 新增的資料.

    MariaDB [(none)]> USE testdb;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MariaDB [testdb]> SELECT * FROM test;
    +------+
    | ID   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
    

    Note: 在 Slave 新增的資料並不會同步到 Master.

遇過的錯誤訊息

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mariadb-bin.000001' at 473, the last event read from 'mariadb-bin.000001' at 473, the last byte read from 'mariadb-bin.000001' at 492.'
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

檢視一下 Salve 與 Master 的 File 與 Position 值是否相同.

MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      554 | testdb       |                  |
+--------------------+----------+--------------+------------------+
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.111.129
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 554
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testdb
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 554
              Relay_Log_Space: 827
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料