MariaDB Galera Cluster

In this example, we'll deploy MariaDB Galera Cluster and MaxScale.

1. Create shared variables

Go to "Environment variables" menu and create 4 variables.

  • MYSQL_PASSWORD (don't forget to check the "hide value" box)
  • MYSQL_USER
  • MYSQL_DATABASE
  • MYSQL_ROOT_PASSWORD (don't forget to check the "hide value" box)
  • MYSQL_MAXSCALE_PASSWORD (don't forget to check the "hide value" box)

2. Create MariaDB cluster + MaxScale

services:
  - id: clustermariadb1
    name: ClusterMariaDB1
    dockerConfiguration:
      image: mariadb
      imageVersion: "11"
      args: --wsrep-new-cluster --wsrep_node_name=node1 --wsrep_sst_receive_address=127.0.0.1:14444 --wsrep_provider_options=base_port=14567;ist.recv_addr=127.0.0.1:14568 --wsrep-cluster-address=gcomm://127.0.0.1:14567,127.0.0.1:24567,127.0.0.1:34567 --wsrep_provider=/usr/lib/galera/libgalera_smm.so --wsrep_on=ON --binlog_format=ROW
    countMin: 1
    countMax: 1
    capacityCpuMhz: 1024
    capacityMemoryMB: 1024
    ports:
      - listeningPort: 3306
        healthCheckEnabled: false
      - listeningPort: 14444
        healthCheckEnabled: false
      - listeningPort: 14567
        healthCheckEnabled: false
      - listeningPort: 14568
        healthCheckEnabled: false
    sharedEnvironmentVariables:
      - MYSQL_ROOT_PASSWORD
      - MYSQL_USER
      - MYSQL_PASSWORD
      - MYSQL_DATABASE
      - MYSQL_MAXSCALE_PASSWORD
    links:
      - toServiceId: clustermariadb2
        toServicePort: 24567
        localExposedPort: 24567
        variableHost: DBHOST2
        variablePort: DBPORT2
        variableAddress: DBADDRESS2
      - toServiceId: clustermariadb2
        toServicePort: 24568
        localExposedPort: 24568
        variableHost: ISTHOST2
        variablePort: ISTPORT2
        variableAddress: ISTADDRESS2
      - toServiceId: clustermariadb2
        toServicePort: 24444
        localExposedPort: 24444
        variableHost: SSTHOST2
        variablePort: SSTPORT2
        variableAddress: SSTADDRESS2
      - toServiceId: clustermariadb3
        toServicePort: 34567
        localExposedPort: 34567
        variableHost: DBHOST3
        variablePort: DBPORT3
        variableAddress: DBADDRESS3
      - toServiceId: clustermariadb3
        toServicePort: 34568
        localExposedPort: 34568
        variableHost: ISTHOST3
        variablePort: ISTPORT3
        variableAddress: ISTADDRESS3
      - toServiceId: clustermariadb3
        toServicePort: 34444
        localExposedPort: 34444
        variableHost: SSTHOST3
        variablePort: SSTPORT3
        variableAddress: SSTADDRESS3
    volumes:
      - name: ClusterMariaDB1_data
        path: "/var/lib/mysql"
    files:
      - content: |
          CREATE USER 'maxscale'@'%' IDENTIFIED BY '%MYSQL_MAXSCALE_PASSWORD%';
          GRANT ALL PRIVILEGES ON *.* TO maxscale@'%';
          CREATE USER 'maxscale'@'127.0.0.1' IDENTIFIED BY '%MYSQL_MAXSCALE_PASSWORD%';
          GRANT ALL PRIVILEGES ON *.* TO maxscale@'127.0.0.1';
        path: "/docker-entrypoint-initdb.d/init_maxscale.sql"
        parse: true
  - id: clustermariadb2
    name: ClusterMariaDB2
    dockerConfiguration:
      image: mariadb
      imageVersion: "11"
      args: --wsrep_node_name=node2 --wsrep_sst_receive_address=127.0.0.1:24444 --wsrep_provider_options=base_port=24567;ist.recv_addr=127.0.0.1:24568 --wsrep-cluster-address=gcomm://127.0.0.1:14567,127.0.0.1:24567,127.0.0.1:34567 --wsrep_provider=/usr/lib/galera/libgalera_smm.so --wsrep_on=ON --binlog_format=ROW
    countMin: 1
    countMax: 1
    capacityCpuMhz: 1024
    capacityMemoryMB: 1024
    ports:
      - listeningPort: 3306
        healthCheckEnabled: false
      - listeningPort: 24444
        healthCheckEnabled: false
      - listeningPort: 24567
        healthCheckEnabled: false
      - listeningPort: 24568
        healthCheckEnabled: false
    environmentVariables:
      - key: MARIADB_RANDOM_ROOT_PASSWORD
        value: "1"
    links:
      - toServiceId: clustermariadb1
        toServicePort: 14567
        localExposedPort: 14567
        variableHost: DBHOST1
        variablePort: DBPORT1
        variableAddress: DBADDRESS1
      - toServiceId: clustermariadb1
        toServicePort: 14568
        localExposedPort: 14568
        variableHost: ISTHOST1
        variablePort: ISTPORT1
        variableAddress: ISTADDRESS1
      - toServiceId: clustermariadb1
        toServicePort: 14444
        localExposedPort: 14444
        variableHost: SSTHOST1
        variablePort: SSTPORT1
        variableAddress: SSTADDRESS1
      - toServiceId: clustermariadb3
        toServicePort: 34567
        localExposedPort: 34567
        variableHost: DBHOST3
        variablePort: DBPORT3
        variableAddress: DBADDRESS3
      - toServiceId: clustermariadb3
        toServicePort: 34568
        localExposedPort: 34568
        variableHost: ISTHOST3
        variablePort: ISTPORT3
        variableAddress: ISTADDRESS3
      - toServiceId: clustermariadb3
        toServicePort: 34444
        localExposedPort: 34444
        variableHost: SSTHOST3
        variablePort: SSTPORT3
        variableAddress: SSTADDRESS3
    volumes:
      - name: ClusterMariaDB2_data
        path: /var/lib/mysql
  - id: clustermariadb3
    name: ClusterMariaDB3
    dockerConfiguration:
      image: mariadb
      imageVersion: "11"
      args: --wsrep_node_name=node3 --wsrep_sst_receive_address=127.0.0.1:34444 --wsrep_provider_options=base_port=34567;ist.recv_addr=127.0.0.1:34568 --wsrep-cluster-address=gcomm://127.0.0.1:14567,127.0.0.1:24567,127.0.0.1:34567 --wsrep_provider=/usr/lib/galera/libgalera_smm.so --wsrep_on=ON --binlog_format=ROW
    countMin: 1
    countMax: 1
    capacityCpuMhz: 1024
    capacityMemoryMB: 1024
    ports:
      - listeningPort: 3306
        healthCheckEnabled: false
      - listeningPort: 34444
        healthCheckEnabled: false
      - listeningPort: 34567
        healthCheckEnabled: false
      - listeningPort: 34568
        healthCheckEnabled: false
    environmentVariables:
      - key: MARIADB_RANDOM_ROOT_PASSWORD
        value: "1"
    links:
      - toServiceId: clustermariadb1
        toServicePort: 14567
        localExposedPort: 14567
        variableHost: DBHOST1
        variablePort: DBPORT1
        variableAddress: DBADDRESS1
      - toServiceId: clustermariadb1
        toServicePort: 14568
        localExposedPort: 14568
        variableHost: ISTHOST1
        variablePort: ISTPORT1
        variableAddress: ISTADDRESS1
      - toServiceId: clustermariadb1
        toServicePort: 14444
        localExposedPort: 14444
        variableHost: SSTHOST1
        variablePort: SSTPORT1
        variableAddress: SSTADDRESS1
      - toServiceId: clustermariadb2
        toServicePort: 24567
        localExposedPort: 24567
        variableHost: DBHOST2
        variablePort: DBPORT2
        variableAddress: DBADDRESS2
      - toServiceId: clustermariadb2
        toServicePort: 24568
        localExposedPort: 24568
        variableHost: ISTHOST2
        variablePort: ISTPORT2
        variableAddress: ISTADDRESS2
      - toServiceId: clustermariadb2
        toServicePort: 24444
        localExposedPort: 24444
        variableHost: SSTHOST2
        variablePort: SSTPORT2
        variableAddress: SSTADDRESS2
    volumes:
      - name: ClusterMariaDB3_data
        path: /var/lib/mysql
  - id: lbmariadb
    name: LBMariadb
    dockerConfiguration:
      image: "mariadb/maxscale"
      imageVersion: "23.08"
    ports:
      - listeningPort: 3306
        healthCheckEnabled: false
      - listeningPort: 8989
        healthCheckEnabled: false
        loadBalancerRules:
          - publicPort: 443
    sharedEnvironmentVariables:
      - MYSQL_MAXSCALE_PASSWORD
    links:
      - toServiceId: clustermariadb1
        toServicePort: 3306
        variableHost: DB_HOST1
        variablePort: DB_PORT1
        variableAddress: DB_ADDR1
      - toServiceId: clustermariadb2
        toServicePort: 3306
        variableHost: DB_HOST2
        variablePort: DB_PORT2
        variableAddress: DB_ADDR2
      - toServiceId: clustermariadb3
        toServicePort: 3306
        variableHost: DB_HOST3
        variablePort: DB_PORT3
        variableAddress: DB_ADDR3
    capacityCpuMhz: 1024
    capacityMemoryMB: 1024
    files:
      - content: |
          [mariadb1]
          type            = server
          address         = %DB_HOST1%
          port            = %DB_PORT1%
          monitorpw       = %MYSQL_MAXSCALE_PASSWORD%
          monitoruser     = maxscale

          [mariadb2]
          type            = server
          address         = %DB_HOST2%
          port            = %DB_PORT2%
          monitorpw       = %MYSQL_MAXSCALE_PASSWORD%
          monitoruser     = maxscale

          [mariadb3]
          type            = server
          address         = %DB_HOST3%
          port            = %DB_PORT3%
          monitorpw       = %MYSQL_MAXSCALE_PASSWORD%
          monitoruser     = maxscale

          [maxscale]
          admin_host        = 0.0.0.0
          admin_port        = 8989
          admin_secure_gui  = false

          [Galera]
          module=galeramon
          password=%MYSQL_MAXSCALE_PASSWORD%
          servers=mariadb1,mariadb2,mariadb3
          type=monitor
          user=maxscale

          [RW]
          auth_all_servers=true
          password=%MYSQL_MAXSCALE_PASSWORD%
          router=readwritesplit
          targets=mariadb1,mariadb2,mariadb3
          type=service
          user=maxscale

          [RW-3306]
          address=0.0.0.0
          port=3306
          proxy_protocol_networks=127.0.0.1
          service=RW
          type=listener
        path: /etc/maxscale.cnf
        parse: true

You can now access the generated link for port '8989' (username: admin, password: mariadb). You'll see the MaxScale dashboard with the status of your mariadb cluster.

This configuration is just an example, you will probably want to set up mariadb and/or maxscal according to your needs.

3. Detailed example breakdown

To create a cluster, we need to create 3 galera nodes. Each node is given the necessary information about the other 2 in args:

- id: clustermariadb1
  name: ClusterMariaDB1
  dockerConfiguration:
    image: mariadb
    imageVersion: "11"
    args: --wsrep-new-cluster --wsrep_node_name=node1 --wsrep_sst_receive_address=127.0.0.1:14444 --wsrep_provider_options=base_port=14567;ist.recv_addr=127.0.0.1:14568 --wsrep-cluster-address=gcomm://127.0.0.1:14567,127.0.0.1:24567,127.0.0.1:34567 --wsrep_provider=/usr/lib/galera/libgalera_smm.so --wsrep_on=ON --binlog_format=ROW

In links, we'll link to the other nodes by setting the ports so we can use them in the args attribute.

links:
  - toServiceId: clustermariadb2
    toServicePort: 24567
    localExposedPort: 24567
    variableHost: DBHOST2
    variablePort: DBPORT2
    variableAddress: DBADDRESS2
  - toServiceId: clustermariadb3
    toServicePort: 34567
    localExposedPort: 34567
    variableHost: DBHOST3
    variablePort: DBPORT3
    variableAddress: DBADDRESS3

Node 1 has been designated for executing SQL queries to initialize the database, specifically for the purpose of creating the maxscale user. By utilizing the 'file' attribute, it becomes possible to generate a new file within the container, with the added benefit of variable parsing across all contents. In this scenario, the %MYSQL_MAXSCALE_PASSWORD% placeholder will be replaced by the value of the environment variable.

files:
  - content: |
      CREATE USER 'maxscale'@'%' IDENTIFIED BY '%MYSQL_MAXSCALE_PASSWORD%';
      GRANT ALL PRIVILEGES ON *.* TO maxscale@'%';
      CREATE USER 'maxscale'@'127.0.0.1' IDENTIFIED BY '%MYSQL_MAXSCALE_PASSWORD%';
      GRANT ALL PRIVILEGES ON *.* TO maxscale@'127.0.0.1';
    path: "/docker-entrypoint-initdb.d/init_maxscale.sql"
    parse: true

And the same technique is used for the MaxScale service to pass its configuration.