Guide to Deploying and Managing MySQL InnoDB ReplicaSet with MySQL Shell and Router

MySQL InnoDB ReplicaSet, introduced in MySQL 8.0.19, is an GTID‑based asynchronous replication feature that defines a Primary (master) and one or more Secondary (slave) instances and is managed through the MySQL Shell AdminAPI.

Using MySQL Shell, three sandbox instances are deployed on ports 3306, 3307, and 3308:

# mysqlsh

MySQL JS> dba.deploySandboxInstance(3306)

MySQL JS> dba.deploySandboxInstance(3307)

MySQL JS> dba.deploySandboxInstance(3308)

After setting a root password, the instances are created under $HOME/mysql-sandboxes/port .

A cluster admin account repl is created and the replica set named renzy is initialized by connecting to the first instance:

MySQL JS> \connect root@localhost:3306

MySQL JS> var rs = dba.createReplicaSet("renzy")

The initial status shows instance 3306 as PRIMARY.

MySQL JS> rs.status()

Secondary instances 3307 and 3308 are added using full‑clone synchronization:

MySQL JS> rs.addInstance('localhost:3307')

MySQL JS> rs.addInstance('localhost:3308')

Both new nodes become SECONDARY and replicate from the Primary.

Manual primary promotion is performed with:

MySQL JS> rs.setPrimaryInstance('127.0.0.1:3308')

After promotion, instance 3308 becomes PRIMARY and the former primary (3306) is demoted to SECONDARY.

When the PRIMARY (3308) process is killed, the replica set does not fail over automatically; a manual promotion is required:

# ps -ef | grep 3308

# kill -9

MySQL JS> rs.forcePrimaryInstance("127.0.0.1:3306")

The set then reports an AVAIABLE_PARTIAL state because one node is unavailable.

MySQL Router can bootstrap the replica set and provide a read/write endpoint that always points to the current PRIMARY:

mysqlrouter --user=root --bootstrap root@localhost:3308

Clients connect to the Router’s R/W port (e.g., 6446) and automatically reach the active PRIMARY, even after manual failover.

In conclusion, MySQL Router works well with InnoDB ReplicaSet for automatic primary routing, but the replica set lacks automatic failover, supports only asynchronous GTID replication, and is not yet ready for production without manual intervention.