Creating a Highly Available MySQL InnoDB Cluster

Setting up a highly available MySQL InnoDB Cluster with MySQL Router, group replication, automatic failover, and point-in-time recovery can be complex, but I’ll break everything down into step-by-step instructions with explanations, suitable for beginners. This guide summarizes my real-world setup journey for a production-ready MySQL cluster with a dedicated MySQL Router VM, backups, and failover handling.

🧱 MySQL InnoDB Cluster Architecture

1. 🧠 3 MySQL Nodes: 1 primary (writer) + 2 secondaries (readers)
2. 🔁 Group Replication for HA
3. 🚪 MySQL Router (deployed on a separate VM for better failover control)
4. 🛡️ Backups (dump + binary logs) for PITR

✅ Pre-requisites
Ubuntu VMs (each with at least 2 vCPUs and 4GB RAM recommended)
All VMs must be reachable via an internal network.
MySQL port (3306) and Group Replication ports (33061, 33062) must be open.

I had created 3 VMs on Azure Cloud to create the MySQL Cluster architecture

✅ Step 1: Install MySQL on All Nodes

sudo apt update && sudo apt install mysql-server net-tools -y
wget https://repo.mysql.com//mysql-apt-config_0.8.34-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.34-1_all.deb
sudo apt update && sudo apt install mysql-shell -y

Check that MySQL is running and listening on 3306 on each node (especially node01):

sudo netstat -tulnp | grep 3306

#You should see something like:
**tcp   0  0 0.0.0.0:3306   0.0.0.0:*   LISTEN   1234/mysqld**

If it only shows 127.0.0.1:3306, then MySQL is **not listening on external IPs**.
👉 **To fix that:**
Edit your mysqld.cnf (location: /etc/mysql/mysql.conf.d/mysqld.cnf):

**bind-address = 0.0.0.0**

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Set bind-address to:
bind-address = 0.0.0.0

Then restart:

sudo systemctl restart mysql

⚙️ Step 2: Configure Each MySQL Node

Create a config file /etc/mysql/mysql.conf.d/group_replication.cnf on each node.
Example (for node01):

[mysqld]
# Server identity (must be unique for each node)
server-id=1  # node01 = 1, node02 = 2, node03 = 3 (change accordingly)

# Enable binary logging
log_bin = mysql-bin
binlog_format = ROW
binlog_checksum = NONE
transaction_write_set_extraction = XXHASH64

# Enable GTID
gtid_mode = ON
enforce_gtid_consistency = ON

# Group Replication settings
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=off
loose-group_replication_bootstrap_group=off
loose-group_replication_local_address="node01:33061"  # Change per node
loose-group_replication_group_seeds="node01:33061,node02:33061,node03:33061"
loose-group_replication_single_primary_mode=ON
loose-group_replication_enforce_update_everywhere_checks=OFF

# Networking
report_host=node01  # change for each node
bind-address=0.0.0.0
mysqlx-bind-address=0.0.0.0

# InnoDB settings
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Make sure each node has a unique server-id and correct report_host.

👤 Step 3: Create a Cluster Admin User

CREATE USER 'clusteradmin'@'%' IDENTIFIED BY 'StrongPass!123';
GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

🌐 Step 4: Configure InnoDB Cluster

On node01, use MySQL Shell:

mysqlsh --uri clusteradmin@localhost

Please enter clusteradmin password “StrongPass!123” once it pops up on your terminal

Then: Run the following command one by one on node01

dba.configureInstance('clusteradmin@node01')
dba.configureInstance('clusteradmin@node02')
dba.configureInstance('clusteradmin@node03')

After once you have completed above command, then run following command on node01

var cluster = dba.createCluster('wordpressCluster')
cluster.addInstance('clusteradmin@node02')
cluster.addInstance('clusteradmin@node03')

Finally, you can check the cluster status.

cluster.status()

✅ At this point, the cluster is formed, and MySQL group replication is working.
✅ Success! You now have a functional InnoDB Cluster 🎉

✅ Step 5: Setup mysqldump Cron Job for Daily Backups

Create a script /usr/local/bin/mysql_backup.sh:
You can mount a volume to this directory /mnt/mysql_backup. This way, you can make sure your backup will be safe if the server has any issues.

#!/bin/bash
DATE=$(date +%F_%H-%M)
mysqldump -u root --all-databases > /mnt/mysql_backup/db_backup_$DATE.sql

Make it executable:

chmod +x /usr/local/bin/mysql_backup.sh

Add a cron job:

crontab -e

0 2 * * * /usr/local/bin/mysql_backup.sh

This runs daily at 2 AM.

✅ Step 6: Backup Binary Logs Every Minute

Use mysqlbinlog to extract binlog content every minute:
Create /usr/local/bin/binlog_backup.sh:

#!/bin/bash
DATE=$(date +%F_%H-%M)
BINLOG_PATH="/mnt/mysql_binlog"
DEST_PATH="/mnt/mysql_backup/binlogs"
mkdir -p "$DEST_PATH"
cp $BINLOG_PATH/mysql-bin.* "$DEST_PATH/binlog_$DATE"

Add to crontab:

* * * * * /usr/local/bin/binlog_backup.sh

This ensures you can do point-in-time recovery using full dump + binlogs.

✅ Important Concepts to Know

🔁 MySQL Router – On a Dedicated VM
For better failover and load balancing, deploy MySQL Router separately:

sudo apt install mysql-router -y
mysqlrouter --bootstrap clusteradmin@node01:3306 --directory /etc/mysqlrouter
sudo systemctl start mysqlrouter

It ensures connections always route to the right node. no manual switching needed 🔄

🧪 Failover Test

Try stopping MySQL on node01:

sudo systemctl stop mysql

Once you stop MySQL services, to see the failover, you have to connect to the MySQL shell. For that, follow the commands below

mysqlsh --uri clusteradmin@localhost

var cluster = dba.getCluster('wordpressCluster')

cluster.status()

🔚 Wrapping Up

Setting up a MySQL InnoDB Cluster 🛠️ isn’t just about replication. It’s about building a resilient, production-ready database architecture with automatic failover, dedicated routing, and reliable backups. With 3 MySQL nodes, a separate MySQL Router VM, binary logs, and regular dumps, you’re all set for high availability and peace of mind. 🚀💾