How to Configure MariaDB Synchronous Replication

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 (although there is experimental support for MyISAM - see the wsrep_replicate_myisam system variable).

Starting with MariaDB 10.1, the wsrep API for Galera Cluster is included by default. This is available as a separate download for MariaDB 10.0 and MariaDB 5.5.

MariaDB has one of the best and easiest solutions to do a Master-Master replication: Galera. Galera uses WSREP, which it is responsible for maintaining all the Database nodes in the cluster in sync. This means, after changing one record on node A, it is just a matter of seconds to see the change on node B.

I will talk in this article how I did this configuration using MariaDB 10.1.x. Remember you can install MariaDB under Centos 6 and 7 by using OKay's repository.

How-to

The first thing you must install the galera plugin. You can do that by typing yum install galera. This will install the file /usr/lib64/galera/libgalera_smm.so which is very important.

After that, what I did is to create a file /etc/my.cnf.d/galera.cnf with the following content.

[mysqld]
wsrep_on=on
# Galera Provider Configuration
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="CLUSTER_NAME"
wsrep_cluster_address=gcomm://NODE_A_IP,NODE_B_IP

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

# Galera Node Configuration
wsrep_node_address=THIS_NODE_IP
wsrep_node_name="SHORT_NODE_NAME"
wsrep_slave_threads=8
wsrep_provider_options="socket.ssl_key=/some/path/cert.pem;socket.ssl_cert=/some/path/cert.pem;socket.ssl_ca=/some/path/CA.crt"

You should substitute the IP addresses and the node names. The wsre_provider_options are to add security; because I do recommend to use public IP's instead of any VPN when doing synchronization, you should have some kind of confidentiality control. To archive this, we use certificates. Take note that certificates must be in PEM format.

Copy this sample on all your nodes.

To start the node, the first node must be started with an option. In this case, first MariaDB node must be started like this: service mysql start --wsrep-new-cluster (for Centos 6). Wait one minute, and then you can start others as usual. For Centos 7, you can not do that, you should do something like: systemctl set-environment _WSREP_NEW_CLUSTER='--wsrep-new-cluster' && systemctl start mariadb

Notes

• Don't use query cache (query_cache_size=0)
• Set BIN LOG Format to ROW (binlog_format=ROW)
• The following ports must be opened between the nodes: 3306/tcp, 4567/tcp, 4567/udp, 4568/tcp, 4444/tcp