How to Build and Manage MySQL InnoDB Replica Sets with MySQL Shell & Router

Introduction

The article explains how MySQL Shell can be used to manage MySQL InnoDB Cluster and, starting with MySQL 8.0.19, the new ReplicaSet feature that simplifies master‑slave replication management.

1. Environment preparation

Three MySQL instances are defined:

Host A – 192.168.2.171:4010

Host B – 192.168.2.171:4011

Host C – 192.168.2.171:4012

Each instance receives a minimal my.cnf configuration that enables GTID, binary logging, and sets the server‑id.

# my.cnf
server-id = 4010
log-bin=ytt
enforce_gtid_consistency=on
gtid_mode=on
log_slave_updates=on
master-info-repository=table
relay-log-info-repository=table
slave_parallel_type=logical
slave_parallel_workers=4

All instances are restarted with systemctl restart mysql@{4010,4011,4012}, and a replication user is created on the primary.

2. New ReplicaSet feature (MySQL 8.0.19)

2.1 Create a ReplicaSet

Connect to the X protocol port (40100) with MySQL Shell and run:

var c1 = dba.createReplicaSet('ytt_replicaset');

The shell validates the primary instance (A) and creates the ReplicaSet metadata.

2.2 Check status

c1.status();

The JSON output shows the ReplicaSet name, primary instance, status, and topology of all members.

2.3 Add replicas

Instances B and C are added with:

c1.addInstance('ytt@ytt-pc:4011');
c1.addInstance('ytt@ytt-pc:4012');

The shell performs validation, clones data (if needed), and updates the topology. After the operations the status command shows A as primary and B, C as secondaries.

2.4 Configure MySQL Router

MySQL Router is bootstrapped to provide read/write splitting and a virtual port:

mysqlrouter --bootstrap ytt@ytt-pc:4010 --user=mysqlrouter --force-password-validation --report-host ytt-pc

The generated /etc/mysqlrouter/mysqlrouter.conf is used to start the router, exposing classic protocol ports 6446 (RW) and 6447 (RO) and X protocol ports 64460 (RW) and 64470 (RO).

2.5 Test the routing

Connecting to the RW port (6446) always reaches instance 4010, while the RO port (6447) load‑balances between 4011 and 4012:

mysql -uytt -p -hytt-pc -P6446 -e "select @@port" -ss   # returns 4010
mysql -uytt -p -hytt-pc -P6447 -e "select @@port" -ss   # returns 4011 or 4012

3. Scenario experiments

3.1 Promote a secondary to primary

Promote instance 4012: c1.setPrimaryInstance('ytt@ytt-pc:4012'); The shell reconfigures replication so that 4012 becomes PRIMARY and the former primary (4010) becomes a secondary.

3.2 Remove and re‑add a node

Instance 4010 is removed and later added back:

c1.removeInstance('ytt@ytt-pc:4010');
c1.addInstance('ytt@ytt-pc:4010');

The removal and addition are reflected in the ReplicaSet status.

Conclusion

The MySQL Shell ReplicaSet commands together with MySQL Router provide a much simpler workflow for setting up, managing, and testing MySQL master‑slave (now called primary‑secondary) replication compared with traditional manual configuration.