MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Being a fork of a leading open source software system, it is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle. Contributors are required to share their copyright with the MariaDB Foundation.
MariaDB intends to maintain high compatibility with MySQL, ensuring a “drop-in” replacement capability with library binary equivalency and exact matching with MySQL APIs and commands. It includes the XtraDBstorage engine for replacing InnoDB, as well as a new storage engine, Aria, that intends to be both a transactional and non-transactional engine perhaps even included in future versions of MySQL.
About MariaDB Galera Cluster
The MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines. In this article we will show you how you can install this tool on a Debian or Ubuntu system and how we can make its configuration.
From its features, MariaDB Galera Cluster is characterized by:
- Its synchronous replication
- An active-active multi-master topology
- Automatic membership control, failed nodes drop from the cluster
- An automatic node for joining
- A true parallel replication, on row level
- A direct client connections
Due to those features, with MariaDB Galera Cluster there is no slave lag, no lost transactions, there are also read and write scalability and smaller clients latencies.
For this article, we assume that we have 3 nodes: node1, node2 and node3, we have also three IP addresses: 172.18.8.7, 172.18.8.8, 172.18.8.9 and we will install the following packages: rsync, galera and mariadb-galera-server.
Steps to follow
We will start by adding the repository, so for Debian we will do as below:
apt-get install python-software-properties apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/debian wheezy main' apt-get update
apt-get install python-software-properties apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/ubuntu precise main' apt-get update
There aren’t a big difference between the two blocs of commands.
And now we will install the required packages:
DEBIAN_FRONTEND=noninteractive apt-get install -y rsync galera mariadb-galera-server
After making the installation of those packages, you will get a MariaDB server on each one of our three nodes but they aren’t yet configured.
So to make the configuration, we just need to change the 3 existed IP addresses by our IP’s. We will use the following configuration file that will be distributed for our 3 nodes.
[mysqld] #settings of mysql binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 query_cache_size=0 query_cache_type=0 bind-address=0.0.0.0 #settings of the Galera wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_name="my_wsrep_cluster" wsrep_cluster_address="gcomm://172.18.8.7,172.18.8.8,172.18.8.9" wsrep_sst_method=rsync
It is possible also to change the name of the cluster by changing the value of the wserp_cluster_name. With
wsrep_cluster_address you can check the IP addresses of your setup.
The wsrep_sst_method tells you which method to use for syncronising the nodes, there are also other methods such as mysqldump and xtrabackup. In this article, we use rsync which is the easiest one, and if you prefer to use other method don’t forget to install the corresponding package.
Now we will start the use of our Galera cluster, so we wil stop the “mysqld” on all the three nodes by using the following commands:
node1# service mysql stop node2# service mysql stop node3# service mysql stop
As we previously said the configuration file galera.cnf was distributed for all of those nodes, now we will use the following command to initialise the cluster:
node1# service mysql start --wsrep-new-cluster
And to check its functionality, you have to use the following command:
node1# mysql -u root -e 'Select your value as "cluster size" where variable name="wsrep_cluster_size"'
+--------------+ | cluster size | +--------------+ | 1 | +--------------+
So if you got the same text as the above one, everything is good and the Cluster is already installed.
node2# service mysql start [ ok ] Starting MariaDB database server: mysqld . . . . . . . . . .. [info] Checking for corrupt, not cleanly closed and upgrade needing tables.. node1:/home/debian# ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Don’t worry about this error, you can ignore it on this step.
Now we will use the following command to check the second node:
mysql -u root -e 'Select your value as "cluster size" where variable name="wsrep_cluster_size"'
+--------------+ | cluster size | +--------------+ | 2 | +--------------+
Even the second node is okay, everything is good. So let’s check the third node:
MariaDB Galera Cluster node3# mysql -u root -e 'Select your value as "cluster size" where variable name="wsrep_cluster_size"'
+--------------+ | cluster size | +--------------+ | 3 | +--------------+
Everything is okay, now we have a MariaDB Galera Cluster installed and working well.
Now we will try to solve the found errors with the node2 and the node3. As we know Debian/Ubuntu uses a special user (‘debian-sys-maint’@’localhost’) in their init script and the credentials for that user are stored in /etc/mysql/debian.cnf directory. This user is important to check the start-up of the MySQL and the shutting-down of the mysqld. Which we can ignore since there is the SIGTERM which can shutdown the mysqld.
But since we copied the data from the first node (node1) to the other ones, the credentials in
/etc/mysql/debian.cnf will not be the same on
node3. So we will not be able to shutdown mysqld on either of these nodes. You can see the following verification that justify our affirmation:
node2# service mysql stop [FAIL] Stopping MariaDB database server: mysqld failed!
So it is important to fix it by copying /etc/mysql/debian.cnf from the first node (
node1) to the other nodes that’s the data and configuration files will be the same. After making this task we will be able to shutdown the mysqld on the second and the third node and we received the following result:
node2# service mysql stop [ ok ] Stopping MariaDB database server: mysqld.
Now you will have an installed and configured MariaDB Galera cluster on your system. Don’t forget to make the required changes and if you have any further question you can leave a comment.