KubernetesでMariaDB+Maxscaleを起動してみる

Windows 10
Docker Desktop 2.1.0.4
　Engine: 19.03.4
　Compose: 1.24.1
　Kuberneteis: v1.14.7

MariaDB 10.4.8
Maxscale 2.4.2

```mariadb.yaml
apiVersion: v1
kind: Service
metadata:
  name: mariadb-svc
  labels:
    app: mariadb
spec:
  selector:
    app: mariadb
  clusterIP: None
  ports:
  - name: mariadb
    port: 3306
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mariadb-config
  labels:
    app: mariadb
data:
  # my.cnf: |
  #   [mysqld]

  users.sql: |
    RESET MASTER;

    CREATE USER 'maxuser'@'127.0.0.1' IDENTIFIED BY 'maxpwd';
    CREATE USER 'maxuser'@'%' IDENTIFIED BY 'maxpwd';
    GRANT ALL ON *.* TO 'maxuser'@'127.0.0.1' WITH GRANT OPTION;
    GRANT ALL ON *.* TO 'maxuser'@'%' WITH GRANT OPTION;

    SET GLOBAL gtid_strict_mode=ON;

  replication.sql: |
    RESET MASTER;
    STOP SLAVE;
    SET GLOBAL gtid_slave_pos='0-3000-0';
    CHANGE MASTER TO 
        MASTER_HOST='mariadb-0.mariadb-svc.default.svc.cluster.local', 
    	MASTER_PORT=3306, 
    	MASTER_USER='maxuser', 
    	MASTER_PASSWORD='maxpwd',
    	MASTER_USE_GTID=slave_pos;  
    START SLAVE;
    SET GLOBAL gtid_strict_mode=ON;

  master-start-replication.sql: |
    RESET MASTER;
    STOP SLAVE;
    SET GLOBAL gtid_slave_pos='0-3000-0';
    CHANGE MASTER TO 
        MASTER_HOST='localhost', 
    	MASTER_PORT=3306, 
    	MASTER_USER='maxuser', 
    	MASTER_PASSWORD='maxpwd',
    	MASTER_USE_GTID=slave_pos;
    START SLAVE;
    SET GLOBAL gtid_strict_mode=ON;

  start-mariadb.sh: |
    [[ $(hostname) =~ -([0-9]+)$ ]] || exit 1
    export server_id=${BASH_REMATCH[1]}
    cat /mnt/config-map/users.sql > docker-entrypoint-initdb.d/init.sql
    if [ "$server_id" -eq 0 ]; then
      cat /mnt/config-map/master-start-replication.sql >> docker-entrypoint-initdb.d/init.sql
    else
      cat /mnt/config-map/replication.sql >> docker-entrypoint-initdb.d/init.sql
    fi
    /usr/local/bin/docker-entrypoint.sh mysqld \
      --relay-log=relaylog \
      --log-bin=mariadb-bin \
      --binlog-format=ROW \
      --server-id=$((3000 + $server_id)) \
      --log-slave-updates=1 \
      --gtid-strict-mode=1 \
      --innodb-flush-method=fsync
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mariadb
  labels:
    app: mariadb
spec:
  serviceName: mariadb-svc
  selector:
    matchLabels:
      app: mariadb
  replicas: 3
  template:
    metadata:
      labels:
        app: mariadb
    spec:
      containers:
      - name: mariadb
        image: mariadb:10.4.8
        command:
        - bash
        - /mnt/config-map/start-mariadb.sh
        env:
        # 本番環境ではパスワードはSecretにすること
        - name: MYSQL_ROOT_PASSWORD
          value: "root"
        ports:
        - name: mariadb
          containerPort: 3306
        volumeMounts:
        # - name: mariadb
        #   mountPath: /etc/mysql/conf.d/my.cnf
        #   subPath: my.cnf
        - name: mariadb-sql
          mountPath: /mnt/config-map
        - name: mariadb-entrypoint-vol
          mountPath: /docker-entrypoint-initdb.d
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        livenessProbe:
          exec:
            command:
            - /bin/bash
            - -ec
            - >-
              mysqladmin -h localhost --user=root --password=${MYSQL_ROOT_PASSWORD} ping
          initialDelaySeconds: 5
          periodSeconds: 30
          timeoutSeconds: 5
        readinessProbe:
          exec:
            command:
            - /bin/bash
            - -ec
            - >-
              mysql -h localhost --user=root --password=${MYSQL_ROOT_PASSWORD} -e "SELECT 1"
          initialDelaySeconds: 5
          periodSeconds: 30
          timeoutSeconds: 5
      volumes:
      - name: mariadb
        configMap:
          name: mariadb-config
      - name: mariadb-sql
        configMap:
          name: mariadb-config
      # データディレクトリは本番環境はemptyDir以外にすること(hostPath、NFSなど)
      - name: data
        emptyDir: {}
      - name: mariadb-entrypoint-vol
        emptyDir: {}
```

```maxscale.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: mariadb-maxscale-config
  labels:
    app: maxscale
data:
  maxscale.cnf: |
    [maxscale]
    threads=auto
    admin_host=0.0.0.0

    [server1]
    type=server
    address=mariadb-0.mariadb-svc.default.svc.cluster.local
    port=3306
    protocol=mariadbbackend

    [server2]
    type=server
    address=mariadb-1.mariadb-svc.default.svc.cluster.local
    port=3306
    protocol=mariadbbackend

    [server3]
    type=server
    address=mariadb-2.mariadb-svc.default.svc.cluster.local
    port=3306
    protocol=mariadbbackend

    # Monitor for the servers
    # This will keep MaxScale aware of the state of the servers.
    # MySQL Monitor documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.3/Documentation/Monitors/MariaDB-Monitor.md
 
    # replication_user と replication_password が未設定の場合、
    # フェイルオーバー時に組まれるレプリケーションは
     #user で設定された maxscale ユーザーでレプリケーションが組まれます

    [MariaDB-Monitor]
    type=monitor
    module=mariadbmon
    servers=server1,server2,server3
    user=maxuser
    password=maxpwd
    auto_failover=true
    auto_rejoin=true
    enforce_read_only_slaves=1
    monitor_interval=2000

    # Service definitions
    # Service Definition for a read-only service and a read/write splitting service.

    # ReadConnRoute documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.3/Documentation/Routers/ReadConnRoute.md

    [Read-Only-Service]
    type=service
    router=readconnroute
    servers=server1,server2,server3
    user=maxuser
    password=maxpwd
    router_options=slave

    # ReadWriteSplit documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.3/Documentation/Routers/ReadWriteSplit.md

    [Read-Write-Service]
    type=service
    router=readwritesplit
    servers=server1,server2,server3
    user=maxuser
    password=maxpwd
    master_failure_mode=fail_on_write

    # Listener definitions for the services
    # Listeners represent the ports the services will listen on.

    [Read-Only-Listener]
    type=listener
    service=Read-Only-Service
    protocol=mariadbclient
    port=4008

    [Read-Write-Listener]
    type=listener
    service=Read-Write-Service
    protocol=mariadbclient
    port=4006

  start-maxscale-instance.sh: |
    cp /mnt/config-template/maxscale.cnf /mnt/config-map/maxscale.cnf
    maxscale -d -U maxscale --configdir=/mnt/config-map -lstdout
---
apiVersion: v1
kind: Service
metadata:
  name: maxscale-svc
  labels:
    app: maxscale
spec:
  selector:
    app: maxscale
  clusterIP: None
  ports:
  - name: read-write
    port: 4006
    targetPort: 4006
  - name: read-only
    port: 4008
    targetPort: 4008
  - name: rest-api
    port: 8989
    targetPort: 8989
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: maxscale
  labels:
    app: maxscale
spec:
  serviceName: maxscale
  selector:
    matchLabels:
      app: maxscale
  replicas: 1
  template:
    metadata:
      labels:
        app: maxscale
    spec:
      containers:
      - name: maxscale
        image: mariadb/maxscale:2.4.2-0
        command:
        - bash
        - /mnt/config-template/start-maxscale-instance.sh
        ports:
        - name: read-write
          containerPort: 4006
        - name: read-only
          containerPort: 4008
        - name: rest-api
          containerPort: 8989
        volumeMounts:
        - name: mariadb-config-vol
          mountPath: /mnt/config-map
        - name: start-maxscale
          mountPath: /mnt/config-template
      volumes:
      - name: mariadb-config-vol
        emptyDir: {}
      - name: start-maxscale
        configMap:
          name: mariadb-maxscale-config
```

```
kubectl apply -f mariadb.yaml

kubectl get pod

NAME        READY   STATUS    RESTARTS   AGE
mariadb-0   1/1     Running   0          6m22s
mariadb-1   1/1     Running   0          4m24s
mariadb-2   1/1     Running   0          2m16s

kubectl apply -f maxscale.yaml
```

Maxscaleの確認

```

 kubectl exec -it maxscale-0  --container=maxscale -- maxctrl list servers
┌─────────┬─────────────────────────────────────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server  │ Address                                         │ Port │ Connections │ State           │ GTID     │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ mariadb-0.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Master, Running │ 0-3000-0 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ mariadb-1.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3000-0 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server3 │ mariadb-2.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3000-0 │
└─────────┴─────────────────────────────────────────────────┴──────┴─────────────┴─────────────────┴──────────┘
```

動作確認

```
kubectl run -it --rm --image=mariadb:10.4 --restart=Never mariadb-client -- mysql -h maxscale-svc -u maxuser -pmaxpwd  -P 4006
If you don't see a command prompt, try pressing enter.
MariaDB [(none)]> create database sample;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> use sample;
Database changed
MariaDB [sample]> create table test(id int,name varchar(20));
Query OK, 0 rows affected (0.102 sec)

MariaDB [sample]> insert into test values(1,'test1');
Query OK, 1 row affected (0.020 sec)

MariaDB [sample]> insert into test values(2,'test2');
Query OK, 1 row affected (0.011 sec)

MariaDB [sample]> exit
Bye
pod "mariadb-client" deleted
PS C:\Users\rurur\OneDrive\Desktop\k8s\mariadb>  kubectl exec -it maxscale-0  --container=maxscale -- maxctrl list servers

┌─────────┬─────────────────────────────────────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server  │ Address                                         │ Port │ Connections │ State           │ GTID     │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ mariadb-0.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Master, Running │ 0-3000-4 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ mariadb-1.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3000-4 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server3 │ mariadb-2.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3000-4 │
└─────────┴─────────────────────────────────────────────────┴──────┴─────────────┴─────────────────┴──────────┘

kubectl delete pod mariadb-0

kubectl exec -it maxscale-0  --container=maxscale -- maxctrl list servers
┌─────────┬─────────────────────────────────────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server  │ Address                                         │ Port │ Connections │ State           │ GTID     │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ mariadb-0.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Down            │ 0-3000-4 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ mariadb-1.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Master, Running │ 0-3000-4 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server3 │ mariadb-2.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3000-4 │
└─────────┴─────────────────────────────────────────────────┴──────┴─────────────┴─────────────────┴──────────┘

kubectl run -it --rm --image=mariadb:10.4 --restart=Never mariadb-client -- mysql -h maxscale-svc -u maxuser -pmaxpwd  -P 4006
If you don't see a command prompt, try pressing enter.
MariaDB [(none)]> use sample;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sample]> insert into test values(3,'test3');
Query OK, 1 row affected (0.016 sec)

MariaDB [sample]> exit

kubectl exec -it maxscale-0  --container=maxscale -- maxctrl list servers
┌─────────┬─────────────────────────────────────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server  │ Address                                         │ Port │ Connections │ State           │ GTID     │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ mariadb-0.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Down            │ 0-3000-4 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ mariadb-1.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Master, Running │ 0-3001-5 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server3 │ mariadb-2.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3001-5 │
└─────────┴─────────────────────────────────────────────────┴──────┴─────────────┴─────────────────┴──────────┘

kubectl exec -it maxscale-0  --container=maxscale -- maxctrl list servers
┌─────────┬─────────────────────────────────────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server  │ Address                                         │ Port │ Connections │ State           │ GTID     │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ mariadb-0.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Running         │ 0-3000-0 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ mariadb-1.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Master, Running │ 0-3001-5 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server3 │ mariadb-2.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3001-5 │
└─────────┴─────────────────────────────────────────────────┴──────┴─────────────┴─────────────────┴──────────┘

kubectl run -it --rm --image=mariadb:10.4 --restart=Never mariadb-client -- mysql -h mariadb-0.mariadb-svc -u root -proot -P 3306

MariaDB [(none)]> RESET MASTER;
Query OK, 0 rows affected (0.060 sec)

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

MariaDB [(none)]> SET GLOBAL gtid_slave_pos='0-3001-5';
Query OK, 0 rows affected (0.133 sec)

MariaDB [(none)]> CHANGE MASTER TO
    ->     MASTER_HOST='mariadb-1.mariadb-svc.default.svc.cluster.local',
    ->     MASTER_PORT=3306,
    ->     MASTER_USER='maxuser',
    ->     MASTER_PASSWORD='maxpwd',
    ->     MASTER_USE_GTID=slave_pos;
Query OK, 0 rows affected (0.046 sec)

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

MariaDB [(none)]> SET GLOBAL gtid_strict_mode=ON;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> exit
kubectl exec -it maxscale-0  --container=maxscale -- maxctrl list servers
┌─────────┬─────────────────────────────────────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server  │ Address                                         │ Port │ Connections │ State           │ GTID     │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ mariadb-0.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3001-5 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ mariadb-1.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Master, Running │ 0-3001-5 │
├─────────┼─────────────────────────────────────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server3 │ mariadb-2.mariadb-svc.default.svc.cluster.local │ 3306 │ 0           │ Slave, Running  │ 0-3001-5 │
└─────────┴─────────────────────────────────────────────────┴──────┴─────────────┴─────────────────┴──────────┘
```

## 参考URL

[GTIDを有効にしているのにレプリケーションのポジションがズレる場合](https://yoku0825.blogspot.com/2017/07/gtid.html)
[レプリケーションを始める](https://qiita.com/annyamonnya/items/84f3f8da6decd2cf685c)
[MySQLのmasterとslaveの手動切り替え](https://qiita.com/shotaTsuge/items/83e4387d6c8488677e17)
[[MySQL][MariaDB] DBのレプリケーション構築手順](https://qiita.com/suzutsuki0220/items/e5be03ea8f44ad2f6533)
[CentOS7 + MariaDB でGTIDレプリケーション構築](https://qiita.com/junsan50/items/c9e44f8d6b2599b6a2c1)