Prerequisites
- 3 Virtual Machines or servers (Using Ubuntu 24.04 LTS here).
- Ensure
mysql-serverandmysql-shellare 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.cnfand changebind-address = 127.0.0.1tobind-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