MySQL InnoDB Cluster Setup Guide

Overview

This guide walks you through setting up a 3-node MySQL InnoDB Cluster using Docker Compose, complete with high availability and load balancing options using either MySQL Router or ProxySQL.

Architecture

• 3 MySQL Nodes: Primary cluster nodes for high availability
• Network: All nodes communicate via mysql-network
• Proxy Options: Choose between MySQL Router or ProxySQL for load balancing
• Admin User: clusteradmin with full privileges for cluster management

Prerequisites

• Docker and Docker Compose installed
• Basic understanding of MySQL and clustering concepts
• Network connectivity between all nodes

Step 1: Initial Container Setup

Docker Compose Configuration

Your docker-compose.yml should include 3 MySQL nodes, all connected to the mysql-network.

Startup Script

Each MySQL node runs the following initialization script:

CREATE USER 'clusteradmin'@'%' IDENTIFIED BY 'cladmin';
GRANT ALL privileges ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION;
RESET MASTER;

This script:

• Creates the cluster administrator user
• Grants full privileges needed for cluster operations
• Resets the binary log to ensure clean cluster initialization

Step 2: Start the Containers

docker-compose up -d

Wait for all 3 MySQL containers to be fully initialized and running.

Step 3: Cluster Configuration

Access MySQL Shell

Connect to the first node using MySQL Shell:

docker exec -it <mysql-node-1> mysqlsh

Configure Each Node

For each of the 3 nodes, perform the following steps:

1. Check Instance Configuration

dba.checkInstanceConfiguration('clusteradmin@<node-hostname>:3306')

This command verifies that the MySQL instance is properly configured for InnoDB Cluster.

2. Configure Instance

dba.configureInstance('clusteradmin@<node-hostname>:3306')

This automatically configures the instance with the required settings for clustering. You may need to restart the MySQL instance if prompted.

Repeat for all 3 nodes:

• clusteradmin@mysql-node-1:3306
• clusteradmin@mysql-node-2:3306
• clusteradmin@mysql-node-3:3306

Step 4: Create the Cluster

Initialize the Cluster

On the first node, create the cluster:

var cluster = dba.createCluster('myCluster')

Add Additional Nodes

Add the remaining nodes to the cluster:

cluster.addInstance('clusteradmin@mysql-node-2:3306')
cluster.addInstance('clusteradmin@mysql-node-3:3306')

Verify Cluster Status

cluster.status()

This should show all 3 nodes as part of the cluster with one PRIMARY and two SECONDARY nodes.

Multi-Primary Mode (Optional)

By default, InnoDB Cluster operates in single-primary mode. You can optionally configure it for multi-primary mode where all nodes accept writes:

Switch to Multi-Primary Mode

cluster.switchToMultiPrimaryMode()

Create Cluster in Multi-Primary Mode

Alternatively, create the cluster directly in multi-primary mode:

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

Switch Back to Single-Primary Mode

cluster.switchToSinglePrimaryMode()

Verify Cluster Mode

cluster.status()

The output will show "topologyMode": "Multi-Primary" or "topologyMode": "Single-Primary"

Important Considerations for Multi-Primary Mode:

• All nodes can accept read and write operations
• Requires careful application design to avoid conflicts
• May have different performance characteristics
• Ensure your application can handle potential write conflicts

Step 5: Handling Cluster Failures

Complete Outage Recovery

If all nodes go down and the cluster becomes unavailable:

dba.rebootClusterFromCompleteOutage('myCluster')

This command:

• Analyzes the cluster state
• Determines which node has the most recent data
• Restarts the cluster from that node
• Rejoins other nodes automatically

Individual Node Recovery

If a single node fails and needs to rejoin:

cluster.rejoinInstance('clusteradmin@<failed-node>:3306')

Step 6: Proxy Configuration

Choose between MySQL Router or ProxySQL for load balancing and high availability.

Option A: MySQL Router

Setup

1. Navigate to the mysql-router folder
2. Build and start the router container using the provided Dockerfile:

cd mysql-router
docker build -t mysql-router .
docker run -d --name mysql-router --network <network-name> mysql-router

Option B: ProxySQL

Setup

1. Navigate to the proxysql folder
2. Build and start the ProxySQL container using the provided Dockerfile:

cd proxysql
docker build -t proxysql .
docker run -d --name proxysql --network <network-name> proxysql

Maintenance Commands

Check Cluster Status

var cluster = dba.getCluster()
cluster.status()

Force Cluster Quorum

If you lose quorum (majority of nodes):

cluster.forceQuorumUsingPartitionOf('clusteradmin@<surviving-node>:3306')

Remove Failed Node

cluster.removeInstance('clusteradmin@<failed-node>:3306', {force: true})

Dissolve Cluster

To completely remove the cluster configuration:

cluster.dissolve({force: true})

Connection Details

Direct Node Connections

• Node 1: mysql-node-1:3306
• Node 2: mysql-node-2:3306
• Node 3: mysql-node-3:3306

Proxy Connections

• MySQL Router: Usually exposes ports 6446 (RW) and 6447 (RO)
• ProxySQL: Usually exposes port 6033

Credentials

• Username: clusteradmin
• Password: cladmin

Best Practices

1. Monitoring: Regularly check cluster status
2. Backups: Implement regular backup strategies
3. Network: Ensure stable network connectivity between nodes
4. Resources: Monitor CPU, memory, and disk usage
5. Logs: Check MySQL error logs for any issues
6. Testing: Regularly test failover scenarios

Troubleshooting

Common Issues

1. Network connectivity: Ensure all nodes can communicate
2. Version compatibility: Use the same MySQL version across all nodes
3. Resource constraints: Ensure adequate memory and disk space
4. Configuration drift: Verify all nodes have consistent configuration

Useful Commands

// Check instance readiness
dba.checkInstanceConfiguration('clusteradmin@<node>:3306')

// Get cluster object
var cluster = dba.getCluster()

// Detailed cluster status
cluster.status({extended: true})

// Check routing options
cluster.listRouters()

This setup provides a robust, highly available MySQL cluster suitable for production workloads with automatic failover and load balancing capabilities.