Quantum Ink

Writing about the future as it unfolds.

Diagram of a MySQL InnoDB Cluster setup on Linux servers for a test lab environment.

How to Build a Test Lab for MySQL InnoDB Cluster on Linux

Testing MySQL InnoDB Cluster in a controlled lab is essential before committing to it in production. This guide walks through a practical, repeatable setup on RHEL/Rocky Linux that you can run on VMs or cloud instances.

1. Lab goals and topology

Define what you want from the lab:

  • Understand Group Replication behaviour (failover, conflicts, flow control).
  • Test application connection patterns via MySQL Router.
  • Practise planned and unplanned failover.
  • Validate backup and recovery in a clustered context.

A simple but realistic topology is three data nodes plus one router node.

┌───────────────────────────────────────────────┐
│              InnoDB Cluster                   │
│                                               │
│  ┌───────────┐  ┌───────────┐  ┌───────────┐  │
│  │  node1    │  │  node2    │  │  node3    │  │
│  │ (RW)      │  │ (RO/RW)   │  │ (RO/RW)   │  │
│  └───────────┘  └───────────┘  └───────────┘  │
│        ▲             ▲              ▲         │
│        │ Group Replication (GCS)    │         │
└────────┴─────────────┴──────────────┴─────────┘
             ▲
             │
     ┌──────────────┐
     │ MySQL Router │
     └──────────────┘

Host suggestions (can be VMs on a single laptop):

  • node1.lab.local – MySQL server (primary seed).
  • node2.lab.local – MySQL server.
  • node3.lab.local – MySQL server.
  • router.lab.local – MySQL Router and test client tools.

2. Base OS preparation (RHEL / Rocky Linux)

On each node, perform basic OS setup.

2.1 System packages and networking

sudo dnf -y update
sudo dnf -y install wget curl vim net-tools firewalld

# Enable and start firewalld
sudo systemctl enable firewalld --now

Ensure hostnames resolve consistently. For a lab, you can use /etc/hosts on all nodes:

192.168.56.11  node1.lab.local  node1
192.168.56.12  node2.lab.local  node2
192.168.56.13  node3.lab.local  node3
192.168.56.14  router.lab.local router

2.2 Time synchronisation

Group Replication is sensitive to clock drift. Enable NTP:

sudo dnf -y install chrony
sudo systemctl enable chronyd --now

3. Install MySQL server on the three data nodes

Install a standard MySQL Server package from a vendor repository that supports Group Replication and InnoDB Cluster. The exact repo commands vary by vendor, but on RHEL/Rocky you typically:

# Example: enable vendor repo (adjust for your chosen distribution)
# sudo dnf -y install https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
# sudo dnf config-manager --enable mysql80-community

sudo dnf -y install mysql-community-server

sudo systemctl enable mysqld --now

Retrieve the temporary root password (for the first login):

sudo grep 'temporary password' /var/log/mysqld.log

Log in and set a strong root password:

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongRootPassw0rd!';

4. Configure MySQL for Group Replication

On each of the three data nodes, edit /etc/my.cnf (or a drop-in under /etc/my.cnf.d/). Adjust server IDs, hostnames and IPs appropriately.

4.1 Basic InnoDB and binary logging

[mysqld]
server_id=1                        # 2 on node2, 3 on node3
log_bin=binlog
binlog_format=ROW
binlog_checksum=CRC32
transaction_write_set_extraction=XXHASH64

# InnoDB settings suitable for lab
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# Required for Group Replication
plugin_load_add=group_replication.so

4.2 Group Replication configuration

Still in [mysqld] on each node:

group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"

# Local address for this node
# Example for node1; change port or IP per node if needed
group_replication_local_address="node1.lab.local:33061"

# List all members (same on all nodes)
group_replication_group_seeds=
  "node1.lab.local:33061,node2.lab.local:33061,node3.lab.local:33061"

group_replication_start_on_boot=OFF
group_replication_bootstrap_group=OFF

group_replication_single_primary_mode=ON
group_replication_enforce_update_everywhere_checks=OFF

Restart MySQL on each node:

sudo systemctl restart mysqld

5. Create an InnoDB Cluster with MySQL Shell

Install MySQL Shell on one host (router node is a good place):

sudo dnf -y install mysql-shell

5.1 Create an admin user

On each data node, create a dedicated cluster admin user:

mysql -uroot -p
CREATE USER 'clusterAdmin'@'%' IDENTIFIED BY 'Str0ngClusterPass!';
GRANT ALL PRIVILEGES ON *.* TO 'clusterAdmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

5.2 Configure instances for InnoDB Cluster

From the router node, use MySQL Shell in JavaScript or Python mode. This example uses JavaScript:

mysqlsh --js

// Connect to node1
shell.connect('[email protected]:3306');

// Configure instance for Group Replication
dba.configureInstance({
  user: 'clusterAdmin',
  host: 'node1.lab.local',
  port: 3306
});

// Repeat for node2 and node3
shell.connect('[email protected]:3306');
dba.configureInstance({ user: 'clusterAdmin', host: 'node2.lab.local', port: 3306 });

shell.connect('[email protected]:3306');
dba.configureInstance({ user: 'clusterAdmin', host: 'node3.lab.local', port: 3306 });

5.3 Create and grow the cluster

// Back to node1 as seed
shell.connect('[email protected]:3306');

var cluster = dba.createCluster('labCluster');

// Add the other nodes
cluster.addInstance('[email protected]:3306');
cluster.addInstance('[email protected]:3306');

// Check status
cluster.status();

You should see all three instances ONLINE, with one PRIMARY and two SECONDARY members.

6. Add MySQL Router for application access

On router.lab.local, install MySQL Router:

sudo dnf -y install mysql-router

Bootstrap the router against the cluster (this creates configuration and systemd unit files):

mysqlrouter --bootstrap [email protected]:3306 \
  --directory /etc/mysqlrouter \
  --user mysqlrouter

Enable and start the router:

sudo systemctl enable mysqlrouter --now

Check which ports are in use (usually one for read-write and one for read-only):

grep "bind_port" /etc/mysqlrouter/mysqlrouter.conf

Test connectivity from the router node:

mysql -h 127.0.0.1 -P 6446 -u clusterAdmin -p  # RW port example
mysql -h 127.0.0.1 -P 6447 -u clusterAdmin -p  # RO port example

7. Firewall and SELinux considerations

Open MySQL and Group Replication ports on each data node:

sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --permanent --add-port=33061/tcp
sudo firewall-cmd --reload

On the router node, open router ports as needed (example 6446 and 6447):

sudo firewall-cmd --permanent --add-port=6446/tcp
sudo firewall-cmd --permanent --add-port=6447/tcp
sudo firewall-cmd --reload

If SELinux is enforcing, use semanage port to label additional ports for MySQL-related services instead of disabling SELinux.

8. Basic lab exercises

8.1 Write and read tests

From router.lab.local, connect via the RW port and create a test schema:

mysql -h 127.0.0.1 -P 6446 -u clusterAdmin -p

CREATE DATABASE labtest;
USE labtest;
CREATE TABLE t1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  payload VARCHAR(100)
) ENGINE=InnoDB;

INSERT INTO t1 (payload) VALUES ('row1'), ('row2');

On another terminal, connect to any node directly and verify data:

mysql -h node2.lab.local -u clusterAdmin -p -e "SELECT * FROM labtest.t1"

8.2 Failover simulation

Observe which node is PRIMARY:

mysqlsh --js
shell.connect('[email protected]');
var cluster = dba.getCluster('labCluster');
cluster.status();

Simulate a failure by stopping MySQL on the PRIMARY (lab only):

sudo systemctl stop mysqld   # On the PRIMARY node only, in the lab

Watch the cluster re-elect a PRIMARY and confirm that RW connections via MySQL Router continue to work. Then restart the stopped node and verify it rejoins:

sudo systemctl start mysqld
cluster.status();

9. Best practices for a useful lab

  • Automate builds: Use scripts or Ansible to recreate the lab quickly. This makes it easy to test new versions or topology changes.
  • Use realistic schemas: Load anonymised production-like data to exercise replication, indexes and query plans.
  • Test backups: Combine physical (e.g. file-level or snapshot) and logical (e.g. mysqldump) backups and practise restoring a node into the cluster.
  • Exercise maintenance tasks: Rolling restarts, configuration changes, and version upgrades are all safer when rehearsed in the lab.
  • Observe metrics: Enable the Performance Schema and sys schema; monitor replication lag, flow control and transaction conflicts.
  • Document scenarios: Keep a runbook of commands and outcomes for failure tests, so you can compare future behaviour after upgrades.

10. Conclusion

A small InnoDB Cluster lab built on three MySQL nodes and one router node is enough to understand how Group Replication behaves, how applications should connect, and how operational tasks work in practice. By automating the build, using realistic data, and repeatedly testing failure and recovery scenarios, you gain confidence and clear procedures before deploying InnoDB Cluster into any production environment.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

Smart reads for curious minds

We don’t spam! Read more in our privacy policy

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *