Galera cluster with GCP Compute Engine | Sovereign Solutions

Galera cluster with GCP Compute Engine

Database Clustering is the process of combining more than one servers or instances connecting a single database. Sometimes one server may not be adequate to manage the amount of data or the number of requests, that is when a Data Cluster is needed.

Advantages of database clustering

The main reasons for database clustering are its advantages a server receives:

  1. Data redundancy: Multiple computers work together to store data amongst each other with database clustering. This gives the advantage of data redundancy. All the computers are synchronised that means each node is going to have the exact same data as all the other nodes.
  2. Load balancing: Load balancing or scalability doesn’t come by default with the database. It has to be brought by clustering regularly. It also depends on the setup. Basically, what load balancing does is allocating the workload among the different computers that are part of the cluster. This indicates that more users can be supported and if for some reasons if a huge spike in the traffic appears, there is a higher assurance that it will be able to support the new traffic.
  3. High availability: When you can access a database, it implies that it is available. High availability refers the amount of time a database is considered available. The amount of availability you need greatly depends on the number of transactions you are running on your database and how often you are running any kind of analytics on your data.
  4. Monitoring and automation: With a clustered database, automation is helpful because it will allow getting notifications if a system is being demanded too much. However, a cluster will have a designated machine that will be used as database management system/control panel for the whole cluster. This selected machine can have scripts that run automatically for the entire database cluster and work with all of the database nodes

How does clustering architecture works?

In cluster architecture, all requests are split with many computers so that an individual user request is executed and produced by a number of computer systems. The clustering is serviceable definitely by the ability of load balancing and high-availability. If one node collapses, the request is handled by another node. Consequently, there are few or no possibilities of absolute system failures.

Types of Database clusters :

  1. Failover/High Availability Clusters
  2. High-Performance Clusters
  3. Load Balancing Clusters

About Galera cluster

Galera Cluster is a synchronous multi-master replication plug-in for InnoDB. It is very different from the regular MySQL Replication, and addresses a number of issues including write conflicts when writing on multiple masters, replication lag and slaves being out of sync with the master. Users do not have to know which server they can write to (the master) and which servers they can read from (the slaves).

An application can write to any node in a Galera cluster, and transaction commits (row-based replication events) are then applied on all servers, via a certification-based replication. Certification-based replication is an alternative approach to synchronous database replication using Group Communication and transaction ordering techniques.

A minimal Galera cluster consists of 3 nodes and it is recommended to run with odd number of nodes. The reason is that, should there be a problem applying a transaction on one node (e.g., network problem or the machine becomes unresponsive), the two other nodes will have a quorum (i.e. a majority) and will be able to proceed with the transaction commit.

There are 3 Galera variants:

  1. MySQL Galera Cluster by Codership
  2. Percona XtraDB Cluster by Percona and
  3. MariaDB Galera Cluster (5.5 and 10.0) by MariaDB.

Galera on GCP configuration details

I.Project creation

Select or create a GCP project.Make sure that billing is enabled for your Google Cloud Platform project.

II. Create instances

From Google Cloud Platform Console, select all default things as show in below screenshot.

From Boot disk,select CentOS 7 by clicking Change button and create the instance.

Similarly create other instances.Below screenshot shows created instances.

III. Galera configuration details:

Connect to respective instances using SSH.

  1. Disable SELinux
    1. The sestatus command returns the SELinux status and the SELinux policy being used
      >>sestatus shows
      SELinux status: enabled
      SELinuxfs mount: /selinux
      Current mode: enforcing
      Mode from config file: enforcing
      Policy version: 23
      Policy from config file: targeted
      To disable this update SELINUX=disabled as given below
    2. sudo vi /etc/selinux/config
      # 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.
      # SELINUXTYPE= can take one of these two values:
      # targeted – Targeted processes are protected,
      # mls – Multi Level Security protection.
    3. sudo shutdown -r now
      Now verify the SELinux sestatus
      SELinux status: disabled
  2. Once SELinux disabled on instance, create the MariaDB repository to install Galera on each of the three instances:
    1. Remove if previous configuration things exists on the instance
      1. sudo yum erase mysql-server mysql mysql-devel mysql-libs
      2. sudo rm -rf /var/lib/mysql
    2. Create MariaDB repository
      1. sudo vi /etc/yum.repos.d/cluster.repo
  3. After the repository has been created, it’s time to install Galera and any software requirements on each of the three instances:
    1. sudo yum install socat -y
    2. sudo yum install MariaDB-Galera-server MariaDB-client rsync galera -y
      Restart mysql and secure mysql on each instance
    3. sudo systemctl start mysql or sudo service mysql start
    4. sudo /usr/bin/mysql_secure_installation
    5. sudo vi .my.conf
      Connect to mysql
      >mysql -u root -p
      >GRANT USAGE ON *.* to db_user@’%’ IDENTIFIED BY ‘admin’;
      >grant all privileges on *.* to db_user@’%’ IDENTIFIED BY ‘admin’;
      >flush privileges;
      >sudo systemctl stop mysql
  4. When MySQL is secured, you can add the Galera configuration to each instance
    Add below configuration details and save&auth.
    >>sudo vi /etc/my.cnf.d/server.cnf
  5. After completing the changes, start the cluster on instance-1 only
    >sudo /etc/init.d/mysql start –wsrep-new-cluster

IV. Testing

Once after cluster starts, log into MySQL on each node to verify the cluster is functioning correctly:

  1. Now create database to test the cluster on instance-1:

    mysql -u root -p
    MariaDB [(none)]> show status like ‘wsrep%’;

    Start mysql on other instances also with systemctl start mysql

  2. Now verify on instance-1 database for incoming addresses and cluster size.

    MariaDB [(none)]> show status like ‘wsrep%’;
    wsrep_incoming_addresses |,, |

    | wsrep_cluster_size | 3 |

    Fig.wsrep details

    MariaDB [(none)]> show databases;
    | Database |
    | information_schema |
    | mysql |
    | performance_schema |
    3 rows in set (0.00 sec)

  3. Verify on instance-2 and instance-3 also also shows 3 rows.
  4. Now create database sampledb1 on instance-1.
    MariaDB [(none)]> create database sampledb1;
    MariaDB [(none)]> show databases;
    | Database |
    | sampledb1 |
    | information_schema |
    | mysql |
    | performance_schema |
    4 rows in set (0.00 sec).
  5. Verify on instance-2 and instance-3 number of databases

    >>show databases;
    It shows 4 rows with created new database sampledb1.

  6. Go to instance-2 , create new table emp1 on sampledb1 database.
    >>use sampledb1;
    >>create table emp1(id INTEGER,emp name char(50));
    >>select * from emp1.
    Shows table without any records.
  7. Go to instance- 1 and instance-3 connect to sampledb1.
    >>select * from emp1;
    Shows new table emp1 on both instance-1 and instance-3.
  8. Go to instance-3 insert the record into emp1 table.
    >>insert into table emp1 (1,’John’);
    >>insert into table emp1 (2,’Robert’);
    >>select * from emp1;
    Shows 2 records in emp1 table.
  9. Go to instance-1 and instance-2 and verify number of records in emp1 table.
    >>select * from emp1;
    Emp1 table shows with two records in both the tables I.e instance-1 and instace-2
    In all above scenarios shows replication of the cluster between different nodes I.e instances.

V. Monitoring details:

Below images shows galera monitoring information i.e CPU,Network bytes and Nework packet details.

Fig. CPU usage details

Fig.Network bytes details

Fig.Network packet details


Talk to our experts now to learn how Sovereign Solutions can help your organization meet its business objectives

*This content is being provided for informational and educational purposes, and should not be regarded as a specific recommendation for your situation.