3 ms·

Ask HN: Do you know a good MySQL replication *failover* tutorial?

• eklitzke 4y ago
  I don't have any links but the way I would recommend doing this in production is a replication topology like A -> B -> {C, D, E, ...}. In this notation A is the master database, B is a replica of A, and {C, D, E, ...} are all replicas of B. A and B need to have identical hardware specs (but the replicas can have a different spec if that makes sense). You won't issue any queries to B in normal operation, only possibly during failover. This is important because it lets you know that when a failover happens B will actually be able to handle the load that A was previously handling. If A fails then you just change your app configs/proxies/whatever to use B as the master instead of A. Depending on how you have this set up you may not even need to run any commands on B, you can have it set up to just accept writes, so all you need to do for failover in this scenario is update your app/routing configs to use B as the master instead of A. If B fails then things are still quite easy: you just need to update all the replicas {C, D, E, ...} to use A as their master. In this scenario you're not changing anything in your configs/proxies, you're just running some commands on the replicas to change which host they replicate from. This is always safe because A is by definition always ahead of B, so there are no replication race conditions. There's a small risk here that the additional load on A (because it's now replicating to N downstream hosts instead of just one) will cause problems, but usually replication overhead is low so this won't be an issue if you just make sure you've provisioned things so that A always has a bit of excess bandwidth/CPU capacity in case of a failover (having some excess capacity is just good common sense anyway). 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. 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). I would recommend using some kind of staging cluster or docker/vm environment to make sure you're confident in running all the commands and have playbooks before you implement this in production.
  • toast0 4y ago
    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.