Disclaimer: my MySQL knowledge is oooooold, some things may have changed since 5.x. But I'd bet the basics remain similar.

Adding on to this design:

A and B should be in dual master replication, so that when you failover to B, A will still get the writes.

You want to make sure that all of your normal database users are not administrators, and set read-only mode on on all your database servers in the config file. So that when a server (including A and B) comes up, any writes directed to it will fail. On intentional failover, set A to read only with administrative commands, then set B to read-write. Your service discovery layer should check both A and B for read-only status and direct writes to one of them only if it gets exactly one response from a server that is not in read only mode.

I strongly prefer doing manual failover for unexpected failures, because you can avoid having to do split-brain reconciliation; in that scenario, you writes are unavailable when server A goes offline, until an operator can verify A is not going to come back in read-write mode (mysqld crashed, OS crashed, power lost) and is not merely network partitioned; once the operator is confident of A's status, they can set B to read-write, and availability continues (transactions in flight from A to B during the outage may be lost, or may come back and cause a small amount of headache upon A's return).

If you prefer automated failover, you should limit to one failover without operator intervention. Failover is messy, and you definitely don't want to get into a situation where the service is flapping and the state diverges. But maybe you can build something where A will deterministically go read only if it can't contact a majority of health detectors for a certain amount of time, and B will deterministically go read-write if it can contact a majority of health detectors that can't contact A.

Roughly half of your read-only replicas should use A as the master and roughly half should use B. Your service discovery for read-only replicas should somehow try to check that replication is up to date before directing read-only traffic to a replica; but you may not want A or B in that group, as overloading them with read-only queries when replication is behind may be problematic.

> If you have a LOT of database hosts (say, 10+) then in this design pattern at some point you're going to have a problem where there are so many replicas replicating from B that it will struggle to keep up with the bandwidth/load from replication. If you get to this point you can have some more complicated tiered fanout architecture where you have replicas of replicas.

Load from replication is usually pretty low, it's just tailing the replication log and/or sending older logs in case a replica goes offline for a while. In my admittedly pretty dated experience, I'd run out of CPU for write queries before getting anywhere close to bandwidth limits for the replication streams. I wouldn't worry about needing to do fanout, but it's possible if you need it, I guess. If you do have a problem with bandwidth of the replication stream, I'd wager you'd have trouble with replication falling behind because the master can run writes concurrently, and the replicas run queries one at a time.

> Definitely make sure you practice failover from time to time (probably once when you set things up, and then once a year or so after that).

Once a year may not be often enough, maybe 2-4 times a year would be better. If you frequently upgrade mysqld and/or your os, maybe you have the restart volume organically anyway.