Reading and Interpreting MySQL Router Logs for HA Troubleshooting

MySQL Router is often dropped into an HA architecture and then forgotten until something breaks. When connections stall or fail over unexpectedly, the Router logs are your primary source of truth. This article walks through how to read and interpret those logs so you can debug issues quickly and confidently.

1. Where MySQL Router Logs Live

The exact location depends on how you installed MySQL Router, but on RHEL/Rocky Linux you will commonly see:

RPM/YUM install (systemd service): /var/log/mysqlrouter/mysqlrouter.log
Manual tarball install: relative to --logdir in mysqlrouter.conf

Check the systemd unit and configuration:

sudo systemctl status mysqlrouter

# Look for the config path, then:
grep -i logdir /etc/mysqlrouter/mysqlrouter.conf

If logdir is not set, Router may write to the current working directory when launched manually.

2. Basic Log Format and Levels

MySQL Router uses a simple, line-based format. A typical log entry looks like:

2025-01-10 12:34:56 info [router] routing connection 12345 to 10.0.0.11:3306

Key parts:

Timestamp: usually local time, no timezone. Correlate with MySQL server logs using NTP-synchronised clocks.
Level: debug, info, warning, error, sometimes critical.
Module: e.g. [router], [metadata_cache], [io].
Message: human-readable text describing the event.

Adjust log level in mysqlrouter.conf:

[logger]
level = INFO
# or DEBUG for deeper troubleshooting (be careful in production)

After changing the level, restart the service:

sudo systemctl restart mysqlrouter

3. Understanding Router Topology and Metadata

In InnoDB Cluster setups, Router relies on the metadata stored in the MySQL Shell-created schema. Conceptually:

┌───────────────┐      ┌─────────────────┐
│  Applications │ ───▶ │  MySQL Router   │ ───▶  Primary / Replicas
└───────────────┘      └─────────────────┘
                              ▲
                              │
                     InnoDB Cluster Metadata

Metadata-related log entries typically come from [metadata_cache] or similar modules. For example:

2025-01-10 12:35:01 info [metadata_cache] metadata refresh succeeded, view_id=42
2025-01-10 12:35:02 info [metadata_cache] primary: 10.0.0.11:3306, secondaries: 10.0.0.12:3306, 10.0.0.13:3306

These lines tell you what Router believes the cluster topology is at that moment. If Router sends traffic to the wrong node, check when the last metadata refresh occurred and whether it succeeded.

4. Common Log Sections and What They Mean

4.1 Startup and Configuration

On startup, Router logs configuration and plugin loading. Example:

2025-01-10 12:30:00 info [router] MySQL Router 8.x.x starting
2025-01-10 12:30:00 info [router] reading config from '/etc/mysqlrouter/mysqlrouter.conf'
2025-01-10 12:30:00 info [routing:classic_rw] listening on 0.0.0.0:6446

Look for:

Which routing sections are active ([routing:classic_rw], [routing:classic_ro], etc.).
Listening addresses and ports.
Any syntax or configuration errors logged at error level.

If Router fails to start, the log usually includes a clear error such as an invalid option or port already in use.

4.2 Connection Routing Events

When clients connect, you will see messages like:

2025-01-10 12:34:56 info [routing:classic_rw] new connection from 192.168.10.20:54321
2025-01-10 12:34:56 info [routing:classic_rw] routing connection 12345 to 10.0.0.11:3306
2025-01-10 12:34:57 info [routing:classic_rw] connection 12345 closed, bytes_in=1024, bytes_out=2048

These lines help you answer:

Which client connected and to which routing service.
Which backend server was selected.
Whether the connection ended normally or due to an error.

For failures, you might see:

2025-01-10 12:35:10 warning [routing:classic_rw] failed to connect to 10.0.0.11:3306: connection refused
2025-01-10 12:35:10 error   [routing:classic_rw] no available destinations, closing client connection 12346

These messages indicate backend unavailability or misconfiguration (e.g. host, port, firewall, MySQL not listening).

4.3 Health Checks and Failover

Router periodically checks backend nodes. Health-related logs might look like:

2025-01-10 12:36:00 info [metadata_cache] checking server health
2025-01-10 12:36:00 warning [metadata_cache] server 10.0.0.11:3306 is not reachable
2025-01-10 12:36:01 info [metadata_cache] new primary detected: 10.0.0.12:3306 (view_id=43)

These lines are critical for understanding failover timing:

When Router first detected a node as unhealthy.
When it learned about a new primary.
How quickly it reacted to cluster changes.

5. Step-by-Step: Debugging Common Issues Using Logs

5.1 Clients Cannot Connect Through Router

Check Router is running and listening:

sudo systemctl status mysqlrouter
ss -lntp | grep mysqlrouter

Inspect startup logs for bind or port errors:

grep -i "listening on" /var/log/mysqlrouter/mysqlrouter.log
grep -i "error" /var/log/mysqlrouter/mysqlrouter.log | head

Look for client connection attempts:

grep "new connection" /var/log/mysqlrouter/mysqlrouter.log | tail

If you do not see connection attempts, traffic is not reaching Router (network, firewall, DNS, or application configuration issue).

Check for “no available destinations” errors:

grep -i "no available destinations" /var/log/mysqlrouter/mysqlrouter.log

If present, Router is running but has no healthy backends. Investigate MySQL server availability and metadata status.

5.2 Router Sends Writes to the Wrong Node

Identify which node Router used for a sample connection:

grep "routing connection" /var/log/mysqlrouter/mysqlrouter.log | tail

Example:

2025-01-10 12:40:00 info [routing:classic_rw] routing connection 13000 to 10.0.0.13:3306

Check Router’s current view of the cluster around that time:

grep "metadata refresh" /var/log/mysqlrouter/mysqlrouter.log | tail

Compare the view_id and primary node in the logs with the actual cluster status from MySQL Shell or performance_schema.replication_group_members.

Look for metadata errors (e.g. cannot connect to metadata server):

grep -i "metadata" /var/log/mysqlrouter/mysqlrouter.log | grep -i error

If Router cannot refresh metadata, it may continue using an outdated topology until connectivity is restored.

5.3 Slow or Hanging Connections

Correlate timestamps between Router logs and MySQL server logs (general log, error log, slow query log).
Look for repeated connection attempts to a failing node:

grep -i "failed to connect" /var/log/mysqlrouter/mysqlrouter.log

If Router repeatedly tries a dead node before failing over, clients may experience timeouts. Tune TCP-level timeouts and any Router-specific connect timeout options in the configuration (check your version’s documentation for the exact option names).

6. Log Rotation and Retention

Verbose logs are useful until they fill your disk. On RHEL/Rocky Linux, configure logrotate for /var/log/mysqlrouter/mysqlrouter.log:

sudo vi /etc/logrotate.d/mysqlrouter

Example snippet:

/var/log/mysqlrouter/mysqlrouter.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    create 640 mysqlrouter mysqlrouter
    postrotate
        systemctl reload mysqlrouter &gt; /dev/null 2&gt;&amp;1 || true
    endscript
}

Validate rotation on a non-production system first, ensuring Router continues to log after rotation.

7. Best Practices for Using Router Logs

Standardise time: Use NTP on all hosts so Router and MySQL logs align.
Use INFO in production, DEBUG temporarily: DEBUG is powerful but can generate large volumes and expose sensitive details.
Centralise logs: Forward Router logs to a central logging system (e.g. rsyslog, journald forwarding, or an ELK/observability stack) for cross-host correlation.
Tag by environment: Include environment and cluster name in hostnames or log metadata so you can quickly filter logs by cluster.
Document common patterns: Build a small runbook mapping frequent log messages to remediation steps for your team.

8. Conclusion

MySQL Router logs are straightforward once you know what to look for: startup configuration, routing decisions, metadata refreshes, and health checks. By systematically correlating these logs with MySQL server behaviour and client symptoms, you can quickly isolate whether an issue is in the application, Router, or the database layer. Invest a bit of time in log rotation, centralisation, and a simple runbook, and Router will become an observable, predictable part of your HA stack instead of a black box.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.