In this project, I completed the following objectives:

• Configured and Networked the following systems:
  • db01(MariaDB Database)
  • db02(MariaDB Database)
  • db03(MariaDB Database)
• Configured MariaDB on db01, db02, and db03
• Configured the database servers as a galera cluster
• Created a Simple PHP Web Application that interacts with the databases

The next portion of this page will cover each system and the setup and configuration involved for this project.

db01 Configuration

This sections will outline the setup configuration for db01.

Network Information:

• IP : 10.0.5.201/24
• Default Gateway : 10.0.5.1
• Name Server: 10.0.5.1

Network Configuration with Netplan:

Netplan can be used to configure the network on db01. The file /etc/netplan/00-installer-config.yaml contains the network configuration. The configuration used for db01 is shown below:

The commands sudo netplan try and sudo netplan apply can be used to apply the configuration.

db02 Configuration

This sections will outline the setup configuration for db02.

Network Information:

• IP : 10.0.5.202/24
• Default Gateway : 10.0.5.1
• Name Server: 10.0.5.1

Network Configuration with Netplan:

Netplan can be used to configure the network on db01. The file /etc/netplan/00-installer-config.yaml contains the network configuration. The configuration used for db02 is shown below:

The commands sudo netplan try and sudo netplan apply can be used to apply the configuration.

db03 Configuration

This sections will outline the setup configuration for db03.

Network Information:

• IP : 10.0.5.203/24
• Default Gateway : 10.0.5.1
• Name Server: 10.0.5.1

Network Configuration with Netplan:

Netplan can be used to configure the network on db03. The file /etc/netplan/00-installer-config.yaml contains the network configuration. The configuration used for db03 is shown below:

The commands sudo netplan try and sudo netplan apply can be used to apply the configuration.

MariaDB and Galera Cluster Configuration:

The following section will describe how to install and configure mariaDB and galera on each database system. Note that the primary node must be fully configured first. In this scenario, db01 is the primary node.

db01 Configuration

• Before installing mariadb or galera, run the command sudo apt update to make sure packages are up to date.
• To install MariaDB, use the command sudo apt install mariadb-server
• Install galera using sudo apt install galera-4 Now galera needs to be configured to be used with the database servers. There are two files that need to be edited. The first file is /etc/mysql/mariadb.conf.d/60-galera.conf The portion of the 60-galera.conf file for db01 that needs to be changed can be seen below:

Additionally, the file /etc/mysql/mariadb.conf.d/50-server.cnf needs to be edited for the bind address to be 0.0.0.0. The screenshot below shows this:

Once the configurations have been set on db01, stop the mariadb service using sudo systemctl stop mariadb

Now run the command galera_new_cluster to start a cluster. Now its time to set up the other database nodes.

db02 and db03 MariaDB and Galera Node Configuration

On both db02 and db03, run the commands, sudo apt update and sudo apt install mariadb-server. Then run sudo apt install galera-4 to install galera.

Both the /etc/mysql/mariadb.conf.d/60-galera.conf and /etc/mysql/mariadb.conf.d/50-server.cnf file need to be edited on each server. The configurations are the same as for db01 with slight differences per database server. In the 60-galera.conf file, wsrep_node_name and wsrep_node_address are specific to the database server.

Once configurations are set, run the command systemctl restart mariadb

Cluster

Now that the nodes have been set up, mariadb can be started on db01, using sudo systemctl restart mariadb

To test redundancy across the databases, I ran the command sudo mariadb_secure_installation. The test is sucessful if the created root user and password are active on all database servers. Below are the options I used with the command.

Current User Password: None
Unix : no
Set root password: yes
Remove Anonymous Users: yes
Disallow Root Login Remotely: no
Remove Test Database: yes
Reload Privileges table: yes

After executing the command, I was able to login to the database as the root user on all three databases, confirming synchronization and redundancy.

HAProxy Configuration:

The HAProxy servers can be used for load balancing over the database servers. The /etc/haproxy/haproxy.cfg file needs to be updated. The additions on HA01 are below:

The additions on HA02 are below:

Creating a database

• To work within the MariaDB database, the command mysql -u root -p can be used. This logs into the database as the root database user.
• The following steps outline the steps I took for database and user creation.

1. I used CREATE DATABASE zach_db; to create a database called zach_db
2. I created a user, called zachary, that can be logged into from any system using CREATE USER 'zachary'@'%' IDENTIFIED BY 'password_goes_here';
3. I granted the new user permissions to databases using the command GRANT ALL PRIVILEGES ON django_db.* TO ‘zachary’@'%'; IDENTIFIED BY ‘password_goes_here’ WITH GRANT OPTION;
4. I then ran FLUSH PRIVILEGES; to make the privilege changes immediately effective

Testing Database Access from LAN

• On the LAN system I installed the mysql client using sudo apt install mysql-client
• Using the command mysql -u zachary -h 10.0.6.10 -p, I am able to log into the database. The screenshot below shows this test
  • The 10.0.6.10 address is the VIP used for HAProxy Load Balancing.

Creating A New Table

The following steps outline how I added a table to be used in this project. These steps take place from the database interface on db01

1. First, I entered the zach_db database using use zach_db;
2. I then used the CREATE TABLE command to create a table. The full command I used is seen below. It creates a table called recipes with columns called id, name, type, owner, makeTime, and addedDate.

CREATE TABLE recipes
(
  id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
  name           VARCHAR(150) NOT NULL,                # Name of the recipe
  type            VARCHAR(150) NOT NULL,                # type of the recipe(dessert, appetizer, etc.)
  owner           VARCHAR(150) NOT NULL,                # person who added the recipe
  makeTime        INT unsigned NOT NULL,                # how long the recipe takes to make in minutes
  addedDate       DATE NOT NULL,                        # The date the recipe was added
  PRIMARY KEY     (id)                                  # Make the id the primary key
);

3. The show tables; and describe recipes; commands can confirm that the table creation was successful.

4. Information can be added to the table using INSERT INTO. The command I used is seen below.

INSERT INTO recipes ( name, type, owner, makeTime, addedDate) VALUES
  ( 'Scrambled Eggs', 'Breakfast', 'Zach', '5', '2023-10-04'),
  ( 'Grilled Cheese', 'Lunch', 'Zach', '12', '2023-10-04');

5. The additions can be checked using SELECT * FROM recipes;

Simple PHP Web Application

A Simple PHP web application will be used in this project. The web application will allow users to pull information and from and put information into the zach_db database.

Web01 and Web02 Prerequisites

On web01 and web02, the following commands must be run:

• sudo yum install php php-mysql mysql
• sudo setsebool -P httpd_can_network_connect_db on
• sudo systemctl restart httpd.service These command install php and mysql on the web servers, as well as allowing apache to interact with the databases.

Web Page and PHP Page

On both web01 and web02, I created files to create a web application that interacts with the databases. The files can be found here

recipes.html : This file give the user the option to submit a search for a recipe by name. It inputs the information entered into the recipes.php file to be queried.

recipes.php : This file uses the information from recipes.html to query the information for the desired recipe and outputs it.

addrecipes.html: This file give the user the option to add a recipe to the database. It inputs the information entered into the addrecipes.php file to be inserted into the recipes table in the database

addrecipes.php : This file inserts the information specified, by addrecipes.html, into the recipes table in the database, and confirms that the addition was successful.

Demonstration from WAN:

All of the Web Application pages can be accessed via 10.0.17.114. This is because that is the virtual IP address used on the WAN, which forwards the connections to the web servers.

Recipe Searching

By navigating to 10.0.17.114/recipes.html, the user can search for a recipe. This is shown below: Hitting submit navigates to recipes.php with the results, as shown below:

Recipe Adding

By navigating to 10.0.17.114/addrecipes.html, the user can add a recipe. This is shown below: Hitting submit navigates to addrecipes.php with the confirmation and adds the recipe to the database as shown below: The new recipe can be viewed using recipes.html The added recipe is also reflected in the database:

Reflection

In this lab, I learned about galera clustering and dataase setup. I also learned more about web applications and database interaction. Originally, I had chosen Django as the web application to use, but encountered various difficulties and bumps in the process of installation and implementation. After trying hard and for a long time at working with Django, I changed to Wordpress in which I encountered similar challenges and difficulties that made it quite difficult to continue. Eventually, after many attempts at the other frameworks, I landed on using PHP for my web application, and all went smoothly as demonstrated by this page.