MARIADB CLUSTER ON CENTOS 7

UPDATED!!!

PREREQUISITES

update yum
$ sudo yum update

install networking and other tools
(Socat is a command line based utility that establishes two bidirectional byte streams and transfers data between them.)
$ sudo yum install net-tools socat nmap-ncat rsync

checkout your release
$ cat /etc/redhat-release

CentOS Linux release 7.4.1708 (Core)

Let’s assume we have only 3 nodes; db1, db2 and db3 nodes

db1: 10.10.4.1
db2: 10.10.4.2
db3: 10.10.4.3

point hosts on each 3 nodes
$ sudo vi /etc/hosts

10.10.4.1 db1
10.10.4.2 db2
10.10.4.3 db3

ping each other
db1$ ping db2 db1$ ping db3

db2$ ping db1 db2$ ping db3

db3$ ping db1 db3$ ping db2

set SELinux in permissive mode in each node
$ sudo setenforce 0
check out, you will see it’s now in permissive mode
$ sudo sestatus

uninstall mysql-server if already installed (same if you already installed MariaDB-client and MariaDB-server)
$ sudo yum erase mysql-server mysql mysql-devel

remove folder /var/lib/mysql
don’t delete this data dir if you need to keep your data
$ sudo rm -rf /var/lib/mysql/

kill the previous mysql process if exists
learn mysql pid
$ sudo pidof mysqld

1814

kill previous mysql
$ sudo kill -9 1814

STEP 1 – INSTALL APACHE

to install apache
$ sudo yum install httpd
to start apache
$ sudo systemctl start httpd.service
enable at startup
$ sudo systemctl enable httpd.service
check status
$ sudo systemctl status httpd.service
to restart Apache gracefully and avoid noticeable downtime
$ sudo apachectl graceful
to restart
$ sudo systemctl restart httpd.service
to see version of apache
$ httpd -v

Server version: Apache/2.4.6 (CentOS)
Server built: Oct 19 2017 20:39:16

http://10.10.4.1/ should work (your local ip)

STEP 2 – ADD MARIADB REPOSITORY

select your OS to get the latest MariaDB repo
https://downloads.mariadb.org/mariadb/repositories
you can rename a similar name to MariaDB
$ sudo vi /etc/yum.repos.d/MariaDB.repo
copy paste this (for 64 bit):

# MariaDB 10.2 CentOS repository list - created 2017-10-19 20:17 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

STEP 3 – INSTALL MARIADB GALERA CLUSTER 10.2

now install mariadb galera cluster
$ sudo yum install MariaDB-server MariaDB-client MariaDB-common rsync galera socat jemalloc

mariadb commands, you can replace mysql with mysql as well
$ sudo systemctl start mariadb $ sudo systemctl stop mariadb $ sudo systemctl restart mariadb $ sudo systemctl status mariadb $ sudo systemctl enable mariadb
setup mysql security
$ sudo mysql_secure_installation
($ sudo /usr/bin/mysql_secure_installation)

STEP 4 – CREATE MARIADB GALERA CLUSTER USERS

login mysql
$ sudo mysql -u root -p
The cluster_user is the user which a database node will use for authenticating to another database node

MariaDB [(none)]> DELETE FROM mysql.user WHERE user='';
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'Memo3480$';
MariaDB [(none)]> GRANT USAGE ON *.* TO cluster_user@'%' IDENTIFIED BY 'Memo3480$';
MariaDB [(none)]> GRANT ALL PRIVILEGES on *.* to cluster_user@'%';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> quit;

verify by connecting db with cluster_user
$ sudo mysql -u cluster_user -p

MariaDB [(none)]> show databases;
MariaDB [(none)]> quit;

STEP 5 – UPDATE MARIADB GALERA CLUSTER CONFIG

stop mysql on all nodes
$ sudo systemctl stop mariadb

add cluster config to server.cnf for each node
wsrep_node_name, wsrep_node_address will be different in each node
wsrep_sst_auth stays same because each node has the same authentication

you will see mysql and Gelara-related settings
this is how node 1 looks like, you just need to change the wsrep_node_name and wsrep_node_address
$ sudo vi /etc/my.cnf.d/server.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
log_error=/var/log/mariadb.log

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.4.1,10.10.4.2,10.10.4.3"
wsrep_cluster_name='moodle_galera_cluster'
wsrep_node_address='10.10.4.1'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth=bla:bla
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

we didn’t define the followings because they are by default, you can find the data files under /var/lib/mysql/
port=3306
datadir=/var/lib/mysql

we need to create the initial mariadb log file
$ sudo touch /var/log/mariadb.log
give the appropriate permissions
$ sudo chown mysql:mysql /var/log/mariadb.log

STEP 6 – INITIALIZE AND START THE FIRST CLUSTER MASTER NODE

do initialize master node on db1
$ sudo galera_new_cluster

PORTS
after the master node started make sure it has bound to the correct ports using lsof

4567 for replication traffic
$ sudo lsof -i:4567

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 29529 mysql 11u IPv4 91271 0t0 TCP *:tram (LISTEN)

and port 3306 for MySQL client connections
$ sudo lsof -i:3306

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 29529 mysql 29u IPv4 91066 0t0 TCP *:mysql (LISTEN)

FIREWALL
let’s add the ports to the firewall

$ sudo firewall-cmd --zone=public --add-service=mysql --permanent
$ sudo firewall-cmd --permanent --add-service=http --permanent
$ sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
$ sudo firewall-cmd --zone=public --add-port=4567/tcp --permanent
$ sudo firewall-cmd --zone=public --add-port=4567/udp --permanent
$ sudo firewall-cmd --zone=public --add-port=4568/tcp --permanent
$ sudo firewall-cmd --zone=public --add-port=4568/udp --permanent
$ sudo firewall-cmd --zone=public --add-port=4444/tcp --permanent
$ sudo firewall-cmd --zone=public --add-port=4444/udp --permanent
$ sudo firewall-cmd --reload

let’s check out the master node
$ sudo mysql -u root -p

MariaDB [(none)]> show status like ‘wsrep_cluster_size’;

or
$ sudo mysql -uroot -p -e "show status like 'wsrep_cluster_size'"

+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| wsrep_cluster_size | 1 |
+——————–+——-+

check out for more galera settings
$ sudo mysql -uroot -p -e "show status like 'wsrep%’"

STEP 7 – ADD ADDITIONAL CLUSTER NODES

follow the same firewall steps above for other 2 nodes (db2, db3)
and do not forget that the only difference in the galera settings for other nodes will be only 2 lines
$ sudo vi /etc/my.cnf.d/server.cnf

for db1 server (we already started that as a master node)

wsrep_node_address=’10.10.4.1′
wsrep_node_name=‘db1′

for db2 server

wsrep_node_address=’10.10.4.2′
wsrep_node_name=‘db2′

for db3 server

wsrep_node_address=’10.10.4.3′
wsrep_node_name=’db3′

we start other nodes db2 and db3 in standard way
$ sudo systemctl start mariadb

check out the logs
$ sudo tail -100 /var/log/mariadb.log

let’s check it out again. Each node should show the same
$ sudo mysql -uroot -p -e "show status like 'wsrep%’"

node: 1if you see the below, then you are good

wsrep_local_state_comment    | Synced
wsrep_incoming_addresses     | 10.10.4.1:3306,10.10.4.2:3306,10.10.4.3:3306
wsrep_cluster_conf_id        | 3
wsrep_cluster_size           | 3
wsrep_connected              | ON
wsrep_ready                  | ON

STEP 8 – VERIFY REPLICATION

on each node you can see the same thing first

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
+——————–+
3 rows in set (0.00 sec)

on node1 (db1) create a test db

MariaDB [(none)]> CREATE DATABASE clustertest;

on each node db1, db2 and db3 verify clustertest

mysql> show databases;
+——————–+
| Database |
+——————–+
| clustertest |
| information_schema |
| mysql |
| performance_schema |
+——————–+
4 rows in set (0.00 sec)

on db1 create a new table mycluster

MariaDB [(none)]> CREATE TABLE clustertest.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));

on db2, db3 verify tables
MariaDB [(none)]> show tables in clustertest;
+———————–+
| Tables_in_clustertest |
+———————–+
| mycluster |
+———————–+
1 row in set (0.00 sec)

on db1 insert a new record

MariaDB [(none)]> INSERT INTO clustertest.mycluster (name, ipaddress) VALUES (“db1”, “10.10.4.1”);
on db2, db3 verify the records
MariaDB [(none)]> select * from clustertest.mycluster;

+—-+——+———–+
| id | name | ipaddress |
+—-+——+———–+
| 5 | db1 | 10.10.4.1 |
+—-+——+———–+

on db2 add another new record

MariaDB [(none)]> INSERT INTO clustertest.mycluster (name, ipaddress) VALUES (“db2”, “10.10.4.2”);
on db1, db2 verify the new record
MariaDB [(none)]> select * from clustertest.mycluster;

+—-+——+———–+
| id | name | ipaddress |
+—-+——+———–+
| 5 | db1 | 10.10.4.1 |
| 9 | db2 | 10.10.4.2 |
+—-+——+———–+

on db3 add another new record

MariaDB [(none)]> INSERT INTO clustertest.mycluster (name, ipaddress) VALUES (“db3″, “10.10.4.3”);
on db1, db3 verify
MariaDB [(none)]> select * from clustertest.mycluster;

+—-+——+———–+
| id | name | ipaddress |
+—-+——+———–+
| 5 | db1 | 10.10.4.1 |
| 9 | db2 | 10.10.4.2 |
| 13 | db3 | 10.10.4.3 |
+—-+——+———–+

STEP 9 – INSTALL PHPMYADMIN

To install phpMyAdmin easily on CentOS 7, first install extra packages for enterprise linux (epel)
$ sudo yum install epel-release
to install phpMyAdmin
$ sudo yum install phpMyAdmin

conf file -> /etc/httpd/conf.d/phpMyAdmin.conf
$ sudo vi /etc/httpd/conf.d/phpMyAdmin.conf
to access only from certain ip
1- either put Require ip 10.10.4.1 under each Require ip 127.0.0.1
2- or disable the first block

# Disabled by Mehmet Sen
#
# AddDefaultCharset UTF-8
#
#
# # Apache 2.4
#
# Require ip 127.0.0.1
# Require ip ::1
#
#
#
# # Apache 2.2
# Order Deny,Allow
# Deny from All
# Allow from 127.0.0.1
# Allow from ::1
#
#

# and add this

Options none
AllowOverride Limit
Require all granted

now install php, don’t worry if it’s 5.4 version, the purpose is only to run phpmyadmin later
$ sudo yum install php

restart apache
$ sudo systemctl restart httpd.service

check out each node

http://10.10.4.1/phpmyadmin
http://10.10.4.2/phpmyadmin
http://10.10.4.3/phpmyadmin

STEP 10 – PHPMYADMIN OWNER RULES

If you see the blowfish_secret error after you enter PhpMyAdmin, then probably you changed some permission rules

let’s check it out db1 node
$ ls -l /etc/

drwxr-x—. 2 root root 27 Oct 20 11:44 phpMyAdmin

give the apache permission, the error should be gone
$ sudo chown -R root.apache /etc/phpMyAdmin/ $ ls -l /etc/

drwxr-x—. 2 root apache 27 Oct 20 11:44 phpMyAdmin

Normally, the permission comes as apache by default, so no need to change anything. Just FYI

STEP 11 – REVERIFY REPLICATION

open up each page