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.

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:

If you're building a high-performance MySQL environment, MySQL Router isn't optional—it's essential. With built-in load balancing, flexible routing, and seamless failover capabilities, it turns your MySQL backend into a self-healing, highly available system.