MySQL Router

MySQL Router Configuration

MySQL Router tools allow monitoring and management of MySQL Router instances via the REST API.

---

Prerequisites

• MySQL Router 8.0.17+ with REST API enabled
• Router REST API credentials (username/password)
• Network access to Router REST API endpoint (default: HTTPS on port 8443)
• For InnoDB Cluster mode: The cluster must be running for REST API authentication

Important: Router REST API typically uses metadata_cache authentication, meaning it authenticates against the InnoDB Cluster. If the cluster is down, authentication will fail with 401 errors.

---

Available Tools (9)

Tool	Description	Parameters
mysql_router_status	Get Router process status and version	None
mysql_router_routes	List all configured routes	None
mysql_router_route_status	Get status of a specific route	routeName (e.g., bootstrap_rw)
mysql_router_route_health	Check health/liveness of a route	routeName
mysql_router_route_connections	List active connections on route	routeName
mysql_router_route_destinations	List backend MySQL server destinations	routeName
mysql_router_route_blocked_hosts	List blocked IP addresses for a route	routeName
mysql_router_metadata_status	InnoDB Cluster metadata cache status ⚠️	metadataName (typically bootstrap)
mysql_router_pool_status	Connection pool statistics ⚠️	poolName

⚠️ = Requires InnoDB Cluster configuration

---

Setting Up Router API Authentication

Option A: InnoDB Cluster (Recommended)

When Router is bootstrapped against an InnoDB Cluster, it uses metadata_cache authentication by default. This authenticates REST API users against the cluster's metadata database.

1. Create REST API user in the cluster:

-- Connect to any cluster node
-- The user will be stored in mysql_innodb_cluster_metadata.router_rest_accounts

# Use mysqlrouter_passwd to generate the password hash
mysqlrouter_passwd set /tmp/rest_api.pwd rest_api

# Insert into cluster metadata (on PRIMARY node)
mysql -h localhost -P 3307 -u cluster_admin -p -e "
INSERT INTO mysql_innodb_cluster_metadata.router_rest_accounts
(cluster_id, user, authentication_method, authentication_string, description)
SELECT cluster_id, 'rest_api', 'modular_crypt_format',
       '<hash_from_mysqlrouter_passwd>', 'REST API user'
FROM mysql_innodb_cluster_metadata.clusters LIMIT 1;"

2. Router config uses metadata_cache backend:

[http_auth_backend:default_auth_backend]
backend=metadata_cache

Option B: File-based Authentication (Standalone)

For standalone Router deployments without InnoDB Cluster:

[http_server]
port=8443
ssl=1
ssl_cert=/path/to/router-cert.pem
ssl_key=/path/to/router-key.pem

[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm

[http_auth_backend:default_auth_backend]
backend=file
filename=/path/to/mysqlrouter.pwd

[rest_router]
require_realm=default_auth_realm

[rest_routing]
require_realm=default_auth_realm

# Generate password hash (prompts for password)
mysqlrouter_passwd set /path/to/mysqlrouter.pwd router_admin

---

Environment Variables

Variable	Default	Description
MYSQL_ROUTER_URL	https://localhost:8443	Router REST API base URL
MYSQL_ROUTER_USER	-	Router API username
MYSQL_ROUTER_PASSWORD	-	Router API password
MYSQL_ROUTER_API_VERSION	/api/20190715	API version path
MYSQL_ROUTER_INSECURE	false	Skip TLS verification (for self-signed certs)

⚠️ Never commit Router credentials to version control. Use environment variables or secure secrets management.

---

MCP Configuration

{
  "mcpServers": {
    "mysql-mcp": {
      "command": "node",
      "args": [
        "C:/path/to/mysql-mcp/dist/cli.js",
        "--transport",
        "stdio",
        "--mysql",
        "mysql://user:password@localhost:3306/database"
      ],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "app_user",
        "MYSQL_PASSWORD": "secure_password",
        "MYSQL_DATABASE": "production",
        "MYSQL_ROUTER_URL": "https://router.example.com:8443",
        "MYSQL_ROUTER_USER": "router_admin",
        "MYSQL_ROUTER_PASSWORD": "router_password",
        "MYSQL_ROUTER_INSECURE": "true"
      }
    }
  }
}

---

Router-Only Configuration

If you only want Router tools (e.g., for a dedicated monitoring agent):

{
  "args": [
    "--transport",
    "stdio",
    "--mysql",
    "mysql://user:password@localhost:3306/database",
    "--tool-filter",
    "router"
  ]
}

This exposes only the 9 Router management tools.

---

Troubleshooting

"fetch failed" Error

Cause: Router REST API is unreachable or TLS handshake failed.

Solutions:

1. Verify Router is running: docker ps | grep router
2. Check Router logs: docker logs mysql-router
3. Test API manually: curl -k -u rest_api:router_api https://localhost:8443/api/20190715/router/status
4. Ensure MYSQL_ROUTER_INSECURE=true is set for self-signed certificates

401 Unauthorized Error

Cause: Authentication failed. For metadata_cache backend, this usually means the InnoDB Cluster is not running.

Solutions:

1. Start the InnoDB Cluster: docker compose -f innodb-cluster.yml up -d
2. Reboot cluster from outage if needed: dba.rebootClusterFromCompleteOutage()
3. Restart Router to reconnect: docker restart mysql-router
4. Verify credentials match the router_rest_accounts table

Router Shows "not an online GR member"

Cause: Cluster nodes are running but Group Replication is not active.

Solution: Reboot cluster from complete outage using MySQL Shell:

mysqlsh --uri cluster_admin:password@localhost:3307 --js \
  -e "dba.rebootClusterFromCompleteOutage('clusterName', {force: true})"

404 Not Found for mysql_router_pool_status

Cause: The connection pool name doesn't exist or connection pooling is not enabled.

Solution: This is expected if Router doesn't have connection pooling configured. The tool itself is working correctly.

---

v3.0.2 Changes

• Graceful error handling — All 9 mysql_router_* tools now return { available: false, error: "..." } with descriptive error messages when the Router REST API is unreachable, instead of throwing raw errors. Improved messages for common issues: connection refused, timeout, TLS certificate errors.
• mysql_router_pool_status — Fixed description to accurately reflect actual API response fields (idleServerConnections, stashedServerConnections).

---

See Also

• Tool-Reference - Complete tool list
• Tool-Filtering - Custom tool filtering
• Configuration - General configuration