How to Setup Fault-Tolerant Database with MySQL Group Replication on Ubuntu 18.04

MySQL group replication is a plugin that provides the functionality of creating a shared-nothing fault-tolerant database architecture. The add-on utilizes a group of servers that interact with each other to agree on the state of a database at any given time and any changes that may occur.

The plugin is useful when creating mission critical applications that require  highly available databases. It simply creates an additional layer of security by coordinating redundant servers in a simple way.

Data is replicated across multiple servers and if a member leaves a group, an auto-detect mechanisms notifies the other members about the change.  If the remaining servers can agree on a quorum, database operations resume as expected.

In this guide, we will walk you through the steps of setting up MySQL Group Replication plugin on Ubuntu 18.04 server.

Prerequisite

Step 1: Installing MySQL Community Edition

The default MySQL package available on the Ubuntu software repository does not support MySQL Group Replication plugin. So we will manually pull deb packages from the official MySQL download page and install the Community Edition.

We will repeat this procedure in all the 3 servers since each server will house its own copy of the replicated MySQL database:

SSH to server1 and navigate to the tmp directory using Linux cd command:

$ cd /tmp

Then, use wget command to download a tar archive with all the deb package files that we are going to install:

$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.23-1ubuntu18.04_amd64.deb-bundle.tar

Once the file is downloaded, make an installation directory:

$ mkdir installation

Unzip the archive file to the installation directory that we created:

$ tar -xvf mysql-server_5.7.23-1ubuntu18.04_amd64.deb-bundle.tar -C installation/

All the necessary deb files should now be placed under the installation directory. However, before we install them, we need to install libaio1 and libmecab2 dependencies for MySQL to work.

So,first update the package information index, then install the dependencies using the commands below:

$ sudo apt-get update
$ sudo apt-get install libaio1
$ sudo apt-get install libmecab2

Next, cd  to the installation directory:

$ cd installation

Then, install MySQL community server using the dpkg package manager by running the below commands one by one:

$ sudo dpkg -i mysql-common_5.7.23-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-community-client_5.7.23-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-client_5.7.23-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-community-server_5.7.23-1ubuntu18.04_amd64.deb

Enter a secure root password for your MySQL server when prompted.Repeat this procedure in all the 3 servers participating in the group replication.

Step 2: Generating a Universally Unique Identifier (UUID) for the Group

Next, we will generate Universally Unique Identifier (UUID) for our group. To do this, login to MySQL on server1 using the command below:

$ sudo mysql -uroot -p

Enter the root password of MySQL server that your created above  when prompted and press Enter to continue.

Then, run the below SQL command to generate the UUID:

SELECT UUID();
+————————————–+
| UUID()                               |
+————————————–+
| 99bfc355-cd41-11e8-ba46-5600018c8695 |
+————————————–+

Copy the value generated above, we will need it when making changes to the MySQL server configuration file.

Step 3: Configuring MySQL Group Replication Settings on server1

Next, we are going to configure server1 to support group replication by editing the MySQL configuration file using nano editor:

$ sudo nano /etc/mysql/my.cnf

Then, paste the below content at  the end of the file:

[mysqld]

bind-address= 0.0.0.0
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

The above configurations enable remote access to the MySQL server and assigns 1 as the unique server identifier. They also instruct MySQL  to enable global transaction identifiers and disable checksum for the binary log file.

Next, paste the settings below:

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

These configurations enable binary logging in a row-based format and instruct MySQL to store replication information in system tables instead of files to speed up replication.

Finally, paste the below group replication information and remember to replace the group name with the UUID that we generated earlier.

The address 192.0.2.1 should match the private IP address of your virtual machine instances.

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”99bfc355-cd41-11e8-ba46-5600018c8695″
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= “192.0.2.1:33061”
loose-group_replication_group_seeds= “192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061”
loose-group_replication_bootstrap_group=offreport_host=192.0.2.1

The loose-group_replication_local_address should match the private IP address of  server1.

Also, make sure to include all the 3 private IP addresses for the servers participating in group replication as the value of the loose-group_replication_group_seeds.  

For the sake of simplicity, we have used the IP addresses 192.0.2.1, 192.0.2.2 and 192.0.2.3 for server1, server2 and server3 respectively.

Remember, the recommended port for MySQL Group Replication is 33061.

The loose- prefix  prevents MySQL server to encounter problems if the server starts before the plugin is installed.

The transaction_write_set_extraction configures the server to gather the write instructions set and encode it using the XXHASH64 algorithm

The group_replication_group_name sets the name of the group that is being created.

When set to off, the group_replication_start_on_boot directive disables the plugin from loading when the server starts.

The value specified in the group_replication_local_address tells the plugin to use the IP address and port combination to communicate to the other members of the group.

The  group_replication_group_seeds parameter is used to set the hosts and ports that will be used by new members joining the group. Although we have included all the 3 server addresses here, it is not mandatory to do this and you can choose only a few members as the value for the seeds depending on your group size.

We have set the loose-group_replication_bootstrap_group to off to avoid creating a new group with the same name every time our server reboots.

Once you have made the above changes, save the file by pressing CTRL+X, Y and Enter. Then run the command below to restart MySQL service on server1:

$ sudo service mysql restart

Step 4: Setting up User Credentials for the Group Replication Recovery Channel

To achieve distributed recovery, MySQL Group Replication uses the asynchronous replication protocol. This technology synchronizes new members before adding them to the group using group_replication_recovery channel .

Therefore a replication user must be created on each member in the group to aid in transferring transactions. To achieve this, log in to server1 using the command below:

$ sudo mysql -uroot -p

Enter the root password when prompted and hit Enter. Then disable binary logging to avoid logging the changes on the binary file:

mysql> SET SQL_LOG_BIN=0;

Then, run the commands below one by one to create the replication user and assign the correct privileges. Remember to replace PASSWORD with a strong value for security purposes.

mysql> CREATE USER ‘rep_user’@‘%’ IDENTIFIED BY ‘PASSWORD’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep_user’@‘%’;
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;

With the user configured, we can use the CHANGE MASTER TO query to tell MySQL  to assign the user to the group_replication_recovery channel:

mysql> CHANGE MASTER TO MASTER_USER=‘rep_user’, MASTER_PASSWORD=‘PASSWORD’      FOR CHANNEL ‘group_replication_recovery’;

Step 5: Installing the Group Replication Plugin and Bootstrapping the Group

We can now go ahead and install the group replication plugin on server1 using the command below:

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

Everything is now set and we can now bootstrap the group with the commands below:

mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql>START GROUP_REPLICATION;

To avoid bootstrapping multiple groups with the same name when the server restarts, we will set the GLOBAL group_replication_bootstrap_group back to off :

mysql>SET GLOBAL group_replication_bootstrap_group=OFF;

Once the group is started, we can check its status:

mysql> SELECT MEMBER_ID,MEMBER_HOST,MEMBER_STATE FROM performance_schema.replication_group_members;

Output:

+————————————–+————-+————–+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+————————————–+————-+————–+
| 18cf9650-cde5-11e8-9e26-560001b743c7 | 192.0.2.1   | ONLINE |
+————————————–+————-+————–+

The output above shows that server1 is online and indeed a member of our group.

MySQL Group Replication works with Innodb tables. So , we are going to create a test database to see if the database server is working as expected.

mysql> create database test_replication;

Then, we can switch to the database:

mysql> use test_replication;

Next, we need to create a test_table:

mysql> create table test_table (student_id INT PRIMARY KEY,
student_name VARCHAR(30) NOT NULL) Engine = InnoDB;

We can confirm the presence of the table by running the command below:

mysql> show tables;

Output:

+—————————-+
| Tables_in_test_replication |
+—————————-+
| test_table                 |
+—————————-+

Step 6: Configuring server2

Once server1 is up and our group is running, we can now go ahead and configure server2 and join it to the group.

SSH to server2 and make sure you have installed MySQL Community Edition server as outlined in step 1. Then, edit the MySQL configuration file using a text editor:

$ sudo nano /etc/mysql/my.cnf

Paste the below settings at the end of the file and remember to replace 192.0.2.2 with the correct private addresses of your VPS :

[mysqld]

bind-address= 0.0.0.0
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”99bfc355-cd41-11e8-ba46-5600018c8695″
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= “192.0.2.2:33061”
loose-group_replication_group_seeds= “192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061”
loose-group_replication_bootstrap_group=off

report_host=192.0.2.2

Press CTRL+X, Y and Enter to save the file.

Restart MySQL server for the changes to take effect using the command below:

$ sudo service mysql restart

Login to MySQL on server2:

$ sudo mysql-uroot -p

Enter your database password when prompted and hit Enter.

Next, create the replication user for the group replication recovery channel on server2 by running the commands below.

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER ‘rep_user’@‘%’ IDENTIFIED BY ‘PASSWORD’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep_user’@‘%’;
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER=‘rep_user’, MASTER_PASSWORD=‘PASSWORD’      FOR CHANNEL ‘group_replication_recovery’;

Then, install the MySQL group replication plugin on server2 using the commands below:

mysql>INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

Then, start the plugin to join server2 to the group:

mysql> START GROUP_REPLICATION;

You  can confirm the status of the group by running the command below on server2:

mysql> SELECT MEMBER_ID,MEMBER_HOST,MEMBER_STATE FROM performance_schema.replication_group_members;

Output:

+————————————–+————-+————–+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+————————————–+————-+————–+
| 18cf9650-cde5-11e8-9e26-560001b743c7 | 192.0.2.1   | ONLINE |
| 210cc012-cdf4-11e8-8be1-560001b74419 | 192.0.2.2   | ONLINE |
+————————————–+————-+————–+

As you can see from the output above, we now have two members in the group. To confirm if server2 was able to pick up data from server1, we can run the command below and see whether the sample database that we created above was synchronized succesfully:

mysql> show databases;

Output:

+——————–+
| Database           |
+——————–+

| test_replication   |
+——————–+

Step 7: Configuring server3

Just like we have done on server2, we can configure server3 and join it to the group. Before doing this, make sure you have installed MySQL community on server3 as discussed in step 1.

Then, edit the configuration file of server3 using nano text editor:

$ sudo nano /etc/mysql/my.cnf

Paste the below configuration information at the end of the file. Remember to replace 192.0.2.3 with the correct private address assigned to your server3 virtual machine instance.

[mysqld]

bind-address= 0.0.0.0
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=”99bfc355-cd41-11e8-ba46-5600018c8695″
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= “192.0.2.3:33061”
loose-group_replication_group_seeds= “192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061”
loose-group_replication_bootstrap_group=off

report_host=192.0.2.3

Press CTRL+X, Y and hit Enter to save the file.Then, restart MySQL server for the changes to take effect:

$ sudo service mysql restart

Next, log in to MySQL on server3:

$ sudo mysql -uroot -p

Enter your MySQL password when prompted and hit Enter. We need to create a user for the group replication channel on server3 just like we did on server1 and server2:

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER ‘rep_user’@‘%’ IDENTIFIED BY ‘PASSWORD’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep_user’@‘%’;
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER=‘rep_user’, MASTER_PASSWORD=‘PASSWORD’      FOR CHANNEL ‘group_replication_recovery’;

Next, run the command below to install the Group Replication plugin:

mysql>INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

Then start the Group Replication plugin to join server3 the group:

mysql> START GROUP_REPLICATION;

We can then confirm the status of our group using the commands below:

mysql> SELECT MEMBER_ID,MEMBER_HOST,MEMBER_STATE FROM performance_schema.replication_group_members;

Output:

+————————————–+————-+————–+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+————————————–+————-+————–+
| 1634bce6-cdfb-11e8-8f8d-560001b74425 | 192.0.2.1   | ONLINE |
| 18cf9650-cde5-11e8-9e26-560001b743c7 | 192.0.2.2   | ONLINE |
| 210cc012-cdf4-11e8-8be1-560001b74419 | 192.0.2.3   | ONLINE |
+————————————–+————-+————–+

As you can see above, our group has 3 members like we expected and our Group Replication setup is working like expected.

We can confirm if server3 was able to synchronize data from the group by running the command below:

mysql> show databases;

Output:

+——————–+
| Database           |
+——————–+

| test_replication   |
+——————–+

The database that we created is already synchronized and our Group Replication is working as expected.

Conclusion

In this guide, we have taken you through the steps of setting up and configuring MySQL group replication on Ubuntu 18.04 server.

If you have followed along, you are able to create a fault-tolerant database cluster using a shared-nothing architecture that ensures high availability for your database server.

New to VPS hosting, sign up with Digital Ocean and get $100 worth of free trial credit.

Faiba 4G Network and MiFi Router Review

Are you looking for a honest Faiba 4G review. Then, this guide will answer most of your questions.

Faiba_4G_Review

Faiba 4G network is a unique internet service product offered by Jamii Telecom. The company guarantees the fastest and most reliable broadband service in major towns in Kenya.

Some Facts about Faiba 4G Internet Service

The Faiba 4G network uses the regular mobile network but it works on 4G only. This means increased speed to the end users.

To use the product, you require a 4G sim card from Jamii Telecom and a phone or any device that supports 4G network. Luckily, since most phones in Kenya can only support 3G network, Jamii Telecom sells a MiFi(Mobile Wifi) router alongside their sim cards.

The MiFi router is manufactured by Huawei and it is quite a good device that can connect up to 12 users simultaneously on the internet. It goes for around Kshs. 5,500/=

Faiba 4G Bundles

The Faiba 4G bundles are quite cheap compared to the other networks bearing in mind that they have the fastest network.

  • A 1GB daily bundle costs Kshs. 50/=
  • If you want a weekly bundle, you will have to part with Kshs. 300 and get a whopping 8GB
  • For those who like monthly data plans, you can grab 25GB at Kshs. 1,000/=

Faiba 4G Coverage

The areas below are covered by Faiba 4G network

Nairobi Area:

  • Nairobi
  • Rongai
  • Kiserian
  • Ngong Town
  • Athi River
  • Syokimau
  • Kitengela
  • Ruiru
  • Kiambu
  • Kikuyu
  • Juja
  • Limuru

Machakos

  • Town Center
  • Miwani

Thika

  • Town center
  • Makongeni
  • Kiganjo
  • Landless

Nakuru

  • Engoshura Jandu
  • Barracks
  • Koinange
  • Lanet
  • Milimani
  • Naka
  • Pangani
  • Railway Station
  • Unga Estate
  • Bondeni
  • Eden
  • Kiamunyi
  • Kiamunyeki

Eldoret

  • Testimony
  • West Indies
  • Kapsoya
  • Railways
  • Kimumu
  • East
  • Langas

Kisumu

  • Manyatta 2
  • Migosi
  • Mountain View
  • Stadium
  • Nyalenda
  • Mamboleo
  • Carwash
  • Milimani
  • Industrial Area

Mombasa

  • NSSF
  • Mtwapa
  • Makaburini
  • Bamburi Cement
  • Voyager
  • Mbaraki
  • Shimanzi road
  • Makadara
  • Kiembeni
  • Mikindani
  • Mombasa Polytechnic
  • Bombolulu

Faiba 4G Agents and Shops

There are many places where you can buy the Faiba 4G product. I will list just a few of them but I will keep adding more shops to the list:

Nairobi

Compnet Vedic Hse room 201/202

Mama Ngina St., Nairobi Kenya

0722 356620/ 0722648222

Kiambu

Naivas Naivakom Kiambu

Thika

Tuscom – on Tuskys Chania(near Gatitu). This is where I bought my device before writing this Faiba 4G MiFi router review.

Thika Road

Samsung Shop TRM

Limuru

Naivas Naivakom Limuru

Athi River

Tuskys Tuscom Athi River

Syokimau

Naivas Naivakom Syokimau

Kitengela

Naivas Naivakom Kitengela

Buru buru

Tuskys Tuscom Buru Buru

Ngong Town

Naivas Naivakom Ngong Town

Machakos

Naivas Naivakom Machakos

Mombasa

Naivas Naivakom Nyali

Nakuru

Naivas Naivakom Nakuru

Kisumu

Tuskys Tuscom Kisumu

Managing your Faiba 4G MiFi

When you purchase the Faiba 4G MiFi device, you will have to download Huawei HiLink app on your smartphone.
You will use this software to  change the wifi password, block users, restart the device remotely and even view the battery usage in percentage.

Purchasing Bundle for Jamii Telcom Faiba 4G Router

Jamii Telecom has a very good app available on Googe Play for managing the credit or data bundles for your account. See the image below:
Faiba_4G_App_Google_Play
The procedure for purchasing bundles is quite straightforward.
You simply press Buy Bundle and you get prompted to choose a bundle. Then, enter your Mpesa PIN and after a few seconds, your account will be updated with the bundles purchased.

Using your Faiba 4G MiFi on a Computer or Laptop

As mentioned earlier the MiFi 4G router from Jamii Telcom supports up to 12 users. If you are using a computer or a laptop, you can simply connect the device to your machine via a USB cable that comes with the device.
Faiba_4G_Review
Alternative, if your laptop or desktop PC has a Wifi receiver, you simply search your MiFi network and connect to it. From there you can start enjoying very great speed even for streaming YouTube videos.

Conclusion

Faiba 4G has satisfied the ever increasing demand for fast, reliable stable and affordable internet for all people in Kenya.
I believe Jamii Telecom is doing their best to cover most parts in Kenya. So far, if you are in major towns, you can enjoy an internet speed that you have never imagined it existed in Kenya. I hope you enjoyed this Faiba 4G review.