MySQL Router 8.0.17 and the REST API

Since MySQL 8.0.16, the Router as the possibility to launch an internal webserver (see Jan’s blog post).

Even if this webserver could serve static files, it was the first piece of a much more interesting solution that is now available since 8.0.17.

It’s possible now to query the MySQL Router via its REST API and get a lot of useful information.

Setup

Let’s first configure our MySQL Router to take advantages of this new feature. In this example, I will add the following lines to /etc/mysqlrouter/mysqlrouter.conf that I created using the --bootsrapcommand line argument:

[http_server]
port=8080

[rest_api]

[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

Now, I will create the required credentials:

# mysqlrouter_passwd set /etc/mysqlrouter/mysqlrouter.pwd fred

I can of course verify it:

# mysqlrouter_passwd verify /etc/mysqlrouter/mysqlrouter.pwd fred
Please enter password:

And finally, I must change the ownership of my new password file:

# chown mysqlrouter /etc/mysqlrouter/mysqlrouter.pwd

It’s time to restart MySQL Router.

Using the REST API

Now that the MySQL Router is running and it has been configure, what can we do with it ?

For example, if we want to know which server will be reached for RW, we can use the following command:

$ curl -s -u fred:fred \
  http://192.168.91.2:8080/api/20190715/routes/myCluster_default_rw/destinations
{"items":[{"address":"mysql2","port":3306}]}

It’s also possible to list all the possible calls that can be performed using /api/20190715/swagger.json:

$ curl -s -u fred:fred http://192.168.91.2:8080/api/20190715/swagger.json | jq '.paths'
{
   "/metadata/{metadataName}/config": {
     "get": {
       "tags": [
         "cluster"
       ],
       "description": "Get config of the metadata cache of a replicaset of a cluster",
       "responses": {
         "200": {
           "description": "config of metadata cache",
           "schema": {
             "$ref": "#/definitions/MetadataConfig"
           }
         },
         "404": {
           "description": "cache not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/metadataNameParam"
       }
     ]
   },
   "/metadata/{metadataName}/status": {
     "get": {
       "tags": [
         "cluster"
       ],
       "description": "Get status of the metadata cache of a replicaset of a cluster",
       "responses": {
         "200": {
           "description": "status of metadata cache",
           "schema": {
             "$ref": "#/definitions/MetadataStatus"
           }
         },
         "404": {
           "description": "cache not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/metadataNameParam"
       }
     ]
   },
   "/metadata": {
     "get": {
       "tags": [
         "cluster"
       ],
       "description": "Get list of the metadata cache instances",
       "responses": {
         "200": {
           "description": "list of the metadata cache instances",
           "schema": {
             "$ref": "#/definitions/MetadataList"
           }
         }
       }
     }
   },
   "/routes/{routeName}/config": {
     "get": {
       "tags": [
         "routes"
       ],
       "description": "Get config of a route",
       "responses": {
         "200": {
           "description": "config of a route",
           "schema": {
             "$ref": "#/definitions/RouteConfig"
           }
         },
         "404": {
           "description": "route not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/routeNameParam"
       }
     ]
   },
   "/routes/{routeName}/status": {
     "get": {
       "tags": [
         "routes"
       ],
       "description": "Get status of a route",
       "responses": {
         "200": {
           "description": "status of a route",
           "schema": {
             "$ref": "#/definitions/RouteStatus"
           }
         },
         "404": {
           "description": "route not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/routeNameParam"
       }
     ]
   },
   "/routes/{routeName}/health": {
     "get": {
       "tags": [
         "routes"
       ],
       "description": "Get health of a route",
       "responses": {
         "200": {
           "description": "health of a route",
           "schema": {
             "$ref": "#/definitions/RouteHealth"
           }
         },
         "404": {
           "description": "route not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/routeNameParam"
       }
     ]
   },
   "/routes/{routeName}/destinations": {
     "get": {
       "tags": [
         "routes"
       ],
       "description": "Get destinations of a route",
       "responses": {
         "200": {
           "description": "destinations of a route",
           "schema": {
             "$ref": "#/definitions/RouteDestinationList"
           }
         },
         "404": {
           "description": "route not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/routeNameParam"
       }
     ]
   },
   "/routes/{routeName}/connections": {
     "get": {
       "tags": [
         "routes"
       ],
       "description": "Get connections of a route",
       "responses": {
         "200": {
           "description": "connections of a route",
           "schema": {
             "$ref": "#/definitions/RouteConnectionsList"
           }
         },
         "404": {
           "description": "route not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/routeNameParam"
       }
     ]
   },
   "/routes/{routeName}/blockedHosts": {
     "get": {
       "tags": [
         "routes"
       ],
       "description": "Get blocked host list for a route",
       "responses": {
         "200": {
           "description": "blocked host list for a route",
           "schema": {
             "$ref": "#/definitions/RouteBlockedHostList"
           }
         },
         "404": {
           "description": "route not found"
         }
       }
     },
     "parameters": [
       {
         "$ref": "#/parameters/routeNameParam"
       }
     ]
   },
   "/routes": {
     "get": {
       "tags": [
         "routes"
       ],
       "description": "Get list of the routes",
       "responses": {
         "200": {
           "description": "list of the routes",
           "schema": {
             "$ref": "#/definitions/RouteList"
           }
         }
       }
     }
   },
   "/router/status": {
     "get": {
       "tags": [
         "app"
       ],
       "description": "Get status of the application",
       "responses": {
         "200": {
           "description": "status of application",
           "schema": {
             "$ref": "#/definitions/RouterStatus"
           }
         }
       }
     }
   }
 }

The current API (version 20190715), supports the following urls:

• /metadata/{metadataName}/config
• /metadata/{metadataName}/status
• /metadata
• /routes/{routeName}/config
• /routes/{routeName}/status
• /routes/{routeName}/health
• /routes/{routeName}/destinations
• /routes/{routeName}/connections
• /routes/{routeName}/blockedHosts
• /routes
• /router/status

Some examples

$ curl -s -u fred:fred http://192.168.91.2:8080/api/20190715/metadata | jq 
{
  "items": [
    {
      "name": "myCluster"
    }
  ]
}
$ curl -s -u fred:fred http://192.168.91.2:8080/api/20190715/metadata/myCluster/status | jq 
{
  "refreshFailed": 0,
  "refreshSucceeded": 3639,
  "timeLastRefreshSucceeded": "2019-07-08T09:17:22.463136Z",
  "lastRefreshHostname": "mysql1",
  "lastRefreshPort": 3306
}
$ curl -s -u fred:fred http://192.168.91.2:8080/api/20190715/metadata/myCluster/config | jq 
{
  "clusterName": "myCluster",
  "timeRefreshInMs": 500,
  "groupReplicationId": "b2025e72-9e5e-11e9-95c9-08002718d305",
  "nodes": [
    {
      "hostname": "mysql1",
      "port": 3306
    },
    {
      "hostname": "mysql2",
      "port": 3306
    }
}

Conclusion

I really invite you to update to the latest version of MySQL Router, don’t forget that even with MySQL InnoDB Cluster 5.7.x you must use the latest MySQL Router 8.0 and MySQL Shell 8.0 and try the new REST API.