Bug #72808 Consider adding SRV support to clients for connecting to a master
Submitted: 30 May 2014 7:01 Modified: 22 Sep 2017 6:25
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Connection Handling Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: client, connetions, dns, GTID, pooling, SRV

[30 May 2014 7:01] Simon Mudd
Description:
With GTID becoming more popular failover scenarios should a master fail become easier to manage and a client or slave may want to connect back to a master and can do this automatically.

However, right now this needs to be done manually and usually the master may be named explicitly or via a VIP.

This process could be simplified by using a "standard" mechanism such as the DNS SRV records where the servers to talk to could be identified and prioritised more transparently, thus reducing the burden from adding the logic to clients.

Pooling of client access to a number of slaves also falls under this umbrella, though perhaps the need to maintain the information with a very low TTL makes it less practical than access to a master which is likely to be more table.

References:
- http://tools.ietf.org/html/rfc2782
- http://en.wikipedia.org/wiki/SRV_record

How to repeat:
Currently the way to connect is to provide the hostname or ip address and port to connect to.  There is no way to specify prioritisation of which host or hosts _can_ be talked to and this must be dealt with manually.

Suggested fix:
Please consider adding an optional mechanism to the connect calls to allow SRV records to be used.

I'd guess something like:

_mysql._tcp.mymysqlmaster.example.com IN SRV 10 0 3306 mainmaster.example.com
_mysql._tcp.mymysqlmaster.example.com IN SRV 20 0 3306 failovermaster1.example.com
_mysql._tcp.mymysqlmaster.example.com IN SRV 30 0 3306 failovermaster2.example.com

Should one be using mainmaster.example.com and this server fail (and assuming the case of a GTID topology) slaves would first try to connect to failovermaster1, and if that were unavailable failovermaster2.

For simple pooling a similar usage could be applied:

_mysql._tcp.mysqlpool.example.com IN SRV 50 33 3306 slave1.example.com
_mysql._tcp.mysqlpool.example.com IN SRV 50 33 3306 slave2.example.com
_mysql._tcp.mysqlpool.example.com IN SRV 50 33 3306 slave3.example.com
_mysql._tcp.mysqlpool.example.com IN SRV 50 1 3306 testing.example.com

which gives 33% weighting to slave1 .. slave3 and a 1% (1 / ( 1+ 33 + 33 + 33 ) weighting to a test slave.

Note: a setup like this simplifies configuration management for several use cases but does require the use of full DNS libraries to obtain the required information so probably should not be used by default if connection speed is of high priority.

That said I can imagine where it would be much easier for an operational point of view to configure something like:

...;use_srv=yes;host=mysqlpool.example.com;...  as connection parameters as this avoids the need to explicitly mention ports to connect to, all of which is managed more cleanly by using the DNS SRV record types.
[2 Jun 2014 7:47] MySQL Verification Team
Hello Simon,

Thank you for the feature request!

Thanks,
Umesh
[22 Sep 2017 6:25] Simon Mudd
Re-reading this my request was focused on master connections. However the more common use case of this would be to connect to one of a pool of MySQL clients in the same "pool". Currently load balancing of connections to such pools has to be handled by the client and everyone does it differently.  Support in the client library for the DNS SRV type would _permit_ the DBAs to provide a "pool name" with the servers to talk to under that. Priority/Weight is handled by the SRV DNS type so that's good and then you can basically distribute load much more easily to back end mysql servers. 

So this need is just as true now as it was when I wrote it and it has various use cases.