SEC‐440 Project 3 (Week 4 & 5) ‐ Application Redundancy - FlameSpyro/Tech-Journal GitHub Wiki
- Your presentation layer (the website) should be redundant across web1 and web2 (you did this with an index page in week 3 on web1 and web2)
- Your application should be integrated with a remote and redundant database that can still operate with an outage of one or more of its database servers.
- Your architecture should make use of redundant addresses such that your application is not pinned to an IP address that is liable to be unavailable if the server goes down. For instance, your remote database IP should be a virtual ip.
- Bootup u1, u2, and u3 adding them to the LAN network.
- Configuration: /etc/netplan/press tab
network:
version: 2
renderer: networkd
ethernets:
ens160:
dhcp4: no
addresses:
- 10.0.5.**201**/24
gateway4: 10.0.5.1
nameservers:
addresses: [10.0.5.1, 8.8.8.8]
- Repeat for all 3 just have
- u1 = 10.0.5.201
- u2 = 10.0.5.202
- u3 = 10.0.5.203
- Once completed make sure to:
sudo netplan apply
- This is the process that will help install MariaDB a sql database that will let us create databases and tables that we can share accross multiple devices or in this lab, nodes.
- On all 3 devices:
sudo apt-get update
sudo apt-get install mariadb-server -y
sudo systemctl start mariadb
sudo systemctl status mariadb
sudo mysql_secure_installation
Enter current password for root (enter for none):
Switch to unix_socket authentication [Y/n] n
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y
- On u1-3
sudo nano /etc/mysql/conf.d/galera.cnf
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://10.0.5.201,10.0.5.202,10.0.5.203"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="* 10.0.5.201"
wsrep_node_name="**mariadb01**"
- Change bolded to proper node number/IP
# On all machines
sudo systemctl stop mariadb
- Dont turn on u1, instead:
sudo galera_new_cluster
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
- On u2 and u3:
systemctl start mariadb
sudo systemctl status mariadb
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
- On u1:
mysql -u root -p
CREATE DATABASE testnamedb;
SHOW DATABASES;
USE testnamedb;
CREATE TABLE names (id int, name varchar(20), surname varchar(20));
SHOW TABLES;
INSERT INTO names VALUES (1,"leon","kennedy");
INSERT INTO names VALUES (2,"ashley","grahm");
SELECT * FROM names;
- It should pump out a table with the names
- On u2, add a new name
mysql -u root -p
SHOW DATABASES;
USE testnamedb;
SELECT * FROM names;
INSERT INTO names VALUES (3,"chris","redfield");
SELECT * FROM names;
- On u3:
mysql -u root -p
SHOW DATABASES;
USE testnamedb;
SELECT * FROM names;
INSERT INTO names VALUES (3,"jill","valentine");
SELECT * FROM names;
- If the chart has all names on it on u3 then congrats! You have all 3 nodes properly running!
- Now to start connecting the puzzle pieces. Haproxy will let us create load balancing on the 3 clusters and backups incase one goes down the database will live on.
- On u1:
mysql -u root -p
CREATE USER 'universal_user'@'%';
FLUSH PRIVILAGES;
GRANT ALL PRIVILEGES ON *.* TO 'universal_user'@'%' IDENTIFIED BY '<**password**>' WITH GRANT OPTION;
SELECT User, Host FROM mysql.user;
-
The % means the user will be compatible on all devices and can be replaced by a ip if needed. This also elevates the user to highest compatibility.
-
On ha1 and ha2:
sudo sed -i "s/ENABLED=0/ENABLED=1/" /etc/default/haproxy
service haproxy
sudo apt-get install mysql-client
sudo nano /etc/haproxy/haproxy.cfg
frontend db
listen mysql-cluster
bind *:3306
mode tcp
default_backend dbpool
backend dbpool
balance roundrobin
mode tcp
server mysql-1 10.0.5.201:3306 check fall 3 rise 2
server mysql-2 10.0.5.202:3306 check fall 3 rise 2
server mysql-3 10.0.5.203:3306 check fall 3 rise 2
- On u1-u3
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
change bind address to 0.0.0.0
save and exit
sudo systemctl restart mysql
- On ha1-ha2
sudo service haproxy start
service haproxy status
reboot
mysql -h 0.0.0.0 -u haproxy_check -e "SHOW DATABASES"
- On ha1-ha2
mysql -h 0.0.0.0 -u haproxy_ch* eck -p -e "show variables like 'hostname'"
mysql -h 0.0.0.0 -u haproxy_check -p -e "show variables like 'hostname'"
mysql -h 0.0.0.0 -u haproxy_check -p -e "show variables like 'hostname'"
- The table should juggle between u1-u3 every time you enter the command
- I ripped this straight from Adam's guide provided as the other stuff I found was too complicated for what we needed to do.
- This is where I struggled the most and needed to get help from my professor to fix. Make sure to do this on both web01 AND web02
- To start
sudo yum install php php-mysql
Enter "Y" to install.
Restart Apache:
sudo systemctl restart httpd.service
- I reccomend ssh-ing into web01 from xubuntu-LAN
mysql -u root -p
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
);
- Then:
INSERT INTO cats ( name, owner, birth) VALUES
( 'Siggy', 'Harold', '2018-01-05' ),
( 'Freya', 'Gutenberg', '2017-11-10' ),
( 'Patches', 'Charlie', '2015-05-15' );
- Create a file in the /var/www/html directory called
birthday.html
<html>
<body>
<form action="birthday.php" method="post">
Look up you cat's birthday! </br>
Cats Name: <input type="text" name="name"></br>
<input type="Submit">
</form>
</body>
</html>
- Then a file called
birthday.php
<?php
//Set the vars used for your DB connection, using new acct creds
//Also, bet you can't guess what my new Mariadb account & password are? :)
$username="universal_user";
$password="**password you entered**";
$database="pets";
//Acquire the Cat's name from your html POST
$name=$_POST['name'];
//Establish MySQL connection called $mysqli
$mysqli=10.0.6.10('localhost',$username,$password,$database);
//Define your query - pay close attention to ' and "!
$query="SELECT * from cats where name='".$name."'";
//Run query - result is reurned as a resource id
//If query has error, _LINE_ will print the error from mysql
$result=$mysqli->query($query) or die($mysqli->error.__LINE__);
//Resource id results must be iterpreted
//This while loop will run thru the results row by row & echo name & birth fields
if ($result->num_rows > 0) {
while($row=$result->fetch_assoc()) {
echo $row['name']."'s birthday is ".$row['birth']."</br>";
}
}
else {
echo 'NO RESULTS';
}
?>
- This was probably one of the most difficult projects I have done. I had to bring my lab late to show my professor because of the php having errors. But after that I finally got it! I can see the fun/use of making a database form like this. Just got to be prepared next time.