DNS cache not working

[pipozzz]

Hello,

I have configured mysql servers as DNS record to mysql servers. When failover occurs and IP has changed, queries are pointing to old mysql servers which is read only in my case and it causing problems with writing to read only node.

Proxysql 2.5.1

+------------------------------------------------+-------+
| Variable_name                                  | Value |
+------------------------------------------------+-------+
| mysql-monitor_local_dns_cache_ttl              | 20000 |
| mysql-monitor_local_dns_cache_refresh_interval | 10000 |
| mysql-monitor_local_dns_resolver_queue_maxsize | 128   |
+------------------------------------------------+-------+

Thank you for any help in advance.

[renecannao]

Can you please provide more details of what "doesn't work" ?

If I had to guess what is going on with the very limited information you provided: proxysql is using already established connections, and in this case the issue has nothing to do with DNS caching.

[pipozzz]

Thanks you for quick response, I thought that with this dns feature I will solve problem which I described above. Could you recommend me some solution if I have one DNS to mysql server and when failover occurs our dns is changes and we would like to forward queries to new IP somehow, please ?

[renecannao]

when failover occurs our dns is changes

I would argue that this is the problem. Why do you do that?

[pipozzz]

I have 2 servers A and B. At certain time only one is writer. I have dns record pointing to writer. When there is some problem with A, failover occurs and dns is switch to new writer B. We have this dns record set in mysql_servers.

[renecannao]

Hi @pipozzz .

I am still trying to understand the reason behind having a DNS entry pointing to the writer, and a DNS entry in mysql_servers , instead of just letting ProxySQL detect which one is the writer and the readers. Relying on DNS server to determine which one is the writer seems (to me) a poor choice.

If you really want to rely on DNS entry to determine which one is the writer, you should kill all connections on old writer as part of the failover process, thus all clients (proxysql and others) will be forced to reconnect.

[pipozzz]

so I misunderstood dns feature, I thought that if DNS record is changed proxysql will reconnect to new IP instead of keeping connections old IPs which could point to something else because dns record is different.

The main reason why I wanted to try this feature like this is because we have used aws hostgroups but there is one bug when you up scale and down-scale often it does not remove old rds instances from mysql_servers #3883