Quantum Ink

Writing about the future as it unfolds.

Diagram illustrating the architecture and setup process of a MySQL InnoDB Cluster.

Setting Up a MySQL InnoDB Cluster from Scratch

,

This guide walks through setting up a MySQL InnoDB Cluster from scratch, focusing on practical steps and safe defaults suitable for general engineers.

What Is a MySQL InnoDB Cluster?

A MySQL InnoDB Cluster is a high-availability setup built on Group Replication, MySQL Shell, and MySQL Router. It provides:

  • Multiple MySQL instances (InnoDB only) replicating using Group Replication
  • Automatic failover via an integrated cluster metadata store
  • Application connectivity through MySQL Router

Conceptually, you have three main layers:

+---------------------+
|   Applications      |
+----------+----------+
           |
           v
    +-------------+
    | MySQL Router|
    +------+------+ 
           |
  +--------+--------+--------+
  v                 v        v
+-----+           +-----+  +-----+
| M1  | <------>  | M2  |  | M3  |
+-----+  (Group   +-----+  +-----+
        Replication)

Prerequisites and Assumptions

This article assumes:

  • Three Linux hosts (or VMs) with network connectivity: db1, db2, db3
  • One optional host for MySQL Router (can be one of the DB nodes in labs)
  • Same MySQL Server major version on all nodes
  • InnoDB as the default storage engine
  • Root or sudo access on all machines

Throughout, replace hostnames, passwords, and IPs with values appropriate for your environment.

Step 1: Install MySQL Server and MySQL Shell

Install MySQL Server and MySQL Shell on all three database nodes. Use your distribution packages or official MySQL repositories.

Example (Debian/Ubuntu-like, adjust as needed):

sudo apt-get update
sudo apt-get install mysql-server mysql-shell

Ensure the MySQL service is enabled and running:

sudo systemctl enable mysql
sudo systemctl start mysql

Step 2: Basic MySQL Configuration for InnoDB Cluster

On each node, configure MySQL with settings compatible with Group Replication and InnoDB Cluster. Edit my.cnf (location varies by distribution) on each node.

Warning: Changing these settings on an existing production server can be disruptive. Validate in a test environment first.

Essential InnoDB and Binary Log Settings

Apply on all nodes, adjusting server IDs and hostnames:

[mysqld]
# Unique per server
server_id = 1            # db1: 1, db2: 2, db3: 3

# Required for Group Replication
binlog_format = ROW
log_bin = mysql-bin
binlog_checksum = NONE
transaction_write_set_extraction = XXHASH64

# InnoDB durability (recommended for HA)
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# Networking and replication
gtid_mode = ON
enforce_gtid_consistency = ON

# Group Replication plugin
plugin_load_add = group_replication.so

Set server_id uniquely on each node. After editing, restart MySQL:

sudo systemctl restart mysql

Create a Cluster Admin User

On the first node (db1), connect as root and create a user for cluster management and replication:

mysql -u root -p
CREATE USER 'cluster_admin'@'%' IDENTIFIED BY 'StrongPass!123';
GRANT ALL PRIVILEGES ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

For stricter security, limit the host (e.g. 'cluster_admin'@'10.%') and refine privileges to what InnoDB Cluster requires, but for a lab environment the above is acceptable.

Step 3: Prepare Instances with MySQL Shell

MySQL Shell (mysqlsh) provides AdminAPI commands to configure and manage InnoDB Cluster.

On db1, start MySQL Shell and connect using the admin user:

mysqlsh cluster_admin@db1:3306

Switch to JavaScript mode (AdminAPI examples often use JS):

\js

Instance Configuration

Use AdminAPI to configure each instance for InnoDB Cluster. From db1:

var dba = require('dba');

// Configure db1
dba.configureInstance('cluster_admin@db1:3306', {
  interactive: false
});

// Configure db2
dba.configureInstance('cluster_admin@db2:3306', {
  interactive: false
});

// Configure db3
dba.configureInstance('cluster_admin@db3:3306', {
  interactive: false
});

These commands adjust instance settings (for example, group replication options) and may prompt you to restart MySQL. If you used interactive: false, review the output carefully and perform any required restarts.

Step 4: Create and Bootstrap the InnoDB Cluster

Initial Cluster Creation

On db1 in MySQL Shell (JS mode), create the cluster and bootstrap Group Replication:

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

Then add the first instance (db1 itself) as the seed member:

cluster.addInstance('cluster_admin@db1:3306', {
  recoveryMethod: 'clone'
});

If the data directory is not empty, recoveryMethod: 'clone' may overwrite data. Use this only on fresh instances or where data loss is acceptable.

Add the Remaining Instances

Still in MySQL Shell on db1:

cluster.addInstance('cluster_admin@db2:3306', {
  recoveryMethod: 'clone'
});

cluster.addInstance('cluster_admin@db3:3306', {
  recoveryMethod: 'clone'
});

AdminAPI will clone data from an existing member and configure Group Replication automatically.

Verify Cluster Status

Check the cluster status:

cluster.status();

You should see all three instances listed as ONLINE and the cluster mode as Single-Primary by default (one writable primary, others read-only).

Always verify cluster status after any topology change; do not assume Group Replication has converged until the AdminAPI reports all members as ONLINE.

Operational best practice

Step 5: Configure MySQL Router

MySQL Router provides a stable endpoint for applications, routing writes to the primary and reads to replicas (if configured).

Install MySQL Router on an application host or one of the DB nodes (for labs):

sudo apt-get install mysql-router

Bootstrap Router from the Cluster Metadata

On the Router host, run:

mysqlrouter --bootstrap cluster_admin@db1:3306 \
  --directory /var/lib/mysqlrouter \
  --user mysqlrouter

This connects to the InnoDB Cluster, reads its metadata, and creates a router configuration with separate endpoints for read-write and read-only traffic.

Start and enable the Router service (command names may vary by packaging):

sudo systemctl enable mysqlrouter
sudo systemctl start mysqlrouter

Connect Applications via Router

Typically, Router exposes ports like:

  • Read-write: 6446
  • Read-only: 6447

Applications can then connect using:

mysql -u app_user -p -h router_host -P 6446

This keeps application configuration stable across failovers.

Step 6: Testing Failover

Once the cluster is up and Router is configured, test failover behaviour in a non-production environment.

  1. Identify the primary:
cluster.status();
  1. Connect an application or MySQL client through Router to the read-write port and perform some writes.
  2. Simulate a primary failure by stopping MySQL on the primary node:
sudo systemctl stop mysql
  1. Observe cluster status again and confirm a new primary is elected.
  2. Verify that writes through Router still succeed.

Warning: Do not perform failover tests on a production cluster during business hours. Always coordinate and test on staging first.

Best Practices and Operational Tips

Instance Sizing and Layout

  • Use identical hardware and MySQL versions across nodes.
  • Keep latency between nodes as low as possible; Group Replication is sensitive to network delays.
  • Use three or more members for production; avoid even numbers when possible to simplify quorum behaviour.

Backups

  • Take logical or physical backups from a non-primary node to reduce impact on the primary.
  • Ensure GTID-consistent backups so they can be used to seed new members if needed.
  • Regularly test backup restore procedures independently of the cluster.

Monitoring

  • Monitor cluster.status() output regularly via automation.
  • Track replication lag, network latency, and InnoDB metrics (buffer pool hit rate, log waits).
  • Alert on member state changes (ONLINE, UNREACHABLE, OFFLINE).

Common Pitfalls

  • Mixed storage engines: InnoDB Cluster relies on InnoDB; avoid MyISAM or other non-transactional engines for replicated data.
  • Non-deterministic SQL: Statements must be deterministic with row-based replication; avoid relying on non-deterministic functions without care.
  • Split-brain risk: Ensure proper network design and avoid manual interventions that could create multiple primaries.
  • Ignoring error logs: Group Replication issues often surface in the MySQL error log; integrate them into your logging stack.

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

With these steps and practices, you can build a reliable MySQL InnoDB Cluster from scratch, then iterate on monitoring, backups, and automation to reach production-grade high availability.

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 *