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

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

Optimize MySQL traffic with MySQL Router — smart load balancing made easyRouter Architecture image widget

Jelson May 20, 2026

Why Does a Database Setup Need a Load Balancer?
Load Balancing options in MySQL
What is MySQL Router ?
Sample setup
MySQL Replica-set
MySQL Router Configuration
Routing Methods
Types of Routing Strategies
MySQL 8.2+ Native Read/Write Splitting
Essential Methods to Monitor MySQL Router
Logging Options in MySQL Router
Log Levels
MySQL Router REST API: Setup, Usage, and Monitoring
Setting Up the REST API
Common REST API Endpoints
Sample Commands
To check the route status
To check the Route Configs
To check the Connection details
Conclusion: Making MySQL Router Setup Reliable, Not Reactive

Subscribe for email updates

Summarize with AI: ChatGPT Google AI Perplexity Claude Grok

Why Does a Database Setup Need a Load Balancer?

In a real-world application, it's common to have multiple MySQL servers, like one for writes (primary) and several for reads (replicas). Managing this manually is complex and error-prone. Learn how Mafiree handles this end-to-end through our MySQL Database Administration services.

Without a load balancer, your app has no idea where to send queries — everything goes to the same server, even if others are available. This can lead to:

Overloaded primary servers
Underused replicas
Complex app logic to manage connections

A load balancer solves this problem by sitting between your application and the database servers. It knows which server is healthy, which handles reads or writes, and routes queries accordingly — all without changes in your app code.

That’s exactly what MySQL Router does.

This blog explores how MySQL Router works under the hood, dives into advanced routing strategies, and shows you how to monitor and fine-tune your setup to keep your MySQL infrastructure fast, resilient, and always available.

Load Balancing options in MySQL:

In MySQL, load balancing is essential for distributing database traffic efficiently across multiple servers to improve performance, availability, and scalability. Here are the key load balancing options in MySQL:

Application-Level Load Balancing: Applications manually route queries to different MySQL instances. While this gives full control, it’s error-prone and difficult to manage at scale.
Third-Party Proxies: Tools like HAProxy, ProxySQL, and MaxScale offer robust, feature-rich load balancing with query-based routing, failover, and analytics. See how we use ProxySQL in production in our guide on High Availability for MySQL using Orchestrator and ProxySQL.
MySQL Router (Native Option): A native and lightweight middleware by Oracle that seamlessly connects applications to MySQL backends with built-in support for InnoDB Cluster and Replica Sets.

What is MySQL Router ?

MySQL Router is a lightweight, transparent proxy designed to route database traffic between your application and a MySQL backend cluster. It acts as an intelligent middleman that directs queries to the appropriate MySQL server based on the role of each server (e.g., primary or replica) and the type of query (read or write). It is a core component of any serious MySQL High Availability & Replication architecture. It's designed for:

High Availability: Keeps database services running smoothly during failures. Routes traffic to backup servers when needed.
Load Balancing: Split the queries across multiple servers. Helps avoid overloading any single server, improving efficiency.
Read/Write Splitting: Directs write operations to the primary server. Distributes read queries to replica servers for better performance.
Automatic Failover: Switches to a standby server if the primary fails. Ensures continuous access to the database without manual intervention.

Sample setup:

MySQL Replica-set

Component	IP	Role	Port
MySQL Primary	127.0.0.1	Read/Write	3313
MySQL Replica 1	127.0.0.1	Read-Only	3306
MySQL Replica 2	127.0.0.1	Read-Only	3000

MySQL Router Configuration

[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

Routing Methods:

MySQL Router supports several routing modes for different use cases:

Read-Write Routing: Routes all traffic to the primary server. Use this for OLTP apps that need transactional consistency.
Read-Only Routing: Distributes read queries across replicas. Ideal for reporting, analytics, and read-heavy applications.
Read-Write Split Routing: Applications route writes to rw port and reads to ro port. Allows independent scaling of reads and writes.

Types of Routing Strategies:

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:

Endpoint	Description	Plugin
/router/status	Check Router status	rest_router
/routes	List all routes	rest_routing
/routes/{routeName}/blockedHosts	Get blocked IP addresses	rest_routing
/routes/{routeName}/config	Get route configuration	rest_routing
/routes/{routeName}/connections	Get active connections	rest_routing
/routes/{routeName}/destinations	Get route destinations	rest_routing
/routes/{routeName}/health	Check route health	rest_routing
/routes/{routeName}/status	Get route status	rest_routing

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.

FAQ

The Router is bound to localhost (127.0.0.1), so it only accepts connections from the same host and blocks external application traffic.

Authentication is enabled, but the password file is missing, misconfigured, or the user is not defined, causing every request to fail authentication.

All connections are going through the read-write endpoint (6446), or the read-only route is not configured correctly.

Increase logging verbosity by setting logging_folder and setting the log level to DEBUG in the [logger] section of the configuration file, then restart the service.

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

Table of Contents

Subscribe for email updates

Why Does a Database Setup Need a Load Balancer?

Load Balancing options in MySQL:

What is MySQL Router ?

Sample setup:

MySQL Replica-set

MySQL Router Configuration

Routing Methods:

Types of Routing Strategies:

MySQL 8.2+ Native Read/Write Splitting

Essential Methods to Monitor MySQL Router:

Logging Options in MySQL Router

Log Levels:

MySQL Router REST API: Setup, Usage, and Monitoring

Setting Up the REST API:

Common REST API Endpoints:

Sample Commands:

To check the route status:

To check the Route Configs:

To check the Connection details:

Conclusion: Making MySQL Router Setup Reliable, Not Reactive

FAQ

Leave a Comment

Subscribe for email updates

Highlights

Database Services

Quick Links

Contacts

Nagercoil Office

Bangalore Office

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

Table of Contents

Subscribe for email updates

Why Does a Database Setup Need a Load Balancer?

Load Balancing options in MySQL:

What is MySQL Router ?

Sample setup:

MySQL Replica-set

MySQL Router Configuration

Routing Methods:

Types of Routing Strategies:

MySQL 8.2+ Native Read/Write Splitting

Essential Methods to Monitor MySQL Router:

Logging Options in MySQL Router

Log Levels:

MySQL Router REST API: Setup, Usage, and Monitoring

Setting Up the REST API:

Common REST API Endpoints:

Sample Commands:

To check the route status:

To check the Route Configs:

To check the Connection details:

Conclusion: Making MySQL Router Setup Reliable, Not Reactive

FAQ

Leave a Comment

Related Blogs

Subscribe for email updates

Highlights

Database Services

Quick Links

Contacts

Nagercoil Office

Bangalore Office