As of October 28, 2018 there are 3 hosts running in the galera cluster. They are:

siosql1.ucsd.edu
siosql2.ucsd.edu
siosql3.ucsd.edu

Networking Requirements

All hosts in the cluster, should have the following ports open to talk between hosts:

TCP/3306
TCP/4567
TCP/4568
TCP/4444

This can be accomplished by modifying /etc/sysconfig/iptables with the following ruleset:

-A SIO-Firewall -m iprange --src-range 172.21.224.208-172.21.224.210 \
   -p tcp -m state --state NEW -m tcp --match multiport --dports \
   3306,4567,4568,4444 -j ACCEPT

Configuration requires that the RHEL system be subscribed to the Red Hat Software Collections(SCL) repos. Once that has been completed, the following packages & their dependencies should be installed.

#yum install rh-mariadb102 rh-mariadb102-mariadb-server rh-mariadb102-mariadb-server-galera

Galera Terminology

quorum: The clustering term for a vote where the nodes decide whether they can agree on what nodes are in the cluster. A 2-node cluster that loses connectivity may have "split-brain" problems.

node: A host that is running mariadb/mysqld with galera clustering enabled(wsrep_on=1)

wsrep: Most/all configuration settings and variables related to Galera clustering are prefaces with "wsrep". This is the API that is used for replication/clustering, and is short for "Write Set Replication"

Red Hat Software Collection Specifics

To run the binaries and services from outside of systemd, you'll need to enable the software collection for use. This can be done interactively:

scl enable rh-mariadb102 bash

or for scripts or one off commands:

scl enable rh-mariadb102 'mysqldump --user=root -p --all-databases'

Paths of useful files

Root Environment:

/opt/rh/rh-mariadb102

Configuration Files:

/etc/opt/rh/rh-mariadb102

Data:

/var/opt/rh/rh-mariadb102/lib/mysql

Logs:

/var/opt/rh/rh-mariadb/log/mariadb

Configuration files

The configuration files will be in the following directory: /etc/opt/rh/rhmariadb102 either in my.cnf or my.cnf.d

The main variables to configure are:

generic galera.cnf

# wsrep_cluster_name must match across all nodes
wsrep_cluster_name="{UNIQUE CLUSTER NAME}"
wsrep_cluster_address="gcomm://{IP of NODE1},{IP OF NODE2},...,{IP OF LAST NODE}"
wsrep_node_address={IP OF THIS NODE}

siosql1 galera.cnf (node1)

wsrep_cluster_name="sio-galera"
wsrep_cluster_address="gcomm://172.21.224.208,172.21.224.209,172.21.224.210"
wsrep_node_address=172.21.224.208

siosql2 galera.cnf (node2)

wsrep_cluster_name="sio-galera"
wsrep_cluster_address="gcomm://172.21.224.208,172.21.224.209,172.21.224.210"
wsrep_node_address=172.21.224.209

siosql3 galera.cnf (node3)

wsrep_cluster_name="sio-galera"
wsrep_cluster_address="gcomm://172.21.224.208,172.21.224.209,172.21.224.210"
wsrep_node_address=172.21.224.210

Note: selinux must either be disabled(as it is on these systems), or exceptions or policies must be created for mariadb/mysql

Bootstrapping the cluster

There are some unique steps required when initializing or bootstrapping the cluster. The goal is to start the mariadb/mysqld process with galera clustering turned on(wsrep_on=1), but to tell the cluster not to attempt connecting to any other nodes, as this is the first one. Mariadb includes a script that does this and integrates well with systemd. This script is named galera_new_cluster. This script should only be run once on the first node.

Example(no pre-existing data)

Bootstrapping Cluster and Initial Config

[root@siosql1 ~]# scl enable rh-mariadb102 bash
[root@siosql1 ~]# galera_new_cluster 
[root@siosql1 ~]# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

The mariadb/mysql credentials for the root account are in 1Password, titled "siosql.ucsd.edu root"

Logging into the first node database

[root@siosql1 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.2.8-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

siosql2

systemctl start rh-mariadb102-mariadb

Check cluster size from siosql1

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.00 sec)

siosql3

systemctl start rh-mariadb102-mariadb

Check cluster size again(siosql1)

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.01 sec)

Now we want to restart the mariadb daemon on siosql1, so that it is no longer in bootstrapping mode:

siosql1

[root@siosql1 ~]# ps aux | grep mysqld | grep -v grep
mysql     6440  0.1  1.6 2494048 128528 ?      Ssl  10:22   0:00 /opt/rh/rh-mariadb102/root/usr/libexec/mysqld --basedir=/opt/rh/rh-mariadb102/root/usr --wsrep-new-cluster
[root@siosql1 ~]# systemctl restart rh-mariadb102-mariadb
[root@siosql1 ~]# ps aux | grep mysqld | grep -v grep
mysql     6981  7.0  1.3 2346424 106892 ?      Ssl  10:34   0:00 /opt/rh/rh-mariadb102/root/usr/libexec/mysqld --basedir=/opt/rh/rh-mariadb102/root/usr

By looking at the options passed to mysqld you can see that the galera_new_cluster command passed the option --wsrep-new-cluster. Once we restart the service, you can see that it is running in normal cluster mode were it will look for other nodes as defined in wsrep_cluster_address variable in /etc/opt/rh/rh-mariadb102/my.cnf.d/galera.cnf.

Now we can enable the service across all nodes

Enable the service across all nodes

[root@siosql1 ~]# systemctl enable rh-mariadb102-mariadb.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/rh-mariadb102-mariadb.service to /usr/lib/systemd/system/rh-mariadb102-mariadb.service.

[root@siosql2 mariadb]# systemctl enable rh-mariadb102-mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/rh-mariadb102-mariadb.service to /usr/lib/systemd/system/rh-mariadb102-mariadb.service.

[root@siosql3 mysql]# systemctl enable rh-mariadb102-mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/rh-mariadb102-mariadb.service to /usr/lib/systemd/system/rh-mariadb102-mariadb.service.

Looking at Cluster Variables

Once we have the cluster up and running, we may want to see the status of the cluster from various nodes.

How to check cluster status

SHOW STATUS LIKE 'wsrep_%';

Sample output from a healthy 3-node cluster(from siosql1)

cluster/wsrep status

[root@siosql1 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.8-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';
+------------------------------+-------------------------------------------------------------+
| Variable_name                | Value                                                       |
+------------------------------+-------------------------------------------------------------+
| wsrep_apply_oooe             | 0.000000                                                    |
| wsrep_apply_oool             | 0.000000                                                    |
| wsrep_apply_window           | 0.000000                                                    |
| wsrep_causal_reads           | 0                                                           |
| wsrep_cert_deps_distance     | 0.000000                                                    |
| wsrep_cert_index_size        | 0                                                           |
| wsrep_cert_interval          | 0.000000                                                    |
| wsrep_cluster_conf_id        | 5                                                           |
| wsrep_cluster_size           | 3                                                           |
| wsrep_cluster_state_uuid     | 5237d698-dc68-11e8-8af3-5b635fa88375                        |
| wsrep_cluster_status         | Primary                                                     |
| wsrep_commit_oooe            | 0.000000                                                    |
| wsrep_commit_oool            | 0.000000                                                    |
| wsrep_commit_window          | 0.000000                                                    |
| wsrep_connected              | ON                                                          |
| wsrep_desync_count           | 0                                                           |
| wsrep_evs_delayed            |                                                             |
| wsrep_evs_evict_list         |                                                             |
| wsrep_evs_repl_latency       | 0/0/0/0/0                                                   |
| wsrep_evs_state              | OPERATIONAL                                                 |
| wsrep_flow_control_paused    | 0.000000                                                    |
| wsrep_flow_control_paused_ns | 0                                                           |
| wsrep_flow_control_recv      | 0                                                           |
| wsrep_flow_control_sent      | 0                                                           |
| wsrep_gcomm_uuid             | 1facb438-dc6a-11e8-b875-060365352ce7                        |
| wsrep_incoming_addresses     | 172.21.224.208:3306,172.21.224.209:3306,172.21.224.210:3306 |
| wsrep_last_committed         | 3                                                           |
| wsrep_local_bf_aborts        | 0                                                           |
| wsrep_local_cached_downto    | 18446744073709551615                                        |
| wsrep_local_cert_failures    | 0                                                           |
| wsrep_local_commits          | 0                                                           |
| wsrep_local_index            | 0                                                           |
| wsrep_local_recv_queue       | 0                                                           |
| wsrep_local_recv_queue_avg   | 0.000000                                                    |
| wsrep_local_recv_queue_max   | 1                                                           |
| wsrep_local_recv_queue_min   | 0                                                           |
| wsrep_local_replays          | 0                                                           |
| wsrep_local_send_queue       | 0                                                           |
| wsrep_local_send_queue_avg   | 0.500000                                                    |
| wsrep_local_send_queue_max   | 2                                                           |
| wsrep_local_send_queue_min   | 0                                                           |
| wsrep_local_state            | 4                                                           |
| wsrep_local_state_comment    | Synced                                                      |
| wsrep_local_state_uuid       | 5237d698-dc68-11e8-8af3-5b635fa88375                        |
| wsrep_protocol_version       | 7                                                           |
| wsrep_provider_name          | Galera                                                      |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                           |
| wsrep_provider_version       | 3.20(rXXXX)                                                 |
| wsrep_ready                  | ON                                                          |
| wsrep_received               | 2                                                           |
| wsrep_received_bytes         | 291                                                         |
| wsrep_repl_data_bytes        | 0                                                           |
| wsrep_repl_keys              | 0                                                           |
| wsrep_repl_keys_bytes        | 0                                                           |
| wsrep_repl_other_bytes       | 0                                                           |
| wsrep_replicated             | 0                                                           |
| wsrep_replicated_bytes       | 0                                                           |
| wsrep_thread_count           | 2                                                           |
+------------------------------+-------------------------------------------------------------+
58 rows in set (0.01 sec)

Testing the Cluster

Create some data on siosql1

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

MariaDB [(none)]> CREATE DATABASE galeratest;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE galeratest;
Database changed

MariaDB [galeratest]> INSERT INTO test_table (msg)
    -> VALUES ("Hello my dear cluster.");
Query OK, 1 row affected (0.00 sec)

MariaDB [galeratest]> INSERT INTO test_table(msg)
    -> VALUES ("Hello, again, cluster dear.");
Query OK, 1 row affected (0.00 sec)

Test the replication to siosql2

[root@siosql2 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.8-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE galeratest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [galeratest]> SELECT * FROM test_table;
+----+-----------------------------+
| id | msg                         |
+----+-----------------------------+
|  4 | Hello my dear cluster.      |
|  7 | Hello, again, cluster dear. |
+----+-----------------------------+
2 rows in set (0.00 sec)

Test replication to siosql3

[root@siosql3 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.8-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE galeratest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [galeratest]> SElECT * FROM test_table;
+----+-----------------------------+
| id | msg                         |
+----+-----------------------------+
|  4 | Hello my dear cluster.      |
|  7 | Hello, again, cluster dear. |
+----+-----------------------------+
2 rows in set (0.00 sec)

SIO Databases

mariadb galera cluster(mysql)