Thanks in advance.

I have always worked with SQL Server and never really had any exposure to MySQL. I now have to determine the preferred setup for MySQL when there are four servers involved. Ideally, the solution needs to allow for Master to Master replication/automatic failover with each Master replicated to its own Slave. The Slave does not need to be promoted to a Master in any scenario, but it does need to be updated from its Master.

In .Net, and with SQL Server, it was always easy to set up mirroring/failover with a witness and then simply incorporating the "failover" server directly in the configuration file's connection string. MySQL seems to be much more involved.

I've read a good bit about MySQL replication and the potential pitfalls it may include and I would like to avoid those if possible. I've also started looking into MySQL clustering, but I'm not sure that is the best solution here since the servers are setup to be separate from each other in a primary/secondary set (with the Master in each being replicated).

Ultimately, it is just Primary Master <-> Secondary Master and then a Slave behind each Master. If there is a better solution such as using all four servers (even though separated by Primary/Secondary domains) with clustering etc. please reply also - just trying to determine the best setup.

I should note that this is for installation on Windows Server 2012 R2.

It is difficult to find this setup anywhere through searching!

Any help is greatly appreciated!

  • Yes, you can change to MariaDB Database. it is binary replacement and there you can use Galera Cluster. Galera is a Multi Master MySQL Database. So in this construct is NO Slave, You can read and write to each node and there are only a few limitations. And if you use MaxScale (DB Proxy) also from MariaDB they can do automatic failover to all active Nodes. You can also add direcly an empty node. see: mariadb.com/kb/en/mariadb/what-is-mariadb-galera-cluster
    – Bernd Buffen
    Commented May 12, 2016 at 19:16
  • Hi Bernd. Thank you for the reply. I meant to mention that this is for installation on Windows Server 2012 R2 and I do not believe MariaDB is supported on Windows (at least to my knowledge). This is one of the limitations I'm facing - or at least it feels like a big limitation! Thanks
    – S9Desi
    Commented May 12, 2016 at 19:34
  • sorry, this looks like a big limitation. i know that in MariaDB version 10.1.x all Galera included and this version is also download for Windows, but i have never seen Galera running on a Windows machine. Sorry for my answer
    – Bernd Buffen
    Commented May 12, 2016 at 19:46
  • No problem, thank you for your reply. I added the information to the post and I appreciate you looking at it!
    – S9Desi
    Commented May 12, 2016 at 19:51

1 Answer 1

  • MHA orchestrates the reconnection of slaves as needed.

  • Orchestrator orchestrates the reconnection of slaves as needed.

  • Galera (PXC, MariaDB) eliminates the need for failover by having all nodes writable, plus automatic repair of nodes that were offline.

  • Fabric is another way.

  • Doing the task yourself is possible, but complex, risky, etc, etc.

  • Thank you for the help Rick - I have a much better understanding of the options now.
    – S9Desi
    Commented May 24, 2016 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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