Recently we had to set-up a 3-node Galera Cluster with a Load Balancer in front of it. Because Galera Cluster nodes (mysqld) still reply to TCP requests on port 3306 when they are expelled from the Cluster it is not sufficient to just leave it to the Load Balancer to check the port if a Galera node is properly running or not.
Helsinki, Finland, Uster, Switzerland – February 18, 2012 – Codership, the provider of Galera Cluster for MySQL, and FromDual, a MySQL consulting company, today announced collaboration to offer Galera Cluster technology and related support and consulting services for Galera users all over the world, especially in German speaking countries Germany, Austria and Switzerland (DACH). Galera Cluster is a synchronous, true multi-master replication cluster for MySQL using the well known InnoDB storage engine.
In the following article I have summarized some steps and hints to set-up a MySQL active/passive fail-over Cluster also sometimes called MySQL HA.
With such a set-up you can achieve a 99.99% (4x9) HA set-up for MySQL (52 minutes downtime per year). The same procedure also works for PostgreSQL, Oracle and other database systems running on Linux (DRBD runs on Linux only).
The concept of an active/passive fail-over Cluster is the following:
The FromDual Performance Monitor for MySQL has now a new module for monitoring DRBD. Since more an more of our customers want to run MySQL High Availability solutions based on DRBD we have implemented this module.
We gather all information which are reported with the command:
When I set-up a MySQL HA environment for customers I usually do some final fail over tests after configuring the whole beast.
To check if the application behaves like expected I always run my little test application (test.sh) from the server(s) where the customers application runs. It displays "graphically" how the application behaves and you can show to the customer immediately what is going on...
Make sure, that you point it to the virtual IP (VIP) or the load balancer (LB).
Electing a slave as new master and aligning the other slaves to the new master
In a simple MySQL Replication set-up you have high-availability (HA) on the read side (r). But for the master which covers all the writes (w) and the time critical read (rt) there is no HA implemented. For some situations this can be OK. For example if you have rarely writes or if you can wait until a new Master is set up.
But in other cases you need a fast fail-over to a new master.
MySQL, InnoDB and Oracle are registered trademarks of Oracle Corp. MariaDB is a trademark of Monty Program AB.
All other trademarks are property of their respective owners. Other product or company names mentioned may be trademarks or trade names of their respective owner.