iT邦幫忙

1

MySQL 主從設定

使用時機:
1. 資料庫效能慢的時候
2. 就是想讀寫分離的時候

主從分別叫做Master, Slave,通常用Master來做寫入/變更/刪除;也就是會改變資料內容的命令,而Slave只用來做查詢;也就是不會改變資料的命令,而簡單理解Master/Slave之間的同步過程,就是master被修改時會傳送事件給所有的replicas,讓replicas也做一次相同的操作,即完成同步。

本次實作流程如下:
先創建mysql作為master,經過日常操作後多了一些資料,之後想要做主從時,先在master做一些設定,然後再創建一個mysql作為slave,也在slave做一些設定,經過雙邊的設定後,最後測試在master上的增刪改是否會即時通知給slave同步。
本次範例使用docker實作mysql,故會將m/s分別創建兩個容器,並使用內部局網互通

創建network

創建docker內部網路,命名為db,並設置網段為172.20.0.0/16
docker network create custom-db --subnet 172.20.0.0/16

創建mysql

vi docker-compose.yml

version: '3.1'

services:

  db-master:
    image: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 1qaz@WSX
    volumes:
      - '/etc/localtime:/etc/localtime:ro'
      - ./db-master:/var/lib/mysql
    ports:
      - 33061:3306
    networks:
      custom-db: # 使用內部網路db
        ipv4_address: 172.20.0.10 


networks:
  custom-db: # 引入內部網路db
    external: true

docker-compose 運行
docker-compose up -d

日常操作

創新表並隨意插入兩筆資料

+----+--------+----------+
| id | color  | plate    |
+----+--------+----------+
|  1 | red    | RMB-3312 |
|  2 | yellow | RCD-9013 |
+----+--------+----------+
2 rows in set (0.000 sec)

Master設定啟用log-bin 這是主從的起手式

修改配置檔
vi /etc/mysql/mariadb.cnf

[mysqld]:
log-bin=mysql-bin # 使用mysql-bin做紀錄
server-id=1 # 服務器id
binlog_do_db=mydb # 需要同步的資料庫名稱
max_binlog_size=100M # binlog檔案最大容量

重啟
/etc/init.d/mariadb restart

然後需要添加一個新帳號,叫做bak給slave使用
grant replication slave on *.* to "bak"@'%' identified by "12345";
上面的replication slave 是關鍵字
更新權限
flush privileges;
鎖定資料表避備份過程中被修改
use mydb;
FLUSH TABLES WITH READ LOCK;
查看當自己身為master的狀態

MariaDB [mydb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      328 | mydb         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

上方的log file以及position要記得,等下slave會需要用到
將資料庫導出
mysqldump -u root -p1qaz@WSX --opt mydb > /mydb.sql
解除只讀鎖
unlock tables;

鎖能確保備份的當下mysql-bin.000003的position 328不會改變,實現slave再導入資料時,接著這個位置同步,能不移失資料。

創建mysql (slave)

修改原本的docker-compose.yml 添加db-slave

version: '3.1'

services:
  db-master:
    ...
    
  db-slave:
    image: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 1qaz@WSX
    volumes:
      - '/etc/localtime:/etc/localtime:ro'
      - ./db-slave:/var/lib/mysql
      - ./db-slave-conf:/etc/mysql
    ports:
      - 33062:3306
    networks:
      custom-db: # 使用內部網路db
        ipv4_address: 172.20.0.20 


networks:
  custom-db: # 引入內部網路db
    external: true

Slave設定

先匯入master的資料(記得先create database mydb之後才能導入資料)
mysql -u root -p1qaz@WSX mydb < mydb.sql

修改配置檔
vi /etc/mysql/mariadb.cnf

[mysqld]:
log-bin=mysql-bin # 使用mysql-bin做紀錄
server-id=2 # 服務器id (別跟master以及其他slave重複即可)
binlog_do_db=mydb # 需要同步的資料庫名稱
max_binlog_size=100M # binlog檔案最大容量

重啟
/etc/init.d/mariadb restart
設定自己的master_host為db-master位置,並將bak使用者寫入
CHANGE MASTER TO MASTER_HOST='172.20.0.10', MASTER_USER='bak', MASTER_PASSWORD='12345', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=328;
開始扮演slave腳色
start slave
此時/var/lib/mysql/master.info這個檔案會自動被產生,若要變更master_host等相關資訊時,建議先手動stop slave,然後手動將這個檔案移除後,再CHANGE MASTER ... 然後再重新start slave

撿查master與slave狀態

show master status;
show slave status\G;

MariaDB [mydb]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.20.0.10
                   Master_User: bak
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 551
                Relay_Log_File: mysqld-relay-bin.000004
                 Relay_Log_Pos: 850
         Relay_Master_Log_File: mysql-bin.000004
              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: 551
               Relay_Log_Space: 1459
               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
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
1 row in set (0.000 sec)

show processlist;

MariaDB [mydb]> show processlist\G;
*************************** 1. row ***************************
      Id: 5
    User: system user
    Host:
      db: NULL
 Command: Slave_IO
    Time: 7547
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 6
    User: system user
    Host:
      db: NULL
 Command: Slave_SQL
    Time: 1548
   State: Slave has read all relay log; waiting for more updates
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 19
    User: root
    Host: localhost
      db: mydb
 Command: Query
    Time: 0
   State: starting
    Info: show processlist
Progress: 0.000
3 rows in set (0.000 sec)


同步測試

在master對mydb做insert/update/delete之後,slave能查到修改後的資料。

若是相互同步,也就是兩個都要完整的CRUD操作,就是彼此互為主從即可。雙方都做一樣的設定,開一個帳號給對方使用,只有server-id仍然不同即可。


1 則留言

0
雷N
iT邦新手 4 級 ‧ 2021-02-09 09:17:53

DB replication = CQRS @@?

看更多先前的回應...收起先前的回應...
JohnnyPy iT邦新手 5 級 ‧ 2021-02-09 10:32:17 檢舉

還請多指教,我的理解是只要能實現讀寫分離,就是做CQRS。只是實作方式有所不同,但既然要分離,勢必會將服務容器切開,並且相互同步,讓使用者完全感受不到資料在不同地方。因此,我認為將DB做replica是一個實做CQRS的起手式。您說呢?

雷N iT邦新手 4 級 ‧ 2021-02-09 23:22:45 檢舉

以下是我自己的理解
關聯式資料庫效能慢或吞吐量低落, 原因非常的多;
初期在流量快速增長的階段, 的確都會先做讀寫分離, 但好像沒正面打中CQRS主要想解的痛點;
CQRS主要解套的是查詢這裡, 因為正歸化或者查詢非常多分枝條件且join關聯到很多表, 導致資料庫這時後會慢.

所以CQRS的Q+R是說查詢用的Repository已經是經過整理, 簡單做法可能就單獨只讀庫有一張表或NoSQL, 來放這些複雜查詢的內容, 直接透過簡單扼要的key來快速查到表達層所需要的內容.
舉例訂單畫面要呈現, 訂單資訊+商品資訊+用戶資訊+物流資訊.
以前可能要join這麼多張表, 現在可能就PK是訂單ID, 然後有其他欄位是訂單月份/訂單日期/物流資料/複雜的表達層Data.

雷N iT邦新手 4 級 ‧ 2021-02-09 23:40:18 檢舉

martinfowler-CQRS

The two models might not be separate object models, it could be that the same objects have different interfaces for their command side and their query side, rather like views in relational databases. But usually when I hear of CQRS, they are clearly separate models.

這裡最後寫通常談到CQRS, 查詢跟寫入模型往往是不同的.
DB讀寫分離, 查詢出來的好像還是相同的

以上是我的理解
其實我只是想問問大家, 是否覺得DB讀寫分離==CQRS呢?

JohnnyPy iT邦新手 5 級 ‧ 2021-02-12 22:56:01 檢舉

您說的很有道裡,我需要再好好研究,謝謝。

我要留言

立即登入留言