Cluster Configuration
In this example configuration of two nodes, we assume that the servers are connected over private network. This allows direct connections without requiring SSH tunnels. The most important parts is to enable listening on IP protocol and to configure addresses and names of the cluster nodes. The config files are similar for each nodes. To avoid editing distribution provided configuration files, the new files are added to /etc/mysql/mariadb.conf.d directory, and on both servers are named 70-cluster.cnf. This layout is provided on Ubuntu system, different operating system or other Linux distributions might have different layout. The number 70 at the beginning ensures, that the file will be included last. In fact configuration files could be split into even more chunks, as only last three lines differ.
Node 1 Configuration
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.0.2,192.168.0.4"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.0.2"
wsrep_node_name="hs1"
server_id=1
Node 2 Configuration
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.0.2,192.168.0.4"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.0.4"
wsrep_node_name="hs2"
server_id=2
These examples of configuration files are for sake of completeness of this article, as far as I remember should suffice to start cluster. Complete setup of Galera Cluster is beyond the scope of this article.
Starting Cluster
After configuring cluster, do not restart MariaDB service as one would expect. First stop services on both machines with systemctl command:
sudo systemctl stop mariadb.service
Then create cluster with the galera_new_cluster command on first node:
sudo galera_new_cluster
On the second node, simply start MariaDB service, the node should connect to the first one:
sudo systemctl start mariadb.service
Checking Cluster Health
To check if cluster is properly running, we can check two parameters of cluster by querying each server for status parameters wsrep_cluster_size and wsrep_local_state_comment. The cluster size should be in our example 2, and the state comment should be synced.
he SQL (executed from bash) command and its output for cluster size is:
sudo mysql -p -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 2 | +--------------------+-------+
To check if the cluster is synced, run following command:
sudo mysql -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment';"
+---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | wsrep_local_state_comment | Synced | +---------------------------+--------+
So far, so good. We could now reboot our servers to double check if everything works fine. And it will be not after rebooting!
Rebooting Cluster
Preparation
The important issue with our setup is that the services will automatically start on boot. We don’t want this to happen, as we need to manually create cluster – as simply starting MariaDB service will not rebuild our cluster. Each node will try to connect to not yet existing cluster.
To remedy this, we need to disable starting MariaDB service on boot on each machine:
sudo systemctl disable mariadb.service
Shutting Down
First of all, check if all nodes are in synced state, with command:
sudo mysql -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment';"
+---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | wsrep_local_state_comment | Synced | +---------------------------+--------+
The above command need to be executed on each node. As the variable name suggest by local word in name – it shows status of each node, not the whole cluster. The ideal situation would be to forbid any more connections to cluster, don’t really know how to achieve that if we have running application.
If we have all clusters synced, I propose to stop service first – it will take longer than stand-alone instance – and then reboot each node. That is, to execute following commands on each machine:
sudo systemctl stop mariadb.service
sudo reboot
The servers should be booted up after some time, but without MariaDB available.
To restart the cluster, we need to create a new one. Starting on last stopped node with galera_new_cluster command:
sudo galera_new_cluster
Now switch to the second node, and simply start the service, the second node should connect to newly created cluster:
sudo systemctl start mariadb.service
Both operations will take longer than in usual MariaDB startup. It also depends on network congestion, physical distance and roundtrip times between nodes. The last thing we might want to do, is to check if our cluster size is as expected:
sudo mysql -p -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 2 | +--------------------+-------+
