Automate the startup of Galera Cluster on 2 nodes after a reboot

• 1/4/2023
• 2-minute read

In the post Galera Cluster for MySQL we saw how to install, configure and start a Galera cluster with MySQL on 2 nodes, and we also focused on the importance of the node startup procedure. A critical condition could occur as each node looks for a primary to connect to on startup. In case of restarting the single server, there would be no problems activating the automatic start of mysqld via systemctl, but in case of a complete restart of the infrastructure on one of the servers it is mandatory to proceed with bootstrapping the cluster.

Automate the reboot

The idea is to activate a cron job that runs when the server reboots, and check if one of the cluster nodes is already active. In case one of the nodes is already up then it will be started via systemctl start mysqld, if there are no active nodes then it will be started using /usr/bin/mysqld_bootstrap --wsrep-new- cluster.

This is the script to be scheduled on reboot on node 1.

	#!/bin/bash

	# Galera node 1 - This node
	GNODE1="172.31.81.55"

	# Galera node 2 - Other node
	GNODE2="172.31.80.162"

	# MySQL port
	GPORT=3336

	if [ `(echo > /dev/tcp/$GNODE2/$GPORT) >/dev/null 2>&1 && echo 1 || echo 0` -eq 1 ]; then
	  echo "Node 2 is up, do not bootstrap the cluster"
	  sed -ie '/safe_to/c\safe_to_bootstarp: 0' /opt/mysql/data/grastate.dat
	  systemctl start mysqld
	else
	  echo "Node 2 is down, bootstrap the cluster"
	  sed -ie '/safe_to/c\safe_to_bootstarp: 1' /opt/mysql/data/grastate.dat
	  /usr/bin/mysqld_bootstrap --wsrep-new-cluster
	fi

This instead is the script to be scheduled at reboot on node 2.

	#!/bin/bash

	# Galera node 1 - Other node
	GNODE1="172.31.81.55"

	# Galera node 2 - This node
	GNODE2="172.31.80.162"

	# MySQL port
	GPORT=3336

	sleep 10

	if [ `(echo > /dev/tcp/$GNODE1/$GPORT) >/dev/null 2>&1 && echo 1 || echo 0` -eq 1 ]; then
	  echo "Node 1 is up, do not bootstrap the cluster"
	  sed -ie '/safe_to/c\safe_to_bootstarp: 0' /opt/mysql/data/grastate.dat
	  systemctl start mysqld
	else
	  echo "Node 1 is down, bootstrap the cluster"
	  sed -ie '/safe_to/c\safe_to_bootstarp: 1' /opt/mysql/data/grastate.dat
	  /usr/bin/mysqld_bootstrap --wsrep-new-cluster
	fi

IP addresses, port and file path are those used in the system setup, note the inversion of the addresses on the 2 nodes.
The script is activated as a job in the crontab like @reboot sleep 5 && /usr/sbin/mysqld_bootstrap.sh, the script must be executable.

Let me say, perhaps (… surely) the solution is not the best one, but it seems to work and so far it hasn’t given me any problems.