Multi Node MySQL Cluster on Linux VPS: Setup Guide

This guide walks you through setting up a multi-node MySQL cluster on Linux VPS servers. You’ll end up with a highly available database that replicates data across multiple nodes, providing redundancy and improved read scalability. The setup uses MySQL Group Replication, which is built into MySQL 5.7 and later, making it a practical choice for VPS environments.

What You’ll Need

At least 3 Linux VPS servers (Ubuntu 20.04/22.04 or Debian 11/12 recommended) with static IPs.
Root or sudo access on each server.
MySQL 5.7+ or MySQL 8.0 installed on each server (MariaDB is not compatible with Group Replication).
Network connectivity between servers on ports 3306 (MySQL) and 33061 (group communication).
Basic familiarity with the Linux command line and MySQL.

Step-by-Step

Install MySQL on each node. On Ubuntu/Debian, run sudo apt update && sudo apt install mysql-server. Ensure the version is 5.7 or higher. After installation, secure the installation with sudo mysql_secure_installation.

Configure MySQL for Group Replication. Edit the MySQL configuration file (usually /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf) on each node. Add the following under the [mysqld] section, adjusting server-id and report_host per node:

[mysqld]
server-id=1  # unique per node (1, 2, 3...)
report_host=node1_ip  # replace with actual IP
bind-address=0.0.0.0
binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"  # generate a UUID
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="node_ip:33061"
loose-group_replication_group_seeds="node1_ip:33061,node2_ip:33061,node3_ip:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_single_primary_mode=ON
loose-group_replication_enforce_update_everywhere_checks=OFF

Replace node1_ip, etc., with actual IPs. Generate a UUID with uuidgen and use it for the group name.

Restart MySQL on each node. Run sudo systemctl restart mysql on all nodes to apply the configuration.

Create a replication user on the first node. Connect to MySQL: sudo mysql. Create a user that will be used for group replication:

CREATE USER 'repl'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Configure the first node to bootstrap the group. On the first node, in the MySQL client, run:
```
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
    
```
This starts the group with this node as the primary.

Add the second and third nodes. On each subsequent node, connect to MySQL and run:

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='strong_password' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

Repeat for all remaining nodes.

Verify the cluster. On any node, run SELECT * FROM performance_schema.replication_group_members; to see the status. All nodes should show as ONLINE. You can also check the primary node with SHOW STATUS LIKE 'group_replication_primary_member';.

Common Mistakes

Using MariaDB instead of MySQL. MariaDB does not support Group Replication. Ensure you have genuine MySQL installed.
Misconfigured server-id. Each node must have a unique server-id. Duplicate IDs cause the node to fail joining.
Incorrect UUID for group name. The group_replication_group_name must be a valid UUID. Using a random string will cause errors.
Firewall blocking ports. Ports 3306 and 33061 must be open between all nodes. Also, ensure the firewall allows traffic on the internal network.
Not setting bind-address to 0.0.0.0. If MySQL only listens on localhost, other nodes cannot connect.
Forgetting to set loose-group_replication_start_on_boot=OFF initially. If set to ON, nodes may try to start replication before the group is bootstrapped, causing errors.

Tips

Use a dedicated network. If your VPS provider offers a private network, use private IPs for group communication to reduce latency and improve security.
Test failover. After setup, stop the primary node and verify that another node takes over as primary automatically. This ensures your cluster is truly highly available.
Monitor the cluster. Set up monitoring for replication lag and member status. Use tools like MySQL Enterprise Monitor or open-source options like Prometheus with mysqld_exporter.
Backup regularly. Even with replication, take regular backups. Use mysqldump or physical backups to a separate location.
Keep the configuration consistent. Ensure all nodes have identical MySQL settings except for server-id and report_host.

Troubleshooting

Node doesn’t join the group. Check the MySQL error log (/var/log/mysql/error.log) for messages. Common issues include network connectivity, wrong credentials, or firewall rules. Verify that the CHANGE MASTER command was executed correctly.
Group replication starts but then stops. Run SHOW STATUS LIKE 'group_replication_%'; to see the state. If it shows ERROR, check the error log. Often this is due to a transaction that violates the group’s consistency rules (e.g., using MyISAM tables). Ensure all tables use InnoDB.
Can’t connect to MySQL from other nodes. Check that bind-address is set to 0.0.0.0 and that the firewall allows connections on port 3306. Test with telnet node_ip 3306 from another node.
Primary node change not happening. In single-primary mode, if the primary fails, a new primary is elected automatically. If not, ensure that all nodes are ONLINE and that the group has a majority. If only two nodes are left, the group may not have quorum.

Frequently Asked Questions

Can I use this setup with just 2 nodes?

Technically yes, but it’s not recommended. With 2 nodes, if one fails, the group loses quorum and stops accepting writes. You need at least 3 nodes for a fault-tolerant cluster.

What is the difference between Group Replication and standard replication?

Standard replication is asynchronous and has a single primary with read replicas. Group Replication is a multi-primary or single-primary group with automatic failover and consistency guarantees.

How do I add a new node to an existing cluster?

Install MySQL, configure it with the same settings (unique server-id), then run the CHANGE MASTER and START GROUP_REPLICATION commands as described in Step 6.

Can I use this cluster with a load balancer?

Yes, you can place a load balancer (like HAProxy or ProxySQL) in front of the cluster to distribute read/write traffic. In single-primary mode, only the primary handles writes.

What happens if a node loses network connectivity?

The node will be marked as UNREACHABLE and eventually as ERROR if it doesn’t recover. The cluster continues to operate as long as a majority of nodes are available.

Is this setup suitable for production?

Yes, MySQL Group Replication is production-ready. However, ensure you have proper monitoring, backups, and a tested failover procedure.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →