Description:
[ Labelling/category may be incorrect, this may be better referred to via the C library, but this is not just a client but also server change and it should be protocol specific and language agnostic and allow drivers in all languages to be adapted, even those not supported by Oracle/MySQL. ]
The MySQL protocol comes from when the client/server setup was nice and simple: you connect to a server: do some SQL and disconnect. That works fine.
Since then life has changed, HTTP saw this so provided different headers to allow the client to indicate which "service" / "endpoint" it wanted to talk to and the back end web server would adjust behaviour accordingly for that website.
MySQL usage has grown too, so often we have a "pool" of readers to a synchronous (GR) or asynchronous cluster and behind that "pool" we have special handling to redirect via a proxy the connection to the specific server.
This is done by an external proxy but you need different endpoints for each service, you can't redirect different endpoints to different servers as the proxy has no idea who the client really wants to talk to.
Improving the MySQL protocol in some way which makes this endpoint optionally visible to the listener means that redirection to the appropriate backend can be done transparently and simplifies connection management if multiple similar services are involved.
Think of the case of a http service mesh like istio / envoy etc. It can not handle this directly because of the way the mysql protocol does not currently provide a way to indicate the desired endpoint.
How to repeat:
read description.
Suggested fix:
A lot of people use a proxy, you can also use other mechanisms for service discovery but it would significantly simplify the management of the client to server connection if the protocol could be extended to provide such an endpoint.
If a service mesh is being used then the service mesh itself could register the health of the candidate members.
What could this endpoint look like in practice?
- hostname/ip address:port which is basically what current connectors are told to connect to
- a simple name. Some of the current tooling I use has applications (doing this differently) referring to the different endpoints as mysqlservice1-rw, mysqlservice1-ro, mysqlservice1-ro-batch, mysqlservice1-appgroupA, mysqlservice1-appgroupB, ...
where:
- mysqlservice1-rw might point at a single "master" (traditional replication)
- mysqlservice1-rw might talk to the current primary/ies (GR)
- mysqlservice1-ro might talk to a group of replicas in the mysqlservice1 cluster
In the end the client is told to talk to this "service"
- the listener/proxy/servicemesh given the name can decide the routing of the request to the appropriate backend. How this happens and which server this is is irrelevant from the protocol perspective
- the proxy/servicemesh can do health checks on candidate members of each of these services so that only the healthy one/ones are used, and if a failure occurs the list is adapted immediately.
I'm aware there is a MySQL REST interface now, but this does not look intended as far as I can see for high performance / highend usage and also seems to be implemented as an abstraction above the existing MySQL protocol so it's not really very helpful.
The intention of this request is to extend the MySQL protocol in a way which allows apps using existing tooling and a modified driver to simply change the "name" of the endpoint used (e.g host/port or maybe user/host/port) and nothing else changes.
The MySQL protocol is well known. I'm not sure how easy it would be to provide such an extension but I see that providing it would be hugely beneficial for any proxy or service mesh users to allow more transparent scaling of connections and improve handling when failure or maintenance scenarios are present.
If you consider doing this please ensure the protocol change is documented and also handling of edge cases is clarified to ensure client and server now how to respond consistently in such cases. The protocol change if made should be backwards compatible so that existing clients are not affected if they don't use whatever extension would be appropriate.
Using the MySQL X protocol might be simpler and more interesting but changing the protocol used would require significant changes to clients so may not be practical.
