Setting Up A MariaDB 10.2.x Galera Cluster on CentOS 7

When looking at setting up a cluster there are many ways to do this but when using MariaDB and Galera this is a simple task and can be completed within a very short space of time.

Here I will show how to setup a cluster with 2 nodes ideally you will want 3+ in a production environment (depending on requirements).

You will need to have 2 server preinstalled with CentOS 7 , if you are not using CentOS 7 then most of whats in this post will still be relivent but you will have to account for things that are different for your OS, the main differences will be file paths.

You will need to install MariaDB 10.2.x (latest stable) you can get details on adding the repo at https://downloads.mariadb.org/mariadb/repositories/

once you have added you repo install MariaDB

yum install MariaDB-server MariaDB-client Once you have installed MariaDB you are going to wan to have a look at SELinux as this can cause issues later on.

If you have selinux configured to enforcing then you will need to make exceptions or set to permissive, for simplicty and to initially get things working you may want to just set this to permissive by editing the config and setting the SELINUX option to permissive.

vi /etc/selinux/config edit to look like

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
#SELINUX=enforcing
SELINUX=permissive
# SELINUXTYPE= can take one of three two values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted

now issue a reboot of the server, after you have logged back in you will need to set MariaDB to load at boot then start the server

systemctl enable mariadb
systemctl start mariadb

After starting the MariaDB run the secure installation script to set the root password and lean up the temp database etc…

mysql_secure_installation

once you have done this login to the newly installed database server.

mysql -p

now you have completed the 1st part of setting up the cluster you will now need to replicate this for the 2nd server and any others you are going to be adding to the cluster.

Once you have both servers setup you will need to configure the Galera cluster

If you have firewallD active on the server then you will need to open up some port for galera to work

• 3306 MySQL client connections and state snapshot transfers that use the mysqldump method.
• 4567 Galera cluster replication traffic, multicast replication uses both UDP and TCP.
• 4568 Incremental state transfer.
• 4444 State snapshot transfer.

firewall-cmd --add-port=4567/tcp --permanent
firewall-cmd --add-port=4568/tcp --permanent
firewall-cmd --add-port=4444/tcp --permanent
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

On the 1st server stop MariaDB

systemctl stop mariadb edit your config file for MariaDB

vi /etc/my.cnf.d/server.cnf

Then edit the Galera options as required

• wsrep_cluster_address – This needs to contain a list of all the IPs of the servers in your cluster
• wsrep_cluster_name – This needs to be set as the name you are giving your cluster
• wsrep_node_address – This needs to be the IP of your server
• wsrep_node_name – This needs to be the unique name of your node/server

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://10.10.100.10,10.10.100.11'
wsrep_cluster_name='Cluster-1'
wsrep_node_address='10.10.100.10'
wsrep_node_name='node-1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

Now at this point you are almost ready to bring the server back on line, the first thing you need to do is create the cluster start MariaDB and test

galera_new_cluster
systemctl start mariadb

Then to test run a query SHOW STATUS LIKE ‘wsrep_cluster_size’ to show the size of the cluster this should return the value of 1

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

If all comes back ok you now need to move on to the 2nd server this will also apply to any additional servers/nodes you are adding.

Setting Up your 2nd server

Login to your 2nd server and stop MariaDB

systemctl stop mariadb

edit your config file for MariaDB

vi /etc/my.cnf.d/server.cnf

Then edit the Galera options as required

• wsrep_cluster_address – This needs to contain a list of all the IPs of the servers in your cluster
• wsrep_cluster_name – This needs to be set as the name you are giving your cluster
• wsrep_node_address – This needs to be the IP of your server
• wsrep_node_name – This needs to be the unique name of your node/server

  [galera]
  # Mandatory settings
  wsrep_on=ON
  wsrep_provider=/usr/lib64/galera/libgalera_smm.so
  wsrep_cluster_address='gcomm://10.10.100.10,10.10.100.11'
  wsrep_cluster_name='Cluster-1'
  wsrep_node_address='10.10.100.11'
  wsrep_node_name='node-2'
  wsrep_sst_method=rsync
  binlog_format=row
  default_storage_engine=InnoDB
  innodb_autoinc_lock_mode=2
  #
  # Allow server to accept connections on all interfaces.
  #
  bind-address=0.0.0.0
  #
  # Optional setting
  #wsrep_slave_threads=1
  #innodb_flush_log_at_trx_commit=0

Once you have updated the config file start MariaDB and then check the cluster size this should then report a value of 2.

You have now setup the Galera cluster, you can now start importing databases which you should see replicated across all servers/nodes.

One thing that you may also find useful for changing to INNODB by modifying your sql dump is this little one liner.

cat database.sql | sed -e 's/ENGINE=MyISAM/ENGINE=INNODB/g' > database_innodb.sql