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
login on each node
$ sudo mysql -u root -p
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
http://10.10.4.1/phpmyadmin
http://10.10.4.2/phpmyadmin
http://10.10.4.3/phpmyadmin
update the clustertest.mycluster and see if each page displays it
This was a really good tutorial but on centos 7 I needed to run:
galera_new_cluster
before I ran:
sudo service mysql start –wsrep-new-cluster
LikeLike
Thanks Aaron, the tutorial is updated now
LikeLike
I could to raise the culster only with this tuto. Tanks a lot , bro.
LikeLike