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.
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.
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.
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;
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
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
Restart the MySQL server to apply the changes.
$ sudo service mysql restart
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';
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 |
+----------------------------+
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 |
+----------------------------+
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.
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.