Project 2: Web Application and MariaDB Database with PHP-MYADMIN

Introduction:   

   We are going to create a web server and a database server on vvm08. Packages use will be Apache for the webserver and MariaDB for a database server. We will create a web page using HTML and PHP which will also be used to connect to the database. We will create database and tables using PHP-Admin.

    In Linux operating system, Apache, MariaDB, and PHP are jointly known as LAMP (Linux, Apache, MariaDB and PHP).

Prerequisite:

• Centos 7 install servers.
• Yum configured.

Environment:

Hostname: vvm08
IP-Address: 192.168.2.201
Application: Web Servers.

Part 1: Apache Web Server Installation on VVM06

Install Apache HTTP Server.

[root@vvm08 ~]# yum install httpd -y

Start the Apache service.

[root@vvm08 ~]# systemctl start httpd

Enable the Apache service (Start automatically on every reboot).

[root@vvm08 ~]# systemctl enable httpd

Check your Apache server status.

[root@vvm08 ~]# systemctl status httpd

Open Firewall ports permanently

[root@vvm08 ~]# firewall-cmd --permanent --zone=public --add-service=http
[root@vvm08 ~]# firewall-cmd --permanent --zone=public --add-service=https

Reload the firewall service.

[root@vvm08 ~]# firewall-cmd --reload

List the port open

[root@vvm08 ~]# firewall-cmd --permanent --zone=public --list-services

We will test Apache web server. Open your web browser and navigate to http://192.168.2.201/

Part 2: MariaDB Database Installation, configuration and creation of database and tables on VVM07

To install MariaDB & MariaDB-server.

[root@vvm08 ~]# yum install mariadb mariadb-server -y

Start the MariaDB service .

[root@vvm08 ~]# systemctl start mariadb

Enable the mariadb service.(Start automatically on every reboot)

[root@vvm08 ~]# systemctl enable mariadb

Check your MariaDB status.

[root@vvm08 ~]# systemctl status mariadb

Setup MySQL root Password

By default, MariaDB does not set root user password. But to secure mariadb, we have to setup root user password. To set root user password, run the following command from your terminal and follow the instructions.

[root@vvm08 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):Enter
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n]
New password: redhat
Re-enter new password: redhat
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!

[root@vvm08 ~]#

Part 3: PHP Installation on VVM06

Install php and php-mysql packages.

[root@vvm08 ~]# yum install php php-mysql -y

To test PHP installation, we will create a simple php file (testphp.php) in Apache document root folder (by default /var/www/html).

[root@vvm08 ~]# vi /var/www/html/testphp.php
<?php
phpinfo();
?>

Now restart httpd service.

[root@vvm08 ~]# systemctl restart httpd

Now open the phptest.php file in your browser following the http://192.168.2.201/testphp.php. It will display all the details about php such as version, build date and commands etc.

Part 3: PHP-MyAdmin Installation on vvm08.

PHP-MyAdmin is a free open source web interface tool that is used to manage MariaDB databases. By default phpMyAdmin is not found in CentOS official repositories. So, we have to install it using EPEL repository.To install EPEL repository

[root@vvm08 ~]#  yum install wget -y

[root@vvm08 ~]#  wget  http://rpms.famillecollet.com/enterprise/remi-release-7.rpm

[root@vvm08 ~]#  wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

[root@vvm08 ~]#  rpm -Uvh remi-release-7.rpm epel-release-latest-7.noarch.rpm

EPEL repository is now ready. So, install phpMyAdmin with the following command.

[root@vvm08 ~]#  yum install phpmyadmin –y

Once phpmyadmin installation is completed we need to configure phpmyadmin.

To get phpMyAdmin web interface, we have to edit the phpmyadmin.conf file.

[root@webserver~]# vi /etc/httpd/conf.d/phpMyAdmin.conf
Now find and comment the whole <Directory> section as shown below.
Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin
#<Directory /usr/share/phpMyAdmin/>
#   <IfModule mod_authz_core.c>
# …
# Allow from ::1
#   </IfModule>
#</Directory>

Now add below lines in this file.

<Directory /usr/share/phpMyAdmin/>
Options none
  AllowOverride Limit
  Require all granted
</Directory>

Now restart the Apache service.

[root@vvm08 ~]# systemctl restart httpd

Login to phpadmin webpage.

https://192.168.2.201/phpmyadmin/

 

User Name : root 

Password: redhat (database password)

Create database using phpmyadmin. 

Database name : database123

Collation : utf8_general_ci

Create Table using phpmyadmin.

 Table Name : entry_details

Columns : 5

Create table fields and save.  

 

Part 4: Create Web page on VVM08

• We will create index.html page 
• We will create sample_register.html page for get the data for web site.
• We will create details_entry.php to send the data collected from sample_register.html page to database
• We will create display-data.php to display data from dababase. 

[root@vvm08 html]# cat index.html

<h1 style="text-align: center;">Welcome to Job Search</h1>

<p style="text-align: center;"><a href="http://192.168.2.201/display-data.php"><strong>Details of users&nbsp;</strong></a></p>

<p style="text-align: center;"><a href="http://192.168.2.201/user-register.html"><strong>Add Information</strong></a></p>

[root@vvm08 html]#

[root@vvm08 html]# cat sample_register.html
<html>
<head>
<title>
A Sample Tutorial for database connection.
</title>
</head>
<body bgcolor="#32e692">
<div align="center">
<h1>Details Entry Form</h1>
</div>
<form action="details_entry.php" method="post">
<table border="1" align="center">
<tr>
<td>
<label>Enter First Name</label></td>
<td><input type="text" name="first_name"></td>
</tr>
<tr>
<td>
<label>Enter Last Name</label></td>
<td><input type="text" name="last_name"></td>
</tr>
<tr>
<td>
<label>Gender</label></td>
<td><input type="radio" name="gender" value="male">Male
<input type="radio" name="gender" value="female">Female</td>
</tr>
<tr>
<td>
<label>Enter Email</label></td>
<td><input type="email" name="email"></td>
</tr>
<tr>
<td>
<label>Enter Phone</label></td>
<td><input type="phone" name="phone"></td>
</tr>
<tr>
<td colspan="2" align="center" ><input type="submit" nam e="save" value="Submit" style="font-size:20px"></td>
</tr>
</table>
</form>
</body>
</html>
[root@vvm08 html]#\

[root@vvm08 html]# cat details_entry.php
//Full Code of php file for mySql database connection with html form
<?php
$server_name="vvm07";
$username="root";
$password="redhat”;
$database_name="database123";

$conn=mysqli_connect($server_name,$username,$password,$database_name);
//now check the connection
if(!$conn)
{
die("Connection Failed:" . mysqli_connect_error());

}

if(isset($_POST['save']))
{
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$gender = $_POST['gender'];
$email = $_POST['email'];
$phone = $_POST['phone'];

$sql_query = "INSERT INTO entry_details (first_name,last_name,gender,email,mobile)
VALUES ('$first_name','$last_name','$gender','$email','$phone')";

if (mysqli_query($conn, $sql_query))
{
echo "New Details Entry inserted successfully !";
}
else
{
echo "Error: " . $sql . "" . mysqli_error($conn);
}
mysqli_close($conn);
}
?>
<html>
<body>
<p style="text-align: center;"><a href="http://192.168.2.201/"><strong>HOME PAGE</strong></a></p>

</body>
</html>
[root@vvm08 html]#

[root@vvm08 html]vi display-data.php

#<!DOCTYPE html>
<html>
<head>
<title> Display data </title>
</head>
<body>
<div align="center">
<h1>Below are user details</h1>
</div>
<table border="1" align="center">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Email</th>
<th>Phone</th>
</tr>
</thead>
<?php
$server_name="localhost";
$username="root";
$password="redhat";
$database_name="database123";
$conn=mysqli_connect($server_name,$username,$password,$database_name);
//now check the connection
if(!$conn)
{
die("Connection Failed:" . mysqli_connect_error());
}
$sql_query = "select * from  entry_details";
$query = mysqli_query($conn,$sql_query);
$nums = mysqli_num_rows($query);
while ($res = mysqli_fetch_array($query)) {
?>
<tr>
<th><?php echo $res['first_name'] ?></th>
<th><?php echo $res['last_name'] ?></th>
<th><?php echo $res['gender'] ?></th>
<th><?php echo $res['email'] ?></th>
<th><?php echo $res['mobile'] ?></th>
</tr>
<?php
}
$conn-> close();
?>
</table>
<p style="text-align: center;"><a href="http://192.168.2.201/"><strong>HOME PAGE</strong></a></p>
</body>
</html>

[root@vvm08 html]#

Part 5: Verify the data in table on VVM08

[root@vvm08 html]# mysql -u root --password=redhat -D database123 -e "select * from entry_details ;"

To check the database size.

[root@vvm08 html]# mysql -u root -p 
# SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;

[root@vvm08 html]# du -sh /var/lib/mysql  

To update Tables using scripts. 

[root@vvm08 html]# cat Update-Table.sh
#/bin/bash
#!/bin/bash
# Purpose: Update data in table entry_details of database123
# Version: 1.0
# Created Date: 29 June 2022
# Modified Date:
# Author : Vallabh Darole

for i in {1..420}
do
cat /root/data/user-data | head -$i | tail -1 > /root/data/tmp
FN=`cat /root/data/tmp|awk '{print $1}'`
LA=`cat /root/data/tmp|awk '{print $2}'`
GN=`cat /root/data/tmp|awk '{print $3}'`
EM=`cat /root/data/tmp|awk '{print $4}'`
MO=`cat /root/data/tmp|awk '{print $5}'`
mysql -u root --password=redhat -D database123 -e "INSERT INTO entry_details (first_name,last_name,gender,email,mobile) VALUES ('$FN' , '$LA' , '$GN' , '$EM' , '$MO')"
done
[root@vvm08 html]# cat /root/data/user-data
Aadi Acharya male Aadi.Acharya@baby.com 1948230775
Aarav Agarwal male Aarav.Agarwal@baby.com 2868424056
Aarnav Khatri male Aarnav.Khatri@baby.com 0975527967
Aarush Ahuja male Aarush.Ahuja@baby.com 2651306134
Aayush Anand male Aayush.Anand@baby.com 2944418539
Abdul Laghari male Abdul.Laghari@baby.com 1399922734
Abeer Patel male Abeer.Patel@baby.com 2167726692
Abhimanyu Reddy male Abhimanyu.Reddy@baby.com 3224506847
Abhiram Bakshi male Abhiram.Bakshi@baby.com 2638719472
Aditya Anthony male Aditya.Anthony@baby.com 2972204959
[root@vvm08 html]#

Backup and Restoration of MariaDB

Update the configuration file

[root@vvm08 ~]# vi /etc/mysql/mariadb.conf.d/50-server.cnf
log_bin = /var/log/mysql/mysql-bin.log

Take backup of database.

[root@vvm08 ~]#  mysqldump -u root -p --all-databases > all-databases.sql

Delete database for testing
MariaDB [(none)]> drop database database123;

Restore of database.
[root@vvm08 ~]# mysql -u root -p <all-databases.sql

 

 

Delete blank line from jobsearch table .
MariaDB [jobsearch]> delete from emp_details where f_name ='';

 Alter database field. 

MariaDB [jobsearch]> ALTER TABLE emp_details MODIFY dob varchar(12) NOT NULL;

 

[root@lamp01 scripts]# cat database.sh
#!/bin/bash
# Database variables
DB_NAME="jobsearch"
DB_USER="root"
DB_PASSWORD="redhat"
DB_DUMP="/opt/db_backups/db.sql"
DB_DUMP_FILE="/opt/db_backups/jobsearch_db01.sql"
# a. Create a new database and user
if mysql -u $DB_USER --password=$DB_PASSWORD -e "use $DB_NAME;" 2> /dev/null; then
echo "Database already exists: $DB_NAME"
else
# Create the database
mysql -u $DB_USER --password=$DB_PASSWORD -e "create database $DB_NAME"
echo "Database $DB_NAME has been created."
# Create the user and grant full access
mysql -u $DB_USER --password=$DB_PASSWORD -e "GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'%' IDENTIFIED BY '$DB_PASSWORD';"
mysql -u $DB_USER --password=$DB_PASSWORD -e "FLUSH PRIVILEGES;"
echo "User $DB_USER has been created with full access to $DB_NAME."
fi
# b. Check if the database is empty and import the dump
RESULT=`mysql -u $DB_USER --password=$DB_PASSWORD -D $DB_NAME -e "SHOW TABLES LIKE 'emp_details' ;"`
if [[ -n $RESULT ]]; then
echo "Table Exist."
else
echo "Database is not empty."
# Import the database dump
mysql -u $DB_USER --password=$DB_PASSWORD -D $DB_NAME < "$DB_DUMP"
echo "Imported database dump into $DB_NAME database."
fi
# c. Take a MySQL dump
mysqldump -u $DB_USER --password=$DB_PASSWORD $DB_NAME > "$DB_DUMP_FILE"
echo "MySQL dump taken and saved as $DB_DUMP_FILE."