LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@rururu_kenken(nao)

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

Last updated at Posted at 2019-10-14

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を有効にしているのにレプリケーションのポジションがズレる場合
レプリケーションを始める
MySQLのmasterとslaveの手動切り替え
[MySQL][MariaDB] DBのレプリケーション構築手順
CentOS7 + MariaDB でGTIDレプリケーション構築

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?