MySQL Bugs: #72808: Consider adding SRV support to clients for connecting to a master

Bug #72808	Consider adding SRV support to clients for connecting to a master
Submitted:	30 May 2014 7:01	Modified:	8 Sep 14: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

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.

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh

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.

Now available in 8.4, see: https://dev.mysql.com/doc/refman/8.4/en/connecting-using-dns-srv.html

Actually to be honest it's here:

commit a3b495730d2932a5790962daac0a993add32ea78
Author: Georgi Kodinov <georgi.kodinov@oracle.com>
Date:   Mon May 18 14:31:06 2020 +0200

    WL#13905: libmysql support for DNS SRV
    RB#24157

However, I seem to be unable to find the release notes which indicate in which version it was released.  Sometime in 8.0.