Hello list, I'm using haproxy to handle failover between a mysql master and slave. The slave replicates from master and is read-only. I specify both mysql servers in my haproxy configuration, and use the "backup" option on the slave. Applications connect to haproxy instead of mysql directly. Haproxy routes all connections to the master, unless the master is down in which case it routes them all to the slave.
This actually works well enough, but a couple of peculiarities arise from the fact that haproxy doesn't disturb existing connections when servers go up and down: 1) Even if haproxy notices within seconds that the mysql master is down, existing connections remain pointed to the master. I set "timeout server 5m" so that within 5 minutes of inactivity, haproxy will eventually kill the connections, causing clients to reconnect and get routed to the slave. This means that in practice, the failover takes 5 minutes to fully complete. I could reduce this timeout value futher but this does not feel like the ideal solution. 2) If the master eventually comes back, all connections that ended up routing to the slave will stay on the slave indefinitely. The only solution I have for this is to restart mysql on the slave, which kicks everyone off causing them to reconnect and get routed back to the master. This is acceptable if restoring master required some kind of manual maintenance, since I'd already be getting my hands dirty anyway. However, if master disappears and comes back due to brief network outage that resolves itself automatically, it's unfortunate that I'd still have to manually react to this by kicking everyone off the slave. I wonder if both of these could be solved with some options that could make it so all clients are disconnected whenever master (not slave!) goes up or down. Or maybe there are some consequences to this approach that I'm not aware of. Thanks, Justin
