Run a mariadb galera cluster with three nodes
The cluster is composed of three database services mariadb_server_1, mariadb_server_2 and mariadb_server_3, and an phpmyadmin service. The database services use volumes named as the service they are bound to for all database related data. They are connected to each other by an docker network db_network. The database services expose the following ports to the host:
- 3306 mariadb_server_1
- 3307 mariadb_server_2
- 3308 mariadb_server_3
- 50080 phpmyadmin
The root account and password is root/rootpass.
A new galera cluster needs to be bootstrapped. For that purpose run
docker compose -f docker-compose.yml -f bootstrap1.yaml up -d
This will boot all services with mariadb-server-1 as the bootstrap node. As this node cannot be restarted the same way, once all containers are up and running, redeploy mariadb-server-1 as a standard node with
docker compose up -d mariadb-server-1
Each individual service may be stopped by
docker compose stop <servicename>
and restarted deliberately by
docker compose up <servicename>
Just note that phpmyadmin connects to mariadb-server-1 only, so do not expect phpmyadmin being operational with mariadb-server-1 down. Once that node is restarted, phpmyadmin will reconnect.
As a galera cluster decides quorum-based for the most advanced data node to synchronize with, stopping two nodes and restarting them requires care: restart one node and wait for this node to catch up with the unstopped node, then start the other node. This will ensure that each returning node is in sync with the unstopped node.
In short: avoid doing this as recovery from that is work. So refrain from doing docker compose stop or docker compose down unless you really need to.
With all nodes gone the cluster's life has come to an end, and you need to create a new one. However, for not loosing any data you first have to identify which was the last node turned off. If that node is unknown or just uncertain (which is the case when the whole cluster has been stopped by docker compose stop), determine that last node off by running:
docker compose -f docker-compose.yml -f recovery.yaml up | grep "Recovered position"
This will give you an output like this:
mariadb-server-1: [Note] WSREP: Recovered position: ...:1122
mariadb-server-2: [Note] WSREP: Recovered position: ...:1127
mariadb-server-3: [Note] WSREP: Recovered position: ...:1124
not necessarily with the nodes reporting in that order. Note the numbers at the end of the lines, those are sequence numbers. The node with the highest sequence number needs to be started as the bootstrap node for a new cluster. If more than one node reports the most advanced sequence number, choose any such node for bootstrapping.
In the example given this is mariadb-server-2. We need to make sure that this node is able to bootstrap by running
docker compose run <bootstrapnodename> sed -i -e's/safe_to_bootstrap: 0/safe_to_bootstrap: 1/' /var/lib/mysql/grastate.dat
as there are occasions where no node is able to bootstrap a new cluster by default. Then bootstrap the new cluster from that node:
docker compose -f docker-compose.yml -f bootstrap2.yaml up -d
Again, once all nodes are up and running, restart the bootstrap node in normal mode:
docker compose up -d
I warned you.
You can add nodes to the cluster utilizing any service definition of a database cluster node from docker-compose.yml as a template. Requirements are:
- a distinguished service name,
- a distinguished port mapping for the container port 3306,
- a distinguished bootstrap.yml file for that node to be started as a bootstrap node
- a distinguished volume for /var/lib/mysql (double check that!), do not forget to add that volume to the list of volumes in docker-compose.yml,
- a distinguished node name passed in command to mysqld by --wsrep-node-name (important!),
- adding that node name to the list of nodes in my.cnf
Be aware that a working galera cluster needs an odd number of nodes to avoid split-brain situations. If you add nodes, please keep the number of nodes odd.
Once you're done with editing the config files, add the nodes to the cluster one at a time with
docker compose up -d <servicesname1> <servicesname2> ...
with your new node service names replacing the placeholders.
Don't do
docker compose restart
This will kill the cluster but skips the bootstrap step for a new one. You will end up with data nodes endlessly trying to raise from the dead. Stop all nodes and bootstrap a new cluster on the most advanced data node.
Don't share volumes for /var/lib/mysql between services. This is a nothing-shared cluster!
This work is based on https://github.com/habibiefaried/mysql-ndb.git, who gives a docker setup for MySQL NDB Cluster. Thank you.