2

My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered through different geographical locations, each containing a MySQL 5.5 server with several DBs, that for the time being are merely slave-replicating from the production server - the objective up until now was just checking the latency and general resilience of MySQL replication.

The plan however is to add a Master-Master replication environment between two servers in two separate locations, and these two instances would handle all the DB writes. The idea wouldn't necessarily imply concurrency; rather the intention is having a single one of the instances handling the writes, and upon a downtime situation using a DNS Failover service to direct the requests to the secondary server. After the primary comes back online, the b-log generated in the meantime in the secondary would be replicated back, and the DNS Failover restored the requests back to the first one.

I am not an experienced administrator, so I'm asking for your own thoughts and experiences. How wrong is this train of thought? What can obviously go wrong? Are there any much better alternatives? Bash away!

Thanks!

0

I'm thinking of doing exactly as you describe using Amazon's Route 53 DNS Failover. So the idea is to setup 2 databases as master-master

db1.domain.com db2.domain.com

Then do a Route53 "failover" set on

db.domain.com -> primary is db1.domain.com => TCP-based health check on port 3306 -> secondary is db2.domain.com => TCP-based health check on port 3306

I have not gotten to this point yet but that is my plan

0

As you may know, the problem with master-master setup is conflicts. If the same row is (for example) deleted and updated at the same time on different masters, this will result in a replication breakage. You mention that you want to do this only for failover, but then this risk remains during failover. For example, you want to upgrade master1. You switch the DNSs. New writes are directed to master2. A long transaction completes on master1, it's replicated to master2, and a conflict happens.

This can also happen if whatever you are using to automate the failover cannot connect to master1 for some seconds, but master1 is still running. It switches. New writes go to master2. Some clients are still connected to master1, they write, conflict happens.

Of course reusing the same connections (for example via a proxy) will increase this risk - but still, I consider it as a good optimization.

Other possible disaster causes include human errors. You do something manually with your data, but you do it on master2 by mistake, et voila' a conflict appears.

This should answer your question "what could go wrong", but there is one more important point: someday you may need to scale more, and not only reads. In most cases, one cannot simply distribute the writes and maybe even add one more master - because of conflicts.

I am not saying that master-master is bad. I use it. But I suggest you to evaluate 2 alternatives, in case you didn't:

  • Cluster (like Galera). Conflicts are not persisted: if 2 simultaneous queries on different servers produce a conflict, one of them will fail. Well, there are edge cases actually. But the worse thing I've seen in edge cases is one single node crashing. You still have 2. Of course clusters have also a lot of disadvantages, like expensive writes and DBAs committing suicide whenever an SST happens. So I'm not suggesting it, I'm just pointing out that I would consider this option.
  • More traditional environment with 1 master and N slaves, plus Orchestrator or similar software. If Orchestrator cannot connect to the master, it checks if slaves can. If they can't, it elects one of them for failover. It keeps into account several aspects, like server version and binary log format. And replication lag of course - but instead of relying on Seconds_behind_master (which is unreliable and has ridiculously low granularity) it checks the last replicated transaction. Also, it uses its own implementation of GTID - which is useful for you because 5.5 doesn't have it, and for lots other people because it's the more error-prone feature ever appeared in a piece of software (not to mention its bugs).

If you eventually decide to use master-master, I suggest to regularly test failover during normal work hours with a non-crazy traffic. So you will avoid bad surprises when the situation is bad.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.