Step 5 – Single Primary Or Multi Primary

Next, you need to determine whether to go with a single-primary or multi-primary configuration. In a single-primary setup, MySQL assigns one primary server (typically the first member of the group) to manage all write operations. On the other hand, a multi-primary group allows any member to handle writes.

To set up a multi-primary configuration, simply uncomment the lines for loose-group_replication_single_primary_mode and loose-group_replication_enforce_update_everywhere_checks. This adjustment will enable the multi-primary setup. If you prefer a single primary configuration, keep those lines commented.

Step 6 – Host Specific Configuration

Now, we can focus on settings that will vary across the servers, such as:

• The server ID
• The address to bind to
• The address to report to other members
• The local replication address and listening port

Make sure to assign a unique number to the server_id directive. For the first member, you should set it to 1 and increase the number for each subsequent host. The bind-address and report_host settings should correspond to the respective server’s IP address, ensuring that the MySQL instance can accept external connections and accurately report its address to other hosts. Additionally, the loose-group_replication_local_address needs to reflect the current server’s IP address with the group replication port (33061) added to it.

For instance, here is how this part of the configuration would look for server1 using a sample IP address:

. . .
# Host specific replication configuration
server_id = 1
bind-address = "<server1-ip>"
report_host = "<server1-ip>"
loose-group_replication_local_address = "<server1-ip>:33061"

Complete this process on each of your MySQL servers. Here’s the configuration for member2:

. . .
# Host specific replication configuration
server_id = 2
bind-address = "<server2-ip>"
report_host = "<server2-ip>"
loose-group_replication_local_address = "<server2-ip>:33061"

And here’s the configuration for member3:

. . .
# Host specific replication configuration
server_id = 3
bind-address = "<server3-ip>"
report_host = "<server3-ip>"
loose-group_replication_local_address = "<server3-ip>:33061"

Be sure to update each highlighted IP address to that of the server whose configuration you’re editing.

When you are finished, double check that the shared replication settings are the same on each host and that the host-specific settings are customized for each host. Save and close the file on each host when you’re finished.

Each of your servers’ MySQL configuration files now contains the directives required to bootstrap MySQL group replication. To apply the new settings to the MySQL instance, restart the service on each of your servers with the following command:

sudo systemctl restart mysql

 

Step 7 — Configuring Replication Users and Enabling Group Replication Plugin

In order to establish connections with the other servers in the replication group, each MySQL instance must have a dedicated replication user.

On each of your MySQL servers, log into your MySQL instance with the administrative user to start an interactive session:

sudo mysql

Because each server will have its own replication user, you need to turn off binary logging during the creation process. Otherwise, once replication begins, the group would attempt to propagate the replication user from the primary to the other servers, creating a conflict with the replication user already in place. Run the following command from the MySQL prompt on each of your servers:

SET SQL_LOG_BIN=0;

Now you can run a CREATE USER statement to create your replication user. Run the following command, which creates a user named repl. This command specifies that the replication user must connect using SSL. Also, make sure to use a secure password in place of password when creating this replication user:

CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘password‘ REQUIRE SSL;

Next, grant the new user replication privileges on the server:

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@‘%’;

Then flush the privileges to implement the changes:

FLUSH PRIVILEGES;

Following that, re-enable binary logging to resume normal operations:

SET SQL_LOG_BIN=1;

Next, set the group_replication_recovery channel to use your new replication user and their associated password. Each server will then use these credentials to authenticate to the group:

CHANGE REPLICATION SOURCE TO SOURCE_USER=‘repl’, SOURCE_PASSWORD=‘password‘ FOR CHANNEL ‘group_replication_recovery’;

With the replication user in place, you can enable the group_replication plugin to prepare to initialize the group:

INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

Verify that the plugin is active by running the following command:

SHOW PLUGINS;

The group_replication plugin will appear at the bottom of the list since it is the most recently added plugin:

Output

+----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | | | | | | | . . . | . . . | . . . | . . . | . . . | | | | | | | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 45 rows in set (0.00 sec)

This output confirms that the plugin was loaded and is currently active. Before continuing on to the next step, make sure that you’ve run each command in this section on each of your MySQL instances.

Step 8 — Starting Group Replication

Now that each MySQL server has a replication user configured and the group replication plugin enabled, you can begin to bring up your group.

Bootstrapping the First Node

To start up the group, complete the following steps on a single member of the group. For demonstration purposes, this guide will complete these steps on member1

Group members rely on existing members to send replication data, up-to-date membership lists, and other information when initially joining the group. Because of this, you need to use a slightly different procedure to start up the initial group member so that it knows not to expect this information from other members in its seed list.

If set, the group_replication_bootstrap_group variable tells a member that it shouldn’t expect to receive information from peers and should instead establish a new group and elect itself the primary member. You can turn this variable on with the following command:

SET GLOBAL group_replication_bootstrap_group=ON;

Then you can start replication for the initial group member:

START GROUP_REPLICATION;

Following that, you can set the group_replication_bootstrap_group variable back to OFF, since the only situation where this is appropriate is when there are no existing group members:

SET GLOBAL group_replication_bootstrap_group=OFF;

The group will be started with this server as the only member. Verify this by checking the entries within the replication_group_members table in the performance_schema database:

SELECT * FROM performance_schema.replication_group_members;

This query will return a single row representing the current host:

Output

+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 13324ab7-1b01-11e7-9dd1-22b78adaa992 | 203.0.113.1  |        3306 | ONLINE       | PRIMARY     | 8.0.28         | XCom                       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)

The ONLINE value for MEMBER_STATE indicates that this node is fully operational within the group.

Next create a test database and table with some sample data. Once more members are added to this group, this data will be replicated out to them automatically.

Start by creating a sample database named playground:

CREATE DATABASE playground;

Next create an example table named equipment within the playground database with the following command:

CREATE TABLE playground.equipment (id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));

This table contains the following four columns:

• id: This column will contain integer values that increment automatically, meaning you won’t have to specify values for this column when you load the table with sample data
• type: This column will contain string values describing what type of playground equipment the row represents
• quant: This column will contain integer values to represent the quantity of the given type of playground equipment
• color: This column will hold string values specifying the color of the given equipment

Also, note that the id column is specified as this table’s primary key. In MySQL, every table replicated to a group must have a column designated as the table’s primary key.

Lastly, run the following command to insert one row of data into the table:

INSERT INTO playground.equipment (type, quant, color) VALUES (“slide”, 2, “blue”);

Query the table to make sure the data was entered correctly:

SELECT * FROM playground.equipment;

+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+
1 row in set (0.00 sec)

After verifying that this server is a member of the group and that it has write capabilities, the other servers can join the group.

Starting Up the Remaining Nodes

Next, start group replication on member2. Since you already have an active member, you don’t need to bootstrap the group and this member can join straightaway:

START GROUP_REPLICATION;

On server3, start group replication the same way:

START GROUP_REPLICATION;

Check the membership list again on any of the three servers. This time, there will be three servers listed in the output:

SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 13324ab7-1b01-11e7-9dd1-22b78adaa992 | <server1_ip>  |        3306 | ONLINE       | PRIMARY     | 8.0.28         | XCom                       |
| group_replication_applier | 1ae4b211-1b01-11e7-9d89-ceb93e1d5494 | <server2_ip>  |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
| group_replication_applier | 157b597a-1b01-11e7-9d83-566a6de6dfef | <server3_ip>  |        3306 | ONLINE       | SECONDARY   | 8.0.28         | XCom                       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

All members should have a MEMBER_STATE value of ONLINE. For a new group, if any of the nodes are listed as RECOVERING for more than a few seconds, it’s usually an indication that an error has occurred or something has been misconfigured. Check the logs at /var/log/mysql/error.log to get additional information about what went wrong.

Next, check whether the test database information has been replicated over on the new members:

SELECT * FROM playground.equipment;

+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+
1 row in set (0.01 sec)

If the data is available on the new members, it means that group replication is working correctly.

Step 9 — Testing Write Capabilities of New Group Members

Next, you can try writing to the database from your new replication group members. Whether this succeeds or not is a function of whether you chose to configure a single primary or multi-primary group.

Testing Writes in a Single Primary Environment

In a single primary group, you should expect any write operations from a non-primary server to be rejected for consistency reasons. You can find the current primary at any time by running the following query on any member of your replication group:

SHOW STATUS LIKE ‘%primary%’;

+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 13324ab7-1b01-11e7-9dd1-22b78adaa992 |
+----------------------------------+--------------------------------------+
1 row in set (0.01 sec)

The value of the query will be a MEMBER_ID that you can match to a host by querying the group member list like you did before:

SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 13324ab7-1b01-11e7-9dd1-22b78adaa992 | <server1_ip>  |        3306 | ONLINE       |
| group_replication_applier | 1ae4b211-1b01-11e7-9d89-ceb93e1d5494 | <server2_ip>  |        3306 | ONLINE       |
| group_replication_applier | 157b597a-1b01-11e7-9d83-566a6de6dfef | <server3_ip>  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.01 sec)

As this example output indicates, the host at 203.0.113.1 — member1 — is currently the primary server. If you attempt to write to the database from another member, the operation will fail:

INSERT INTO playground.equipment (type, quant, color) VALUES (“swing”, 10, “yellow”);

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

This is expected since the group is currently configured with a single write-capable primary. If the primary server has issues and leaves the group, the group will automatically elect a new member to be the primary and accept writes.

Testing Writes in a Multi-Primary Environment

For groups that have been configured in a multi-primary orientation, any member should be able to commit writes to the database.

You can double-check that your group is operating in multi-primary mode by checking the value of the group_replication_primary_member variable again:

SHOW STATUS LIKE ‘%primary%’;

+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| group_replication_primary_member |       |
+----------------------------------+-------+
1 row in set (0.02 sec)

If the variable is empty, this means that there is no designated primary host and that any member should be able to accept writes.

Test this on member2 by attempting to write some data to the equipment table:

INSERT INTO playground.equipment (type, quant, color) VALUES (“swing”, 10, “yellow”);

Query OK, 1 row affected (0.00 sec)

member2 committed the write operation without any errors.

On member3, run the following query to check whether the new item was added:

SELECT * FROM playground.equipment;

+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
+----+-------+-------+--------+
2 rows in set (0.00 sec)

This confirms that the member2’s write was successfully replicated.

Now, test write capabilities on member3 by running the following INSERT statement:

INSERT INTO playground.equipment (type, quant, color) VALUES (“seesaw”, 3, “green”);

Query OK, 1 row affected (0.02 sec)

Back on member1, test to make sure that the write operations from both of the new members were replicated back:

SELECT * FROM playground.equipment;

+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  1 | slide  |     2 | blue   |
|  2 | swing  |    10 | yellow |
|  3 | seesaw |     3 | green  |
+----+--------+-------+--------+
3 rows in set (0.01 sec)

This confirms that replication is working in each direction and that each member is capable of performing write operations.

Step 10 — Bringing the Group Back Up

Once the group is bootstrapped, individual members can join and leave without affecting availability, so long as there are enough members to elect primary servers. However, if certain configuration changes are made (like switching between single and multi-primary environments), or all members of the group leave, you might need to re-bootstrap the group the same way that you did initially.

On server1, set the group_replication_bootstrap_group variable to ON:

SET GLOBAL GROUP_REPLICATION_BOOTSTRAP_GROUP=ON;

Then initialize the group:

START GROUP_REPLICATION;

Following that, you can set the group_replication_bootstrap_group variable back to OFF:

SET GLOBAL GROUP_REPLICATION_BOOTSTRAP_GROUP=OFF;

Once the first member has started the group, other members can join:

START GROUP_REPLICATION;

Follow this process for any additional members:

START GROUP_REPLICATION;

The group should now be online with all members available:

SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 13324ab7-1b01-11e7-9dd1-22b78adaa992 | <server1_ip>  |        3306 | ONLINE       |
| group_replication_applier | 1ae4b211-1b01-11e7-9d89-ceb93e1d5494 | <server2_ip>  |        3306 | ONLINE       |
| group_replication_applier | 157b597a-1b01-11e7-9d83-566a6de6dfef | <server3_ip>  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.01 sec)

This process can be used to start the group again whenever necessary.

Step 11 — Joining a Group Automatically When MySQL Starts

With the current settings, if a member server reboots, it will not automatically rejoin the group on start up. If you want members to automatically rejoin the group, you can modify the configuration file slightly.

The setting outlined in this step is helpful when you want members to automatically join when they boot up. However, there are some things you should be aware of. First, this setting only affects when the MySQL instance itself is started. If the member is removed from the group because of timeout issues, but the MySQL instance remains online, the member will not automatically rejoin.

Secondly, having this setting enabled when first bootstrapping a group can be harmful. When there is not an existing group to join, the MySQL process will take a long while to start because it will attempt to contact other, non-existent members to initialize. Only after a lengthy timeout will it give up and start normally. Afterwards, you will have to use the procedure outlined above to bootstrap the group.

With the above caveats in mind, if you wish to configure nodes to join the group automatically when MySQL starts, open up the main MySQL configuration file:

sudo nano /etc/mysql/my.cnf

Inside, find the loose-group_replication_start_on_boot variable, and set it to ON:

[mysqld]
. . .
loose-group_replication_start_on_boot = ON
. . .

Save and close the file when you are finished. The member should automatically attempt to join the group the next time its MySQL instance is started.

Conclusion

By completing this tutorial, you learned how to configure MySQL group replication between three Ubuntu 20.04 servers. For single-primary setups, the members will automatically elect a write-capable primary when necessary. For multi-primary groups, any member can perform writes and updates.

Group replication provides a flexible replication topology that allows members to join or leave at will while simultaneously providing guarantees about data consistency and message ordering. MySQL group replication may be a bit more complex to configure, but it provides capabilities that aren’t possible in traditional replication.