Owen Ouyang's Home

Daily Life

0%

mariadb 主從伺服器

發表於 更新於 Disqus：
文章字數： 11k 所需閱讀時間 ≈ 10 分鐘

讀寫分離

我們可以設定兩台 mysql 可以同步資料，分為主及從伺服器。
一般主伺服器是寫入，而從伺服器是用來讀出資料。

如果設定得到，可以用多台伺服器來分擔負載。

master slave 配置

master-slave

master: master1 (172.19.0.2)
slave: slave1 (172.19.0.3)

docker-compose.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
version: '2.2'

services:
 master1:
  image: yobasystems/alpine-mariadb
  restart: always
  hostname: master1
  container_name: master1
  volumes:
   - /master1.cnf:/etc/my.cnf.d/mariadb-server.cnf
  ports:
   - 3301:3306
  environment:
   - MYSQL_ROOT_PASSWORD=my-secret-pw
   - MYSQL_DATABASE=wordpress
   - MYSQL_USER=slave
   - MYSQL_PASSWORD=my-secret-pw
 slave1:
  image: yobasystems/alpine-mariadb
  restart: always
  hostname: slave1
  container_name: slave1
  volumes:
   - /slave1.cnf:/etc/my.cnf.d/mariadb-server.cnf
  ports:                                          
   - 3302:3306                                    
  environment:                                    
   - MYSQL_ROOT_PASSWORD=my-secret-pw
   - MYSQL_DATABASE=wordpress
   - MYSQL_USER=slave                
   - MYSQL_PASSWORD=my-secret-pw

master cnf

  • master1.cnf

    1
    2
    3
    4
    5
    6
    7
    [mysqld]
    server-id=1
    log-bin=mysql-bin
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-do-db=wordpress
    binlog_format=STATEMENT

    slave cnf

  • slave1.cnf

    1
    2
    3
    4
    autocommit=1
    log-bin=mysql-bin
    server-id=2
    lower_case_table_names=1

    啟動

1
$ docker-compose up -d master1 slave1

slave 權限

在 master1 上執行

1
$ docker exec -ti master1 mysql -uroot -pmy-secret-pw wordpress
1
MariaDB [wordpress]> grant replication slave on *.* to 'slave'@'%' identified by'my-secret-pw'; flush privileges; 
1
2
3
4
5
6
7
8
9
MariaDB [wordpress]> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 1308
    Binlog_Do_DB: wordpress
Binlog_Ignore_DB: mysql,information_schema
1 row in set (0.001 sec)

ERROR: No query specified

slave 設定

在 slave1 上執行

1
$ docker exec -ti slave1 mysql -uroot -pmy-secret-pw wordpress

設定連上 master1 資訊。

1
MariaDB [wordpress]> CHANGE MASTER TO MASTER_HOST = 'master1', MASTER_USER = 'slave', MASTER_PASSWORD = 'my-secret-pw';
1
MariaDB [wordpress]> start slave;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
MariaDB [wordpress]> show slave status \G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: master1
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 1308
                Relay_Log_File: slave1-relay-bin.000004
                 Relay_Log_Pos: 1607
         Relay_Master_Log_File: mysql-bin.000003
              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: 1308
               Relay_Log_Space: 2487
               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: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 6
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.002 sec)

最重要的是要看到下面兩行出現，就代表我們的設定正確了。

1
2
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

驗證

先在 slave1 上確認狀態

1
$ docker exec -ti slave1 mysql -uroot -pmy-secret-pw wordpress
1
MariaDB [wordpress]> show tables;

現在應該看到沒有任何 table。

在 master1 上執行

1
$ docker exec -ti master1 mysql -uroot -pmy-secret-pw wordpress

我們在 master1 上建立新的 table，然後在 slave1 上確認會同步過去。

1
MariaDB [wordpress]> create table contacts ( contact_id INT(11) not null auto_increment,last_name varchar(30) not null, first_name varchar(25), birthday date, constraint contacts_pk primary key (contact_id));

目前在 master1 上我們新建立了一個 contacts 的 table;
我們如果設定都正確，應該可以在 slave1 上看到同樣的 table 被建立了。

在 slave1 上。

1
$ docker exec -ti slave1 mysql -uroot -pmy-secret-pw wordpress
1
2
3
4
5
6
7
8
MariaDB [wordpress]> show tables;
+---------------------+
| Tables_in_wordpress |
+---------------------+
| contacts            |
+---------------------+
1 row in set (0.001 sec)

2 master 配置

雙主環境

host OS MariaDB
masterm1 alpine 3.11 10.4.15-MariaDB
masterm2 alpine 3.11 10.4.15-MariaDB 
1
注意：主從複製，主主複製時，主從服務器的時間也要同步，需要有同一個同步源，數據庫的版本也最好一致。

docker-compose

基本的安裝和設定 mariadb 不是我們的重點，我們以 docker 來建立 mariadb 的伺服器。
我們是用 docker-compose 來建立實驗環境。有 docker 來協助，我們有很多方便的地方。
網路的問題和兩個伺服器主機的獨立性也得到了保證。

1
2
3
為了方便，我們在系統中建立了兩個用戶 root 及 slave。
我們都是用 slave 帳號來同步彼此的資料。
預設我們建立了 wordpress 的 database 用以驗證結果。
  • root 密碼 my-secret-pw
  • 一般用戶 slave 密碼 my-secret-pw
  • 設定檔是 masterm1.cnf/masterm2.cnf
  • 伺服器對外 port 是 3331/3332
  • 伺服器的 hostname/container_name 都是 masterm1/masterm2
  • masterm1 及 masterm2 因為 docker 的幫助可以用彼此的名字互相網路相通
  • masterm1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
masterm1:
 image: yobasystems/alpine-mariadb
 restart: always
 hostname: masterm1
 container_name: masterm1
 volumes:
  - /db/master/masterm1.cnf:/etc/my.cnf.d/mariadb-server.cnf
 ports:
  - 3331:3306
 environment:
  - MYSQL_ROOT_PASSWORD=my-secret-pw
  - MYSQL_DATABASE=wordpress
  - MYSQL_USER=slave
  - MYSQL_PASSWORD=my-secret-pw
  • masterm2
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    masterm2: 
     image: yobasystems/alpine-mariadb                       
     restart: always
     hostname: masterm2
     container_name: masterm2
     volumes:                           
      - /db/master/masterm2.cnf:/etc/my.cnf.d/mariadb-server.cnf
     ports:             
      - 3332:3306                  
     environment:
      - MYSQL_ROOT_PASSWORD=my-secret-pw
      - MYSQL_DATABASE=wordpress
      - MYSQL_USER=slave
      - MYSQL_PASSWORD=my-secret-pw

    伺服器設定檔

伺服器設定裡有很多項可以調整，在我們的例子中我們一定要設定的是 server-id 及 log-bin。

  • server-id : masterm1 是 3331 而 masterm2 是 3332
  • log-bin : 都是設為 mysql-bin
  • masterm1
1
2
3
4
5
6
7
[mysqld]
server-id=3331
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#binlog-do-db=wordpress
binlog_format=STATEMENT
  • masterm2
1
2
3
4
5
6
7
[mysqld]
server-id=3332
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#binlog-do-db=wordpress
binlog_format=STATEMENT

下面是一些其他的設定，及相對的說明，供參考。

1
2
3
4
5
6
7
8
9
10
11
12
datadir = /data/madata/mysql/		# 數據庫數據文件存放目錄-->也是同步目錄
log-bin = log-bin = /var/lib/mysql/binlogs/master1-bin        #啓用二進制日誌
relay-log = /var/lib/mysql/relaylogs/relay-bin                #啓用中繼日誌
auto_increment_increment = 1    #設定為主服務器的數量，防止auto_increment字段重復
auto_increment_offset = 2       #自增長字段的初始值，在多台master環境下，不會出現自增長ID衝突
server-id = 131                 # 用於標識該語句最初是從哪個server寫入的，server-id一定要有的 
log-error = /data/mdata/logs/mariadb.err 		#記錄錯誤日誌文件
log-slave-updates = on          # 建議開啓，多主多從時要開啓，否則不能同步
expire-logs-days = 14           # 日誌過期時間
sync-binlog = 1                 # # 確保主從事務安全
 
#binlog-ignore-db=mysql          # 設置忽略binlog數據庫-->根據需要自行設置

啟動兩個 mariadb

1
$ docker-compose up -d masterm1 masterm2

設定 masterm1 及 masterm2 的 slave 帳戶權限

讓 slave 可以在彼此之間有同步的權限。

1
2
3
$ sql="grant replication slave on *.* to 'slave'@'%' identified by'my-secret-pw'; flush privileges;"
$ docker exec masterm1 mysql -uroot -pmy-secret-pw -e "$sql" wordpress
$ docker exec masterm2 mysql -uroot -pmy-secret-pw -e "$sql" wordpress

打通 masterm1 及 masterm2 的 slave 主從連接帳戶

1
2
3
4
sql="CHANGE MASTER TO MASTER_HOST = 'masterm2', MASTER_USER = 'slave', MASTER_PASSWORD = 'my-secret-pw';start slave;"
docker exec masterm1 mysql -uroot -pmy-secret-pw -e "$sql" wordpress
sql="CHANGE MASTER TO MASTER_HOST = 'masterm1', MASTER_USER = 'slave', MASTER_PASSWORD = 'my-secret-pw';start slave;"
docker exec masterm2 mysql -uroot -pmy-secret-pw -e "$sql" wordpress

參數解釋：

  • MASTER_HOST : 設置要連接的主服務器的ip地址
  • MASTER_PORT : 設置要連接的主服務器的端口
  • MASTER_USER : 設置要連接的主服務器的用戶名
  • MASTER_PASSWORD : 設置要連接的主服務器的密碼
  • MASTER_LOG_FILE : 設置要連接的主服務器的bin日誌的日誌名稱，即第4步得到的信息
  • MASTER_LOG_POS : 設置要連接的主服務器的bin日誌的記錄位置，即第4步得到的信息，（這裡注意，最後一項不需要加引號。否則配置失敗）

檢視 slave 狀態

最主要確認下面兩項都是 yes。

1
2
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
  • Slave_IO_Running: 這是I/O線程狀態，I/O線程負載從從庫去主庫讀取binlog日誌，並寫入從庫的中繼日誌中，狀態為Yes表示I/O線程工作正常。
  • Slave_SQL_Running: 這個是SQL線程狀態，SQL線程負載讀取中繼日誌（relay-log）中的數據並轉換為SQL語句應用到從庫數據庫中，狀態為Yes表示I/O線程工作正常

如果有問題，可以從下面四個項目中找到一些線索

1
2
3
4
 Last_IO_Errno: 0
 Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error:

masterm1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
$ docker exec masterm1 mysql -uroot -pmy-secret-pw wordpress -e 'show slave status \G;'
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: masterm2
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 675
                Relay_Log_File: masterm1-relay-bin.000004
                 Relay_Log_Pos: 974
         Relay_Master_Log_File: mysql-bin.000003
              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: 675
               Relay_Log_Space: 2009
               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: 3332
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 6
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

masterm2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
$ docker exec masterm2 mysql -uroot -pmy-secret-pw wordpress -e 'show slave status \G;'
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: masterm1
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 675
                Relay_Log_File: masterm2-relay-bin.000004
                 Relay_Log_Pos: 974
         Relay_Master_Log_File: mysql-bin.000003
              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: 675
               Relay_Log_Space: 2009
               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: 3331
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 6
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

從庫提升為主庫

1
2
sql="stop slave;reset slave all;"
docker exec masterm2 mysql -uroot -pmy-secret-pw -e "$sql" wordpress

RESET SLAVE ALL 是清除從庫的同步複製信息，包括連接信息和二進制文件名、位置
從庫上執行這個命令後，使用 show slave status 將不會有輸出

雙主雙從

基本架構相同，兩個 master 及兩個 slave。

host OS MariaDB port
mastera1 alpine 3.11 10.4.15-MariaDB 3341
slavea1 alpine 3.11 10.4.15-MariaDB 3342
mastera2 alpine 3.11 10.4.15-MariaDB 3343
slavea2 alpine 3.11 10.4.15-MariaDB 3344
  1. 啟動四個全新的 MariaDB container，建立好同樣的 slave 帳號。
  2. 設定好 slave 來同步資料的帳號權限。
  3. 設定好 mastera1 及 slavea1 和 mastera2 及 slavea2 同步帳號及連通，並啟動 slave。
  4. 設定 mastera1 及 mastera2 之間的同步連通帳號，並啟動 slave 。
  5. 驗證建立 database 可以在任一 db 伺服器中可以同步到更新。

原理和上面差不多，只是設定上多了兩個slave，我們直接看錄影。

github repo

上面提用到的所有資料都可以從 github 找到

參考 README.md 了解如何使用這些資料

結論

其實整個設定程序上都很簡易，讓我們好好使用 mysql 吧。