Every online system use database to store its information. Keeping the data safe and secure is top priority for such system. There are a lot of possible solutions to achieve that nowadays. One of the most widely used solution for relational databases is Master-slave replication.
In master-slave replication, the master host is the one that is handling all data manipulations operations in the databases and then it distributes the data to number of different hosts. If something happens to the master host at any time, each of the slaves is able to takeover at any time and become the new master.
However, it will be far better if each of the hosts could be able to perform data manipulations operations and then sync the data with all other hosts. This setup (known as master-master) is a bit more complicated and restrictive. MariaDB version for Ubuntu 12.04 doesn’t have stable version in the default repositories, but there is set of patches for MariaDB, known as “Galera”, that has master-master replication implemented.
We will be using the minimum recommend number of nodes for a production system which is three, so we will have three nodes in our cluster. Since both MariaDB and Galera packages are not available in the default Ubuntu repositories, the first step is to add their repositories on every Ubuntu 12.04 instance in our cluster. In order to add the repositories, first we install another package:
# sudo apt-get install python-software-properties
Once it is installed, we can add the keys for the repository and the actual repositories then install MariaDB and Galera patches. During the installation we will be asked to set MariaDB admin password.
# 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 rsync mariadb-galera-server galera
Now that we have all the required packages on all three servers, it is time to start the cluster configuration. Every node should have same configuration, so we will create the configuration on one of the nodes and then copy it to the other two servers. We will create our file inside /etc/mysql/conf.d. MariaDB is monitoring this folder and processing all configuration files in it on startup. We will save our file as /etc/mysql/conf.d/cluster.cnf with the following content:
[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=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="our_cluster"
wsrep_cluster_address="gcomm://**node1_ip,node2_ip,node3_ip**"
# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass
# Galera Node Configuration
wsrep_node_address="**local_node_ip**"
wsrep_node_name="**local_node_name**"
The [mysqld] section contains some basic MySQL settings that will allow MySQL to work properly. The second section, “Galera Provider Configuration” is section that is important for providing WriteSet replication API. There are more options available for this section, but it will work fine with the line that we have in it.
The third section, “Galera Cluster Configuration”, is used to define the cluster. In this section, we should set the name for our cluster and the IP addresses for every of the nodes in the cluster. In the “Galera synchronization configuration”, we set the method that is used for the synchronization. In this case we are using rsync. And, finally in that last section, we should enter the IP of the machine where the file will be saved, as well as the machine’s name.
Once all is set, we should copy this file on same location on the other two servers. We must change the parameters in the last section only, and match the configuration on the server where the file resides.
Before we start and test the cluster, we must fix one thing first. During MariaDB installation on Ubuntu, special maintenance user is created. This used is created with random login credentials, so they are different on each server. That means that each maintenance user will be able to access its local node database only. In order to fix this, we should get the maintenance file for one of the servers and copy it to the other two. We don’t have to copy the entire file, the [client] section is enough.
[client]
host = localhost
user = debian-sys-maint
password = 03P8rdlknkXr1upf
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = 03P8rdlknkXr1upf
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
We should open the file /etc/mysql/debian.cnf on every server, and make sure that the lines above are same on each of them. After that, we are ready to start the cluster. First, we should make sure that the service is not running, by executing:
# sudo service mysql stop
Then, we start the service on the first node:
# sudo service mysql start --wsrep-new-cluster
On the other two nodes, the same command will work, but it will suffice to start the servers with:
# sudo service mysql start
This should conclude everything and our new three node cluster should be up and running. The only thing left for us to do is to test it. The best way to test is it to connect to all three nodes, using command line or our favorite MySQL client manager software. We can start with creating database with some tables on the first nodes, then insert some new data from the second node, then edit the data from the third node. Regardless where we made the changes, the data should be up to date on all three servers in the cluster almost immediately after making the changes on any node. The point of our test is to prove that we can write data on any of the nodes and have it available everywhere.
The setup that we just did, will help a lot with application with high write demands in production environment. Instead having one server for writing we have three. There are also some techniques that allow adding additional nodes without any interruption on the existing ones. Master-Master replication is possible in the standard MySQL server (without MariaDB and Galera patches), but it is far more complicated to setup and configure. MariaDB and Galera patches make it simple and easy to use.