MariaDB Galera Cluster Haproxy - samuel-richardson/Sam-Tech-Journal GitHub Wiki

Galera Cluster

Install MariaDB on Each Server

sudo apt update
sudo apt -y install mariadb-server mariadb-client
sudo systemctl start mariadb
sudo mysql_secure_installation #Make sure to set the same root password and allow root remote.

Setup GaleraDB on node u1

sudo vim /etc/mysql/mariadb.cnf

make GaleraDB config sudo vim /etc/mysql/mariadb.conf.d/galera.cnf add this config

[galera] 
bind-address=0.0.0.0 
default_storage_engine=InnoDB 
binlog_format=row 
innodb_autoinc_lock_mode=2  
# Galera cluster configuration 
wsrep_on=ON 
wsrep_provider=/usr/lib/galera/libgalera_smm.so 
wsrep_cluster_address="gcomm://10.0.5.201,10.0.5.202,10.0.5.203" 
wsrep_cluster_name="mariadb-galera-cluster" 
wsrep_sst_method=rsync  
# Cluster node configuration 
wsrep_node_address="10.0.5.201" 
wsrep_node_name="galera-db-01"

Setup GaleraDB on node 2

make GaleraDB config sudo vim /etc/mysql/mariadb.conf.d/galera.cnf add this config

[galera] 
bind-address=0.0.0.0 
default_storage_engine=InnoDB 
binlog_format=row 
innodb_autoinc_lock_mode=2  
# Galera cluster configuration 
wsrep_on=ON 
wsrep_provider=/usr/lib/galera/libgalera_smm.so 
wsrep_cluster_address="gcomm://10.0.5.201,10.0.5.202,10.0.5.203" 
wsrep_cluster_name="mariadb-galera-cluster" 
wsrep_sst_method=rsync  
# Cluster node configuration 
wsrep_node_address="10.0.5.202" 
wsrep_node_name="galera-db-02"

Setup GaleraDB on node 3

make GaleraDB config sudo vim /etc/mysql/mariadb.conf.d/galera.cnf add this config

[galera] 
bind-address=0.0.0.0 
default_storage_engine=InnoDB 
binlog_format=row 
innodb_autoinc_lock_mode=2  
# Galera cluster configuration 
wsrep_on=ON 
wsrep_provider=/usr/lib/galera/libgalera_smm.so 
wsrep_cluster_address="gcomm://10.0.5.201,10.0.5.202,10.0.5.203" 
wsrep_cluster_name="mariadb-galera-cluster" 
wsrep_sst_method=rsync  
# Cluster node configuration 
wsrep_node_address="10.0.5.203" 
wsrep_node_name="galera-db-03"

Initalize GaleraDB

On all three ndoes stop mariadb sudo systemctl stop mariadb

On node 1 sudo galera_new_cluster

Check if Galera is running with mysql -u root -p -e "show status like 'wsrep_%'"

Check cluster size with mysql -u root -p -e "show status like 'wsrep_cluster_size'"

Start mariadb on other 2 nodes and check cluuster size to see new nodes.

Allow remote access

This gives root privileges to the root user remote at 10.0.6.x

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.6.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

HAProxy config for both nodes

frontend mysql_cluster_frontend
    bind *:3306
    mode tcp
    option tcplog
    default_backend galera_cluster_backend

# MySQL Cluster BE configuration
backend galera_cluster_backend
    mode tcp
    option tcpka
    balance leastconn
    server u1-sam 10.0.5.201:3306  check weight 1
    server u2-sam 10.0.5.202:3306  check weight 1
    server u3-sam 10.0.5.203:3306  check weight 1

Installing wordpress

setsebool -P httpd_can_network_connect=1

Reference: