How to deploy a MariaDB Galera cluster on Ubuntu 14.04

MariaDB Galera cluster on Ubuntu 14.04

We need at least 3 hosts running together with Ubuntu 14.04 Operating system to form a reliable cluster. The following is the hosts list that we had setup for this article, where we will deploy the MariaDB Galera cluster:

ubuntu-node1 172.25.10.21
ubuntu-node2 172.25.10.22
ubuntu-node3 172.25.10.23

Now we will install the its required packages rsync, galera and mariadb-galera-server that need to be installed on all the three nodes.

Adding Repositories and Packages

Now we will start by adding the repository for Ubuntu so, first add the key files for the MariaDB repository to build the trust between the maintainers of repositories and that we can install the required packages.

Installing the GPG key

Run the following command of all the three hosts to install its GPG key as follow.

root@ubuntu-node1:~# apt-get install python-software-properties
root@ubuntu-node2:~# apt-get install python-software-properties
root@ubuntu-node3:~# apt-get install python-software-properties

Adding APT Repository

Now to add its APT repository have to execute the below commands.

root@ubuntu-node1:~# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
root@ubuntu-node2:~# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
root@ubuntu-node3:~# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Adding Ubuntu Repository

Now can add the Ubuntu repository as per required whether to use its repository as we have the trusted key in the database, let’s add the actual repository using following command on all the nodes.

root@ubuntu-node1:~# add-apt-repository 'deb http://mirror.jmu.edu/pub/mariadb/repo/5.6/ubuntu trusty main'
root@ubuntu-node2:~# add-apt-repository 'deb http://mirror.jmu.edu/pub/mariadb/repo/5.6/ubuntu trusty main'
root@ubuntu-node3:~# add-apt-repository 'deb http://mirror.jmu.edu/pub/mariadb/repo/5.6/ubuntu trusty main'

Update OS with Latest Repo

To get the latest repositories run the update command on the three nodes we are working on.

root@ubuntu-node1:~# apt-get update
root@ubuntu-node2:~# apt-get update
root@ubuntu-node3:~# apt-get update

Installing MariaDB Cluster, Galera and Rsync

Let’s install the MariaDB and its related packages on each of the node with following command as:

root@ubuntu-node1:~# apt-get install -y galera  mariadb-galera-server-5.5 mariadb-client-5.5 libmariadbclient18 mariadb-client-core-5.5 rsync netcat-openbsd
root@ubuntu-node2:~# apt-get install -y galera  mariadb-galera-server-5.5 mariadb-client-5.5 libmariadbclient18 mariadb-client-core-5.5 rsync netcat-openbsd
root@ubuntu-node3:~# apt-get install -y galera  mariadb-galera-server-5.5 mariadb-client-5.5 libmariadbclient18 mariadb-client-core-5.5 rsync netcat-openbsd

During the installation process you will be asked to configure the root password for the MariaDB, so make sure that you configured the same root password on all the three nodes.

Once the installations of these packages are done, you will get a MariaDB server on each one of your three nodes but they aren’t yet configured.

Configuring MariaDB Cluster

Let’s proceed with the configurations changes for the MariaDB Galera Cluster setup.

MySQL Settings

First of all open the my.cnf file and comment the following lines on all the three nodes.

root@ubuntu-nodeX:~# vim /etc/mysql/my.cnf
#bind-address           = 127.0.0.1
#default_storage_engine = InnoDB
#query_cache_limit              = 128K
#query_cache_size               = 64M

MariaDB Settings

Now add following lines for wsrep configuration options in my.cnf file under [mysqld] directive as shown below.

root@ubuntu-nodeX:~# vim /etc/mysql/my.cnf
[mysqld]
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1

VSRep Providers Configurations

Here we will configure the vsrep configurations on each node under the [mysqld] directory by adding the following lines in /etc/mysql/my.cnf file on each node with their specific hostnames, root password and IP address.

Configurations for ubuntu-node1

[mysqld]
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://ubuntu-node1
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="ubuntu-node1"
wsrep_node_name="ubuntu-node1"
wsrep_sst_auth="root:root123"
wsrep_node_incoming_address=172.25.10.21
wsrep_sst_receive_address=172.25.10.21
wsrep_slave_threads=16

Configurations For ubuntu-node2

[mysqld]
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://ubuntu-node1
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="ubuntu-node2"
wsrep_node_name="ubuntu-node2"
wsrep_sst_auth="root:root123"
wsrep_node_incoming_address=172.25.10.22
wsrep_sst_receive_address=172.25.10.22
wsrep_slave_threads=16

Configurations For ubuntu-node3

[mysqld]
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://ubuntu-node1
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="ubuntu-node3"
wsrep_node_name="ubuntu-node3"
wsrep_sst_auth="root:root123"
wsrep_node_incoming_address=172.25.10.23
wsrep_sst_receive_address=172.25.10.23
wsrep_slave_threads=16

Restart MySQL Services

After configuring the mysql and wsrep parameters we need to restart its service on all the node.

root@ubuntu-node1:~# service mysql restart
root@ubuntu-node2:~# service mysql restart
root@ubuntu-node3:~# service mysql restart

Testing MariaDB Galera Cluster

To confirm the status of MariaDB cluster VSRep, let’s login to the MariaDB console and execute the following command to check the output of give command.

root@ubuntu-node1:~# mysql -u root –p
MariaDB [(none)]> show status like 'wsrep_%';

You can also confirm the status of your running cluster and its replication by running the below command on each of your node. You will see the cluster size in response to the output of that command.

root@ubuntu-node1:~# mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
root@ubuntu-node2:~# mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
root@ubuntu-node3:~# mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'

www.hackthesec.co.in