You raise two issues.
MySQL Topology In order (from OK to Best)
- Primary -> Replica -- A "failover" can be achieved, but it takes manual effort, hence time.
- Primary <=> Primary -- This is only slightly more complex to set up, while providing "instant" use of the other server.
- Cluster of at least 3 servers. This further automates failover. See "InnoDB CLuster" (MySQL 8) or "Galera" (included with MariaDB).
Geography -- Be aware that even a data center can fail. For example, how much of, say Florida, can be taken offline by a single hurricane?
Be aware of the "split-brain" scenario. This is where you have only two servers and both are alive and well, but the network is down. They can't tell and you can't tell what the situation is. If each thinks that it is the only server alive and continues to take writes, you will end up with a mess. So, instead, you have to assume the entire system is down.
Bottom line -- You need at least 3 servers, physically separated.
Proxy
There is still the problem of clients knowing what part of the database system is alive (for reading and/or writing). When only "reading" is important, many topologies with any number of Replicas suffices -- and provide 'unlimited' scaling. "Writes" are where the real challenges are.
There are several 3rd party products that are good at noticing that one server is down and "doing the right thing" of rerouting to some other server. Research them.
Coding
When a failure occurs, your code is likely to get some kind of error. You must check for errors, some are not self-repairing. And most network errors take some time to notice.
