Centos 8 Stream - haproxy balancer - percona 5.7 cluster

 CentOS Stream release 8 - minmal install

192.168.137.9 db1
192.168.137.10 db2
192.168.137.11 db3
192.168.137.12 clustercontroldb

dnf groupinfo "Development Tools"
dnf group install "Development Tools"
dnf update
dnf install mlocate
dnf install vim
dnf install net-tools
dnf install wget
vim /etc/sysconfig/selinux
firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --zone=public --add-port=3306/tcp       --permanent
firewall-cmd --zone=public --add-port=4567/tcp       --permanent
firewall-cmd --zone=public --add-port=4568/tcp       --permanent
firewall-cmd --zone=public --add-port=4444/tcp       --permanent
firewall-cmd --zone=public --add-port=4567/udp       --permanent
firewall-cmd --zone=public --add-port=9200/tcp --permanent
firewall-cmd --reload
yum install epel-release
yum install socat
yum remove mariadb-libs
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install rsync nc
percona-release enable-only pxc-57
percona-release enable tools release
yum module disable mysql
yum install Percona-XtraDB-Cluster-full-57
systemctl  start mysql
grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation
mysql -u root -p
mysql> create user sstuser@'%' identified by 'parolagrea';
mysql> grant all on *.* to sstuser@'%';
mysql> flush privileges;
mysql> quit

vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
db1
wsrep_cluster_address = gcomm://
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so

wsrep_slave_threads = 8
wsrep_cluster_name = pxc-cluster
wsrep_node_name = pxc-cluster-node-db1
wsrep_node_address = db1
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sstuser:parolagrea

db2
wsrep_cluster_address = gcomm://db1,db2,db3
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so

wsrep_slave_threads = 8
wsrep_cluster_name = pxc-cluster
wsrep_node_name = pxc-cluster-node-db2
wsrep_node_address = db2
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sstuser:parolagrea

db3
wsrep_cluster_address = gcomm://db1,db2,db3
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so

wsrep_slave_threads = 8
wsrep_cluster_name = pxc-cluster
wsrep_node_name = pxc-cluster-node-db3
wsrep_node_address = db3
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sstuser:parolagrea

on db2,db3
vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
change server-id=1 in server-id=2 and server-id=3

db1
systemctl start mysql@bootstrap

db2 - after db1 finish
systemctl start mysql

db3 - after db3 finish
systemctl start mysql

db1
mysql -u root -p

mysql> SHOW STATUS LIKE 'wsrep_local_state_comment';
mysql> show global status like 'wsrep_cluster_size';

In cluster of 3 at least 2 need to be up.
if systemctl stop mysql@bootstrap was gived on db1, and somehow gone off.
db2 and db3 will be up.
When db1 come alive, need to have setup wsrep_cluster_address = gcomm://db1,db2,db3 and use
systemctl start mysql

if u decide to make it again db1 as bootstrap proccess owner, after previous case, u need shutdown all the others nodes, systemctl stop mysql and then the last one systemctl stop mysql on db1.
After all mysql stoped, u can use again systemctl stop mysql@bootstrap on db1 and continue with usual starting nodes schema.

HAproxy settings

on all cluster nodes  ( might allready exist after full install percona cluster, u can overwrite it)

if not ...

on all nodes.

wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck
chmod +x clustercheck
mv /usr/bin/clustercheck /usr/bin/clustercheck.old
mv clustercheck /usr/bin/
vim /usr/bin/clustercheck

#MYSQL_USERNAME="${MYSQL_USERNAME:=-clustercheckuser}"
#MYSQL_PASSWORD="${MYSQL_PASSWORD-clustercheckparola!}"
MYSQL_USERNAME="clustercheckuser"
MYSQL_PASSWORD="clustercheckparola!"

yum install xinetd

check if /etc/xinetd.d/mysqlchk does exist.

if not

create it

vim /etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
  disable = no
  flags = REUSE
  socket_type = stream
  type = UNLISTED
  port = 9200
  wait = no
  user = nobody
  server = /usr/bin/clustercheck
  log_on_failure += USERID
  only_from = 0.0.0.0/0
  # recommended to put the IPs that need
  # to connect exclusively (security purposes)
  per_source = UNLIMITED
}

chmod

service xinetd restart

on one node
mysql -u root -p

mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckparola!';

exit;

vim /etc/services

find line who contain 9200, comment and ad new line
mysqlchk 9200/tcp # mysqlchk

On HAproxy server

systemctl stop firewalld
setenforce 0
dnf install gcc gcc-c++ make pcre-devel bzip2-devel
dnf install haproxy
mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.bk

vim /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
chroot /usr/share/haproxy
user haproxy
group haproxy
daemon

defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000

frontend pxc-front
bind *:3307
mode tcp
default_backend pxc-back

frontend stats-front
bind *:80
mode http
default_backend stats-back

frontend pxc-onenode-front
bind *:3306
mode tcp
default_backend pxc-onenode-back

backend pxc-back
mode tcp
balance leastconn
option httpchk
server db1 192.168.137.9:3306 check port 9200 inter 12000 rise 3 fall 3
server db2 192.168.137.10:3306 check port 9200 inter 12000 rise 3 fall 3
server db3 192.168.137.11:3306 check port 9200 inter 12000 rise 3 fall 3

backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth pxcstats:secret

backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server db1 192.168.137.9:3306 check port 9200 inter 12000 rise 3 fall 3
server db2 192.168.137.10:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server db3 192.168.137.11:3306 check port 9200 inter 12000 rise 3 fall 3 backup



systemctl start firewalld
firewall-cmd --permanent --add-port=9000/tcp
firewall-cmd --permanent --add-port=3030/tcp
firewall-cmd --permanent --add-port=80/tcp

firewall-cmd --reload


yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install rsync nc
percona-release enable-only pxc-57
percona-release enable tools release
yum module disable mysql
yum install Percona-XtraDB-Cluster-client-57

vim /etc/services

find line who contain 9200, comment and ad new line
mysqlchk 9200/tcp # mysqlchk


dnf install sysbench

http://clustercontroldb/haproxy/stats
user:pxcstats
pass:secret

on db1

mysql> create database sbtest;
Query OK, 1 row affected (0.01 sec)

mysql> grant all on sbtest.* to 'sbtest'@'%' identified by 'sbpass';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

on haproxy TEST

sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbpass --mysql-db=sbtest --table_size=10000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare

sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbpass --mysql-db=sbtest --range_size=100 --table_size=10000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua run