Why

Redirection mechanism is widely used in proxy-based scenario.
Currently when multiple servers share one proxy, proxy forwards all the packets between servers and clients. Thus, the proxy adds latency, take many computing resources and impacts the overall performance. Especially for short connections scenarios like WordPress, the latency can be a critical issue.
Supporting redirection mechanism is helpful for proxy-based scenario. It is more like HTTP redirects or Oracle redirected connections. Clients get the servers’ address from proxies and connect to servers transparently without latency and computing resource wasted.

What to do

We'll add a new global/session variable, say, redirect_url. The value should be an empty string or a connection string in the conventional format (in the style of a connection url of C/J or C/NodeJS or C/C or FederatedX).

Internally the server might have a check to ensure that the value of this variable is a valid url.

This variable is appended to the default value of session_track_system_variables variable.

When this variable is changed, the existing SESSION_TRACK_SYSTEM_VARIABLES implementation will send its new value to the client as a part of the OK packet. It's up to the client or to the connector to use this value or to ignore it.

Note that the server should not disallow changing of this variable in an active transaction. This is up to connectors, perhaps a connector would want to reconnect and replay the transaction.

Thoughts

• perhaps the server should always send this variable if it's set, even if it was not just changed. This will require some tweaking of the sysvar tracker code.

Possible use cases:

• always redirect all clients to a new location
  • set @@global.redirect_url or start the server with --redurect-url= or put it in my.cnf
• redirect to a group of servers randomly
  • create a table with connection urls, one per row.
  • use an sql script that selects a random row from it and sets @@redirect_url to this value
  • specify this script in the --init-connect server parameter
• dynamically redirect from the master to one of the slaves
  • same as above, but use INFORMATION_SCHEMA.PROCESSLIST to get the list of active slaves.

and so on. An ability to redirect any time using SQL to calculate a new location opens limitless possibilities.

Location: mysql:[replication:|loadbalance:|sequential:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]]\n

<host>[:<portnumber>] or address=(host=<host>)[(port=<portnumber>)][(type=(master|slave))]