MariaDB Galera Cluster Installation

March 8, 2018

INTRODUCTION:

 

In the last period we encounter a growing interest in MariaDB products (Galera Cluster, MaxScale for example) among our customers. Due to a number of projects related to this platform I would like to concentrate some prominent advantages of the platform and introduce a simple installation of Galera Cluster.

 

Galera is a new kind of clustering engine which, unlike traditional MySQL master-slave replication, provides master-master replication and thus enables a new kind of scalability architecture for MariaDB. Despite that Galera development already started in 2007, it has never been a part of the official Oracle MySQL version while both Percona and MariaDB flavors have shipped a Galera based cluster version for years.

 

Additional, unmatched features in MariaDB provide better monitoring through the introduction of microsecond precision and extended user statistics. MariaDB also enhances the KILL command to allow you to kill all queries for a user (KILL USER username) or to kill a query ID (KILL QUERY ID query_id). MariaDB also switched to Perl-compatible regular expressions (PCRE), which offer more powerful and precise queries than standard MySQL regex support.


In addition to more features, MariaDB has also applied a number of query optimizations for queries connected with disk access, join operations, subqueries, derived tables and views, execution control, and even explain statements.


MariaDB in particular excels as the amount of storage engines and other plugins it ships with: Connect and Cassandra storage engines for NoSQL backends or rolling migrations from legacy databases, Spider for sharding, TokuDB with fractal indexes etc. These plugins are available for MySQL as well via 3rd parties, but in MariaDB they are part of the official release, which guarantees that the plugins are well integrated and easy to use.

 

Here are some common use cases for Galera replication as described on the MariaDB internet site:

GALERA CLUSTER INSTALLATION:

 

If you have previous MariaDB package you must remove it:

# yum remove MariaDB-server 
# yum remove MariaDB* 

 

If your system does not have swap space available or if the allotted space is insufficient for your needs, you can fix this by creating swap files. 

1.       Create an empty file on your disk, set the file size to whatever size you require. 

# fallocate -l 4096M /swapfile

 

Alternatively, you can manage the same using dd.

 

# dd if=/dev/zero of=/swapfile bs=1M count=4096

 

2.       Secure the swap file. 

# chmod 600 /swapfile 

 

This sets the file permissions so that only the root user can read and write to the file. No other user or group member can access it. You can view the results with ls: 

# ls -a / | grep swapfile 
 

3.       Format the swap file. 

# mkswap /swapfile

 

4.       Activate the swap file. 

# swapon /swapfile 

 

5.       Using your preferred text editor, update the /etc/fstab file to include the swap file by adding

the following line to the bottom: 

/swapfile none swap defaults 0 0
 
After you save the /etc/fstab file, you can see the results with swapon

# swapon –summary 
 

To disable SELinux for mysql run the following command: 

# semanage permissive -a mysqld_t

 

Update the firewall settings on each node so that they can communicate with the cluster. How you do this varies depending upon your distribution and the particular firewall software that you use. 

# iptables --append INPUT --protocol tcp --source 192.168.0.120 --jump ACCEPT 
# iptables --apend INPUT --protocol tcp --source 192.168.0.121 --jump ACCEPT 
# iptables --append INPUT --protocol tcp --source 192.168.0.122 --jump ACCEPT 

 

For systems that use systemd, you need to save the current packet filtering rules to the path the iptables unit reads from when it starts. This path can vary by distribution, but you can normally find it in the /etc directory. For example: 

  • /etc/sysconfig/iptables 

  • /etc/iptables/iptables.rules 

Once you find where your system stores the rules file, use iptables-save to update the file:

# iptables-save > /etc/sysconfig/iptables

 

When your system reboots, it now reads this file as the default packet filtering rules.

# reboot

 

Another alternative is to stop firewalld:

# systemctl stop firewalld

# systemctl disable firewalld

 

Create repository file: 

# cd /etc/yum.repos.d/ 
# vim MariaDB.repo 

 

Insert these line to a file:

# MariaDB 10.1 CentOS repository list 
# http://downloads.mariadb.org/mariadb/repositories/ 
[mariadb] 
name = MariaDB 
baseurl = http://yum.mariadb.org/10.1/centos7-amd64 
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 
gpgcheck=1 

 

Before file’s transferring it’s preferably to backup a previous my.cnf file: 

# mv /etc/my.cnf /etc/my.cnf.backup 

 

Transfer my.cnf file acroos other nodes (rsync overwrite the file (!)): 

# rsync /etc/yum.repos.d/MariaDB.repo root@192.168.0.121:/etc/yum.repos.d/MariaDB.repo 
# rsync /etc/yum.repos.d/MariaDB.repo root@192.168.0.122:/etc/yum.repos.d/MariaDB.repo 
# rsync /etc/yum.repos.d/MariaDB.repo root@192.168.0.123:/etc/yum.repos.d/MariaDB.repo 

 

Install the last version of MariaDB (this fork includes Galera Cluster) and configure: 

# yum install MariaDB-server MariaDB-client 
# vim /etc/my.cnf

[client-server] 
!includedir /etc/my.cnf.d 
# MariaDB database server settings # 
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
user=mysql 
log-bin=MariaDB-bin 
log_slave_updates 
server_id=1 
binlog_format=ROW 
default-storage-engine=innodb 
#innodb_flush_log_at_trx_commit=0 
innodb_buffer_pool_size=1024M 
innodb_autoinc_lock_mode=2 
innodb_doublewrite=1 
query_cache_size=0 
query_cache_type=0 
# Galera Cluster settings # 
wsrep_on=ON 
wsrep_provider=/usr/lib64/galera/libgalera_smm.so 
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M" 
wsrep_cluster_name="MariaDB_Cluster" 
wsrep_cluster_address="gcomm://192.168.0.120,192.168.0.121,192.168.0.122" 
wsrep_sst_method=rsync 
#[mysql_safe] 
#log-error=/var/log/mysqld.log 
#pid-file=/var/run/mysqld/mysqld.pid 

 

Correct my.cnf: 

# find / -name libgalera_smm.so 

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
 

Copy to other nodes: 

# rsync /etc/my.cnf root@192.168.0.121:/etc/my.cnf 
# rsync /etc/my.cnf root@192.168.0.122:/etc/my.cnf 

 

Change server_id in my.cnf on the other nodes. 
Initialize first primary node: 

# mysqld --wsrep-new-cluster 

 

Initialize other node: 

# systemctl start mariadb 
 

Check the cluster size on each node and the wsrep variables:

mysql> SHOW GLOBAL VARIABLES LIKE '%wsrep%';

ASYNCHRONOUS REPLICATION FROM GALERA CLUSTER’S MASTER:

 

Create user on master:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Pa$$w0rd' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

 

(*) Of course it must be another user with replications privileges only. The root user was used only for testing purpose only.

Update /etc/ hosts file on both master and slave:

 

192.168.0.120 MariaDBNode01

192.168.0.121 MariaDBNode02

192.168.0.122 MariaDBNode03

192.168.0.123 MariaDBNodeSlave

 

Update /etc/my.cnf file on the slave:

# rsync /etc/my.cnf root@192.168.0.123:/etc.my.cnf

 

Change server_id on it to the next incremental integer and delete wsrep configurations:

server_id=4

 

The mandatory configurations are:

binlog_format=ROW

log_bin=<binlog name>

log_slave_updates

 

Add the replications filters and skip errors numbers if necessary (!).

Reboot the both servers:

# reboot

 

Start MariaDB instance on the slave:

# systemctl start mariadb

 

Check the master’s status:

mysql> SHOW MASTER STATUS \G

 

Check the binlog position:

mysql> SELECT BINLOG_GTID_POS('mariadb-bin.000006',655) AS BinlogPosition;

 

(*) If you have a position it will be necessary to start the replication from this position:

mysql> STOP SLAVE; # optional

mysql> SET GLOBAL gtid_slave_pos = '1-101-1';

mysql> CHANGE MASTER TO master_use_gtid=slave_pos;

mysql> START SLAVE;

 

Start the replication on the slave:

mysql> CHANGE MASTER TO master_host="192.168.0.122", master_port=3306, master_user="root", master_password="Pa$$w0rd", master_use_gtid=current_pos;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G

 

Link to documentation:

https://mariadb.com/kb/en/mariadb/gtid/

SEMI-SYNCHRONOUS REPLICATION FROM GALERA CLUSTER’S MASTER:

 

Install an asynchronous replication as usual. If you encounter an error “'Error: connecting slave requested to start from GTID 0-5-55, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions'”,

start replication so:

 

1.       Go to the master and execute:

SHOW MASTER STATUS \G

 

2.       Go to the slave and execute:

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO master_host="192.168.0.121", master_port=3306, master_user="root", master_password="Pa$$w0rd", master_log_file='MariaDB-bin.000003',master_log_pos=1348946;

 

(*) master log file and master log position you found in the paragraph #1

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO master_use_gtid=slave_pos;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G

 

After a valid replication’s installation start to change the replication to semi-synchronous mode:

 

1.       Go to the master and execute:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> SHOW PLUGINS;

 

2.       Go to the slave and execute:

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> SHOW PLUGINS;

 

3.       Go to the master and add the following line to my.cnf :

rpl_semi_sync_master_enabled=1

 

Restart the master server.

 

4.       Go to the slave and add the following line to my.cnf: rpl_semi_sync_slave_enabled=1 Restart

the slave.

 

Execute:

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

mysql> SHOW SLAVE STATUS \G

 

(*) This plugin was installed on all masters so it’s possible now to change the previous installed asynchronous replication to a semi-synchronous now.

 

 

Good luck !

 

 

Please reload

Featured Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Recent Posts

October 31, 2017

October 29, 2017

Please reload

Archive
Please reload

Search By Tags