Knowledgebase

Introduction

MySQL Group Replication is a plugin to create a highly-available fault-tolerant database architecture. Group replication eliminates the chances of a single point of failure (SPOF) with a shared-nothing architecture. In this guide, you'll install and configure the MySQL Group Replication plugin to create a highly available database cluster.

Prerequisites

Before you begin, ensure you have the following:

• A set of three Ubuntu 20.04 servers configured with private networking. This guide uses example private IP addresses 192.0.2.1, 192.0.2.2, and 192.0.2.3.
• A non-root user with sudo privileges on each server.
• All three servers are up to date with the latest upgrades.

1. Install MySQL Server

SSH to the first server as your non-root user. Install the MySQL server.

$ sudo apt install mysql-server

Set a root password for MySQL and configure security.

$ sudo mysql_secure_installation

Repeat the same process on the other two servers.

2. Generate the Group Replication Name

SSH to Server 1 and log in to MySQL server as root.

$ sudo mysql -u root -p

Enter your MySQL server root password and press Enter to continue.

Generate the UUID.

mysql> SELECT UUID();

Make a note of your new UUID. It will be formatted similarly to the example shown below. Each server uses this UUID as the Replication Group Name.

+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 00000000-1111-2222-3333-444444444444 |
+--------------------------------------+

Exit MySQL.

mysql> QUIT;

3. Configure Server 1

SSH to Server 1. Edit /etc/mysql/my.cnf

$ sudo nano /etc/mysql/my.cnf

Paste the information below at the end of the file.

• Replace 192.0.2.1 with the address of Server 1.

• Replace 192.0.2.2 and 192.0.2.3 with the addresses of Server 2 and 3 respectively.

• Replace the value of the loose-group_replication_group_name with the UUID from Step 2.

    [mysqld]
  
    server_id=1 
    bind-address=0.0.0.0
    gtid_mode=ON 
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
  
    plugin_load_add='group_replication.so'
    group_replication_single_primary_mode=OFF
    loose-group_replication_group_name="00000000-1111-2222-3333-444444444444"
    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=OFF
    report_host=192.0.2.1

Note: The loose- prefix for the group_replication variables instructs the server to start even if the Group Replication plugin is not loaded when the server starts.

Save and close the file.

The MySQL configuration settings explained:

• server_id=1
  • This is server 1 in the replication group.
• bind-address=0.0.0.0
  • Listen on all IP addresses.
• gtid_mode=ON
  • Run replication with global transaction identifiers.
• enforce_gtid_consistency=ON
  • MySQL will only execute statements that can be logged safely with GTID.
• binlog_checksum=NONE
  • Disable writing checksums to the binary log.
• plugin_load_add='group_replication.so'
  • Load the group replication plugin.
• group_replication_single_primary_mode=OFF
  • Multi-master replication model, all members have read-write access.
• loose-group_replication_group_name="00000000-1111-2222-3333-444444444444"
  • The unique name of the replication group.
• loose-group_replication_start_on_boot=OFF
  • Do not start replication at boot time. You will need to manually start replication. Read more about the implications and precautions of auto-starting replication.
• loose-group_replication_local_address= "192.0.2.1:33061"
  • The address and port used by this server for replication.
• loose-group_replication_group_seeds="192.0.2.1:33061, 192.0.2.2:33061, 192.0.2.3:33061"
  • The host and port combinations of the other group members.
• loose-group_replication_bootstrap_group=OFF
  • Set OFF to avoid bootstrapping the group from multiple servers and creating conflicts.
• report_host=192.0.2.1
  • The IP address this server uses to report to the other members.

Restart the MySQL server to apply the changes.

$ sudo service mysql restart

4. Create a Replication User

Log in to MySQL on Server 1.

$ sudo mysql -u root -p

Create the replication user. Replace EXAMPLE_PASSWORD with a strong password.

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

Bootstrap the Group Replication Plugin on the First Server

Only bootstrap one member of the group to avoid creating multiple groups with the same name. To do this, run the commands below on the first server.

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

Turn group_replication_bootstrap_group off to avoid creating multiple groups when you restart the MySQL server.

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Verify the status of the group by querying the replication_group_members.

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

Verify your output is similar to this. Your MEMBER_ID will be different.

+--------------------------------------+-------------+--------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+--------------------------------------+-------------+--------------+
| 11111111-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.1   | ONLINE       |
+--------------------------------------+-------------+--------------+

Create a test database, test_db.

mysql> CREATE DATABASE test_db;

Switch to the database.

mysql> USE test_db;

Create a test table, test_tbl.

mysql> CREATE TABLE test_tbl (employee_id INT PRIMARY KEY, employee_name VARCHAR(30) NOT NULL) Engine = InnoDB;

Confirm the table exists.

mysql> SHOW TABLES;

+----------------------------+
| Tables_in_test_replication |
+----------------------------+
| test_tbl                   |
+----------------------------+

5. Configure Server 2

SSH to Server 2. Edit /etc/mysql/my.cnf

$ sudo nano /etc/mysql/my.cnf

Paste the information below at the end of the file.

• Replace 192.0.2.2 with the address of Server 2.

• Replace 192.0.2.1 and 192.0.2.3 with the addresses of Server 1 and 3, respectively.

• Replace the value of the loose-group_replication_group_name with the UUID from Step 2.

    [mysqld]
  
    server_id=2
    bind-address=0.0.0.0
    gtid_mode=ON 
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
  
    plugin_load_add='group_replication.so'
    group_replication_single_primary_mode=OFF
    loose-group_replication_group_name="00000000-1111-2222-3333-444444444444"
    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

Save and close the file.

Restart MySQL.

$ sudo service mysql restart

Log in to MySQL as root.

$ sudo mysql -u root -p

Create the replication user for Server 2. Replace EXAMPLE_PASSWORD with your strong password.

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

Start the Group Replication plugin.

mysql> START GROUP_REPLICATION;

Verify Server 2 is now a member of the group.

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

Verify your output is similar to this. Your MEMBER_IDs will be different.

+--------------------------------------+-------------+--------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+--------------------------------------+-------------+--------------+
| 11111111-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.1  | ONLINE        |
| 22222222-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.2  | ONLINE        |
+--------------------------------------+-------------+--------------+

Verify Server 2 has replicated the test database.

mysql> SHOW databases;

+--------------------+
| Database           |
+--------------------+
| test_db            |
+--------------------+

Confirm the test table exists on Server 2.

mysql> SHOW TABLES;

+----------------------------+
| Tables_in_test_replication |
+----------------------------+
| test_tbl                   |
+----------------------------+

6. Configure Server 3

SSH to Server 3. Edit /etc/mysql/my.cnf

$ sudo nano /etc/mysql/my.cnf

Paste the information below at the end of the file.

• Replace 192.0.2.3 with the address of Server 3.

• Replace 192.0.2.1 and 192.0.2.2 with the addresses of Server 1 and 2 respectively.

• Replace the value of the loose-group_replication_group_name with the UUID from Step 2.

    [mysqld]
  
    server_id=3
    bind-address=0.0.0.0
    gtid_mode=ON 
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
  
    plugin_load_add='group_replication.so'
    group_replication_single_primary_mode=OFF
    loose-group_replication_group_name="00000000-1111-2222-3333-444444444444"
    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

Save and close the file.

Restart MySQL.

$ sudo service mysql restart

Log in to MySQL as root.

$ sudo mysql -u root -p

Create the replication user for Server 3. Replace EXAMPLE_PASSWORD with your strong password.

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

Start the Group Replication plugin.

mysql> START GROUP_REPLICATION;

Verify Server 3 is now a member of the group.

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

Verify your output is similar to this. Your MEMBER_IDs will be different.

+--------------------------------------+-------------+--------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_STATE |
+--------------------------------------+-------------+--------------+
| 11111111-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.1  | ONLINE        |
| 22222222-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.2  | ONLINE        |
| 33333333-40c4-11e9-92b4-7a4c400acda6 | 192.0.2.3  | ONLINE        |
+--------------------------------------+-------------+--------------+

Verify Server 3 has replicated the test database.

mysql> SHOW databases;

+--------------------+
| Database           |
+--------------------+
| test_db            |
+--------------------+

Confirm the test table exists on Server 3.

mysql> SHOW TABLES;

+----------------------------+
| Tables_in_test_replication |
+----------------------------+
| test_tbl                   |
+----------------------------+

The above output confirms that the MySQL Group Replication plugin is working on all three servers.

In a production environment, you must handle clients connected to a failed member and redirect them to an ONLINE member in the group. MySQL doesn't handle client-side fail-over. You must manage connections with a connector, load balancer, middleware, or a router such as MySQL Router 8.0

You can add up to 9 group members to your Group Replication topology.

Conclusion

You have successfully set up MySQL group replication on Ubuntu 20.04 servers. Web applications that use this replicated database will avoid a single point of failure.

For more information, see the official Group Replication documentation.