MariaDB - replication

1. 개요

- MariaDB replication을 구성한다.

   ✓ Primary DB는 읽기/쓰기용으로, Secondary DB는 읽기용으로 사용해야 한다.

   ✓ Secondary DB는 0개부터 n개까지 구성할 수 있다.

- MariaDB 기본 설치는 'MariaDB'를 참조한다.

 

 

2. Environments

- helm chart bitnami/mariadb 9.7.0 

- MariaDB 10.15.12

- Helm 3.3.1

- Kubernetes 1.16.15

 

 

3. MariaDB replication 구성

- helm chart용 parametes yaml 파일 생성 및 replication 구성

   ✓ architecture, rootPassword, database, replicationPassword, primary service type 변경

$ helm inspect values bitnami/mariadb --version 9.7.0 > bizcollabo_9.7.0.values
$ vi bizcollabo_9.7.0.values
...
architecture: replication       # default: standalone

auth:
  rootPassword: "root"
  database: bizcollabo
  replicationPassword: "repli"
...   
primary:
  ...
  service:
    type: NodePort
...
$

 

- MariaDB replication 생성

$ helm install bizcollabo-mariadb bitnami/mariadb --create-namespace --namespace ontact --values bizcollabo_9.7.0.values
NAME: bizcollabo-mariadb
LAST DEPLOYED: Fri Oct 29 17:24:30 2021
NAMESPACE: ontact
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
CHART NAME: mariadb
CHART VERSION: 9.7.0
APP VERSION: 10.5.12
 
** Please be patient while the chart is being deployed **
 
Tip: 
  Watch the deployment status using the command: kubectl get pods -w --namespace ontact -l app.kubernetes.io/instance=bizcollabo-mariadb
 
Services:
  echo Primary: bizcollabo-mariadb-primary.ontact.svc.cluster.local:3306
  echo Secondary: bizcollabo-mariadb-secondary.ontact.svc.cluster.local:3306
 
Administrator credentials:
  Username: root
  Password : $(kubectl get secret --namespace ontact bizcollabo-mariadb -o jsonpath="{.data.mariadb-root-password}" | base64 --decode)
 
To connect to your database:
  1. Run a pod that you can use as a client: 
      kubectl run bizcollabo-mariadb-client --rm --tty -i --restart='Never' --image  docker.io/bitnami/mariadb:10.5.12-debian-10-r70 --namespace ontact --command -- bash
 
  2. To connect to primary service (read/write):
      mysql -h bizcollabo-mariadb-primary.ontact.svc.cluster.local -uroot -p bizcollabo
 
  3. To connect to secondary service (read-only):
      mysql -h bizcollabo-mariadb-secondary.ontact.svc.cluster.local -uroot -p bizcollabo
 
To upgrade this helm chart:
 
  1. Obtain the password as described on the 'Administrator credentials' section and set the 'auth.rootPassword' parameter as shown below:
      ROOT_PASSWORD=$(kubectl get secret --namespace ontact bizcollabo-mariadb -o jsonpath="{.data.mariadb-root-password}" | base64 --decode)
      helm upgrade --namespace ontact bizcollabo-mariadb bitnami/mariadb --set auth.rootPassword=$ROOT_PASSWORD
$

 

- MariaDB 생성 결과 확인

   Primary와 Secondary DB용 Pod와 Service 그리고 PVC가 생성되었다.

   Primary DB 주소는 'bizcollabo-mariadb-primary'이고, Secondary DB 주소는 'bizcollabo-mariadb-secondary'이다.

$ k get pod -n ontact -o wide
NAME                             READY   STATUS    RESTARTS   AGE   IP            NODE           NOMINATED NODE   READINESS GATES
bizcollabo-mariadb-primary-0     1/1     Running   0          22m   10.244.4.97   acp-worker02   <none>           <none>
bizcollabo-mariadb-secondary-0   1/1     Running   0          22m   10.244.4.98   acp-worker02   <none>           <none>
$ k get svc -n ontact
NAME                           TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE
bizcollabo-mariadb-primary     NodePort    10.108.104.232   <none>        3306:32751/TCP   9m36s
bizcollabo-mariadb-secondary   ClusterIP   10.99.135.195    <none>        3306/TCP         9m36s
$ k get pvc -n ontact
NAME                                  STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
data-bizcollabo-mariadb-primary-0     Bound    pvc-955dd962-ef07-42cd-8f78-938021a8d012   8Gi        RWO            nfs-sc-acp     10m
data-bizcollabo-mariadb-secondary-0   Bound    pvc-355fee16-36c4-4172-8db0-776ac1929932   8Gi        RWO            nfs-sc-acp     10m
$

 

 

- Slave 정보 확인

   'show slave hosts' 명령어로 Slave host('10.244.4.98')를 확인할 수 있다. 해당 IP 주소는 'bizcollabo-mariadb-secondary-0' Pod이다.

$ kubectl run bizcollabo-mariadb-client --rm --tty -i --restart='Never' --image  docker.io/bitnami/mariadb:10.5.12-debian-10-r70 --namespace ontact --command -- bash
I have no name!@bizcollabo-mariadb-client:/$  mysql -h bizcollabo-mariadb-primary.ontact.svc.cluster.local -uroot -p bizcollabo
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 60
Server version: 10.5.12-MariaDB-log Source distribution
 
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 [bizcollabo]> show slave hosts;
+-----------+-------------+------+-----------+
| Server_id | Host        | Port | Master_id |
+-----------+-------------+------+-----------+
|       876 | 10.244.4.98 | 3306 |       366 |
+-----------+-------------+------+-----------+
1 row in set (0.000 sec)
 
MariaDB [bizcollabo]> show processlist;
+----+------------+--------------------+------------+-------------+------+---------------------------------------------------------------+------------------+----------+
| Id | User       | Host               | db         | Command     | Time | State                                                         | Info             | Progress |
+----+------------+--------------------+------------+-------------+------+---------------------------------------------------------------+------------------+----------+
|  6 | replicator | 10.244.4.98:36152  | NULL       | Binlog Dump |  368 | Master has sent all binlog to slave; waiting for more updates | NULL             |    0.000 |
| 73 | root       | 10.244.4.100:43918 | bizcollabo | Query       |    0 | starting                                                      | show processlist |    0.000 |
+----+------------+--------------------+------------+-------------+------+---------------------------------------------------------------+------------------+----------+
2 rows in set (0.000 sec)

MariaDB [bizcollabo]>

 

 

4. MariaDB replication 수 변경

- replication 증가

   ✓ 'k scale' 명령어로 replication 수를 변경할 수 있다.

   ✓ 예제에서는  replicas를 1에서 2로 증가하였고, bizcollabo-mariadb-secondary-1 Pod가 신규로 생성되고 데이터가 복제되었다.

   ✓ bizcollabo-mariadb-secondary 서비스의 endpoint에 신규로 생성된 Pod의 IP 주소(10.244.7.92)가 추가되었다.

$ k scale statefulset bizcollabo-mariadb-secondary -n ontact --replicas=2
statefulset.apps/bizcollabo-mariadb-secondary scaled
$ k get pod -n ontact  -o wide
NAME                             READY   STATUS    RESTARTS   AGE     IP             NODE           NOMINATED NODE   READINESS GATES
bizcollabo-mariadb-primary-0     1/1     Running   0          112m    10.244.4.97    acp-worker02   <none>           <none>
bizcollabo-mariadb-secondary-0   1/1     Running   0          6m40s   10.244.4.110   acp-worker02   <none>           <none>
bizcollabo-mariadb-secondary-1   1/1     Running   0          64s     10.244.7.92    acp-worker01   <none>           <none>
$ k get pvc -n ontact
NAME                                  STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
data-bizcollabo-mariadb-primary-0     Bound    pvc-955dd962-ef07-42cd-8f78-938021a8d012   8Gi        RWO            nfs-sc-acp     111m
data-bizcollabo-mariadb-secondary-0   Bound    pvc-751d87b0-5ecd-436c-962c-29a4a3cdc026   8Gi        RWO            nfs-sc-acp     5m54s
data-bizcollabo-mariadb-secondary-1   Bound    pvc-e91b6825-1fd5-4754-9ab7-87be28f3ef19   8Gi        RWO            nfs-sc-acp     18s
$ k get svc -n ontact
NAME                           TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE
bizcollabo-mariadb-primary     NodePort    10.108.104.232   <none>        3306:32751/TCP   111m
bizcollabo-mariadb-secondary   ClusterIP   10.99.135.195    <none>        3306/TCP         111m
$ k describe svc bizcollabo-mariadb-secondary -n ontact | grep Endpoints
Endpoints:         10.244.4.110:3306,10.244.7.92:3306
$

 

- replication 제거

   replicas를 0으로 설정하면, Secondary DB가 삭제된다. PVC는 수동으로 삭제해야 한다.

$ k scale statefulset bizcollabo-mariadb-secondary -n ontact --replicas=0
statefulset.apps/bizcollabo-mariadb-secondary scaled
$ k get pod -n ontact
NAME                             READY   STATUS        RESTARTS   AGE
bizcollabo-mariadb-primary-0     1/1     Running       0          103m
$ k get pv | grep biz
pvc-355fee16-36c4-4172-8db0-776ac1929932   8Gi   RWO   Delete   Bound   ontact/data-bizcollabo-mariadb-secondary-0   nfs-sc-acp   104m
pvc-955dd962-ef07-42cd-8f78-938021a8d012   8Gi   RWO   Delete   Bound   ontact/data-bizcollabo-mariadb-primary-0     nfs-sc-acp   104m
$ k delete pv pvc-355fee16-36c4-4172-8db0-776ac1929932
...
$

 

 

5. 주의 사항

- Secondary DB는 읽기용으로만 사용해야 한다.

   Secondary DB에 non select 트랜젝션을 입력하면 에러 없이 실행된다.

   Primary DB에서 연관된 테이블에 트랜젝션이 발생시, Secondary DB에서 복제시 에러가 발생되며 에러를 해결하기 전까지는 복제가 중단 단다.

$ kubectl run bizcollabo-mariadb-client --rm --tty -i --restart='Never' --image  docker.io/bitnami/mariadb:10.5.12-debian-10-r70 --namespace ontact --command -- bash
I have no name!@bizcollabo-mariadb-client:/$ mysql -h bizcollabo-mariadb-secondary.ontact.svc.cluster.local -uroot -p bizcollabo
Enter password:
...
MariaDB [bizcollabo]> drop table tab1;
Query OK, 0 rows affected (0.006 sec)

MariaDB [bizcollabo]>

MariaDB [bizcollabo]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: bizcollabo-mariadb-primary
                   Master_User: replicator
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 1283
                Relay_Log_File: mysql-relay-bin.000004
                 Relay_Log_Pos: 1220
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             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: 1146
                    Last_Error: Error executing row event: 'Table 'bizcollabo.tab1' doesn't exist'
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 921
               Relay_Log_Space: 3364
               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: 1146
                Last_SQL_Error: Error executing row event: 'Table 'bizcollabo.tab1' doesn't exist'
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 366
                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_DDL_Groups: 6
Slave_Non_Transactional_Groups: 2
    Slave_Transactional_Groups: 5
1 row in set (0.000 sec)
 
MariaDB [bizcollabo]>