MySQL Group Replication

In this guide, we will walk you through setting up your first database cluster running on MySQL Group Replication with a HAProxy load balancer using ClusterControl. By the end of this tutorial, you will have a fully operational database cluster that you can monitor, manage, and scale using ClusterControl.

MySQL Group Replication, an open-source plugin by Oracle, offers built-in high availability and fault tolerance for MySQL (version 5.7.17 and later). This replication solution enables multiple MySQL servers to form a clustered environment, ensuring data redundancy and automatic failover.

Utilizing a Paxos-based consensus protocol, MySQL Group Replication guarantees that transactions are replicated and confirmed by a majority of servers before commitment. Key features include automatic failover, distributed recovery and seamless addition of new nodes. This makes it a robust choice for mission-critical MySQL deployments demanding high uptime and fault tolerance.

Prerequisites

Before you start, ensure that you have the following:

ClusterControl should be installed and running. If not, follow the installation instructions in Quickstart or use the Installer Script.
You need at least four hosts (bare-metal or virtual machines) installed with the supported operating system. See Operating System. One host is for database load balancer and another three hosts are for MySQL Group Replication nodes.
SSH access to all servers involved.
All nodes must have internet access during the deployment stage.
NTP is configured and running on all hosts to ensure that all nodes in the cluster maintain synchronized time.

Architecture

Our final system architecture will look like this:

flowchart TB
   a{{**Users/<br>clients/<br>apps**}} -->|RW| b[**lb1** <br>#40;HAProxy#41;<br>192.168.99.100] --> d[(**db1**<br>Primary<br>192.168.99.101)] & e[(**db2**<br>Primary<br>192.168.99.102)] & f[(**db3**<br>Primary<br>192.168.99.103)]
   d & e & f -.-|manages| y[/**ClusterControl server**<br>192.168.99.5/]
   b -.- |manages|y
    subgraph MySQL Group Replication
        d
        e
        f
    end

Step 1: Set up SSH key-based authentication

On the ClusterControl server, generate a new SSH key as root user:
```
ssh-keygen -t rsa
```

Copy the SSH public key to all target nodes (specify the password if prompted):

ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # lb1
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db1
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db2
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db3

If the target node only requires a key-based authentication, you can use the -o flag to customize the SSH option:

ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # lb1
ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # db1
ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # db2
ssh-copy-id -i /root/.ssh/id_rsa -p 22 -o 'IdentityFile /root/myprivatekey.pem' [email protected]  # db3

Perform some tests and make sure you can execute the following commands from the ClusterControl server without any password prompt:

ssh [email protected] "ls /root"
ssh [email protected] "ls /root"
ssh [email protected] "ls /root"
ssh [email protected] "ls /root"

Step 2: Deploy a new cluster

Open your browser and go to the IP address or domain name where your ClusterControl is installed.
From the ClusterControl dashboard, click on the Deploy a cluster button in the top-right corner and choose Create a database cluster. This will open the Deploy cluster wizard.
Choose "MySQL Group Replication" from the Database dropdown, choose "Oracle" from the Vendor dropdown and also the database major version from the Version dropdown. Click Continue.
In the Deploy MySQL Group Replication wizard, configure the database cluster as below:
(1) Cluster details(2) SSH configuration(3) Node configuration(4) Add nodes(5) Preview
Name: Enter a name for your cluster (e.g, My First Group Replication).

Tags: Enter one or more tags separate by pressing "Enter" (e.g, production, group replication, dc1).
SSH user: root

SSH user key path: /root/.ssh/id_rsa

SSH port: 22

SSH sudo password: (leave it blank)

SSH sudo / OS elevation command: Choose either uses sudo(default) or doas or pbrun

Install sofware: On (default)

Disable firewall: Checked (default)

Disable SELinux/AppArmor: Checked (default)
Server port: 3306 (default)

Server data directory: /var/lib/mysql (default)

Admin/Root user: root

Admin/Root password: Enter the password for the database admin/root

Repository: Use vendor repositories (default)

Version: Choose a database minor version.

Configurion template: my.cnf.grouprepl (default)

Enable SSL encryption: On (default)
MySQL node:

Specify "192.168.99.101" and press "Enter", followed by "192.168.99.102" and press "Enter", followed by ""192.168.99.103" and press Enter.

Wait until everything is green. If red notice appears, inspect the error seen by ClusterControl and fix it.

You can only proceed to the next step if all of the specified nodes are reachable (shown in green).
Review the summary of your deployment. You can always go back to any previous section to modify your configurations if you wish. The deployment settings will be kept until you exit the deployment wizard.
To deploy the cluster, click Finish.
ClusterControl will now start provisioning the MySQL Group Replication. You can monitor the progress from Activity center, where you will see detailed logs of the deployment. This process can take a few minutes. Once the deployment is complete, the cluster will appear in the Home page.

Step 3: Monitor your cluster

After the cluster has been deployed, you will be redirected to the Home page. This page provides an at-a-glance view of your cluster's status and important aspects of a database, including:

Cluster health: The Home page provides the cluster state
Node health: Rollover on the honeycomb diagram and get a more detailed view of a particular node. You can also see more detailed histograms under ClusterControl GUI → Clusters → choose the cluster → Dashboards.
Recent alarms: View most recent alarms of your cluster.
Automatic recovery status: Green means ClusterControl will raise an alarm and perform recovery procedures if it detects the database service is down. Red means ClusterControl will raise an alarm but the recovery procedures will be skipped.
Topology viewer: Roll over on each cluster entry to see the summary of the cluster visualized in topology view to better understand your overall database state. Also available under ClusterControl GUI → Clusters → choose the cluster → Nodes → Topology.

Step 4: Import data

To import data into a MySQL Group Replication, you need to take into account that MySQL Group Replication uses synchronous replication and all changes are replicated across all nodes. Performing restore using mysqldump or LOAD DATA INFILE will have a significant impact to the cluster and it is a slow operation since all inserts will be cerfified by the group replication. To avoid this, you should perform the restore on one of the database nodes.

Here are the steps to safely import data into a MySQL Group Replication using ClusterControl:

Stop two out of three nodes. Firstly, stop db2. Go to ClusterControl GUI → Nodes → locate db2 (1) → Actions → Stop node → Stop.
1. Find the node with Hostname: 192.168.99.102, Port: 3306, Status: Operational, Type: MySQL Group Replication and Role: Primary from the nodes list.
Next, stop db3. Go to ClusterControl GUI → Nodes → locate db3 (1) → Actions → Stop node → Stop.
1. Find the node with Hostname: 192.168.99.103, Port: 3306, Status: Operational, Type: MySQL Group Replication and Role: Primary from the nodes list.
At this point, the database should be running as a single-node cluster with only db1 is running. Depending on the format of your data, you can use different MySQL utilities to import data:
Using MySQL dumpUsing LOAD DATA INFILE
If you are importing from a dump file, transfer the dump file to db1 and use the following command to restore:
mysql -u root -p -h database_name < dumpfile.sql
If you are importing from a CSV or similar file format, transfer the file to db1 and run the following SQL statement from the MySQL console:
LOAD DATA INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Once imported, start db2 using Start Node. This will automatically sync the transaction write-set through Group Communication System (GCS) in MySQL Group Replication. Go to ClusterControl GUI → Nodes → locate db2 (1) → Actions → Start Node.
1. Find the node with Hostname: 192.168.99.102, Port: 3306, Status: Shut Down, Type: MySQL Group Replication and Role: Primary from the nodes list.
Start db3 using Resync Node. This will automatically sync the transaction write-set through Group Communication System (GCS) in MySQL Group Replication. Go to ClusterControl GUI → Nodes → locate db3 (1) → Actions → Start Node.
1. Find the node with Hostname: 192.168.99.103, Port: 3306, Status: Shut Down, Type: MySQL Group Replication and Role: Primary from the nodes list.
Once all nodes up and running, verify that all nodes should be green from the Home page. The import exercise is complete.

Step 5: Deploy a new load balancer

Using HAProxy in front of a MySQL Group Replication enhances performance, scalability, and high availability by providing intelligent query routing, load balancing, connection pooling, failover handling, and security. It allows you to fully leverage the power of cluster by managing traffic efficiently while ensuring the application remains connected even during node failures or maintenance events.

Go to ClusterControl GUI → Clusters → locate the cluster → Actions → Add new → Load balancer → Create balancer → HAProxy → Continue.
In the Create a load balancer wizard, configure HAProxy as below:
(1) Where to install(2) Installation settings(3) Advanced settings(4) Server instances in the load balancer(5) Preview
Server address: 192.168.99.100. Press "Enter" and wait until it turns green.

Policy: leastconn (default) (1)

You can choose the policy that suits your requirement.

Listen port (read/write): 5433 (default)

Listen port (read-only): 5434 (default)

Install for read/write splitting (master-slave replication): On (default)

Disable firewall: Checked (default)

Disable SELinux/AppArmor: Checked (default)
Overwrite existing /usr/local/sbin/mysqlchk_rw_split on targets: On (default)
Stats socket: /var/run/haproxy.socket (default)

Admin port: 9600 (default)

Admin user: admin

Admin password: Enter the password for the Admin user

Backend name (RW): haproxy_5433_rw (default)

Backend name (RO): haproxy_5434_ro (default)

Timeout server (in seconds): 10800 (default)

Timeout client (in seconds): 10800 (default)

Max connections frontend: 8192 (default)

Max connections backend (per instance): 1024

xinted allow connections from: 0.0.0.0/0 (default)
192.168.99.101:3306 (Primary): On (default)

Advance options

Role: Active (default)

Connection address: External Address [192.168.99.101]

192.168.99.102:3306 (Primary): On (default)

Advance options

Role: Active (default)

Connection address: External Address [192.168.99.102]

192.168.99.103:3306 (Primary): On (default)

Advance options

Role: Active (default)

Connection address: External Address [192.168.99.103]
Review the summary of your deployment. You can always go back to any previous section to modify your configurations if you wish. The deployment settings will be kept until you exit the deployment wizard.
To deploy the load balancer, click Finish.
ClusterControl will now start provisioning the HAProxy instance. You can monitor the progress from Activity center, where you will see detailed logs of the deployment. This process can take a few minutes. Once the deployment is complete, the load balancer instance will appear in the Nodes page.

Tips

For production environment, it is recommended to have at least two load balancers to eliminate single-point of failure. You can deploy another HAProxy instance and tie them together with a virtual IP address. See Create Load Balancers.

Step 6: Connecting to the database cluster

At this point, your application should be able to access the database cluster via the following endpoints:

Load balancer instance, 192.168.99.100 on port 3307. This is a load-balanced MySQL connection via HAProxy. Use the database user credentials that you created at Step 5.
If there are two or more load balancers, tie them together with a virtual IP address provided by Keepalived. Use the virtual IP address in your connection string instead of the load balancers' primary IP address.

Step 7: Enable automatic backups

To ensure your cluster data is protected, you should schedule an automatic backup. For MySQL Group Replication, ClusterControl supports both logical and physical backups. For a small dataset, a daily logical backup should be sufficient since by default, ClusterControl configures every database server with binary logs enabled that can be used for point-in-time recovery.

Go to ClusterControl GUI → choose the cluster → Backups to open the backup overview page of the cluster.
Go to Create Backup → Schedule a Backup to schedule an automated backup. It will open a backup wizard to follow.
In the Create a backup schedule wizard, configure the backup as below:
(1) Configuration(2) Advanced settings(3) Storage(4) Schedule(5) Preview
Schedule name: Daily backup - mysqldump

Cluster: (leave as it is)

Backup host: db3

Backup method: mysqldump

Dump type: Complete PITR-compatible

Upload backup to cloud: Off
Compression: On (default)

Compression level: 6

Enable encryption: On

Verify backup: Off (default)

Retention: On (default)

Use extended insert: On (default)

Failover backup if node is down: On
Storage location: Store on controller (default)

Storage directory: /root/backups (default)

Backup subdirectory: BACKUP-%I (default)
Set backup schedule: Simple

Every: day at 2:00

Select timezone: Use your preferred timezone
Review the summary of the backup configuration. You can always go back to any previous section to modify your configurations if you wish. The backup configuration settings will be kept until you exit the wizard.
Click Create to schedule the backup.

Once configured, ClusterControl will take care of the automated backups, ensuring your data is safe. ClusterControl also support advanced backup features like partial backup, backup encryption, backup verification, backup throttling, back up to cloud and point-in-time recovery. All successful backups will be listed in the Backups → All Backups page, where you can see the backup logs, size and perform restoration if necessary.

Step 8: Configure alerts

To keep track of any issues or incidents in your cluster, it's important to set up alerting. ClusterControl supports sending alarms and alerts to email, web hooks and third-party notification services like Slack or Telegram. In this example, we are going to use email.

Firstly, configure the mail server. Go to ClusterControl GUI → Settings → You don't have a mail server configured. Configure now → SMTP Server. Fill up all necessary information about the SMTP server. You can also opt for Sendmail, however a mail transfer agent (sendmail, postfix or exim) must be installed on the ClusterControl server.

Once configured, we can configure the alert and recipients as below:

Go to ClusterControl GUI → choose the cluster → Settings → Email Notifications.
Choose a user group where you are belong to from the User group dropdown.
Choose your email address to from the Users in the selected group dropdown.
Click Enable.
Set the Digest delivery time when you want a digested (summarized events) to be sent to you every day.
Set all Critical events to "Deliver" (default), all Warning events to "Digest" and you may ignore the Info events.

This ensures you are always notified when critical issues arise and can act quickly to resolve them. You will also get an email with summarized warning events every day at the configured delivery time.

Tip

You can also configure alarms to be sent to third-party notification systems (Slack, Telegram), incident management systems (PagerDuty, ServiceNow, OpsGenie) or web hooks. See Integration → Notification Services.

Step 9: Manage your cluster and load balancer

ClusterControl provides many other features for managing your cluster, including:

Database upgrades and patching: Perform rolling upgrades to keep your cluster up to date without downtime. This feature is available at ClusterControl GUI → choose the cluster → Actions → Upgrades.
Database node management: Perform start/stop/restart node, reboot host, bootstrap cluster, activate/deactivate read-only, resync node, change primary source and rebuild replica. These features are available at ClusterControl GUI → choose the cluster → Nodes → Actions.
HAProxy management: Perform HAProxy management related disable nodes, see the detailed status, restart service and reboot host. These features are available at ClusterControl GUI → choose the cluster → Nodes → Actions and ClusterControl GUI → choose the cluster → Nodes → HAProxy.
Configuration management: Perform database configuration changes globally or on individual database node. This feature is available at ClusterControl GUI → choose the cluster → Manage 🡒 Configuration.
Database user management: Manage database users and privileges globally. This feature is available at ClusterControl GUI → choose the cluster → Manage → DB Users.
Backup management: Create, schedule, restore, perform point-in-time recovery, upload backup to cloud, verify an existing backup, and set retention period. These features are available at ClusterControl GUI → choose the cluster → Backups → Actions and ClusterControl GUI → choose the cluster → Backups → More.
Maintenance management: Activate, deactivate, remark and schedule maintenance mode for all nodes. This feature is available at ClusterControl GUI → choose the cluster → Nodes → Actions → Schedule maintenance.
SSH console: Access your nodes directly from ClusterControl GUI via web SSH console. This feature is available at ClusterControl GUI → choose the cluster → Nodes → Actions → SSH Console.

Step 10: Scale your cluster

One of the key benefits of using ClusterControl is the ease of scaling your cluster. For MySQL Group Replication, you can scale out the cluster with another database node.

Adding a new database node is an online and non-blocking operation. It shall not cause downtime to the running cluster however expect increasing load on the source node if you stream directly from the primary. You may opt to stream from an existing backup to minimize this risk.

ClusterControl assumes that the new node that you want to add meets the requirements as decribed under Prerequisites and configured with a proper SSH key-based authentication as shown under Step 1: Set up SSH key-based authentication.

To add a new node, go to ClusterControl GUI → choose the cluster → Actions → Add new → Node → Create a database node.
In the Create a database node wizard, configure the following:
(1) Node Configuration(2) Advanced settings(3) Add node(4) Preview
Port: 3306

Data directory: /var/lib/mysql

Install software: On (default)

Disable firewall: Checked (default)

Disable SELinux/AppArmor: Checked (default)
Rebuild from a backup: Off (default)

Include in load balancer set (if exists): On
Node: Specify the IP address or hostname of the node that you want to add and press "Enter".
Review the summary of the deploymnent. You can always go back to any previous section to modify your configurations if you wish. The deployment configuration settings will be kept until you exit the wizard.
Click Finish to trigger the deployment job.
ClusterControl will start provisioning the new node. You can monitor the progress from Activity center, where you will see detailed logs of the deployment. This process can take a few minutes. Once the deployment is complete, the node will appear in the Nodes page.

Conclusion

Congratulations! You’ve successfully deployed, monitored, managed and scaled your first MySQL Group Replication using ClusterControl. From here, you can explore additional features such as failover management, advanced monitoring, advanced backup management, database restore, cluster cloning and many more. As you grow, ClusterControl will help ensure that your database infrastructure remains highly available and performant.