MySQL Router, Connection Routing, Read/Write Splitting, and Application Failover

Learning outcomes

An application that connects directly to db1:3306 still has a single-host dependency even if db2 and db3 can elect a new primary. MySQL Router solves a different layer of the HA problem: it watches InnoDB Cluster metadata and directs client connections to servers matching the route's role. It does not make a dead TCP session immortal, and it does not know whether an interrupted business operation is safe to retry.

Router is metadata-aware middleware, not a database

MySQL Router is a lightweight process between clients and MySQL servers. When bootstrapped against InnoDB Cluster, it stores metadata-cache configuration and learns which member is PRIMARY or SECONDARY. The application connects to Router rather than selecting a server address itself.

Bootstrap Router from the cluster

# Run with MySQL Router 8.4.10 installed locally.# The command prompts for the admin credential; do not place it on the command line.mysqlrouter --bootstrap icadmin@127.0.0.1:33151   --directory ./servicehub-router# Start using the generated configuration../servicehub-router/start.sh        # Linux/macOS bootstrap directory# On Windows use the generated start script/executable instructions shown by bootstrap.

Bootstrap creates Router metadata/configuration and reports the actual endpoints. In production, create a dedicated Router account through the supported tooling and protect the generated keyring/configuration directory. The cluster administrator is convenient for this disposable bootstrap exercise, not an application credential.

Prove which backend Router selected

-- Client command example:-- mysql -h 127.0.0.1 -P 6446 -u servicehub_app -pSELECT @@hostname AS backend_host,       @@port AS backend_port,       @@global.super_read_only AS super_read_only;CREATE DATABASE IF NOT EXISTS servicehub_router_lab;CREATE TABLE IF NOT EXISTS servicehub_router_lab.router_markers (  marker_id BIGINT AUTO_INCREMENT PRIMARY KEY,  marker_text VARCHAR(100) NOT NULL,  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;INSERT INTO servicehub_router_lab.router_markers(marker_text)VALUES ('before-primary-change');

The read/write route should land on the current primary. The hostname/port values shown here identify the backend; the client still believes it connected to Router. That distinction is what lets Router redirect a later new connection after topology changes.

Planned primary switch: new connections follow metadata

var cluster = dba.getCluster('ServiceHubHA')cluster.setPrimaryInstance('icadmin@127.0.0.1:33152', {  runningTransactionsTimeout:30})cluster.status({extended:1})

-- Close the old client and open a NEW client to 127.0.0.1:6446.SELECT @@hostname AS backend_host,       @@port AS backend_port,       @@global.super_read_only AS super_read_only;INSERT INTO servicehub_router_lab.router_markers(marker_text)VALUES ('after-primary-change');SELECT * FROM servicehub_router_lab.router_markers ORDER BY marker_id;

The application endpoint did not change, but a new Router connection can be directed to the new primary. This is routing continuity, not transaction replay.

Failure injection: kill the current primary while a client is connected

Open a session through port 6446, start a transaction, make a disposable change, but do not commit. Then stop the backend container that the session reports. The existing connection can break; the uncommitted transaction is not magically moved to another server.

START TRANSACTION;INSERT INTO servicehub_router_lab.router_markers(marker_text)VALUES ('in-flight-before-crash');SELECT CONNECTION_ID(), @@hostname, @@port;-- Do not COMMIT yet.

# Example only: stop whichever container is actually primary.docker stop servicehub-ha-db2# Observe election from MySQL Shell connected to a surviving member.cluster.status({extended:2})

When the client detects a broken connection, the application must decide whether the transaction committed. In this specific lab it was deliberately uncommitted, so reconnecting and retrying a transactionally idempotent operation is straightforward. In a real failure the connection may disappear during commit acknowledgment, which creates an ambiguous commit: the server may have committed even though the client did not receive success. Use idempotency keys/business identifiers and post-reconnect verification rather than blindly rerunning arbitrary writes.

Read-only routing and freshness contracts

A read-only Router endpoint can distribute reads to secondaries. That can reduce read load on the primary, but it changes the application's consistency contract: a recently committed transaction may not yet be visible on a secondary during recovery/catch-up or under heavy load. The application must know whether it needs read-after-write consistency, monotonic reads, or whether stale reporting data is acceptable.

-- mysql -h 127.0.0.1 -P 6447 -u servicehub_app -pSELECT @@hostname, @@port, @@global.super_read_only;SELECT COUNT(*) FROM servicehub_router_lab.router_markers;

Current Router read/write splitting

MySQL Router 8.4 supports a read/write-splitting route that classifies statements and can use one read-write and one read-only backend within a client session. The route requires classic protocol, metadata-cache destinations with PRIMARY_AND_SECONDARY, connection sharing, and access_mode=auto. It is more stateful than simply offering separate 6446/6447 ports, so verify the generated configuration and connector behavior.

-- Common current split-route port is 6450; confirm your Router config first.ROUTER SET access_mode='auto';ROUTER SET wait_for_my_writes=1;ROUTER SET wait_for_my_writes_timeout=2;INSERT INTO servicehub_router_lab.router_markers(marker_text)VALUES ('split-route-write');SELECT COUNT(*) FROM servicehub_router_lab.router_markers;

wait_for_my_writes=1 can make a read routed to a read-only destination wait for the session's last write to be applied, with timeout/fallback behavior. It is not a global promise that every read in every session is immediately consistent, and it does not remove the need to design transaction boundaries carefully.

Tempting but wrong: “Router makes failover transparent”

Connection pools make failover behavior visible later

Many applications do not open a new TCP connection for every request. A connection pool can hold Router sessions for minutes or hours. After a topology change, healthy pooled sessions may continue until Router/backend policy closes them, while broken sessions must be detected, discarded, and replaced. This means a Router acceptance test should use the same connector and pool settings as production, not only an interactive mysql client.

Test at least three states: an idle pooled connection during primary loss, an active read, and an in-flight write transaction. Record the driver's exception type, pool replacement behavior, connection-establishment latency, and whether session initialization SQL is replayed. These are application-level facts; Router documentation cannot predict them for every driver and pool configuration.

Session state is part of the failover contract

Applications often carry session variables, transaction isolation, temporary tables, user variables, prepared statements, or advisory locks. A new backend connection starts with server/session defaults unless the application or pool initialization re-establishes required state. Therefore “reconnected successfully” can still be functionally wrong if the new session silently has different SQL mode, time zone, transaction isolation, or application context.

For ServiceHub, a reconnect test should explicitly query the session assumptions the application depends on and compare them with the initial connection contract. Keep that initialization idempotent so it can run every time a pool creates a replacement connection.

Separate routing policy from consistency policy

Read-only routing is a topology decision; consistency is an application requirement. A dashboard query that tolerates a few seconds of staleness can use a secondary differently from a command workflow that writes a work order and immediately reads it back for confirmation. Router's read/write-splitting and wait_for_my_writes capabilities can help with some session-local read-after-write cases, but they do not remove the need to classify requests by consistency requirement.

Document routes in terms of contracts: “command transaction: read/write endpoint,” “eventually consistent reporting: read-only endpoint,” or “split route with wait-for-my-writes enabled and tested timeout/fallback semantics.” Avoid generic labels such as “reads go to replicas” without stating what stale data means to the user.

Production judgment

Use Router as part of an HA architecture, not as the whole architecture. Run enough Router instances to avoid making the middleware itself a single failure point, front them with an appropriate service/discovery/load-balancing mechanism if required, secure Router metadata credentials, and test connection storms after failover. Measure reconnect time and application recovery under your connector/pool configuration rather than publishing a universal “failover takes N seconds” claim.

Next, we put all layers under controlled failure: members, primary election, Router process, network partitions, and maintenance/upgrades. HA becomes real only when these tests produce deterministic safety and acceptance evidence.

Authoritative references

• MySQL Router 8.4 — Bootstrapping
• MySQL Router 8.4 — Read/Write Splitting
• Read/Write Splitting Configuration
• Setting up InnoDB Cluster and MySQL Router