MySQL InnoDB Cluster Installation – A Technical Case Study

MySQL InnoDB Cluster Installation – A Technical Case Study

Reinforcing The Database Fault Tolerance Of A Distributed MySQL Database

Data is the currency of today’s web, mobile, social, enterprise and cloud applications and making sure data is always available is a top priority for any digitally enabled organisation – minutes of downtime will, if client facing, result in significant loss of revenue and reputation. In the case of internal data systems, any downtime can have a serious impact on productivity, the ability to take data-informed decisions and potentially even the viability of operations.

Data sharing methods are not new but the next generation of web, cloud, and communications services and their requirements for uncompromising scalability, uptime and agility has introduced new database challenges.

In this technical case study, we’ll guide you through the step-by-step process of installing and setting up a MySQL InnoDB Cluster and connecting a MySQL Router in the context of a big data-powered application for solar energy plant monitoring and management.

The Context – Big Data-based PV Energy Monitoring & Management Solution

Our client is a pioneer and leading company in PV (photovoltaic) monitoring, smart energy and feed-in management across renewable power and heating. The company offers professional monitoring and management solutions for PV plants across the globe which allows for individual plant management in fleets and for plants to be sorted and views adapted according to the requirements of the user group.

Detailed error analysis and quick problem solving guard against plant yield losses, securing the investment. Users benefit from time and budget efficiencies.

Seamless communication between WEB, data loggers and the individual components ensures the timely identification of any problems, protecting yield levels. An easy-to-use user and system management offers extensive options for filtering, grouping and views exactly according to the needs and wishes of the respective user, be it by region, system type, service contract or any other specification.

Structured presentation of data means the user never loses the overview and intelligent, real-time compilation of individual system information is important allows for error analysis for rapid error analysis, faster troubleshooting and, ultimately, a smoother workflow and yield optimisation.

PV Power Plant Monitoring and Management System

Database Reliability Is Essential

 Clearly the nature of our client’s PV plant monitoring and management system means a secure and reliable flow of data is absolutely vital. Part of our job was to ensure that and the decision was made to do so by using a MySQL InnoDB cluster.

The Solution? MySQL InnoDB Cluster

A MySQL InnoDB cluster is an Oracle High Availability solution installed over MySQL with multi-master capabilities and automatic failover for high availability. It allows for database replications that increase fault tolerance by ensuring failure of one part of the infrastructure never proves fatal because replicated data backups can be pulled from another part of the infrastructure.

Data Duplication Reinforces Fault Tolerance Of A Distributed MySQL Database

When building highly accessible and fault-tolerant systems we create points in the infrastructure where data is duplicated. Duplication of data increases its availability in the event of problems with part of the infrastructure and speeds up the process of writing or reading data.

Data duplication is most common across the following sections of infrastructure:

  • load balancers
  • storage server
  • database server
  • backup server

This technical case study raises the question of how to organize a distributed MySQL database for fault tolerance by increasing the number of available database replications.

Our goal was to achieve this purely through MySQL without resorting to third-party software.

The result was a cluster of 4 servers, 1 entry point (mysql router) and 3 data storage servers (1 master and 2 secondary).

MySQL InnoDB Cluster

MySQL InnoDB Cluster

When initiating the installation, we used 3 servers for MySQL directly and 1 server to connect to our cluster – MySQL router.

MySQL server:

Router server:

OS Centos 7 c disabled selinux and firewalld. MySQL will use version 5.7

Connect the repository:

Turn off version 8 and turn on 5.7:

Rules /etc/my.cnf

We hang Mysql on port 3301, where data will be exchanged between the cluster servers.

We then bring the file /etc/my.cnf to this form on all three servers of the cluster, change the IP in the variables loose-group_replication_local_address and report_host , and also change the server_id – it is unique for each server.

And start MySQL and carry out initial set-up:

We now start creating a cluster. First we’ll create a cladmin user on all 3 servers, for this we’ll use the mysqlsh console:

The output of cl.status ()  should be something like this:

When we make changes to the cluster configuration, be sure to run the command:

We perform similar actions on all 3 servers, do not forget to write the correct IP and ID in /etc/my.cnf (server_id, loose-group_replication_local_address, report_host) 

After the previous steps are performed on the nodes that we connect (the 2nd and 3rd server in our example), we execute:

After this procedure on the second server, we return to the first server:

We also execute dba.configureLocalInstance () on the second server !!!

The output of cluster.status () should be something like this:

In a similar way, add the 3rd server.

If in the future we plan to expand the cluster to 5 or more servers, so we will need to edit the whitelist. This can be done through mysqlsh by going into sql mode or directly in /etc/my.cnf (do not forget to restart mysql daemons).

An example of how to do it using the mysqlsh console (alternately on each of the RW nodes):

 

After adding all 3 servers, cluster.status () will return the following:

If the cluster falls apart to the state of one server, it will need to be started with the loose-group_replication_bootstrap_group = ON

parameter in /etc/my.cnf After starting, the parameter will need to be turned off again, otherwise this server will always separate from the cluster and work independently.

Configure Mysql Router

Add a turnip and put a package:

Create a directory for our cluster configuration files:

We will do the configuration using bootstrap , when specifying the IP address, you must specify the address of the current RW server:

The result of a successful configuration, output:

Port 6446 – for RW connections.

Port 6447 – for RO connections.

Create a systemd service file to start the mysql router with our generated configuration files:

Turn it on at startup and run our router:

Check

Conclusion – High Availability & Fault Tolerance Of Database Achieved

The choice of a MySQL InnoDB Cluster has proven to be extremely robust and a highly effective way of achieving the levels of availability and fault tolerance absolutely necessary for the effective monitoring and management solar plant yield. We hope the combination of the business use case and technical step-by-step installation guide highlights when and where a MySQL InnoDB Cluster can be the right solution for a database-centric software solution in solving availability and fault tolerance challenges.

Add comment

E-mail is already registered on the site. Please use the Login form or enter another.

You entered an incorrect username or password

Sorry that something went wrong, repeat again!
Contact us