Programster: Ubuntu 14.04 - Setup MariaDB Cluster 5.5.x

In this tutorial we are going to set up a MariaDB 5.5.38 cluster on Ubuntu 14.04 LTS servers. I found this to be much easier than setting up a MySQL cluster, and you can keep your InnoDB tables rather than having to switch over to NDBCLUSTER

. One really can think of MariaDB as a "drop in" replacement of the MySQL database system with no need to change your codebase or learn new skills. It's even being led by some of the original creators of MySQL.

I will be using the

editor

command to edit files, so it is useful if you set your favourite editor before starting this tutorial so that your CLI will automatically go to vim or nano based on what you want.

Cluster Limitations

MariaDB Clusters (and MySQL clusters) don't support table locking. MySQL does support it on the node you query, but that's it.

Preparation

For this tutorial, I am going to be setting up and joining three MariaDB nodes to form a cluster. To do this, I am going to clone an Ubuntu 14.04 64bit Server "Base" Virtualbox instance three times, ensuring to generate new MAC addresses each time, and assign different static IPs.

Why Three Nodes?

In order to avoid a split-brain condition, the minimum recommended number of nodes in a cluster is three. This also handles the failure of a node. However feel free to add more, just no fewer.

I will assign the hostnames

db1.programster.com

db2.programster.com

, and

db3.programster.com

appropriately.

I do not own the programster.com domain, hence this only works on my internal LAN on computers that are set to use my internal DNS server.

Steps

Install the software on all three instances.

Maria DB 5.5

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://mirror.jmu.edu/pub/mariadb/repo/5.5/ubuntu precise main'
sudo apt-get update
sudo apt-get install mariadb-galera-server galera -y

# You should already have rsync, but this just ensures you do
sudo apt-get install rsync -y

When prompted, enter an administrative password

Configure The First Node

sudo editor /etc/mysql/conf.d/cluster.cnf

Enter the following details (swapping out db1.programster.com with your own domains or IPs accordingly.

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
query_cache_size=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="my_db_cluster"
wsrep_cluster_address="gcomm://db1.programster.com,db2.programster.com,db3.programster.com"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.1.11"
wsrep_node_name="db1.programster.com"

Configure The Secondary Nodes

Now copy that file (

/etc/mysql/conf.d/cluster.cnf

) onto the other two nodes, changing the last two variables,

wsrep_node_address

, and

wsrep_node_name

accordingly.

Stop All Services

Stop all the services on all of the nodes:

sudo service mysql stop

sudo killall mysqld

Start The Primary Node

To start the cluster the first node needs to know that there are not any other nodes to connect to, so start it with the following command:

sudo service mysql start --wsrep-new-cluster

Add The Other Nodes

Once a cluster node is already up, other nodes can now join the cluster.

sudo service mysql start

Your cluster should now be online and communicating.

Extra Step - Copy The Debian Maintenance Configuration

As we copied the data from the first node to the others, the credentials in

/etc/mysql/debian.cnf

don’t match. This means we will not be able to shutdown mysqld on either of the secondary nodes. To fix this, copy the

/etc/mysql/debian.cnf

file from the primary node into the secondary nodes.

sudo editor /etc/mysql/debian.cnf

Test It's Working

Log into one of the nodes.

mysql -u root -p [password you entered at installation]

Enter the command below and you should see the value 3 for 3 connected nodes.

SHOW STATUS LIKE 'wsrep_cluster_size%';

Ubuntu 14.04 - Setup MariaDB Cluster 5.5.x