MySQL – Running Database Server Cluster in Docker Containers

clusteringgaleraMySQLphysical-designxtradb-cluster

I'm currently looking into setting up a database cluster (MySQL Galera or Percona) but I have only 2 machines.

However, according to many sources I've read (This article from the Mariadb KB), the minimal node size in the cluster should be 3, to avoid split brain conditions.

In order to avoid a split-brain condition, the minimum recommended number of nodes in a cluster is 3. Blocking state transfer is yet another reason to require a minimum of 3 nodes in order to enjoy service availability in case one of the members fails and needs to be restarted. While two of the members will be engaged in state transfer, the remaining member(s) will be able to keep on serving client requests.

So I'm wondering if I can run maybe 3 (or more) docker containers, each running a database server, on both machines. If one of the machine fails, there are still 3 nodes in the cluster, so no split brain problems.

It feels like cheating, but I cannot think of a reason not to do it.

Does this setup make sense ?

Best Answer

Galera (for MySQL, Percona XtraDB Cluster or MariaDB Cluster, they are essentially the same but from different vendors and base mysql version) can work perfectly with 2 machines, and it is a very common setup for substituting standard MySQL replication.

Requiring 3 nodes is not a requirement of Galera, but of any cluster that cares about data consistency over availability. In other words, it is needed in order to provide availability and data integrity in the case of only one node suffering from network partition (causing a "split brain" on the cluster). We need an odd number of nodes and 1 node is not a cluster :-). You probably knew that, but I wanted to clarify this for any people reading this answer.

Given that the only reason to have 3 nodes is for availability in case of a node failure/network failure, the recommended way to setup a 2 node cluster with network split protection is to setup a Galera Arbitrator which is essentially an emulation of a MySQL node, but without storing any local data. You can install that anywhere, although you must be careful because if still may influence the performance of the cluster if it has network latency problems.

Running several instances on the same physical node is useless (docker is not needed, by the way, you can do that by just changing the local configuration), as if you absolutely can only run things on 2 machines, you better disable the automatic shutdown on one of the nodes and manage the failover manually. The 3 limitation is just for data consistency, there is nothing physical or on the protocol requiring a certain number of nodes.

You can read more information about it on the official documentation.

METHOD #1

You could recreate the table on the PXC (Percona XtraDB Cluster) that has the table missing

Step01) Do SHOW CREATE TABLE reportingdb.norep_zonebannertmp_bk\G on Node that still has table
Step 02) Copy and Paste that Query into /root/Fix.sql
Step 03) Change CREATE TABLE to CREATE TABLE IF NOT EXISTS in /root/Fix.sql
Step 04) Execute /root/Fix.sql on the PXC node that does not have the table

METHOD #2

You may have to perform a full SST in the event other tables are out of sync. The most aggressive way to do this is the following:

cd /var/lib/mysql
service mysql stop
rm -f /var/lib/mysql/galera.cache /var/lib/mysql/grastate.dat
service mysql start

Since there is no /var/lib/mysql/galera.cache, IST cannot be done. SST would be initiated.

Mysql – node unable to join a galera cluster; it fails during state transfer

Galera cluster is a quorum-based system, Galera cluster is subject to split-brain condition when quorum algorithm fails to select a primary component. This can happen, for example, in a cluster without the backup switch if the main switch fails. But the most likely split brain situation is when single node fails in a two-node cluster. Thus it is strongly advised that the minimum Galera cluster configuration is 3 nodes. In the state transfer section below we will consider one more reason why 3 is the minimum recommended number of nodes.

So, make sure that you have

wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 #where node1=192.168.1.1 and node2=192.168.1.2 etc.

on all nodes and restart one-by-one all nodes after this change

A typical my.cnf for Galera cluster should be like this: http://www.codership.com/wiki/doku.php?id=mysql_galera_configuration

I hope this information helps

Best Answer

Related Solutions

Mysql – Does this make the XtraDB Cluster hang

METHOD #1

METHOD #2

Mysql – node unable to join a galera cluster; it fails during state transfer

Related Question