Galera cluster is a true multi-master MySQL cluster using synchronous replication. It allows for any of the nodes to be used as a master or all of them as well as providing automatic node joining and node removal. The multi-master configuration is very different from the typical master-slave configuration done with MySQL servers and can provide much more load balancing and fault tolerance. This guide will help you setup a basic Galera Cluster with MariaDB and CentOS 7. We will configure 3 nodes as that is the minimum required for redundancy, if you were to configure a two-node cluster, if one was to leave the cluster ungracefully the second node would cease to function as well.Galera And MariaDB InstallationFirst you are going to want to install MariaDB, version 10.1, on each of the three nodes.Add the repository to each of the 3 nodesnano /etc/yum.repos.d/MariaDB10.1.repoInsert the following repository information and save the file# MariaDB 10.1 CentOS repository list – created 2017-08-10 00:39 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1Install the packages from yum, Galera is included when these are installedyum -y install MariaDB-server MariaDB-client MariaDB-commonOnce yum has completed installing finish the installation by runningmysql_secure_installationWe are going to use rsync to perform the replication so install that as well, we will also use lsof to make sure the server is bound to the correct ports:yum install -y rsync lsofMake sure each of the MariaDB instances starts on rebootsystemctl enable mariadbGalera Master Node ConfigurationAfter installing MariaDB on the master node edit the server.cnf filenano /etc/my.cnf.d/server.cnfYou will want to insert the following under the [galera] sectionbinlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=”gcomm://192.168.1.100,192.168.1.101,192.168.1.102″
## Galera Cluster Configuration
wsrep_cluster_name=”cluster1″
## Galera Synchronization Configuration
wsrep_sst_method=rsync
## Galera Node Configuration
wsrep_node_address=”192.168.1.100″
wsrep_node_name=”centos7-vm1″wsrep_on=ON – Setting this to ON enables replication. In MariaDB 10.1, replication is turned off as a default, so this needs to be explicitly stated.wsrep_cluster_address – This is where we specify each of the IP addresses for the nodes delineated by a comma. The primary node is always the first IP address, this this case its 192.168.1.100wsrep_cluster_name – Is the name of the cluster, you can name this anything you wantwsrep_node_address – Is the IP address of the node you are configuringwsrep_node_name – This is the name of the node you are currently configuring, it can be named anything you want, it just needs to be unique.Under the [mysqld] section add a log location (if you don’t, it will log to the main syslog)log_error=/var/log/mariadb.logOnce you have finished editing and saved server.cnf, go ahead and create the error logtouch /var/log/mariadb.logGive the error log the appropriate permissions:chown mysql:mysql /var/log/mariadb.logYou can now start the new master node by typing the followinggalera_new_clusterAfter you have started it, make sure it has bound to the correct ports using lsofPort 4567 is for replication traffic:# lsof -i:4567
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 4121 mysql 11u IPv4 34770 0t0 TCP *:tram (LISTEN)Port 3306 is for MySQL client connections:# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 4121 mysql 26u IPv4 34787 0t0 TCP *:mysql (LISTEN)You will want to add the the ports to the firewall, in this case we are using firewalldfirewall-cmd –zone=public –add-service=mysql –permanent
firewall-cmd –zone=public –add-port=3306/tcp –permanent
firewall-cmd –zone=public –add-port=4567/tcp –permanent
firewall-cmd –zone=public –add-port=4567/udp –permanentThen reload the firewall:firewall-cmd –reloadMake sure the cluster is running my connecting to MariaDB on the primary nodemysql -uroot -pThen check the cluster sizeMariaDB [(none)]> SHOW STATUS LIKE ‘wsrep_cluster_size’;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| wsrep_cluster_size | 1 |
+——————–+——-+It should say 1 at this point because only the primary node is connected.Adding Additional Nodes To GaleraAfter installing MariaDB on the addtional nodes, you will want to copy the [galera] section of /etc/my.cnf.d/server.cnf that We created earlier and insert it into the server.cnf on each of the additional nodes. The only lines that will each on each of the additional nodes will be the the following:wsrep_node_address=”192.168.1.100″
wsrep_node_name=”centos7-vm1″The wsrep_node_address will be the IP address of the node you are configuring and the wsrep_node_name will be the name of that node.After you have finished each of the servers configuration files, you can start them normallysystemctl start mariadbAs each node connects to the cluster you should see the wsrep_cluster_size increase:MariaDB [(none)]> SHOW STATUS LIKE ‘wsrep_cluster_size’;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| wsrep_cluster_size | 3 |
+——————–+——-+You will also see nodes join in the log:WSREP: Member 1.0 (centos7-vm2) synced with group.The logs will also indicate when a node as left the group:WSREP: forgetting 96a5eca6 (tcp://192.168.1.101:4567)
WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2You can view the full configuration of Galera by typing the following:MariaDB [(none)]> show status like ‘wsrep%’;
+——————————+——————————————————–+
| Variable_name | Value |
+——————————+——————————————————–+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_cert_index_size | 2 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 6173c852-7ca0-11e7-8d8e-0e2551d18de1 |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.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 | 87a5891a-7ca0-11e7-a3bb-fe31f8409645 |
| wsrep_incoming_addresses | 192.168.1.101:3306,192.168.1.7:3306,192.168.1.100:3306 |
| wsrep_last_committed | 2 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 1 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 0 |
| wsrep_local_index | 1 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.125000 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | 6173c852-7ca0-11e7-8d8e-0e2551d18de1 |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 25.3.20(r3703) |
| wsrep_ready | ON |
| wsrep_received | 8 |
| wsrep_received_bytes | 1169 |
| wsrep_repl_data_bytes | 359 |
| wsrep_repl_keys | 1 |
| wsrep_repl_keys_bytes | 31 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 454 |
| wsrep_thread_count | 2 |
+——————————+——————————————————–+ Testing Replication On The Galera ClusterFirst access one of the nodes MariaDB installs# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.25-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)]>Then create a new databaseMariaDB [(none)]> create database galera_test;
Query OK, 1 row affected (0.01 sec)Then check to ensure it was replicated to the other nodesMariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| galera_test |
| information_schema |
| mysql |
| performance_schema |
+——————–+
4 rows in set (0.00 sec)You should see the ‘galera_test’ database appear on the other nodes as well. That is it for the inital configuration of the MariaDB Galera Cluster on CentOS 7Aug 9, 2017LinuxAdmin.io
