Hi.
I am using Proxy SQL version 2.5.5-percona-1.2. OS Debian 11 operating system.
The question, the answer to which I could not find in the official proxy sql documentation. The scheme is as follows.
1)There is one proxy sql host
2)3 mysql hosts have been added to proxy sql, these hosts are not connected to each other in any way (there is no replication between them). These are test hosts with databases. Available for reading/writing.
3)There is one rule that says that all requests should be directed to destination_hostgroup 10
How can I set up an automatic failover to one of the available hosts in case of a failure? Now I’m just doing it by switching hands, throwing an unavailable server into a group of “conditionally” 20, and an available server into a group of 10?
Hi there, usually there is a topology manager installed like Orchestrator to handle the failover and replication between the hosts should be configured. A simpler approach of deciding the primary via read_only value of MySQL can also be used (although this is quite risky). I suggest you take a look at this article: Orchestrator and ProxySQL to understand more about the options, also consider taking a consulting engagement with us for a project like this.
Thank you.
This scheme is not quite suitable.
The fact is that yes, there is such a thing as mysql_replication_hostgroup, but this scheme is suitable for servers that have a master/slave replication mode.
In this case, there is no master/slave mode, between 3 mysql hosts. These hosts work independently of each other, this scheme is not used as productive, only testing.
The orchestrator is powerful, yes, but it is not suitable for this task.
I repeat, the idea is that in the write/read server scheme, only one is always used, the other two do not serve any connections until the first server crashes, and in this case, any available server out of 3 should pick up connections and continue working. Perhaps it all looks as crooked as possible, if you need any clarification, please write.