How to Setup MySQL Group Replication on Ubuntu 16.04

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

MySQL Group Replication is a technology that allows you to create a highly-available database cluster with redundant data spread across multiple servers.

This fault-tolerant database architecture is provided as a plugin in MySQL Server starting from version 5.7.17. In MySQL Group Replication, the database state is replicated to multiple servers. So, if one server fails, the database service will still be available if the remaining servers can agree on a quorum.

To withstand up to one server failure, at least 3 MySQL server instances are required. The status of each server is tracked by a distributed failure detection mechanism. This service monitors servers leaving the group.

A recovery process is then put in place to ensure that servers rejoining the group are brought to the latest state.

MySQL group replication performs multi-master update in all servers to ensure that the database service is always available. However, clients connected to a failed member of a group must redirected to available group members when the server experiences a clash.

This tutorial explains the basic steps of setting up a fault-tolerant, highly available, highly elastic and dependable MySQL Group Replication service on Alibaba Cloud Elastic Compute Service (ECS) instances running Ubuntu 16.04.

Prerequisites

A valid Alibaba Cloud account. Sign up now and get up to $1200 free credit to test over 40 Alibaba Cloud products including MySQL databases on Alibaba Cloud.
3 Alibaba Cloud ECS instances running Ubuntu 16.04 operating system created on the same data center. Please note, you will use the private IP addresses of these servers to setup the group.
A non-root user that can perform sudo tasks on each Alibaba Cloud ECS instances

For the sake of simplicity, we will use the below sample internal/private IP addresses for the 3 Alibaba Cloud ECS instances:

Server 1: 172.16.0.1
Server 2: 172.16.0.2
Server 3: 172.16.0.3

Also, you should open port 3306 and 33061 if you are using a firewall like UFW or security groups on the Alibaba Cloud Console.

MySQL Group Replication Infrastructure Requirements

In order for MySQL Group Replication to work, the infrastructure of your database servers must meet the following requirements:

Storage Engine: Your database tables must use the InnoDB storage engine. This engine works well in group replication because it supports transactions. When performing CRUD (create, read, update and delete) operations, transactions are performed concurrently and optimistically and conflicts are checked before data is committed to the group to ensure consistency.
Primary Keys: Each table on the database to be replicated must have a primary key to uniquely identify records. This helps the group replication services to determine conflicts when modifying rows in a transaction.
IP networks: MySQL group replication works on IPv4 network infrastructure and this is supported by Alibaba Cloud.
Server Location: MySQL group replication is affected by latency and network performance. Hence it is recommended to deploy the technology on servers that are close to each other. In this guide, the ECS instance must be deployed on the same Alibaba Cloud data center e.g. US (Virginia).

With those requirements in place, let's move through each step of setting MySQL group replication.

Step 1: Installing MySQL Community Edition

We will need to SSH to each Ubuntu 16.04 server to install MySQL server. The default MySQL server available on the Ubuntu software repository doesn't have the Group Replication plugin.

So we will have to manually download the deb packages for MySQL Community Edition. First, cd to the tmp directory.

$ cd /tmp

Then, issue the commands below one by one to download the correct deb packages using wget:

$ wget https://downloads.mysql.com/archives/get/file/mysql-common_5.7.20-1ubuntu16.04_amd64.deb

$ wget https://downloads.mysql.com/archives/get/file/mysql-community-client_5.7.20-1ubuntu16.04_amd64.deb

$ wget https://downloads.mysql.com/archives/get/file/mysql-client_5.7.20-1ubuntu16.04_amd64.deb

$ wget https://downloads.mysql.com/archives/get/file/mysql-community-server_5.7.20-1ubuntu16.04_amd64.deb

Before we install the required packages, we must update the package information index and install some dependency required by MySQL server:

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

Then, we are going to run dpkg package manager to install the deb files that we have just downloaded:

$ sudo dpkg -i mysql-common_5.7.20-1ubuntu16.04_amd64.deb
$ sudo dpkg -i mysql-community-client_5.7.20-1ubuntu16.04_amd64.deb
$ sudo dpkg -i mysql-client_5.7.20-1ubuntu16.04_amd64.deb
$ sudo dpkg -i mysql-community-server_5.7.20-1ubuntu16.04_amd64.deb

Repeat this process in all 3 Ubuntu 16.04 instances.

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

Our MySQL Group replication service requires a unique group name. We are going to use the built in Linux uuidgen command to generate Universally Unique IDentifier (UUID). To do this, run the command below:

$ uuidgen

Sample Output:

d9ba453e-76f9-4ef0-95ac-3d2f2d0013a1

Once you get the output shown above, copy paste the ID and keep it in a safe place. We will use the value later to configure MySQL servers.

Step 3: Configuring MySQL Server 1

MySQL server should be up and running on the 3 Alibaba Cloud ECS instances. Next, we need to configure some settings on each server. We can do this by modifying the /etc/mysql/my.cnf file.

So, open the configuration file on server 1 using a nano editor:

$ sudo nano /etc/mysql/my.cnf

Then, paste the below settings at the end of the file and remember to replace the IP addresses with the correct values:

[mysqld]

# Server configuration

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

# Group configuration

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="d9ba453e-76f9-4ef0-95ac-3d2f2d0013a1"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.16.0.1:33061"
loose-group_replication_group_seeds= "172.16.0.1:33061, 172.16.0.2:33061, 172.16.0.3:33061"
loose-group_replication_bootstrap_group=off
bind-address = "0.0.0.0"
report_host = "172.16.0.1"

Press CTRL+X, Y and Enter to save the file. Then, issue the command below to restart MySQL server:

$ sudo service mysql restart

Let's go over each of the parameters that we have specified above:

server_id: This parameter helps to identify each member in the group using a unique id. You must use a different value for each server participating in group replication.

gtid_mode: This directive enables global transaction identifier when set to ON.

enforce_gtid_consistency: This value must be set to ON to instruct MySQL to execute transaction-safe statements in order to ensure consistency when replicating data.

master_info_repository: When set to TABLE this directive allows MySQL to store details about master binary log files and positions into a table rather than a file to allow faster replication.

relay_log_info_repository: When set to TABLE this configures MySQL to store replication information as an InnoDB table.

binlog_checksum: We are setting this value to NONE. This disables MySQL from writing a checksum for each event taking place in the binary log. By setting the value to NONE, we instruct the server to verify events when they are written by checking their length rather than generating a checksum.

log_slave_updates: This value is set to ON to allow members to log updates from each other. In other words, the directive chains the replication servers together.

log_bin: The value determines if binary logging should take place. We will set this value to binlog in order for group replication to take place. Binary logging is responsible for recording all changes taking place in the database.

binlog_format: This specifies the binary logging format used. In our case, we will use the ROW format. This enables the safest form of row-based replication and higher level of concurrency due to fewer row locks on the database.

transaction_write_set_extraction: This instructs the MySQL server to collect write sets and encode them using a hashing algorithm. In this case, we are using XXHASH64 algorithm. Write sets are defined by primary keys on each record.

loose-group_replication_group_name: This is the name of the group that we are creating. We generated this ID earlier and we are going to use 'd9ba453e-76f9-4ef0-95ac-3d2f2d0013a1' as the group name.

loose-group_replication_start_on_boot: When set to off, the value instructs the plugin not to start working when the server starts. You may set this value to on once you are through with configuring all the group members.

loose-group_replication_local_address: This is the internal IP and port address combination used for communicating with other MySQL server members in the group. Please note the recommended port for Group Replication is 33061.

loose-group_replication_group_seeds: This configures the IP addresses or host names of members participating in the group replication together with their communication port. The value is used by new members to establish themselves in the group.
?
loose-group_replication_bootstrap_group: This option instructs the server whether to initiate a group or not. We will only run this option on demand on server 1 to avoid creating multiple groups. So, we it will remain off for now.

bind_address: The value of 0.0.0.0 tells MySQL to listen to all networks.

report_host: This is the IP address or hostname the group members reports to each other when they are registered on the group.

We are using the loose- prefix to instruct the server to start even when the MySQL Group Replication plugin is not installed and configured. This avoids encountering server errors before you finish configuring all the settings.

Setting Up Group Replication User for group_replication_recovery Channel

MySQL Group Replication works on a group_replication_recovery channel to transfer transactions between members. Hence, we must set up a replication user with REPLICATION-SLAVE permission on each server.

So, on server 1, login to the MySQL console by typing the command below:

$ mysql -uroot -p

Enter a password and hit Enter when prompted.

Then to avoid the new user's details from being logged to the binary log, first disable binary logging by issuing the command below:

mysql> SET SQL_LOG_BIN=0;

Then, run the commands below one by one to create the replication user:

mysql> CREATE USER replication_user@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
mysql> FLUSH PRIVILEGES;

Remember to replace PASSWORD with a strong value. Also, you may use a different username but it must be consistent across all the 3 servers participating in MySQL Group Replication.

Remember to enable binary logging once you are through with creating the user by issuing the below SQL command:

mysql> SET SQL_LOG_BIN=1;

To instruct the MySQL server to use the replication user we have created for the group_replication_recovery channel, we must run the command below:

mysql>CHANGE MASTER TO MASTER_USER='replication_user', MASTER_PASSWORD='PASSWORD' FOR CHANNEL 'group_replication_recovery';

The above settings will allow members joining the group to run the distributed recovery process to get to the same state with other members (donors).

Starting Up Group Replication on Server 1

We are going to start the group replication service on server 1. However, before we do this, we need to install the group_replication plugin on the server using the command below:

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Once the plugin is installed successfully, we are going to bootstrap the group using the commands below:

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

To avoid starting up different groups, we have set the value of group_replication_bootstrap_group back to OFF after successfully starting the group.

You can check the status of the new group we have just created by running the command below:

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | e3778cfd-b66e-11e8-aba5-42010a8e0002 | 172.16.0.1   |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

This shows that indeed server 1 is a member of a group. We can now create a database, a table and insert some test data to make sure the database server is working as expected.

mysql> create database sample_db;

Then, switch to the database:

mysql> use sample_db;

Next, create a customers table. Please note, we are using the innoDB database engine:

mysql> create table customers (customer_id INT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

Then, we can load sample data into the table by running the SQL commands below one by one:

mysql> insert into customers (customer_id, customer_name) values ('100', 'John James');
mysql> insert into customers (customer_id, customer_name) values ('200', 'Baby Doe');
mysql> insert into customers (customer_id, customer_name) values ('300', 'Jane Smith');

We can then run a select query to verify the presence of records:

mysql> select * from customers;
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|         100 | John James    |
|         200 | Baby Doe      |
|         300 | Jane Smith    |
+-------------+---------------+
3 rows in set (0.00 sec)

Step 4: Adding Server 2 to the MySQL Group

Once we have successfully configured server 1, we can now go ahead and add the remaining ECS instances to the group.

To configure server 2, make sure you have installed MySQL Community Edition as discussed on Step 1. Then, run the command below to edit the MySQL configuration file on server 2:

$ sudo nano /etc/mysql/my.cnf

Then, add the following settings at the end:

[mysqld]

# Server configuration

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

# Group configuration

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="d9ba453e-76f9-4ef0-95ac-3d2f2d0013a1"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.16.0.2:33061"
loose-group_replication_group_seeds= "172.16.0.1:33061, 172.16.0.2:33061, 172.16.0.3:33061"
loose-group_replication_bootstrap_group=off
bind-address = "0.0.0.0"
report_host = "172.16.0.2"

Press CTRL + X, Y and Enter to save the file. Then, restart MySQL on server 2:

$ sudo service mysql restart

Then, login to MySQL on server 2 :

$ sudo mysql -uroot -p

Enter your password and hit Enter when prompted.

Then, issue the commands below to configure the credentials for the recovery user on server 2:

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

Install the MySQL group replication plugin on server 2:

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Next, add server 2 to the group that we bootstrapped earlier:

mysql> START GROUP_REPLICATION;

If you issue the below SQL command on server 2 to check the performance schema table, you should see two members in the group:

mysql> SELECT * FROM performance_schema.replication_group_members;

Sample Output:

+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 644d815c-b680-11e8-8e58-00163e0003bf | 172.16.0.2 |        3306 | ONLINE  |
| group_replication_applier | c76c6f22-b67e-11e8-979b-00163e007d66 | 172.16.0.1 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+

This confirms that the new member has successfully joined the group.

Step 5: Adding Server 3 to the MySQL Group

Server 1 and Server 2 are now attached to the group, we are now going to add server 3. So SSH to server 3. Make sure the MySQL Community Server is running.

Then, edit the MySQL configuration file:

$ sudo nano /etc/mysql/my.cnf

Paste the below content at the end of the file and replace the IP address and server_id values accordingly:

[mysqld]

# Server configuration

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

# Group configuration
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="d9ba453e-76f9-4ef0-95ac-3d2f2d0013a1"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.16.0.3:33061"
loose-group_replication_group_seeds= "172.16.0.1:33061, 172.16.0.2:33061, 172.16.0.3:33061"
loose-group_replication_bootstrap_group=off
bind-address = "0.0.0.0"
report_host = "172.16.0.3"

Press CTRL + X, Y and Enter to save the file. Then, restart the MySQL server:

$ sudo service mysql restart

Then login to MySQL on server 3:

$ sudo mysql -uroot -p

To configure the credentials for the recovery user on server 3, run the commands below and replace PASSWORD with a strong value:

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

Then, install the MySQL group replication plugin on server 3:

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Next, start group replication on server 3 to join the main group:

mysql> START GROUP_REPLICATION;

If you issue the below SQL command on server 3 to check the performance schema table, you should see 3 members on the group:

mysql> SELECT * FROM performance_schema.replication_group_members;

Sample Output:

+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 644d815c-b680-11e8-8e58-00163e0003bf | 172.16.0.2 |        3306 | ONLINE       |
| group_replication_applier | c76c6f22-b67e-11e8-979b-00163e007d66 | 172.16.0.1 |        3306 | ONLINE       |
| group_replication_applier | e53da6d8-b682-11e8-a5d1-00163e007fd5 | 172.16.0.3 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
3 rows in set (0.00 sec)

Server 3 should now be in sync with server 1 and 2, it must have the data in place. You can try to retrieve the sample data that we created earlier on server 1 and see whether the group has broadcasted the changes to server 3.

Run the commands below:

mysql> use sample_db
mysql> select * from customers;
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|         100 | John James    |
|         200 | Baby Doe      |
|         300 | Jane Smith    |
+-------------+---------------+
3 rows in set (0.00 sec)

As you can see above, server 3 is already updated with the latest data from the group. Congratulations. Your data is now spread across multiple servers in a shared-nothing architecture to ensure high availability in case one server fails.

Conclusion

In this guide, we have discussed the steps of setting up MySQL Group Replication across 3 Ubuntu 16.04 ECS instances on Alibaba Cloud. With the database architecture in place, you can connect your application or website to the server and be assured of database reliability.

Remember, you can add up other Alibaba Cloud ECS instances to form a more reliable MySQL replication group. If you are new to Alibaba, you can sign up to get up to $1200 worth of free credit to test over 40 Alibaba Cloud products.

Community

How to Setup MySQL Group Replication on Ubuntu 16.04

Prerequisites

MySQL Group Replication Infrastructure Requirements

Step 1: Installing MySQL Community Edition

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

Step 3: Configuring MySQL Server 1

Setting Up Group Replication User for group_replication_recovery Channel

Starting Up Group Replication on Server 1

Step 4: Adding Server 2 to the MySQL Group

Step 5: Adding Server 3 to the MySQL Group

Conclusion

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

ECS(Elastic Compute Service)

Marketplace