How I Configure MySQL InnoDB Cluster from Scratch

2025-11-17 11:11:20 - No Comments

First we need to install some dependencies.

sudo apt update
sudo apt -y install wget lsb-release gnupg

Next we will download and configure repositories from mysql.

sudo apt-key adv --recv-keys --keyserver ha.pool.sks-keyservers.net 5072E1F5
wget https://repo.mysql.com/apt/ubuntu/pool/mysql-apt-config/m/mysql-apt-config/mysql-apt-config_0.8.36-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.36-1_all.deb

On the MySQL servers we will install the server application and shell.

sudo apt update
sudo apt -y install mysql-server mysql-shell

On the router machines we install router, client and shell instead.

sudo apt update
sudo apt -y install mysql-router-community mysql-client mysql-shell

Now that we have installed applications we can enter the shell with:

mysqlsh

First we configure all the machines so we can use them as cluster members. For this example we will create a new user called clusteradmin

\js
dba.configureInstance('root@localhost:3306')

Next we connect to the first server in our cluster, I call mine my1 and then we create a new cluster using a name, this name can later be used to get the cluster again to check status. We add the other members to the cluster in my case called my2 and my3.

\js
shell.connect('clusteradmin@my1.ea.org:3306');
cluster = dba.createCluster('my_innodb_cluster');
cluster.addInstance('clusteradmin@my2.ea.org:3306');
cluster.addInstance('clusteradmin@my3.ea.org:3306');

cluster.status()

Now that we have configured the database servers we will configure the routers. Below I will create a new user and directory for the router, as well as bootstrapping it against one of the cluster members.

sudo -H useradd --shell /bin/bash --system --home-dir "/var/share/mysqlrouter" --comment 'MySQL router' mysqlrouter

sudo mkdir -p /var/share/myrouter
sudo chown -R mysqlrouter:mysqlrouter /var/share/myrouter

sudo mysqlrouter --bootstrap clusteradmin@my1.ea.org:3306 --directory /var/share/myrouter --user=mysqlrouter

Setup service

Now that we have a configuration we can configuration service. Open a new service file

sudo rm /etc/init.d/mysqlrouter
sudo vi /etc/systemd/system/mysqlrouter.service

The file below is the minimal service configuration we need for our mysqlrouter.

[Unit]
Description=MySQL router service
Wants=network-online.target

[Install]
WantedBy=multi-user.target

[Service]
RuntimeDirectory=mysqlrouter
Type=simple
User=mysqlrouter
Group=mysqlrouter
TimeoutStartSec=0
Restart=always
RestartSec=10s
PIDFile=/run/mysqlrouter/mysqlrouter.pid
ExecStart=mysqlrouter -c /var/share/myrouter/mysqlrouter.conf

Last but not least, start and enable the service so it will run on reboot.

sudo systemctl daemon-reload
sudo systemctl restart mysqlrouter
sudo systemctl enable mysqlrouter
sudo systemctl status mysqlrouter

If we want to check the status we can connect to a member again, get the cluster by name and ask for status update.

\js
shell.connect('clusteradmin@my1.ea.org:3306');
cluster = dba.getCluster('my_innodb_cluster');
cluster.status()

Below I have some code to test the cluster using sysbench.

First we create a database.

CREATE DATABASE sbtest;
CREATE USER sbtest@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@'%';

Then we install the program and run a bunch of test scripts against different ports.

sudo apt -y install sysbench mysql-client

sysbench /usr/share/sysbench/oltp_common.lua --db-driver=mysql --mysql-user=sbtest --mysql-db=sbtest --mysql-password=password --mysql-port=6450 --mysql-host=r1.ea.org --tables=20 --table-size=100000 prepare

sysbench /usr/share/sysbench/oltp_read_write.lua --report-interval=2 --threads=1 --time=300 --db-driver=mysql --mysql-user=sbtest --mysql-db=sbtest --mysql-password=password --mysql-port=6446 --mysql-host=r1.ea.org --tables=20 --table-size=100000 run

sysbench /usr/share/sysbench/oltp_read_only.lua --report-interval=2 --threads=1 --time=300 --db-driver=mysql --mysql-user=sbtest --mysql-db=sbtest --mysql-password=password --mysql-port=6447 --mysql-host=r1.ea.org --tables=20 --table-size=100000 run

sysbench /usr/share/sysbench/oltp_common.lua --db-driver=mysql --mysql-user=sbtest --mysql-db=sbtest --mysql-password=password --mysql-port=3306 --mysql-host=my1.ea.org --tables=20 --table-size=100000 prepare

sysbench /usr/share/sysbench/oltp_read_write.lua --report-interval=2 --threads=1 --time=600 --db-driver=mysql --mysql-user=sbtest --mysql-db=sbtest --mysql-password=password --mysql-port=3306 --mysql-host=my1.ea.org --tables=20 --table-size=100000 run

sysbench /usr/share/sysbench/oltp_read_only.lua --report-interval=2 --threads=1 --time=600 --db-driver=mysql --mysql-user=sbtest --mysql-db=sbtest --mysql-password=password --mysql-port=3306 --mysql-host=my2.ea.org --tables=20 --table-size=100000 run

We can check the load using top on our database server machines.

top -H -p $(pidof mysqld)