DockerでMariaDB Galera Cluster + Maxscaleを動かしてみる

MariaDB 10.4.12
Maxscale 2.4.2

ディレクトリ構成

|--docker-compose.yml
|--maxscale.cnf.d
|  |--example.cnf
|--dockerfiles
|  |--Dockerfile
|  |--scripts
|     |--init_cluster_conf.sh
|     |--init_galera_user.sh
|     |--init_galera_user.sql
|     |--init_maxscale_user.sh
|     |--init_maxscale_user.sql
|     |--init_root_user.sh
|     |--init_root_user.sql

docker-compose.yml

version: "3.7"
services:
    node-1:
        image: mariadb-galera:10.4.12
        container_name: node-1
        ports:
          - 4001:3306
        command: mysqld --wsrep-new-cluster

    node-2:
        image: mariadb-galera:10.4.12
        container_name: node-2
        links:
          - node-1
        ports:
          - 4002:3306
        command: mysqld

    node-3:
        image: mariadb-galera:10.4.12
        container_name: node-3
        links:
          - node-1
        ports:
          - 4003:3306
        command: mysqld

    maxscale:
        image: mariadb/maxscale:2.4.2
        container_name: maxscale
        depends_on:
            - node-1
            - node-2
            - node-3
        volumes:
            - ./maxscale.cnf.d:/etc/maxscale.cnf.d
        ports:
            - 4006:4006  # readwrite port
            - 4008:4008  # readonly port
            - 8989:8989  # REST API port

example.cnf

[server1]
type=server
address=node-1
port=3306
protocol=mariadbbackend

[server2]
type=server
address=node-2
port=3306
protocol=mariadbbackend

[server3]
type=server
address=node-3
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

[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxuser
password=maxpwd
monitor_interval=5000

# 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

Dockerfile

FROM mariadb:10.4.12

COPY scripts/ /docker-entrypoint-initdb.d/.

# we need to touch and chown config files, since we cant write as mysql user
RUN touch /etc/mysql/conf.d/galera.cnf \
    && chown mysql.mysql /etc/mysql/conf.d/galera.cnf \
    && chown mysql.mysql /docker-entrypoint-initdb.d/*.sql

# we expose all Cluster related Ports
# 3306: default MySQL/MariaDB listening port
# 4444: for State Snapshot Transfers
# 4567: Galera Cluster Replication
# 4568: Incremental State Transfer
EXPOSE 3306 4444 4567 4568

# we set some defaults
ENV GALERA_USER=galera \
    GALERA_PASS=galerapass \
    MAXSCALE_USER=maxuser \
    MAXSCALE_PASS=maxpwd \ 
    CLUSTER_NAME=docker_cluster \
    MYSQL_ROOT_PASSWORD=password

CMD ["mysqld"]

init_cluster_conf.sh

#!/bin/bash

set -e

CLUSTER_ADDRESS="gcomm://"

# we create a galera config
config_file="/etc/mysql/conf.d/galera.cnf"

cat <<EOF > $config_file
# Node specifics 
[mysqld] 
# next 3 params disabled for the moment, since they are not mandatory and get changed with each new instance.
# they also triggered problems when trying to persist data with a backup service, since also the config has to be 
# persisted, but HOSTNAME changes at container startup.
#wsrep-node-name = $HOSTNAME 
#wsrep-sst-receive-address = $HOSTNAME
#wsrep-node-incoming-address = $HOSTNAME

# Cluster settings
wsrep-on=ON
wsrep-cluster-name = "$CLUSTER_NAME" 
wsrep-cluster-address = $CLUSTER_ADDRESS
wsrep-provider = /usr/lib/galera/libgalera_smm.so 
wsrep-provider-options = "gcache.size=256M;gcache.page_size=128M;debug=no" 
wsrep-sst-auth = "$GALERA_USER:$GALERA_PASS" 
wsrep_sst_method = rsync
binlog-format = row 
default-storage-engine = InnoDB 
innodb-doublewrite = 1 
innodb-autoinc-lock-mode = 2 
innodb-flush-log-at-trx-commit = 2 
EOF

init_galera_user.sh

#!/bin/bash

set -e

# we use .sh file to create a .sql file, which will be parsed afterwards due to alphabetical sorting
config_file="/docker-entrypoint-initdb.d/init_galera_user.sql"

# We start config file creation

cat <<EOF > $config_file
GRANT ALL PRIVILEGES on *.* to '$GALERA_USER'@'%' identified by '$GALERA_PASS';
EOF

init_galera_user.sql

# Template file for on the fly config

init_maxscale_user.sh

#!/bin/bash

set -e

# we use .sh file to create a .sql file, which will be parsed afterwards due to alphabetical sorting

config_file="/docker-entrypoint-initdb.d/init_maxscale_user.sql"

# We start config file creation

cat <<EOF > $config_file
CREATE USER '$MAXSCALE_USER'@'%' identified by '$MAXSCALE_PASS';
GRANT SELECT on mysql.user to '$MAXSCALE_USER'@'%';
GRANT SELECT ON mysql.db TO '$MAXSCALE_USER'@'%';
GRANT SELECT ON mysql.tables_priv TO '$MAXSCALE_USER'@'%';
GRANT REPLICATION CLIENT ON *.* to $MAXSCALE_USER@'%';
GRANT SHOW DATABASES ON *.* TO '$MAXSCALE_USER'@'%';
EOF

init_maxscale_user.sql

# Template file for on the fly config

init_root_user.sh

#!/bin/bash

set -e

# we use .sh file to create a .sql file, which will be parsed afterwards due to alphabetical sorting
config_file="/docker-entrypoint-initdb.d/init_root_user.sql"

# We start config file creation

cat <<EOF > $config_file
GRANT ALL PRIVILEGES on *.* to 'root'@'%' identified by '$MYSQL_ROOT_PASSWORD';
EOF

init_root_user.sql

# Template file for on the fly config

動作確認

Maxscaleのイメージのバージョンを2.4.2より大きくして
動作確認したところ、Stateのところが1台だけ
「Master, Synced, Running」で、後の2つは
「Running」になった。原因調査中(2020/02/19追記)

$ cd
$ cd dockerfiles
$ docker build -t mariadb-galera:10.4.12 .
$ cd ..
$ docker-compose up -d
$ docker-compose exec maxscale maxctrl list servers
┌─────────┬─────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server  │ Address │ Port │ Connections │ State                   │ GTID │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server1 │ node-1  │ 3306 │ 0           │ Master, Synced, Running │      │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server2 │ node-2  │ 3306 │ 0           │ Slave, Synced, Running  │      │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server3 │ node-3  │ 3306 │ 0           │ Slave, Synced, Running  │      │
└─────────┴─────────┴──────┴─────────────┴─────────────────────────┴──────┘
$ docker stop node-1
$ docker-compose exec maxscale maxctrl list servers
┌─────────┬─────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server  │ Address │ Port │ Connections │ State                   │ GTID │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server1 │ node-1  │ 3306 │ 0           │ Down                    │      │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server2 │ node-2  │ 3306 │ 0           │ Master, Synced, Running │      │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server3 │ node-3  │ 3306 │ 0           │ Slave, Synced, Running  │      │
└─────────┴─────────┴──────┴─────────────┴─────────────────────────┴──────┘
$ docker restart node-1
$ docker-compose exec maxscale maxctrl list servers
┌─────────┬─────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server  │ Address │ Port │ Connections │ State                   │ GTID │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server1 │ node-1  │ 3306 │ 0           │ Master, Synced, Running │      │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server2 │ node-2  │ 3306 │ 0           │ Slave, Synced, Running  │      │
├─────────┼─────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server3 │ node-3  │ 3306 │ 0           │ Slave, Synced, Running  │      │
└─────────┴─────────┴──────┴─────────────┴─────────────────────────┴──────┘

参考URL

Galera Cluster System Variables
[翻訳] "Tips on Converting to Galera"（Galeraに移行する際のポイント）
Galera Cluster で起動時にエラー
Galera Cluster Documentを読む Configuration編(3) RECOVERING THE PRIMARY COMPONENT
Introducing the “Safe-To-Bootstrap” feature in Galera Cluster
Ubuntu 14.04 & 16.04のMariaDB Galera Clusterが起動できない
MariaDB Galera Clusterの構築
Galera Clusterでsst_methodがrsyncのときDONORノードは本当にblockingになるのか
MySQL/MariaDB ProxyのMaxScaleをWeb向けの設定で気を付けること