UBUNTU18+KVM+VAGRANT+KUBERNETES 7 Provisioning local disk and mariadb sl...

HI.!
This short article shows how to provision and install mariadb-slave  using local-disk provisioning.
In this case, pod must be running the server(node) that has local-disk(physically).

!!!If you want to know more about local-disk provisioning visit below site!!!
https://kubernetes.io/blog/2019/04/04/kubernetes-1.14-local-persistent-volumes-ga/

Prerequisites) Knowledge about Linux command, docker,k8s and some articles.
  If you don't mind, conveniently you can see below youtubu. Sorry for not recording good quality though.^^'
https://www.youtube.com/channel/UCRGmm0EOwn8qRXigPLuPQig
github)
https://github.com/ohyoungjooung2/u18kvk8s/tree/master/k8s/mariadb-slave

Creating mariadb-slave server.(storage is local-disk provision)
1)Creating storage class(sc) and persistent volumes for mariadb-slave
2) Create service for mariadb-slave
3) Create Stateful mariadb-slave
4) Replication test.


1)Creating storage class(sc) and persistent volumes for mariadb-slave
[vagrant@kubemaster mariadb-slave]$ cat mariadb-slave-sc.yaml
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: mariadb-slave-sc
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer


vagrant@kubemaster mariadb-slave]$ kb create -f mariadb-slave-sc.yaml
storageclass.storage.k8s.io/mariadb-slave-sc created

[vagrant@kubemaster mariadb-slave]$ kb describe sc  mariadb-slave-sc
Name:                  mariadb-slave-sc
IsDefaultClass:        No
Annotations:           <none>
Provisioner:           kubernetes.io/no-provisioner
Parameters:            <none>
AllowVolumeExpansion:  <unset>
MountOptions:          <none>
ReclaimPolicy:         Delete
VolumeBindingMode:     WaitForFirstConsumer
Events:                <none>



[vagrant@kubemaster mariadb-slave]$ vi mariadb-slave-pv.yaml
[vagrant@kubemaster mariadb-slave]$ cat mariadb-slave-pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
  name: mariadb-slave-pv0
spec:
  capacity:
    storage: 1Gi
  # volumeMode field requires BlockVolume Alpha feature gate to be enabled.
  volumeMode: Filesystem
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Delete
  storageClassName: mariadb-slave-sc
  local:
    path: /db1
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - kubeworker2
---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: mariadb-slave-pv1
spec:
  capacity:
    storage: 1Gi
  # volumeMode field requires BlockVolume Alpha feature gate to be enabled.
  volumeMode: Filesystem
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Delete
  storageClassName: mariadb-slave-sc
  local:
    path: /db1
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - kubeworker3

[vagrant@kubemaster mariadb-slave]$ kb create -f mariadb-slave-pv.yaml
persistentvolume/mariadb-slave-pv0 created
persistentvolume/mariadb-slave-pv1 created
[vagrant@kubemaster mariadb-slave]$ kb get pv
NAME                  CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS      CLAIM                                        STORAGECLASS        REASON   AGE
mariadb-master-disk   1Gi        RWO            Recycle          Bound       default/mariadb-master-st-mariadb-master-0   mariadb-master-sc            21h
mariadb-slave-pv0     1Gi        RWO            Delete           Available                                                mariadb-slave-sc             3s
mariadb-slave-pv1     1Gi        RWO            Delete           Available                                                mariadb-slave-sc             3s
[vagrant@kubemaster mariadb-slave]$




2) Create service for mariadb-slave
[vagrant@kubemaster mariadb-slave]$ vi mariadb-slave-svc.yaml
[vagrant@kubemaster mariadb-slave]$ cat mariadb-slave-svc.yaml
apiVersion: v1
kind: Service
metadata:
  name: mariadb-slave
  labels:
    app: mariadb-slave
spec:
  ports:
  - name: mariadb-slave
    port: 3306
  clusterIP: None
  selector:
    app: mariadb-slave
 

[

3) Create Stateful mariadb-slave
[vagrant@kubemaster mariadb-slave]$ vi kustomization.yaml
[vagrant@kubemaster mariadb-slave]$ cat kustomization.yaml
secretGenerator:
- name: mariadb-pass
  literals:
  - password=StrongPass$^^$
- name: rep-user
  literals:
  - rep-user=rep-user
- name: rep-password
  literals:
  - rep-password=Good$^Password!
resources:
  - mariadb-slave.yaml


[vagrant@kubemaster mariadb-slave]$ vi mariadb-slave.yaml
[vagrant@kubemaster mariadb-slave]$ cat mariadb-slave.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mariadb-slave
spec:
  selector:
    matchLabels:
      app: mariadb-slave
  serviceName: mariadb-slave
  replicas: 2
  template:
    metadata:
      labels:
        app: mariadb-slave
    spec:
      initContainers:
      - name: init-mariadb-slave
        image: ohyoungjooung2/mariadb:10.1.14-slave2
        command:
        - bash
        - "-c"
        -  |
           set -ex
           [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
           ordinal=${BASH_REMATCH[1]}
           id=$(( $ordinal + 2 ))
           echo [mysqld] > /mnt/conf.d/server-id.cnf
           echo server-id=$id >> /mnt/conf.d/server-id.cnf
           echo log-bin >> /mnt/conf.d/server-id.cnf
           echo log-basename=slave$id >> /mnt/conf.d/server-id.cnf
           echo character_set_server=utf8 >> /mnt/conf.d/server-id.cnf
           echo collation_server=utf8_general_ci >> /mnt/conf.d/server-id.cnf

        volumeMounts:
        - name: conf
          mountPath: /mnt/conf.d
       
 
      containers:
      - name: mariadb
        image: ohyoungjooung2/mariadb:10.1.14-slave2
        lifecycle:
        env:
        - name: MYSQL_ALLOW_EMPTY_PASSWORD
          value: "no"
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
               name: mariadb-pass
               key: password
        - name: MYSQL_MASTER_SERVICE_HOST
          value: mariadb-master
        
        - name: MYSQL_REPLICATION_USER
          valueFrom:
            secretKeyRef:
               name: rep-user
               key: rep-user

        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
               name: rep-password
               key: rep-password



        ports:
        - name: mariadb-slave
          containerPort: 3306
        volumeMounts:
        - name: mariadb-slave
          mountPath: /var/lib/mysql
          subPath: mariadb-sv
        - name: conf     
          mountPath: /etc/mysql/conf.d


        resources:
          requests:
            cpu: 500m
            memory: 1Gi

        livenessProbe:
          exec:
            command: ["mysqladmin", "ping"]
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5


      volumes:
      - name: conf
        emptyDir: {}
  volumeClaimTemplates:
  - metadata:
      #name: data
      name: mariadb-slave
    spec:
      accessModes: ["ReadWriteOnce"]
      storageClassName: "mariadb-slave-sc"
      resources:
        requests:
          storage: 1Gi
[vagrant@kubemaster mariadb-slave]$ kb apply -k .
secret/mariadb-pass-hmt2hb8m6g created
secret/rep-password-8bhkm96dbt created
secret/rep-user-bttd79k4t8 created
statefulset.apps/mariadb-slave created
[vagrant@kubemaster mariadb-slave]$ kb get po
NAME               READY   STATUS     RESTARTS   AGE
mariadb-master-0   1/1     Running    11         21h
mariadb-slave-0    0/1     Init:0/1   0          3s
[vagrant@kubemaster mariadb-slave]$ kb get po
NAME               READY   STATUS    RESTARTS   AGE
mariadb-master-0   1/1     Running   11         21h
mariadb-slave-0    1/1     Running   0          6s
mariadb-slave-1    0/1     Pending   0          1s
[vagrant@kubemaster mariadb-slave]$ kb get po
NAME               READY   STATUS            RESTARTS   AGE
mariadb-master-0   1/1     Running           11         21h
mariadb-slave-0    1/1     Running           0          8s
mariadb-slave-1    0/1     PodInitializing   0          3s
[vagrant@kubemaster mariadb-slave]$ kb get po
NAME               READY   STATUS    RESTARTS   AGE
mariadb-master-0   1/1     Running   11         21h
mariadb-slave-0    1/1     Running   0          10s
mariadb-slave-1    1/1     Running   0          5s


4) mariadb replication test
[vagrant@kubemaster mariadb-slave]$ kb exec -it mariadb-slave-0 bash
root@mariadb-slave-0:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.14-MariaDB-1~jessie mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mariadb-master
                  Master_User: rep-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000018
          Read_Master_Log_Pos: 329
               Relay_Log_File: slave2-relay-bin.000003
                Relay_Log_Pos: 27075
        Relay_Master_Log_File: master1-bin.000001
             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 'Table 'mysql.tmp_proxies_priv' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO  proxies_priv SELECT * FROM tmp_proxies_priv WHERE @had_proxies_priv_table=0;'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 26785
              Relay_Log_Space: 6400572
              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 'Table 'mysql.tmp_proxies_priv' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO  proxies_priv SELECT * FROM tmp_proxies_priv WHERE @had_proxies_priv_table=0;'
  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
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mariadb-master
                  Master_User: rep-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000018
          Read_Master_Log_Pos: 329
               Relay_Log_File: slave2-relay-bin.000006
                Relay_Log_Pos: 1959260
        Relay_Master_Log_File: master1-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: 1958970
              Relay_Log_Space: 6372576
              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: 80826
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
1 row in set (0.00 sec)

ERROR: No query specified

[vagrant@kubemaster mariadb-slave]$ kb exec -it mariadb-slave-1 bash
root@mariadb-slave-1:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.1.14-MariaDB-1~jessie mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mariadb-master
                  Master_User: rep-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000018
          Read_Master_Log_Pos: 329
               Relay_Log_File: slave3-relay-bin.000006
                Relay_Log_Pos: 4221995
        Relay_Master_Log_File: master1-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: 4221705
              Relay_Log_Space: 6372576
              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: 80945
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
1 row in set (0.00 sec)

MariaDB [(none)]> exit
Bye
root@mariadb-slave-1:/# exit
exit

[vagrant@kubemaster mariadb-slave]$ kb exec -it mariadb-master-0 -- bash

MariaDB [(none)]> CREATE DATABASE tttt;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use tttt;
Database changed
MariaDB [tttt]> create table tt (name varchar(20));
Query OK, 0 rows affected (0.04 sec)

MariaDB [tttt]> insert into tt values('ttttt');
Query OK, 1 row affected (0.01 sec)

MariaDB [tttt]> insert into tt values('tttttdddd');
Query OK, 1 row affected (0.00 sec)

MariaDB [tttt]> insert into tt values('Tom');
Query OK, 1 row affected (0.01 sec)

MariaDB [tttt]> insert into tt values('Jerry');
Query OK, 1 row affected (0.00 sec)



MariaDB [tttt]> exit
Bye
root@mariadb-master-0:/# exit
exit

[vagrant@kubemaster mariadb-slave]$ kb exec -it mariadb-slave-0 bash
root@mariadb-slave-0:/# mysql -e 'select * from tttt.tt'
+-----------+
| name      |
+-----------+
| ttttt     |
| tttttdddd |
| Tom       |
| Jerry     |
+-----------+

[vagrant@kubemaster mariadb-slave]$ kb exec -it mariadb-slave-1 bash
root@mariadb-slave-1:/# mysql -e 'select * from tttt.tt'
+-----------+
| name      |
+-----------+
| ttttt     |
| tttttdddd |
| Tom       |
| Jerry     |
+-----------+


Conclusion)
In this  article, I introduce how to provisioing mariadb slave and and storages.
In real production, database server's installation would be better on bare metal server, but this solution could be considered if someone like
all pod and container native.



If you know more about statefulset, please visit https://kubernetes.io/docs/concepts/workloads/controllers/statefulset/