MySQL InnoDB Cluster: High Availability Setup Guide

Imagine your e-commerce platform is experiencing a surge in traffic during a flash sale. Suddenly, your primary database server crashes. Without high availability, your site goes down, orders are lost, and customers are furious. This is where MySQL InnoDB Cluster comes to the rescue. It's a built-in solution that provides automatic failover, ensuring your database remains accessible even when individual nodes fail. InnoDB Cluster combines Group Replication for data consistency, MySQL Shell for management, and MySQL Router for client routing. In this tutorial, you'll learn how to set up a three-node InnoDB Cluster from scratch, configure MySQL Router, and handle real-world scenarios like node failures and recovery. By the end, you'll have a production-ready high-availability setup that keeps your applications running smoothly.

Prerequisites and Environment Setup

Before setting up InnoDB Cluster, ensure you have three MySQL 8.0+ instances (physical or virtual machines) with clean installations. Each instance should have a unique server_id and be able to communicate over ports 3306 (MySQL) and 33061 (Group Replication). Install MySQL Shell on your admin machine. For this tutorial, we'll use three nodes: node1 (192.168.1.10), node2 (192.168.1.11), node3 (192.168.1.12). Configure each MySQL instance with the following minimal settings in my.cnf:

[mysqld] server_id=1 # unique per node gtid_mode=ON enforce_gtid_consistency=ON binlog_format=ROW log_bin=mysql-bin log_slave_updates=ON relay_log=relay-log

Also ensure that group_replication is enabled:

group_replication=ON group_replication_start_on_boot=OFF group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee' group_replication_local_address='192.168.1.10:33061' group_replication_group_seeds='192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061'

Restart MySQL after changes.

# On node1 (192.168.1.10)
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_format=ROW
log_bin=mysql-bin
log_slave_updates=ON
relay_log=relay-log
group_replication=ON
group_replication_start_on_boot=OFF
group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
group_replication_local_address='192.168.1.10:33061'
group_replication_group_seeds='192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061'

Creating the InnoDB Cluster

Once the instances are configured, use MySQL Shell to create the cluster. Connect to the first node (node1) using MySQL Shell:

mysqlsh --uri root@192.168.1.10:3306

Then, create the cluster:

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

This command configures Group Replication on node1 and bootstraps the cluster. After creation, check the status:

cluster.status();

You should see node1 as the PRIMARY (read-write) and the cluster is ONLINE. Now add the other nodes:

cluster.addInstance('root@192.168.1.11:3306'); cluster.addInstance('root@192.168.1.12:3306');

Each addition will prompt for the root password and then synchronize the data from the primary. After adding all nodes, the cluster.status() should show all three nodes as ONLINE with one PRIMARY and two SECONDARIES.

# Connect to node1
mysqlsh --uri root@192.168.1.10:3306

# Inside MySQL Shell
var cluster = dba.createCluster('myCluster');
cluster.status();

# Add node2 and node3
cluster.addInstance('root@192.168.1.11:3306');
cluster.addInstance('root@192.168.1.12:3306');
cluster.status();

Configuring MySQL Router

MySQL Router provides transparent client-side routing. Install MySQL Router on a separate machine (or on the application server). Configure it to connect to the cluster:

mysqlrouter --bootstrap root@192.168.1.10:3306 --user=mysqlrouter

This command discovers the cluster and creates configuration files. The default configuration creates two ports: 6446 (read-write, routed to primary) and 6447 (read-only, load-balanced across secondaries). Start the router:

systemctl start mysqlrouter

Now applications can connect to port 6446 for writes and 6447 for reads. If the primary fails, Router automatically routes to the new primary. Test the connection:

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

You can also verify routing with:

mysqlrouter --list-routes

# Install MySQL Router (example on Ubuntu)
sudo apt-get install mysql-router

# Bootstrap the router
sudo mysqlrouter --bootstrap root@192.168.1.10:3306 --user=mysqlrouter

# Start the router
sudo systemctl start mysqlrouter
sudo systemctl enable mysqlrouter

# Test connection
mysql -h 127.0.0.1 -P 6446 -u appuser -p

Testing Failover and Recovery

Simulate a primary failure to verify automatic failover. First, identify the primary:

mysqlsh -- cluster.status()

Then, stop MySQL on the primary (node1):

sudo systemctl stop mysql

After a few seconds, check cluster status again. You should see that one of the secondaries (e.g., node2) has become the new primary. The cluster status will show 'OK' with a new primary. Applications connected via Router will experience a brief interruption (usually a few seconds) but will automatically reconnect. To recover the failed node, start MySQL on node1 and rejoin it to the cluster:

sudo systemctl start mysql mysqlsh -- cluster.rejoinInstance('root@192.168.1.10:3306')

After rejoining, node1 will become a secondary. Verify with cluster.status().

# On node1 (primary)
sudo systemctl stop mysql

# On admin machine, check cluster status
mysqlsh --uri root@192.168.1.11:3306 -e "cluster = dba.getCluster(); print(cluster.status())"

# Recover node1
sudo systemctl start mysql
mysqlsh --uri root@192.168.1.11:3306 -e "cluster = dba.getCluster(); cluster.rejoinInstance('root@192.168.1.10:3306')"

Monitoring and Maintenance

Monitor cluster health using MySQL Shell and performance_schema. Key commands:

• cluster.status(): Overall cluster health.
• cluster.describe(): Configuration details.
• SELECT * FROM performance_schema.replication_group_members: Member states.
• SELECT * FROM performance_schema.replication_group_member_stats: Transaction stats.

Set up alerts for member state changes and replication lag. Use MySQL Enterprise Monitor or open-source tools like Prometheus with mysqld_exporter. Regular maintenance includes: - Upgrading MySQL versions: Use cluster rolling upgrade procedure. - Adding/removing nodes: Use cluster.addInstance() and cluster.removeInstance(). - Backup: Use mysqldump or MySQL Enterprise Backup. Remember that backups should be taken from a secondary to avoid load on primary.

-- Check member states
SELECT * FROM performance_schema.replication_group_members;

-- Check transaction statistics
SELECT * FROM performance_schema.replication_group_member_stats\G

-- Check replication lag
SELECT MEMBER_ID, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS lag
FROM performance_schema.replication_group_member_stats;

Scaling and Advanced Configuration

InnoDB Cluster supports scaling out by adding more secondary nodes for read scalability. However, adding too many nodes can increase replication lag. For write scaling, consider sharding with MySQL Cluster (NDB). Advanced configuration options include: - group_replication_flow_control_mode: Controls how aggressively to throttle writes to prevent lag. Set to 'QUOTA' for strict control. - group_replication_member_expel_timeout: Timeout for expelling unresponsive members. Increase to 30-60 seconds in production. - group_replication_autorejoin_tries: Number of automatic rejoin attempts.

You can also configure multi-primary mode (all nodes writable) but it requires conflict detection and is not recommended for most applications. To enable multi-primary:

var cluster = dba.createCluster('myCluster', {multiPrimary: true});

But be aware of potential conflicts and performance overhead.

-- Set flow control mode
SET GLOBAL group_replication_flow_control_mode = 'QUOTA';

-- Increase expel timeout
SET GLOBAL group_replication_member_expel_timeout = 30;

-- Enable auto-rejoin (up to 3 attempts)
SET GLOBAL group_replication_autorejoin_tries = 3;

-- Check current settings
SHOW VARIABLES LIKE 'group_replication%';