PostgreSQL17 主從異步流複製詳情 - SSL,SQL,Server,雲原生,雲計算 mob64ca140d61c6 博客

一異步複製(Asynchronous replication)

1.MariaDB本身支持單向的、異步的複製。異步複製意味着在把數據從一台機器拷貝到另一台機器時有一個延時，最重要的是這意味着當應用系統的事務提交已經確認時數據並不能在同一時刻拷貝，應用到從機。通常這個延時是由網絡帶寬、資源可用性和系統負載決定的。然而，使用正確的組件並且調優，複製能做到接近瞬時完成。

2.MariaDB默認的複製即是異步的，主庫在執行完客户端提交的事務後會立即將結果返給給客户端，並不關心從庫是否已經接收並處理，這樣就會有一個問題，主如果crash掉了，此時主上已經提交的事務可能並沒有傳到從上，如果此時，強行將從提升為主，可能導致新主上的數據不完整。

二實驗過程

1.時間要進行同步

2.注意複製的開始位置
從0開始
從備份中恢復到從節點後啓動的複製，複製的起始點備份操作時主節點所處的日誌文件及其實踐位置

3.配置文件
主節點：

[root@node1 ~]# vim /etc/my.cnf.d/server.cnf
skip_name_resolve=ON
innodb_file_per_table=ON
server_id=1
log_bin=log-bin

從節點：

[root@node2 ~]# vim /etc/my.cnf.d/server.cnf/etc/my.cnf.d/server.cnf
skip_name_resolve=ON
innodb_file_per_table=ON
server_id=2
relay_log=relay-log

4.啓動服務，並進入MariaDB

[root@node1 ~]# systemctl start mariadb.service
[root@node2 ~]# systemctl start mariadb.service

5.對數據庫庫進行操作
在主節點上創建用户並授權

MariaDB [(none)]> CREATE USER 'repluser'@'172.18.67.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.18.67.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000001 |      497 |              |                  |
+----------------+----------+--------------+------------------+

為從節點關聯主節點

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.18.67.11',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='log-bin.000001',MASTER_LOG_POS=497;

查看從節點狀態

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.18.67.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000001
          Read_Master_Log_Pos: 497
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          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: 497
              Relay_Log_Space: 245
              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: NULL
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: 0
1 row in set (0.00 sec)

開啓IO和SQL線程

MariaDB [(none)]> HELP START SLAVE;
MariaDB [(none)]> HELP STOP SLAVE;
MariaDB [(none)]> START SLAVE IO_THREAD;
MariaDB [(none)]> START SLAVE SQL_THREAD;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.18.67.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000001
          Read_Master_Log_Pos: 497
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 497
              Relay_Log_Space: 245
              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: NULL
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: 0
1 row in set (0.00 sec)

查看從節點狀態，IO和SQL線程已經啓動：Slave_IO_Running: Yes，Slave_SQL_Running: Yes
在主節點上創建數據庫mydb

MariaDB [(none)]> CREATE DATABASE mydb CHARACTER SET 'utf8';
MariaDB [(none)]> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000001 |      601 |              |                  |
+----------------+----------+--------------+------------------+
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

查看從節點的狀態

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.67.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000001
          Read_Master_Log_Pos: 601
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 631
        Relay_Master_Log_File: log-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 601
              Relay_Log_Space: 919
              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)

此時我們可以從從節點上觀察到Read_Master_Log_Pos由497變為了631。為了使實驗更加深入，準備了一個數據庫，我們將其導入到主節點

[root@node1 ~]# mysql < hellodb.sql
MariaDB [(none)]> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000001 |     8011 |              |                  |
+----------------+----------+--------------+------------------+

再次查看從節點狀態

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.67.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000001
          Read_Master_Log_Pos: 8011
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 8041
        Relay_Master_Log_File: log-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 8011
              Relay_Log_Space: 8329
              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 [(none)]> use hellodb;
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

我們觀察到主節點的數據發生變化，從節點的數據庫都會同步。

三複製時應該注意的問題

1.從服務設定為“只讀”
在從服務器啓動read_only，但僅對非SUPER權限的用户有效
阻止所有用户：MariaDB [(none)]> FLUSH TABLES WITH READ LOCK

2.儘量確保複製時的事務安全
在master節點啓用參數：
sync_binlog = ON
如果用到的是InnoDB存儲引擎：
innodb_flush_logs_at_trx_commit=ON
innodb_support_xa=ON
3.從服務器意外中止時儘量避免自動啓動複製線程

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

mob64ca140d61c6 博客

mob64ca140d61c6 博客

博客 / 詳情