Description:
Related to new functionality in 8.0.22
See:
- https://bugs.mysql.com/bug.php?id=101216
- https://bugs.mysql.com/bug.php?id=101217
where the i/o thread is potentially able to use one of a list of possible masters (use case GR members or intermediate master tier in large asynchronous tiered replication cluster)
There was also a change allowing the client connections to connect to a MySQL host using DNS SRV records, see: https://dev.mysql.com/doc/refman/8.0/en/connecting-using-dns-srv.html
What seems somewhat surprising is that the CHANGE MASTER TO command can not use this same functionality to be able to select a server to connect to.
How to repeat:
Check the docs above.
For the CHANGE MASTER TO command I see no option to use a DNS SRV record. It might be easier to configure this than configure each individual replica as the configuration of DNS would be centralised compared to the proposed use of the UDF functions must be configured on each individual mysql server. That potentially makes configuration management complex on large clusters.
Suggested fix:
1. Add a MASTER_HOST_DNS_SRV_NAME = '_mysql._tcp.example.com' option, default '' (unused)
Given this would provide 3 different parameters for determining the master, MASTER_HOST, MASTER_HOST_DNS_SRV_NAME and SOURCE_CONNECTION_AUTO_FAILOVER it should be possible to choose the order tried if all 3 are provided.
2. Allow asynchronous_connection_failover_add_source() and asynchronous_connection_failover_delete_source() to provide a priority
If you look at https://dev.mysql.com/doc/refman/8.0/en/connecting-using-dns-srv.html you will see that the DNS SRV usage provides more flexibility in determining the server to connect to. You can provide the priority and the weight.
The UDF calls only allow the weight to be provided. In most cases for configuring upstream masters this added flexibility may not be needed but for consistency in the client API DNS SRV support I would suggest adding a priority, the default if not defined can be assumed to be 0.
The Usage of priority is likely to be of interest when you have multiple availability zones and multiple [intermediate] masters, preference for an upstream [intermediate] master in the same AZ for leaf nodes is likely, and it would be more explicit, while allowing for selection amongst the [intermediate] masters in the same AZ might give higher priority to certain servers.