Project 3: Application Redundancy - squatchulator/Tech-Journal GitHub Wiki

Project 3: Application Redundancy

https://www.linuxbabe.com/mariadb/galera-cluster-ubuntu

Configuring Database Servers

  • On u1 u2 and u3, change he network adapters to LAN and boot. Create a new named user, elevate it to sudo group, rename server, delete old user on all 3.
  • Edit the netplan configuration located in /etc/netplan/00-installer-config.yaml. The addresses for this box will be .201, .202, and .203 respectively.
network:
  ethernets:
    ens160:
      dhcp4: false
      addresses: [10.0.5.201(2,3)/24]
      gateway4: 10.0.5.1
      nameservers:
        addresses: [10.0.5.1]
  version: 2

Installing MariaDB

  • Enter the following to import the MariaDB repo key:
sudo apt-get -y install apt-transport-https curl
sudo mkdir -p /etc/apt/keyrings
sudo curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp'
  • Create a new file called /etc/apt/sources.list.d/mariadb.sources and add the following:
# MariaDB 11.3 repository list - created 2024-02-20 15:14 UTC
# https://mariadb.org/download/
X-Repolib-Name: MariaDB
Types: deb
# deb.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# URIs: https://deb.mariadb.org/11.3/ubuntu
URIs: https://ftp.osuosl.org/pub/mariadb/repo/11.3/ubuntu
Suites: jammy
Components: main main/debug
Signed-By: /etc/apt/keyrings/mariadb-keyring.pgp
  • Then, run an apt-get update and install mariadb-server. This should also install galera4 which is what we will need for database redundancy. Run a systemctl enable mariadb as well to enable on boot.
  • Edit the file /etc/mysql/mariadb.conf.d/60-galera.cnfand add the following:
[galera]
# Mandatory settings
wsrep_on                 = ON
wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name       = "MariaDB Galera Cluster"
wsrep_cluster_address    = "gcomm://10.0.5.201,10.0.5.202,10.0.5.203"
binlog_format            = row
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2
innodb_force_primary_key = 1
innodb_doublewrite       = 1

# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0

# Optional settings
wsrep_slave_threads            = 4
innodb_flush_log_at_trx_commit = 0
wsrep_node_name                = <hostname of this node>
wsrep_node_address             = "<this node's IP>"

# By default, MariaDB error logs are sent to journald, which can be hard to digest sometimes.
# The following line will save error messages to a plain file. 
log_error = /var/log/mysql/error.log
  • To let firewall traffic locally through, run sudo ufw allow in from <ip> 3 times on each server, once for each IP in the cluster. Run sudo ufw enable right after. Run the following on all 3 boxes:
cd /etc/apparmor.d/disable/
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld
sudo systemctl restart apparmor
  • Now to start the cluster, we first need to shut off mariadb with systemctl or the following commands will not work. Run this on the primary node (first one, u1):
sudo systemctl stop mariadb
sudo galera_new_cluster
mysql -u root -p
show status like 'wsrep_cluster_size';
  • This should show you that our node is successfully added. To add the other nodes, restart mariadb on the unadded ones and they should just pop up.
  • To secure your cluster, go to u1 and run sudo mariadb-secure-installation and configure a root password. You should now be able to log in to mariadb using the same password on all 3 hosts!

Tiered Web Application

  • First thing to do on our web01 and web02 boxes is to tell SELinux to allow DB connections to our web server. This can be done with sudo setsebool -P httpd_can_network_connect_db on
  • Now we need to install PHP and MySQL.
sudo yum -y install php php-mysql
sudo systemctl restart httpd
  • Head over to /var/www/html and create a new php file called info.php. Put the following in it:
<?php
phpinfo();
?>
  • After saving and closing the file, navigate to http://<server IP>/info.php to check that it worked.
  • We have to create the MariaDB backend. Make sure that your clustering is working properly. Log into MariaDB on one of your databse servers with mysql -u root -p and run CREATE DATABASE <new_database>;. This will make a new database that our frontend will use.
  • Run USE <new_database>; to select the one we just made, and then enter something similar to the following:
CREATE TABLE cats
(
  id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
  name            VARCHAR(150) NOT NULL,                # Name of the cat
  owner           VARCHAR(150) NOT NULL,                # Owner of the cat
  birth           DATE NOT NULL,                        # Birthday of the cat
  PRIMARY KEY     (id)                                  # Make the id the primary key
);
  • When done, you can check that the table was made correctly with SHOW TABLES; and DESCRIBE <new_database>;. If you want to insert additional information into the table, use something like the following:
INSERT INTO cats ( name, owner, birth) VALUES
  ( 'Siggy', 'Harold', '2018-01-05' ),
  ( 'Freya', 'Gutenberg', '2017-11-10' ),
  ( 'Patches', 'Charlie', '2015-05-15' );

  • You can select specific fields from a table using the SELECT statement. For example, running SELECT * FROM cats; will display all records on the "cats" table. This can be extended further by including the WHERE clause, which can look something like SELECT name FROM cats WHERE owner = 'Charlie'; - this allows you to pretty thoroughly narrow down your database navigation.

  • To integrate our MySQL servers with PHP, we need to create a new user in our databases. Replace the user password and change localhost to the IP of the Apache servers. Then to grant all perms to this new user, run GRANT ALL PRIVILEGES ON <new_database>.* TO 'user'@'localhost' IDENTIFIED BY 'password';

  • Now from your web server, try to remotely log into the MySQL database as your new user with the command mysql -u <new_user> -p -h <db_server_ip>.

  • Now to create the webpages. Go onto /var/www/html of your webserver and create three new files: birthday.html, add_cat.php, and birthday.php.

  • birthday.php:

<?php
$username = "miles";
$password = "password";
$database = "pets";

$name = $_POST['name'];

$mysqli=new mysqli('10.0.6.10', $username, $password, $database);

$query = "SELECT * from cats where name='".$name."'";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

$hostnameQuery = "SELECT @@hostname";
$hostnameResult = $mysqli->query($hostnameQuery);
$hostnameRow = $hostnameResult->fetch_assoc();
$hosttname = $hostnameRow['@@hostname'];

if ($result->num_rows > 0) {
  while ($row=$result->fetch_assoc()) {
    echo "Database server currently in use: " . $hostname . "</br>";
    echo $row['name']."'s birthday is ".$row['birth']."</br>";
  }
}
else {
  echo 'NO RESULTS';'
}
?>
  • add_cat.php:
<?php

$username = "miles";
$password = "password";
$database = "pets";

$newName = $_POST['new_name'];
$newOwner= $_POST['new_owner'];
$newBirth= $_POST['new_birth'];

$mysqli = new mysqli('localhost', $username, $password, $database);
$query = "INSERT INTO cats (name, owner, birth) VALUES ('$newName', '$newOwner', '$newBirth')";

$mysqli->query($query) or die($mysqli->error.__LINE__);

$mysqli->close();

header("Location: birthday.html");
exit();
?>
  • birthday.html:
<html>
<body>
  <form action="birthday.php" method="post">
    Currently running web interface on: (web server name here) </br> 
    Look up your cat's birthday! </br>
    Cats Name: <input type="text" name="name"></br>
    <input type="Submit">
  </form>

  <form action="add_cat.php" method="post">
    Add a new cat:</br>
    Cat's Name: <input type="text" name="new_name"></br>
    Owner's Name: <input type="text" name="owner_name"></br>
    Cat's Birthday (YYYY-MM-DD): <input type="text" name="birthday"></br>
    <input type="Submit" value="Add Cat">
  </form>
</body>
</html>
  • On both ha1 and ha2, edit the /etc/haproxy/haproxy.cfg file and add the following block below your haproxy configuration for the web servers:
frontend db
        bind *:3306
        mode tcp
        default_backend dbpool
backend dbpool
        balance roundrobin
        mode tcp
        server u1 10.0.5.201:3306 check
        server u2 10.0.5.202:3306 check
        server u3 10.0.5.203:3306 check
  • Now restart haproxy on both servers with systemctl (NOTE: I also had to disable the firewall on the DB servers.)
  • Install mariadb-client on xUbuntu-lan and make sure that the new account you made can access your new database
  • Then go into xubuntu-wan, and edit the /etc/hosts file. Below the localhost section, add a new entry for your WAN IP and the hostname web-cluster.miles.local.
⚠️ **GitHub.com Fallback** ⚠️