Learning: MySQL InnoDB Cluster for beginners (part 1) - build a 3-node cluster on one machine, connect through MySQL Router and watch a failover

#About this session

By the end you will understand how InnoDB Cluster keeps MySQL available and be able to build and operate a lab cluster on your own. The advanced session takes this to real servers in production.

Note

Sandbox instances are for learning and testing only. All three members run on one machine, so the lab shows how the cluster behaves, but it gives no protection against that machine failing.

#Module 1: the ideas you need first

#The problem InnoDB Cluster solves

A single MySQL server is a single point of failure. If it stops, the application stops. Classic asynchronous replication gives you a copy, but someone (or a script) has to promote the replica, repoint the application and deal with transactions that may not have reached the replica.

InnoDB Cluster packages three pieces so that failover is automatic and safe:

#Primary and secondaries

In the default single-primary mode:

• One member is the primary. It accepts reads and writes.
• The other members are secondaries. They're read-only and apply the primary's changes.
• If the primary fails, the remaining members elect a new primary automatically, usually within seconds.

               Application
                    │
              MySQL Router
         6446 (read/write) │ 6447 (read-only)
          ┌────────────────┼────────────────┐
          ▼                ▼                ▼
   ┌────────────┐   ┌────────────┐   ┌────────────┐
   │  member 1  │◄─►│  member 2  │◄─►│  member 3  │
   │  PRIMARY   │   │ SECONDARY  │   │ SECONDARY  │
   └────────────┘   └────────────┘   └────────────┘
          Group Replication (majority agreement)

#Quorum: why three members

Group Replication needs a majority of members to agree before it commits anything. This protects you from two halves of a cluster both accepting writes ("split brain").

So the minimum useful cluster is three members. Two members give you no more availability than one.

#Rules your data must follow

• Tables must use the InnoDB storage engine.
• Every table needs a primary key (or a non-null unique key).

The cluster setup checks these and tells you what to fix.

#Module 2: install the software

Install the same version (8.4 LTS) of all three tools.

Ubuntu / Debian (after adding the MySQL APT repository from dev.mysql.com):

sudo apt-get update
sudo apt-get install -y mysql-server mysql-shell mysql-router
mysqlsh --version
mysqlrouter --version

Oracle Linux / RHEL (after adding the MySQL Yum repository):

sudo dnf install -y mysql-community-server mysql-shell mysql-router-community

Windows: use the MySQL Installer (or the MSI/ZIP packages) and select MySQL Server, MySQL Shell and MySQL Router.

For the sandbox lab you don't need the system MySQL service to be running. MySQL Shell starts its own instances using the installed server binaries. If a MySQL service is already running on port 3306, leave it alone; the lab uses other ports.

#Module 3: create three sandbox instances

Start MySQL Shell in JavaScript mode:

mysqlsh --js

Deploy three instances on ports 3310, 3320 and 3330. Use the same root password for all three (this is a lab):

dba.deploySandboxInstance(3310, {password: 'Lab#Pass2026'})
dba.deploySandboxInstance(3320, {password: 'Lab#Pass2026'})
dba.deploySandboxInstance(3330, {password: 'Lab#Pass2026'})

Each command creates a data directory (under ~/mysql-sandboxes on Linux and macOS, or %APPDATA%\MySQL\mysql-sandboxes on Windows), writes a configuration file and starts a mysqld process.

Check they're running:

\connect root@localhost:3310
\sql SELECT @@port, @@version;
\js

Shell switches between languages with \js, \sql and \py.

#Module 4: create the cluster

Connect to the first instance and create the cluster:

\connect root@localhost:3310
var cluster = dba.createCluster('labCluster')

Shell checks the instance, enables Group Replication and makes this member the primary. Now add the other two:

cluster.addInstance('root@localhost:3320', {recoveryMethod: 'clone'})
cluster.addInstance('root@localhost:3330', {recoveryMethod: 'clone'})

recoveryMethod: 'clone' copies a full snapshot of the primary's data to the new member, then the member catches up. It's the simplest and most reliable way to add members. (The alternative, incremental, replays binary logs and only works when the new member is already almost in sync.)

The new member restarts during cloning; Shell waits and reports progress.

#Module 5: read the cluster status

cluster.status()

Shortened output:

{
  "clusterName": "labCluster",
  "defaultReplicaSet": {
    "primary": "127.0.0.1:3310",
    "ssl": "REQUIRED",
    "status": "OK",
    "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
    "topology": {
      "127.0.0.1:3310": { "memberRole": "PRIMARY",   "mode": "R/W", "status": "ONLINE" },
      "127.0.0.1:3320": { "memberRole": "SECONDARY", "mode": "R/O", "status": "ONLINE" },
      "127.0.0.1:3330": { "memberRole": "SECONDARY", "mode": "R/O", "status": "ONLINE" }
    }
  }
}

What to look at:

Other statuses you'll meet: OK_NO_TOLERANCE (working, but one more failure stops it) and NO_QUORUM (writes stopped).

You can see the same thing in plain SQL on any member:

SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE
FROM performance_schema.replication_group_members;

#Module 6: put MySQL Router in front

Router "bootstraps" from the cluster: it reads the cluster's metadata and writes its own configuration.

Open a normal terminal (not Shell):

mysqlrouter --bootstrap root@localhost:3310 --directory ~/lab-router
~/lab-router/start.sh

On Windows, bootstrap into a folder such as C:\lab-router and run mysqlrouter -c C:\lab-router\mysqlrouter.conf in its own window.

The bootstrap output lists the ports. The two to remember:

Recent versions also offer port 6450 for read/write splitting in a single connection; we won't use it in this lab.

Test it:

mysql -h 127.0.0.1 -P 6446 -u root -p -e "SELECT @@port AS served_by;"
mysql -h 127.0.0.1 -P 6447 -u root -p -e "SELECT @@port AS served_by;"
mysql -h 127.0.0.1 -P 6447 -u root -p -e "SELECT @@port AS served_by;"

Port 6446 always shows 3310 (the primary). Port 6447 alternates between 3320 and 3330.

Tip

Always use -h 127.0.0.1, not localhost, when testing Router on Linux. With localhost the mysql client uses a Unix socket and skips Router entirely.

#Module 7: write data and watch it replicate

Create a test database through Router's read/write port:

-- mysql -h 127.0.0.1 -P 6446 -u root -p
CREATE DATABASE shop;
CREATE TABLE shop.orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer VARCHAR(50) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO shop.orders (customer, amount) VALUES ('Asha', 120.50), ('Ben', 75.00), ('Chen', 310.20);

Read it through the read-only port:

-- mysql -h 127.0.0.1 -P 6447 -u root -p
SELECT @@port, COUNT(*) FROM shop.orders;

The secondary already has the rows. Now try to write on the read-only port:

INSERT INTO shop.orders (customer, amount) VALUES ('Dev', 10);
-- ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option ...

That's the protection that stops anyone writing to a secondary by mistake.

Try a table without a primary key:

-- on port 6446
CREATE TABLE shop.no_pk (note VARCHAR(20));
INSERT INTO shop.no_pk VALUES ('test');
-- fails: Group Replication requires a primary key

#Module 8: break the primary and watch failover

Keep a loop running in a second terminal that writes through Router once a second:

while true; do
  mysql -h 127.0.0.1 -P 6446 -u root -p'Lab#Pass2026' -N -e \
    "INSERT INTO shop.orders (customer, amount) VALUES ('loop', 1); SELECT @@port, NOW();" 2>&1 | tail -1
  sleep 1
done

In MySQL Shell, kill the primary (this simulates a crash):

dba.killSandboxInstance(3310)

Watch the loop: a few inserts fail with a connection error, then they continue, now served by 3320 or 3330. Check the cluster from a surviving member:

\connect root@localhost:3320
var cluster = dba.getCluster()
cluster.status()

You'll see a new primary, the old one shown as (MISSING), and statusText saying the cluster can't tolerate any more failures (OK_NO_TOLERANCE).

Bring the old member back:

dba.startSandboxInstance(3310)
cluster.status()

It usually rejoins by itself as a secondary (it doesn't take the primary role back). If it stays (MISSING), rejoin it:

cluster.rejoinInstance('root@localhost:3310')

What you learned: the application only noticed a few seconds of errors, because it connects to Router, and applications must retry failed statements.

#Module 9: planned switchover

For maintenance you choose which member becomes primary, with no crash involved:

cluster.setPrimaryInstance('root@localhost:3310')
cluster.status()

Your write loop sees at most a brief error. Use this before patching the current primary.

#Module 10: clean up

# stop Router (Linux/macOS)
~/lab-router/stop.sh

// in mysqlsh
dba.stopSandboxInstance(3310, {password: 'Lab#Pass2026'})
dba.stopSandboxInstance(3320, {password: 'Lab#Pass2026'})
dba.stopSandboxInstance(3330, {password: 'Lab#Pass2026'})
dba.deleteSandboxInstance(3310)
dba.deleteSandboxInstance(3320)
dba.deleteSandboxInstance(3330)

Then delete the ~/lab-router folder.

#Exercises

1. Kill two sandbox members. What does cluster.status() say, and can you still write through port 6446? Start them again. Does the cluster recover by itself? If not, find the AdminAPI command that restores quorum (the advanced session covers it).
2. Add a fourth sandbox instance (port 3340) to the cluster. How many failures can it tolerate now? Is four members better than three?
3. Run cluster.describe() and cluster.options(). Find the consistency and exitStateAction settings and note their values.
4. Connect with the mysql client directly to port 3320 (not through Router) while it's a secondary, and try an UPDATE. What error do you get?
5. Stop Router while your write loop is running. What happens to the application? What does this tell you about where to run Router in production?

#Knowledge check

1. What are the three components of an InnoDB Cluster, and what does each do?
2. Why does a cluster need at least three members?
3. What's the difference between ports 6446 and 6447?
4. Why must every table have a primary key?
5. After a failover, does the old primary become primary again when it comes back?
6. Why should the application retry failed statements?

#Summary

• InnoDB Cluster = Group Replication + MySQL Shell + MySQL Router.
• Single-primary mode: one read/write primary, read-only secondaries, automatic election on failure.
• Three members tolerate one failure; writes need a majority.
• Applications connect to Router: port 6446 for writes, 6447 for reads, and they must retry.
• Sandbox instances are a quick, free way to learn and test the behaviour.
• Next step: the advanced session - real servers, configuration, security, monitoring, failure recovery, backups, upgrades and ClusterSet for disaster recovery.

#Further reading

• MySQL documentation: MySQL InnoDB Cluster (in the MySQL Shell manual), Group Replication, MySQL Router.
• On this site: choosing MySQL high availability and the InnoDB Cluster setup quick reference.