Prerequisites

3 Virtual Machines or servers (Using Ubuntu 24.04 LTS here).
Ensure mysql-server and mysql-shell are installed. (If not installed, use this command: apt install mysql-server mysql-shell -y).
These three servers must have network connectivity with each other.
Edit /etc/mysql/mysql.conf.d/mysqld.cnf and change bind-address = 127.0.0.1 to bind-address = 0.0.0.0. Restart MySQL after making this change.

Getting Started 🚀

Update the `/etc/hosts` file

Run on all 3 servers

127.0.0.1 localhost

192.168.100.140 db-node-01
192.168.100.141 db-node-02
192.168.100.142 db-node-03

Update hostnames

Run on all 3 servers respectively

# Run on server 01
hostnamectl set-hostname db-node-01
exec bash

# Run on server 02
hostnamectl set-hostname db-node-02
exec bash

# Run on server 03
hostnamectl set-hostname db-node-03
exec bash

Configure the MySQL `root` user

Run on all 3 servers

-- Update root password
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Here is your password!';
-- Allow connecting to the MySQL instance via TCP/IP
UPDATE mysql.user SET Host='%' WHERE User='root';
-- Grant permissions
GRANT CLONE_ADMIN, CONNECTION_ADMIN, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'root'@'%' WITH GRANT OPTION;
-- Refresh privileges
FLUSH PRIVILEGES;

Create a cluster admin user

Run on all 3 servers

# Enter the MySQL shell console (Python mode), and input your password
mysqlsh -u root -p

# Run on server 01
dba.configure_instance('root@db-node-01:3306', {'clusterAdmin': 'clusteradmin', 'clusterAdminPassword': 'Here is your password!'})

# Do you want to perform the required configuration changes? [y/n]: y
# Do you want to restart the instance after configuring it? [y/n]: y

# Run on server 02
dba.configure_instance('root@db-node-02:3306', {'clusterAdmin': 'clusteradmin', 'clusterAdminPassword': 'Here is your password!'})

# Do you want to perform the required configuration changes? [y/n]: y
# Do you want to restart the instance after configuring it? [y/n]: y

# Run on server 03
dba.configure_instance('root@db-node-03:3306', {'clusterAdmin': 'clusteradmin', 'clusterAdminPassword': 'Here is your password!'})

# Do you want to perform the required configuration changes? [y/n]: y
# Do you want to restart the instance after configuring it? [y/n]: y

Create the cluster

Run on Server 1

cluster = dba.create_cluster('db_cluster_01')

Add nodes to the cluster

Run on Server 1

cluster.add_instance('clusteradmin@db-node-02:3306')

# Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): c

cluster.add_instance('clusteradmin@db-node-03:3306')

# Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): c

Check cluster status

Run on Server 1

cluster.status()

For example:

{
    "clusterName": "db_cluster_01", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db-node-01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "db-node-01:3306": {
                "address": "db-node-01:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.46"
            }, 
            "db-node-02:3306": {
                "address": "db-node-02:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.46"
            }, 
            "db-node-03:3306": {
                "address": "db-node-03:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.46"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db-node-01:3306"
}

Configure MySQL Router & Connect Applications

Run on all 3 app servers !!! Not the Database servers !!!

Now that the database layer is highly available, you need a way for your applications to connect to it without hardcoding db-node-01’s IP address (otherwise, if Node 01 crashes, your app goes down with it).

The official solution for this is MySQL Router.

You install MySQL Router on your application servers. It acts as a lightweight proxy. Your application connects to the Router on localhost, and the Router automatically forwards the traffic to whichever database node is currently the Primary.

Prerequisites

3 Virtual Machines or servers (Using Ubuntu 24.04 LTS here).
These servers must have network connectivity to the database nodes.

Install the software

apt install mysql-router mysql-client -y

Ensure Linux user and directories exist

(Note: Installing via apt usually creates these automatically, but you can run these commands to be certain).

useradd -r -s /bin/false mysqlrouter

mkdir -p /etc/mysqlrouter
mkdir -p /var/log/mysqlrouter

chown mysqlrouter:mysqlrouter /etc/mysqlrouter
chown mysqlrouter:mysqlrouter /var/log/mysqlrouter

Edit `/etc/hosts`

# ... Other
192.168.100.140 db-node-01
192.168.100.141 db-node-02
192.168.100.142 db-node-03
# ... Other

Initialize the configuration

# The --user=mysqlrouter flag ensures the service runs under the correct, secure Linux system account instead of root.
mysqlrouter --bootstrap clusteradmin@192.168.100.140:3306 --user=mysqlrouter

Create the `systemd` service file

(Note: If apt already created /usr/lib/systemd/system/mysqlrouter.service, you can skip this step).

tee /etc/systemd/system/mysqlrouter.service > /dev/null << 'EOF'
[Unit]
Description=MySQL Router
After=network.target

[Service]
Type=simple
User=mysqlrouter
Group=mysqlrouter
ExecStart=/usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

Start MySQL Router

systemctl daemon-reload
systemctl start mysqlrouter.service
systemctl enable mysqlrouter.service

Test connection to MySQL

# Check that the port is listening
lsof -i:6446
# Connect to MySQL via the Router
mysql -uroot -p -h 127.0.0.1 -P 6446

Check clients via MySQL Shell

root@db-node-01:~# mysqlsh clusteradmin@192.168.100.140:3306 -p
Please provide the password for 'clusteradmin@192.168.100.140:3306': **********
Save password for 'clusteradmin@192.168.100.140:3306'? [Y]es/[N]o/Ne[v]er (default No): 
MySQL Shell 8.0.36

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'clusteradmin@192.168.100.140:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 136
Server version: 8.0.46-0ubuntu0.24.04.3 (Ubuntu)
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.100.140:3306 ssl  Py > cluster = dba.get_cluster()
 MySQL  192.168.100.140:3306 ssl  Py > cluster.list_routers()
{
    "clusterName": "db_cluster_01", 
    "routers": {
        "db-node-01.lan::system": {
            "hostname": "db-node-01.lan", 
            "lastCheckIn": "2026-08-07 03:15:03", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwXPort": "6448", 
            "version": "8.0.46"
        }, 
        "server-node-01::system": {
            "hostname": "server-node-01", 
            "lastCheckIn": "2026-08-07 03:12:12", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwXPort": "6448", 
            "version": "8.0.46"
        }, 
        "server-node-02::system": {
            "hostname": "server-node-02", 
            "lastCheckIn": "2026-08-07 03:15:05", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwXPort": "6448", 
            "version": "8.0.46"
        }, 
        "server-node-03::system": {
            "hostname": "server-node-03", 
            "lastCheckIn": "2026-08-07 03:15:06", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwXPort": "6448", 
            "version": "8.0.46"
        }
    }
}
 MySQL  192.168.100.140:3306 ssl  Py >

Export & Import Data

If you want to export or import data, you can completely use the standard mysqldump command, but make sure the destination host and port point to MySQL Router.

For example, to export (using the read-only port 6447):

mysqldump -h 127.0.0.1 -P 6447 -u root -p your_database_name > backup.sql

To import (using the read-write port 6446):

mysql -h 127.0.0.1 -P 6446 -u root -p your_database_name < backup.sql

How to Configure a MySQL Cluster

Prerequisites

Getting Started 🚀

Update the `/etc/hosts` file

Update hostnames

Configure the MySQL `root` user

Create a cluster admin user

Create the cluster

Add nodes to the cluster

Check cluster status

Configure MySQL Router & Connect Applications

Prerequisites

Install the software

Ensure Linux user and directories exist

Edit `/etc/hosts`

Initialize the configuration

Create the `systemd` service file

Start MySQL Router

Test connection to MySQL

Check clients via MySQL Shell

Export & Import Data

评论

Prerequisites#

Getting Started 🚀#

Update the /etc/hosts file#

Update hostnames#

Configure the MySQL root user#

Create a cluster admin user#

Create the cluster#

Add nodes to the cluster#

Check cluster status#

Configure MySQL Router & Connect Applications#

Prerequisites#

Install the software#

Ensure Linux user and directories exist#

Edit /etc/hosts#

Initialize the configuration#

Create the systemd service file#

Start MySQL Router#

Test connection to MySQL#

Check clients via MySQL Shell#

Export & Import Data#

评论

Prerequisites

Getting Started 🚀

Update the `/etc/hosts` file

Update hostnames

Configure the MySQL `root` user

Create a cluster admin user

Create the cluster

Add nodes to the cluster

Check cluster status

Configure MySQL Router & Connect Applications

Prerequisites

Install the software

Ensure Linux user and directories exist

Edit `/etc/hosts`

Initialize the configuration

Create the `systemd` service file

Start MySQL Router

Test connection to MySQL

Check clients via MySQL Shell

Export & Import Data