The usage of MySQL Router as a Middleware/Proxy/Router has increased along with the rise in MySQL InnoDB Cluster usage. While it is still relatively easy to use in production, monitoring it to stay informed about its current status is essential. This blog post will cover how to check and monitor MySQL routers, routes, and other statistics that can be valuable in identifying or even preventing potential issues.
In version 8.0.16, MySQL Router added a web server interface (HTTP), but the really cool thing related to the web server interface came in version 8.0.17 with the REST API. For those who don’t know, API stands for application programming interface, and REST stands for representational state transfer. Simply put, a REST API is an interface that lets computers trade information over the internet.
Using MySQL Router
Let’s explore the setup and usage. To get started, we’ll assume we have a Group Replication cluster set up on AWS and configure the MySQL Router on a separate server with IP 172.22.214.2. So, let’s start bootstrapping!
|
1 |
$ mysqlrouter --bootstrap cluster_admin@172.22.214.2:3306 --user=router_admin --directory /opt/mysql/myrouter --conf-use-sockets --account routerfriend --account-create always --disable-rest |
Note that from version 8.0.22 and onwards, when bootstrapping the router, the REST API will be already set up with the bootstrap. Since we aim to understand the REST further, we added the –disable-rest flag to the bootstrap command to not set anything. Hence, we need to configure the router properly.
The following settings are the most basic configuration to enable the HTTP server to serve our requests and the REST API basic endpoint:
|
1 2 3 4 5 |
# Exposes http://127.0.0.1:8081 [http_server] # Exposes /api/20190715/swagger.json [rest_api] |
With those only two groups on the MySQL Router conf file, it is already possible to send requests:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# curl http://127.0.0.1:8081/api/20190715/swagger.json | jq { "swagger": "2.0", "info": { "title": "MySQL Router", "description": "API of MySQL Router", "version": "20190715" }, "basePath": "/api/20190715", "tags": [], "paths": {}, "definitions": {} } |
The swagger.json output is the only one able to reply/respond without any authentication, so to check the other endpoints, we need to create the authentication config. The router uses realm for authentication, so please use the following template to create the realm:
|
1 2 3 4 5 |
# Define our realm [http_auth_realm:realmauth] backend=filebackend method=basic name=Real Authentication |
Once we create the realm, we can create the backend mentioned on the template above:
|
1 2 3 4 |
# Define our backend; this file must exist and be a password file generated with mysqlrouter_passwd (more on this below) [http_auth_backend:filebackend] backend=file filename=/etc/mysqlrouter/mysqlrouter.pwd |
With the config done, we need to create the user on the file set on the configuration:
|
1 2 |
# mysqlrouter_passwd set /etc/mysqlrouter/mysqlrouter.pwd roberto Please enter password: |
Giving the correct user permission:
|
1 |
chown router_admin.router_admin /etc/mysqlrouter/mysqlrouter.pwd |
With the user created and the authentication configured, we need to enable the route to other endpoints:
|
1 2 3 |
# Exposes /api/20190715/router/status [rest_router] require_realm=realmauth |
Sending request to the new endpoint:
|
1 2 3 4 5 6 7 8 |
$ curl -s -u roberto:secret http://127.0.0.1:8081/api/20190715/router/status | jq { "processId": 2732, "productEdition": "Percona Server (GPL), Release 28, Revision 47601f19", "timeStarted": "2024-06-11T18:58:14.527154Z", "version": "8.0.36", "hostname": "node3" } |
Besides the two endpoints, we have endpoints for MySQL Router connection pool, routing, and metadata. Below, I will show you how to enable the routing and metadata ones, as well as some examples. So on MySQL Router REST API reference, we have all routes existent and the plugin needed to enable each one:
To enable routing:
|
1 2 |
[rest_routing] require_realm=realmauth |
Testing the routing REST endpoint:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$ curl -s -u roberto:secret http://127.0.0.1:8081/api/20190715/routes/bootstrap_rw/destinations | jq { "items": [ { "address": "172.22.214.2", "port": 3306 } ] } $ curl -s -u roberto:secret http://127.0.0.1:8081/api/20190715/routes/bootstrap_ro/destinations | jq { "items": [ { "address": "172.22.214.3", "port": 3306 }, { "address": "172.22.214.4", "port": 3306 } ] } |
Enabling metadata endpoint:
|
1 2 |
[rest_metadata_cache] require_realm=realmauth |
Using metadata endpoint:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$ curl -s -u roberto:secret http://127.0.0.1:8081/api/20190715/metadata/bootstrap/config | jq { "clusterName": "", "timeRefreshInMs": 500, "groupReplicationId": "c74bf75c-280d-11ef-bdf0-0242ac16d602", "nodes": [ { "hostname": "172.22.214.2", "port": 3306 }, { "hostname": "172.22.214.3", "port": 3306 }, { "hostname": "172.22.214.4", "port": 3306 } ] } |
Use case
If a host reaches the max_connection_errors from the route, the router will block the host on that route. The Rest API is a way to check which host was blocked and how many hosts were blocked. Allow me to show an example:
|
1 2 3 4 5 6 |
$ curl -s -u roberto:secret http://54.242.195.91:8443/api/20190715/routes/bootstrap_rw/status | jq { "activeConnections": 0, "totalConnections": 6, "blockedHosts": 1 } |
Checking the host was blocked:
|
1 2 3 4 5 6 |
$ curl -s -u roberto:secret http://54.242.195.91:8443/api/20190715/routes/bootstrap_rw/blockedHosts | jq { "items": [ "54.160.80.237" ] } |
Also, instead of implementing health checks using port knocking, you can now use the /health endpoint. For example, here is what a healthy route looks like:
|
1 2 3 4 |
$ curl -s -u roberto:secret http://54.242.195.91:8443/api/20190715/routes/bootstrap_rw/health | jq { "isAlive": true } |
Or even:
|
1 2 3 4 5 6 |
$ curl -s -u roberto:secret http://54.242.195.91:8443/api/20190715/routes/bootstrap_rw/status | jq { "activeConnections": 0, "totalConnections": 6, "blockedHosts": 1 } |
Conclusion:
Although it isn’t possible to change configurations through the Router REST API, the values returned from the API consumption give us some monitoring values that can help us understand the health and usage of our MySQL Router.
MySQL Performance Tuning is an essential guide covering the critical aspects of MySQL performance optimization.
Download and unlock the full potential of your MySQL database today!
References:
https://dev.mysql.com/doc/relnotes/mysql-router/8.0/en/news-8-0-16.html
https://dev.mysql.com/doc/relnotes/mysql-router/8.0/en/news-8-0-17.html
https://dev.mysql.com/doc/relnotes/mysql-router/8.0/en/news-8-0-22.html
https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-rest-api-setup.html
