4 ms·

Making Database Maintenance Fun: mysql_role_swap

22 points by chinmoy 14y ago

BryantD 14y ago
Always cool to see how people handle automation. I wonder if they considered implementing this within another framework? Seems like this would make an excellent chef recipe.
- themcgruff 14y ago
  We write out the cluster config and update the script using Chef. In an emergency situation, chef-client is a wee bit slow ;) Also we like having the script standalone so there's just one moving part.
  - BryantD 14y ago
    Heh, that's fair enough. More moving parts is always a problem. Thanks for sharing!
falcolas 14y ago
What does this do that a dedicated failover tool like MySQL High Availability [1] does not do? [1] https://code.google.com/p/mysql-master-ha/ https://code.google.com/p/mysql-master-ha/
- jzawodn 14y ago
  I wonder that as well... Hmm.
- themcgruff 14y ago
  We talked with some popular "mysql consultants" about things like mysql-master-ha and there was always a lot of hand waving murmuring about how existing solutions were incomplete / had lots of bugs / were not to be trusted in production. It looks to me like they do mostly the same thing. We wrote our script in Ruby because it's what we know and have the most expertise with, which makes it easy for us to debug. mysql_role_swap is not black magic, it doesn't do automatic failover, it doesn't run in the background / daemonize itself. It fails gracefully in a way we find predictable and useful.
  - falcolas 14y ago
    The problem with rolling your own is that you're going to make mistakes. MHA, PRM, MMM, Tungsten, DRBD - all were written by people who know a lot about MySQL database failover, and yes, they still have problems. The reason they have problems is because doing failover properly under load is a hard problem to solve. As for them not production ready? Sounds like a line being fed by someone who wants to sell their own solution. I have personally seen all of these solutions in use in production systems. Some of the potential problems I note with your solution (and if you haven't been bitten by these yet, you will at some point): 1) You need to break existing connections to the old master. This is the single biggest cause of split brain that I've seen in these situations. SET GLOBAL read_only=1 only affects new connections. 2) You should verify that the slave has read and applied all of the logs from the master, to ensure that no data is lost when the slave is stopped. 3) You really should capture the binlog file & position from the slave after stopping the slave, to avoid getting inconsistant data in your two databases. 4) You may want to ensure that the arp change actually took effect on your proxy machine: prior to unlocking the proxy, make a connection from the proxy server to the VIP & verify that the server_id matches the slave's server_id. MHA has a number of problems when considered as an automated failover solution. However, it's not required to run in an automated fashion to do manual failovers like this, and is quite good at doing them correctly; it encapsulates the lessons learned from its predecessors, and the knowledge of the engineers who have been bitten by doing a failover under load incorrectly. There are some really great tools out there, and not using them (or learning from them) is going to cause your DBAs hours of hard work to get your DBs back in operational order when something goes wrong. Not if.
SparrowOS 14y ago
Go to http://www.random.org http://www.random.org get a number from 1 to 100,000 use as line number in Bible http://www.sparrowos.com/files/BIBLE.TXT http://www.sparrowos.com/files/BIBLE.TXT God says... 39:20 And they made two other golden rings, and put them on the two sides of the ephod underneath, toward the forepart of it, over against the other coupling thereof, above the curious girdle of the ephod. 39:21 And they did bind the breastplate by his rings unto the rings of the ephod with a lace of blue, that it might be above the curious girdle of the ephod, and that the breastplate might not be loosed from the ephod; as the LORD commanded Moses. 39:22 And he made the robe of the ephod of woven work, all of blue. 39:23 And there was an hole in the midst of the robe, as the hole of an habergeon, with a band round about the hole, that it should not rend. 39:24 And they made upon the hems of the robe pomegranates of blue, and purple, and scarlet, and twined linen. ---- all my underwear got tears. You fucken do that?

22 points by chinmoy 14y ago

Always cool to see how people handle automation. I wonder if they considered implementing this within another framework? Seems like this would make an excellent chef recipe.

themcgruff 14y ago
We write out the cluster config and update the script using Chef. In an emergency situation, chef-client is a wee bit slow ;) Also we like having the script standalone so there's just one moving part.
- BryantD 14y ago
  Heh, that's fair enough. More moving parts is always a problem. Thanks for sharing!

falcolas 14y ago

What does this do that a dedicated failover tool like MySQL High Availability [1] does not do? [1] https://code.google.com/p/mysql-master-ha/ https://code.google.com/p/mysql-master-ha/

jzawodn 14y ago
I wonder that as well... Hmm.
themcgruff 14y ago
We talked with some popular "mysql consultants" about things like mysql-master-ha and there was always a lot of hand waving murmuring about how existing solutions were incomplete / had lots of bugs / were not to be trusted in production. It looks to me like they do mostly the same thing. We wrote our script in Ruby because it's what we know and have the most expertise with, which makes it easy for us to debug. mysql_role_swap is not black magic, it doesn't do automatic failover, it doesn't run in the background / daemonize itself. It fails gracefully in a way we find predictable and useful.
- falcolas 14y ago
  The problem with rolling your own is that you're going to make mistakes. MHA, PRM, MMM, Tungsten, DRBD - all were written by people who know a lot about MySQL database failover, and yes, they still have problems. The reason they have problems is because doing failover properly under load is a hard problem to solve. As for them not production ready? Sounds like a line being fed by someone who wants to sell their own solution. I have personally seen all of these solutions in use in production systems. Some of the potential problems I note with your solution (and if you haven't been bitten by these yet, you will at some point): 1) You need to break existing connections to the old master. This is the single biggest cause of split brain that I've seen in these situations. SET GLOBAL read_only=1 only affects new connections. 2) You should verify that the slave has read and applied all of the logs from the master, to ensure that no data is lost when the slave is stopped. 3) You really should capture the binlog file & position from the slave after stopping the slave, to avoid getting inconsistant data in your two databases. 4) You may want to ensure that the arp change actually took effect on your proxy machine: prior to unlocking the proxy, make a connection from the proxy server to the VIP & verify that the server_id matches the slave's server_id. MHA has a number of problems when considered as an automated failover solution. However, it's not required to run in an automated fashion to do manual failovers like this, and is quite good at doing them correctly; it encapsulates the lessons learned from its predecessors, and the knowledge of the engineers who have been bitten by doing a failover under load incorrectly. There are some really great tools out there, and not using them (or learning from them) is going to cause your DBAs hours of hard work to get your DBs back in operational order when something goes wrong. Not if.

SparrowOS 14y ago

Go to http://www.random.org http://www.random.org get a number from 1 to 100,000 use as line number in Bible http://www.sparrowos.com/files/BIBLE.TXT http://www.sparrowos.com/files/BIBLE.TXT God says... 39:20 And they made two other golden rings, and put them on the two sides of the ephod underneath, toward the forepart of it, over against the other coupling thereof, above the curious girdle of the ephod. 39:21 And they did bind the breastplate by his rings unto the rings of the ephod with a lace of blue, that it might be above the curious girdle of the ephod, and that the breastplate might not be loosed from the ephod; as the LORD commanded Moses. 39:22 And he made the robe of the ephod of woven work, all of blue. 39:23 And there was an hole in the midst of the robe, as the hole of an habergeon, with a band round about the hole, that it should not rend. 39:24 And they made upon the hems of the robe pomegranates of blue, and purple, and scarlet, and twined linen. ---- all my underwear got tears. You fucken do that?