Load Balancing in MySQL: Read and Write Traffic Optimization Using MySQL Router

[logger]
level = INFO

[DEFAULT]
logging_folder=/var/log/mysqlrouter

[http_server]
port=8081
bind_address=127.0.0.1

[routing:primary_write]
bind_address = 0.0.0.0
bind_port = 6446 
mode = read-write 
destinations = 127.0.0.1:3313 
routing_strategy = first-available

[routing:replica_read]
bind_address = 127.0.0.1
bind_port = 6447
mode = read-only
destinations = 127.0.0.1:3306, 127.0.0.1:3000
routing_strategy = round-robin

MySQL Router supports several routing strategies under the routing_strategy configuration option. These define how MySQL Router chooses a backend server (MySQL instance) from a list of candidates when forwarding client connections.

• First-available: The router sends all traffic to a single server. If that server goes down, it switches to the next available one. When the original server comes back online, the router returns traffic to it.
• Next-available: Similar to first-available, The router directs traffic to a single server. If that server fails, it switches to the next available one. Even after the original server is back online, traffic continues to flow to the new server.
• Round-robin: The router distributes traffic across all accessible servers. If one server goes down, the traffic is automatically rerouted to the remaining available servers.

MySQL 8.2+ Native Read/Write Splitting

MySQL 8.2, released in October 2023, introduced native transparent read/write splitting directly into MySQL client connectors — no proxy layer required.

Unlike MySQL Router where your application must explicitly connect to separate ports (6446 for writes, 6447 for reads), MySQL 8.2 handles the split automatically based on the query type. A single connection endpoint intelligently routes SELECT statements to replicas and write operations to the primary, all transparently.

How it works:

• SELECT queries → automatically sent to a read replica
• INSERT, UPDATE, DELETE → automatically sent to primary
• Transactions → always pinned to primary for consistency
• Explicit overrides available via session hints when needed

Enable it in your connection string:

mysqlx://root:password@127.0.0.1:33060?connect-attributes=_read_from_replicas=1

_read_from_replicas=1 tells the connector to automatically route SELECT queries to replicas and writes to the primary.

For production environments running mixed MySQL versions or requiring advanced failover control, MySQL Router remains the recommended approach. 

Essential Methods to Monitor MySQL Router:

MySQL Router can be monitored using several methods: log files, Prometheus metrics, REST API, custom scripts, and integration with monitoring tools.

Among these, logging and the REST API are key—logs help track errors and connections, while the REST API provides real-time router status and detailed statistics for effective monitoring.

Logging Options in MySQL Router

MySQL Router logging is essential for tracking its behavior, system events, and troubleshooting issues. You can configure the level of detail you want in the logs to suit your operational needs.

Log Levels:

• DEBUG: Detailed logs useful for in-depth troubleshooting.
• INFO: General operational logs, showing normal activity.
• WARNING: Logs warnings and recoverable errors.
• ERROR: Logs critical errors only.
• SYSTEM: Logs internal router and system-related errors.

MySQL Router REST API: Setup, Usage, and Monitoring

MySQL Router’s REST API offers a simple and effective way to monitor and manage routing within MySQL replica sets. It allows you to easily check the router’s status, view configured routes, and track important metrics to ensure smooth and reliable database traffic flow.

Setting Up the REST API:

[http_server]
port=8081
bind_address=127.0.0.1

[rest_router]
require_realm=somerealm


[rest_routing]
require_realm=somerealm

[rest_metadata_cache]
require_realm=somerealm

[http_auth_realm:somerealm]
backend=somebackend
method=basic
name=Some Realm

[http_auth_backend:somebackend]
backend=file
filename=/etc/mysqlrouter/mysqlrouter.pwd

• somerealm represents the name of the authentication realm protecting your REST APIs. 
• somebackend represents the name of the authentication backend that verifies user credentials by checking a password file(/etc/mysqlrouter/mysqlrouter.pwd).

Common REST API Endpoints:

Sample Commands:

To check the route status:

[sunil@centos7 ~]$ curl -u sunil:sunil http://127.0.0.1:8081/api/20190715/routes/primary_write/status | jq
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 62 100 62 0 0 156k 0 --:--:-- --:--:-- --:--:-- 62000
{
"activeConnections": 1,
"totalConnections": 17,
"blockedHosts": 0
}

To check the Route Configs:

[root@centos7 sunil]# curl -u sunil:sunil http://127.0.0.1:8081/api/20190715/routes/primary_write/config | jq
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 235 100 235 0 0 506k 0 --:--:-- --:--:-- --:--:-- 229k
{
"bindAddress": "0.0.0.0",
"bindPort": 6446,
"clientConnectTimeoutInMs": 9000,
"destinationConnectTimeoutInMs": 5000,
"maxActiveConnections": 0,
"maxConnectErrors": 100,
"protocol": "classic",
"routingStrategy": "first-available",
"mode": "read-write"
}

To check the Connection details:

[root@centos7 sunil]# curl -u sunil:sunil http://127.0.0.1:8081/api/20190715/routes/primary_write/connections | jq
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 339 100 339 0 0 436k 0 --:--:-- --:--:-- --:--:-- 331k
{
"items": [
{
"bytesFromServer": 11421,
"bytesToServer": 877,
"sourceAddress": "127.0.0.1:52532",
"destinationAddress": "127.0.0.1:3313",
"timeStarted": "2025-06-04T12:00:37.137264Z",
"timeConnectedToServer": "2025-06-04T12:00:37.147759Z",
"timeLastSentToServer": "2025-06-04T12:01:02.614366Z",
"timeLastReceivedFromServer": "2025-06-04T12:01:02.614797Z"
}
]
}

Conclusion: Making MySQL Router Setup Reliable, Not Reactive

Zero-downtime MySQL routing is not about installing MySQL Router and hoping for the best. It is about understanding your traffic patterns, choosing the right routing strategy, and following a disciplined process of configuration and monitoring.

By splitting read and write traffic across dedicated ports and configuring proper failover destinations, you turn a fragile single-server bottleneck into a self-healing, load-balanced infrastructure.

The tools exist. The patterns are proven. What separates a smooth deployment from a 3 AM incident is planning, expertise, and real-time visibility. If you are scaling your MySQL environment and need professional guidance on MySQL High Availability & Replication or MySQL Database Administration, Mafiree is here to help.