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

Configure MariaDB with Galera(3 nodes)

On All nodes

sudo apt install mariadb
sudo systemctl start mariadb
sudo mysql_secure_installation # Make sure to use the same password, and allow remote root connections.
sudo systemctl stop mariadb

On Each node create the respective galera conf

  • May need to change the bind address in server.cnf to 0.0.0.0
  • sudo vim /etc/mysql/mariadb.conf.d/galera.cnf
[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" # Change this to the nodes ip
wsrep_node_name="galera-db-01" # Change this for the node. Can not have the same name.

Start the Galera Cluster

  • On the first node while the others are off. sudo galera_new_cluster
  • Check the cluster size with. sudo mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
  • Start the mariadb on the other two nodes. sudo systemctl start mariadb and check if the cluster size grew.
  • Once don use sudo mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment'" to check the cluster sync.

Check High Availability

  • Add and remove databases checking each time on each node to ensure the database is removed or present on each nodes even while taking one or more up and down.

Configure with haproxy:

Add the following to the haproxy config. And restart.

# DB config
# MySQL Cluster FE configuration
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

Refrence

https://www.linode.com/docs/guides/how-to-set-up-mariadb-galera-clusters-on-ubuntu-2204/

Install Wordpress on web nodes(2).

Create wordpress db and users.

sudo mysql -u root -p
CREATE DATABASE wordpress_db DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
GRANT ALL ON wordpress.* TO 'wordpress_user'@'10.0.6.%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
exit

Wordpress web install

Do one node at a time.

Install updated php and required packages.

sudo dnf install epel-release -y
sudo dnf module reset php
sudo dnf module install php:8.2 -y
sudo dnf install php-gd php-soap php-intl php-mysqlnd php-pdo php-pecl-zip php-fpm php-opcache php-curl php-zip php-xmlrpc wget

Install wordpress files to directory.

wget https://wordpress.org/latest.tar.gz -O wordpress.tar.gz
tar -xvf wordpress.tar.gz
sudo cp -R wordpress /var/www/html/
sudo chown -R apache:apache /var/www/html/wordpress
sudo chmod -R 775 /var/www/html/wordpress

Copy the sample wp-config to wp-config.php and update the databases fields below with the following according to DB config.

define( 'DB_NAME', 'wordpress_db' );

/** Database username */
define( 'DB_USER', 'wordpress_user' );

/** Database password */
define( 'DB_PASSWORD', 'wordpressSEC440' );

/** Database hostname */
define( 'DB_HOST', '10.0.6.10' );

/** Database charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8' );

/** The database collate type. Don't change this if in doubt. */
define( 'DB_COLLATE', '' );

Configure httpd to use wordpress.

Add the following to /etc/httpd/conf.d/wordpress.conf and restart httpd.

<VirtualHost *:80>
ServerName 10.0.6.10
ServerAdmin root@localhost
DocumentRoot /var/www/html/wordpress

<Directory "/var/www/html/wordpress">
Options Indexes FollowSymLinks
AllowOverride all
Require all granted
</Directory>

ErrorLog /var/log/httpd/wordpress_error.log
CustomLog /var/log/httpd/wordpress_access.log common
</VirtualHost>

Configure wordpress with selinux

If any changes or new files are created this may need to be done again.

sudo semanage fcontext -a -t httpd_sys_rw_content_t "/var/www/html/wordpress(/.*)?"
sudo setsebool -P httpd_can_network_connect=1
sudo restorecon -Rv /var/www/html/wordpress

Access The wordpress website.

Access wordpress from the HA front end

Refrence

https://www.tecmint.com/install-wordpress-on-rocky-linux/

If having issues with DB check connection with below.

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
sudo dnf install mariadb-client
sudo systemctl restart httpd
⚠️ **GitHub.com Fallback** ⚠️