MariaDB Galera Cluster Installation
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:
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 !
