Galera Cluster for MySQL, 2 nodes setup
Galera Cluster is a synchronous multi-master cluster for MySQL/MariaDB. It allows you to create an active-active multi-master topology with synchronous data replication, supporting read and write operations on all nodes in the cluster.
For a production use at least 3 nodes are recommended to avoid split-brain situations, but we will test the setup with only 2 nodes in replica.
Environment preparation
For this use case I chose to install Galera Cluster on 2 AWS EC2 instances, but this is a detail, since the setup depends on the operating system in use and not on the type of machine. I chose 2 EC2 instances of the t2.micro type with the CentOS 7 (x86_64) – with Updates HVM image installed, I configured the VPC, the Security Group and the private key to enable ssh access.
In the post attention will also be paid to the security aspects also, we will see how to properly configure selinux and the firewall on the machines.
Let’s proceed with the installation on both nodes
Configure the repo file to access the Galera Cluster repository, create the file /etc/yum.repos.d/galera.repo with the following content.
[galera]
name = Galera
baseurl = https://releases.galeracluster.com/galera-4.10/centos/7/x86_64
gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
[mysql-wsrep]
name = MySQL-wsrep
baseurl = https://releases.galeracluster.com/mysql-wsrep-8.0.23-26.6/centos/7/x86_64
gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
For galera and mysql-wsrep you can choose the versions you prefer.
Install the packages:
yum install rsync firewalld
yum install galera-4 mysql-wsrep-8.0
Repeat the same procedures on both nodes.
Configuration
First of all let’s create the folder /opt/mysql/data and assign the ownership to the mysql user on the folder /opt/mysql.
Next we configure mysql by editing the /etc/my.cnf file and adding the specific section for galera on both nodes, of course you have to use your node_name and node_address.
datadir=/opt/mysql/data
socket=/var/lib/mysql/mysql.sock
port=3336
max_connections=100
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_io_capacity=1000
innodb_buffer_pool_size=512M
innodb_log_buffer_size=32M
innodb_log_file_size=128M
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_name='galera-clusterdb'
wsrep_node_name='galera-node-1'
wsrep_node_address="172.31.81.55"
wsrep_cluster_address="gcomm://172.31.81.55,172.31.80.162"
wsrep_provider_options="gcache.size=256M; gcache.page_size=256M"
wsrep_slave_threads=4
wsrep_sst_method=rsync
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
About the configuration, the main parameters to configure are the following:
- datadir is the storage directory for data files, in my case /opt/mysql/data
- port is the port the system listens on, in my case 3336
- max_connections is the maximum number of connections that the system can accept
- innodb_buffer_pool_size it depends on the characteristics of the server, usually it is configured with half of the RAM available on the server
- wsrep_cluster_name is the name to assign to the cluster, in my case galera-clusterdb
- wsrep_node_name is the name of the node, in my case galera-node-1 for the first node and galera-node-2 for the second node
- wsrep_node_address is the node address, in my case the nodes are 172.31.81.55 and 172.31.80.162
- wsrep_cluster_address the list of nodes belonging to the cluster
- wsrep_provider_options it depends on the server specs
SELinux and Firewall configuration
As mentioned at the beginning of the post, we also want to focus attention on server access security issues, so let’s see how to configure both the firewall and selinux.
Galera Cluster uses the following TCP ports: the port for MySQL (3306 the standard one or 3336 as in our case), 4444, 4567, 4568. And it uses the UDP port 4567.
SELinux
Enable the required ports on selinux with the following commands.
semanage port -a -t mysqld_port_t -p tcp 3306
semanage port -a -t mysqld_port_t -p tcp 3336
semanage port -a -t mysqld_port_t -p tcp 4444
semanage port -a -t mysqld_port_t -p tcp 4567
semanage port -a -t mysqld_port_t -p udp 4567
semanage port -a -t mysqld_port_t -p tcp 4568
semanage permissive -a mysqld_t
Firewall
Configure the firewall.
systemctl enable firewalld
systemctl start firewalld
firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=3336/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4567/udp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --reload
Starting Galera Cluster
Starting the nodes of the cluster is the most critical aspect of the whole setup, because when starting a node by default never starts as primary node, but assumes that the primary is already running trying to connect to it and join the cluster. When starting up, the node always looks for the presence of the primary node, if it finds it, it starts database synchronization, if it doesn’t find it, it remains in a non-operational state.
The problem here is that at the first start there is no primary node, therefore the start of MySQL must be done by specifying an appropriate parameter –wsrep-new-cluster, again it must be used only for starting the first node.
So, let’s start MySQL on the first node of the cluster.
mysqld_bootstrap --wsrep-new-cluster
Note: the –wsrep-new-cluster parameter should only be used when initializing the primary node.
We need to recover the temporary password generated by the system at startup, and run the mysql_secure_installation script, following the on-screen instructions.
grep password /var/log/mysqld.log
[MY-010454] [Server] A temporary password is generated for root@localhost: ul_aj+V5A6py
[MY-010733] [Server] Shutting down plugin 'caching_sha2_password'
[MY-010733] [Server] Shutting down plugin 'sha256_password'
[MY-010733] [Server] Shutting down plugin 'mysql_native_password'
[MY-010733] [Server] Shutting down plugin 'caching_sha2_password'
[MY-010733] [Server] Shutting down plugin 'sha256_password'
[MY-010733] [Server] Shutting down plugin 'mysql_native_password'
In my case the auto generated root password is ul_aj+V5A6py.
/usr/bin/mysql_secure_installation
Before starting the second node, check the status of the cluster.
mysql -p -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
At this point we start MySQL on the second node using systemctl start mysqld and then check the cluster status again.
mysql -p -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
As you can see, the cluster is now composed of 2 nodes.
In case we want to add other nodes to the cluster, just prepare the new nodes with the appropriate configurations and start MySQL using systemctl.
Check if cluster is operational
To verify the replica operation of the Galera cluster, let’s try to insert data into the database on the first node and check that the same data is synchronized on the second and vice versa.
On the first node we create the mydb database, the mytable table and insert a data.
create database mydb;
create table mydb.mytable (Name varchar(255), Value varchar(255));
insert into mydb.mytable (Name, Value) values ("nome1", "valore1");
Let’s move to the second node and read the data inside the mytable table.
select * from mydb.mytable;
+-------+---------+
| Name | Value |
+-------+---------+
| nome1 | valore1 |
+-------+---------+
On the second node insert a new data in the same table.
insert into mydb.mytable (Name, Value) values ("nome2", "valore2");
Let’s go back to the first node and execute the select again on the table, as we can see we find both data.
+-------+---------+
| Name | Value |
+-------+---------+
| nome1 | valore1 |
| nome2 | valore2 |
+-------+---------+
So, in this configuration Galera Cluster is working correctly by replicating the data on the 2 nodes, that works in master - master mode, and both accept read and write operations.
Restart in case of failure
As previously noted, the most critical point of a Galera cluster is the startup order of the nodes, because at startup each node would start looking for an available Primary Node, therefore it is not a good idea to enable the automatic startup of MySQL via systemcl at the servers boot. Let’s think for example of a complete restart of the servers, if on both the start of MySQL is scheduled automatically via systemctl start mysqld, the cluster will not start, because none of the nodes will bootstrap the cluster.
In case of a full reboot, due to abnormal situations or network connectivity issues, what you should do is to identify the most up-to-date node in terms of transaction commits and bootstrap this node back to primary.
To determine the most up-to-date node in terms of commits:
SHOW STATUS LIKE 'wsrep_last_committed';
So you have to stop all nodes and bootstrap the node identified as primary /usr/bin/mysqld_bootstrap --wsrep-new-cluster, then start the other nodes. A useful solution to automate node restart is described here.