Building a Highly Available MySQL 8 InnoDB Cluster

A 3-node Group Replication cluster with MySQL Router, tested end-to-end for automatic failover and manual switchover

By Munish Kumar Karna | Oracle DBA

Over the past few days I set up and stress-tested a production-style MySQL 8.0 InnoDB Cluster from scratch — 3 database nodes running Group Replication behind MySQL Router, with a full failover and switchover drill at the end. Sharing the walkthrough here in case it helps anyone building HA MySQL on-prem or in a private cloud.

Architecture

Three MySQL 8.0.46 nodes in single-primary Group Replication, fronted by MySQL Router for transparent read/write and read-only routing:

Node IP Address (example)
mysql0110.0.10.11
mysql0210.0.10.12
mysql0310.0.10.13
mysqlrouter10.0.10.20

1. Host Prep & Firewall

  • Configured /etc/hosts on all three nodes for name resolution, verified with ping and getent hosts
  • Installed MySQL 8.0 Server + MySQL Shell (mysqlsh) on all nodes
  • Opened the required ports: 3306 (MySQL), 33060 (X Protocol), 33061 (Group Replication) on the DB nodes, and 6446/6447 (Router R/W and R/O) on the router host

2. Preparing Instances for the Cluster

Ran dba.checkInstanceConfiguration() and dba.configureInstance() via MySQL Shell on each node. This created a dedicated cluster admin account (rather than reusing root@%), and auto-fixed the prerequisite server variables:

  • gtid_mode = ON
  • enforce_gtid_consistency = ON
  • binlog_transaction_dependency_tracking = WRITESET
  • unique server_id per node

Each node needed a restart to apply the read-only variables, and MySQL Shell handled that automatically as part of the configuration flow.

3. Creating the Cluster

With all three instances validated, the cluster was created on mysql01 and the other two nodes were added using clone-based provisioning (a full physical snapshot, rather than incremental recovery — the safer default for a freshly stood-up cluster):

  • dba.createCluster('PROD_CLUSTER') on mysql01
  • cluster.addInstance() for mysql02 and mysql03 — each cloned automatically and rejoined online within seconds

Final cluster.status() confirmed: status OK, tolerant to one failure, single-primary topology with mysql01 as PRIMARY and the other two as ONLINE SECONDARY.

4. Validating Replication

Created a test schema and a few rows on the primary, then confirmed the data replicated instantly to both secondaries by querying from mysql02 and mysql03 directly:

Verifying replicated rows from mysql02

Verifying replicated rows from mysql02

Verifying replicated rows from mysql03

Verifying replicated rows from mysql03

5. MySQL Router — Bootstrap & Routing

MySQL Router was installed on a separate host and bootstrapped against the cluster. Bootstrapping connects to the cluster, reads the topology from the metadata schema, generates mysqlrouter.conf, opens the routing ports, and stores its monitoring credentials securely in a local keyring:

  • mysqlrouter --bootstrap <admin_user>@mysql01:3306
  • Read/Write traffic → port 6446, Read-Only traffic → port 6447 (classic protocol)
  • X Protocol equivalents on 6448/6449

Connected through the router on 6446 and confirmed the session landed on the current PRIMARY (mysql01) — proving Router was resolving the write endpoint dynamically rather than pointing at a fixed host.

6. Failover Test

Simulated a primary outage by stopping mysqld on mysql01:

Stopping MySQL on the primary node (mysql01)

Stopping MySQL on the primary node (mysql01)

Reconnecting through Router's R/W port (6446) immediately routed to the new primary — no manual intervention, no application-side reconfiguration:

Router redirects R/W traffic to the newly elected primary

Router transparently redirects R/W traffic to the newly elected primary

Brought mysql01 back online:

Restarting mysqld on mysql01

Restarting mysqld on mysql01

One important behavior to call out: mysql01 rejoined the group automatically, but it came back as a SECONDARY — Group Replication does not hand primary status back just because the original node returns. That's intentional, and it's a good thing to know before you're troubleshooting it live.

replication_group_members confirms mysql02 is now PRIMARY after failover

replication_group_members confirms mysql02 is now PRIMARY after failover

7. Planned Switchover

Beyond the unplanned-failure drill, I also tested a controlled switchover — useful for planned maintenance on the primary node — using cluster.setPrimaryInstance() from MySQL Shell to move primary role back to mysql01 with zero downtime:

replication_group_members confirming mysql01 restored as PRIMARY after a clean switchover

replication_group_members confirming mysql01 restored as PRIMARY after a clean switchover

Key Takeaways

  • MySQL InnoDB Cluster + Router gives you automatic failover and app-transparent read/write routing out of the box — no external orchestrator needed
  • Clone-based provisioning makes adding nodes to an existing cluster fast and low-risk, even with live data
  • A recovered node rejoins as SECONDARY by design — plan your runbooks around that, don't assume automatic primary restoration
  • cluster.setPrimaryInstance() makes planned maintenance painless — you control exactly when and where the primary moves

Always happy to compare notes with anyone else running MySQL InnoDB Cluster, Oracle RAC/Data Guard, or GoldenGate in production — feel free to reach out.

#MySQL #InnoDBCluster #GroupReplication #MySQLRouter #HighAvailability #DatabaseAdministration #DBA #OracleDBA #DatabaseArchitecture

Comments

Post a Comment

Popular posts from this blog

Strengthening Database Security with SQL Firewall in Oracle 26ai

Image
  SQL Firewall in Oracle 26ai A hands-on, step-by-step walkthrough for DBAs Why SQL Firewall Matters ▸    Robust mitigation of SQL injection attacks ▸    Zero-trust session context verification ▸    Bypass-proof, kernel-level inspection ▸    Seamless “capture and enforce” lifecycle ▸    Native integration and scalability The 4-Phase Implementation Flow 1️⃣ Enable SQL Firewall    →    2️⃣ Capture Phase    →    3️⃣ Generate Allow-List    →    4️⃣ Enforce Phase   STEP 1   Enable the Global SQL Firewall Turn on SQL Firewall at the database level before any capture or enforcement can begin. SQL Firewall enabled successfully at the global level. STEP 2   Enable Capture Start a capture session for a target user (schema) to record real, in-flight SQL activity: BEGIN   DBMS_SQL_FIREWALL.CREATE_CAPTURE(  ...
Read more

MySQL Installation on Oracle Cloud’s “Always Free” Compute Instance

Image
I have configured MySQL Replication (Classic and DTID)   on   Oracle Public Cloud “Always Free”   that would like to share . I also would like to mention that It is unofficial  and testing purpose only. 1.Create free Oracle Cloud account You need to go to Oracle's Could Free Website and register. This need you must give your email address and payment method. You won't be charged if you choose the Oracle Cloud free-tier products.         2. Create compute instance         3.Choose SSH key and provide Public Key.       4.After selection of ssh public key it shows like         5.Choose Show Advance Option     6.Choose Management with Proper Fault Domain and choose networking Tab.                 7.Choose Networking launch option. ...
Read more

MySQL Replication on Oracle Cloud’s “Always Free” Compute Instance

Always Free Compute Instance creation on OCI and MySQL Community Server installation Please refer to my previous blog MySQL Installation on Oracle Cloud’s “Always Free” Compute Instance and repeat same step for second instance.In this blog I used one compute Instance as  Master and Other as Slave. MySQL Replication Classic 1.Need to set following System Variable at /etc/my.cnf (Master Host). datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server_id=1 bind-address=172.0.2.7 master_info_repository=TABLE relay_log_info_repository=TABLE log_bin=master_binlog binlog_format=row Property Value Description server_id 1 On a replication master and each replication slave, you must specify server_id to establish a unique replication ID master_info_repository TABLE The master info log contains status and current configuration informat...
Read more