Lab 06 - MySQL Server

Database services are an essential service for web and business applications. MySQL is a widely used open-source relational database management system that efficiently stores, retrieves, and manages data for a wide range of applications, from small websites to large enterprise systems. Its reliability, performance, and scalability make it a popular choice among developers and businesses for managing structured data.

In this lab, you will install the MySQL server software, phpmyadmin and webmin web application.

MySQL Server Installation

Install the mysql-server and mysql-client packages. The installation script takes care of installing the application and the baseline configuration.

sudo apt update
sudo apt install mysql-server mysql-client
sudo apt update
sudo apt install mysql-server mysql-client

Note: The installation scripts implements default configuration to get you started. The default installation contains sample users and databases, and other insecure elements. If this is a production server, you will want to read the documentation and apply security best practices and secure configurations. For example read the man pages for mysql_secure_installation as minimum hardening steps, however, do NOT execute this script for this lab.

You can manage MySQL server using the service management built into the OS (i.e. service and systemctl in Ubuntu) or use the mysqladmin tool. Each method provides a different level of access and insight. View the service status for MySQL and compare it to the output of the mysqladmin utility:

# Show the status of the service from the OS perspective, and a few log entries
sudo service mysql status

# Shows more specific information about the DB operations and performance
sudo mysqladmin -u root processlist
sudo mysqladmin -u root status
# Show the status of the service from the OS perspective, and a few log entries
sudo service mysql status

# Shows more specific information about the DB operations and performance
sudo mysqladmin -u root processlist
sudo mysqladmin -u root status

Examine the content of error.log in /var/log/mysql to see what is being logged for your mysql server. For example, use:

sudo tail /var/log/mysql/error.log
sudo tail /var/log/mysql/error.log

Database Backup

Use the mysqldump tool to dump the MySQL database to a file named mysql-backup.sql in the root's home directory.

# Create a database dump of the "mysql" database
sudo mysqldump -u root mysql --result-file=/root/mysql-backup.sql

# Use the `more` command to see what the `.sql` backup file contains
sudo more /root/mysql-backup.sql
# Create a database dump of the "mysql" database
sudo mysqldump -u root mysql --result-file=/root/mysql-backup.sql

# Use the `more` command to see what the `.sql` backup file contains
sudo more /root/mysql-backup.sql

Database Access and Authentication

Use the mysql command line client tool to log into the MySQL server.

# mysql is a CLI client utility, connects to the local server as the "root" user (-u)
sudo mysql -u root
# mysql is a CLI client utility, connects to the local server as the "root" user (-u)
sudo mysql -u root

If your authentication from the above command is successful, you should be logged into MySQL and see something like the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.44-0ubuntu0.24.04.2 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.44-0ubuntu0.24.04.2 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

View what databases currently exist (do not forget the ; at the end of each SQL query!):

show databases;
show databases;

This should output a list of several DBs. These should be the default DBs at this point in your lab. Review the list of tables for the mysql database, and then see what columns the user table has:

show tables from mysql;
show columns from user from mysql;
show tables from mysql;
show columns from user from mysql;

As you can observe, MySQL has its own users and authentication stored inside the mysql database tables. The user accounts are NOT the same as Linux user accounts. Even if you create MySQL users with the same names, they will have their own credentials stored inside the mysql database. Additionally, you can modify and configure various methods of authentication for different accounts. You can now see what kinds of attributes a user entry can have and what data type each field can contain based on the definition of the columns. Additionally, notice that your earlier mysqldump has made a backup of the mysql database only and not anything else!

View what specific users currently exist in your user table and the authentication plug-in they are using:

SELECT user,plugin,host FROM mysql.user;
SELECT user,plugin,host FROM mysql.user;

The MySQL root account is currently using auth_socket for authentication. Set a password for the database root user. Make sure to set the password to something that you can remember so you will not lock yourself out!

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'enter_password_here';
FLUSH privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'enter_password_here';
FLUSH privileges;

View the user table again to compare to the last output and observe changes:

SELECT user,plugin,host FROM mysql.user;
SELECT user,plugin,host FROM mysql.user;

You can exit the interactive mysql client:

exit
exit

Verify that you can log back in as root using your password:

# The following will no longer work:
sudo mysql -u root

# instead, you should authenticate using the password:
sudo mysql -u root -p
# The following will no longer work:
sudo mysql -u root

# instead, you should authenticate using the password:
sudo mysql -u root -p

Firewall Rule for Direct MySQL Access Over Network

If you are intending to provide direct client access to your server, you will need to allow the port through the firewall and preferably configure MySQL to listen for incoming connections from specific destinations. For this lab, just add a UFW rule to allow mysql access through your firewall:

sudo ufw allow 3306/tcp
sudo ufw allow 3306/tcp

Note: exposing MySQL port to the public internet without hardening and additional protection measures is not a good idea! However, you may be required to allow network access to database services from application servers or from developer machines over a local or protected network anyway. Additionally, you may be doing this over a physical or virtual/cloud network. Make sure to implement "principles of least privilege", or other protections and mitigations.

The phpmyadmin Web Application

Installing and Configuring the Required Packages

The phpmyadmin is a web-based MySQL client software. It is written in php and uses the mysql database for its own back-end. Users use their mysql credentials to log in to the interface, and based on their mysql user privileges, they can perform tasks on their databases, including creating and modifying databases, tables, users and user permissions.

Verify you can access the phpmyadmin web interface using a web browser on your host laptop to view the database server status and configuration (http://yourserver_IP/phpmyadmin).

Notice: This installation is NOT secure. On top of the additional exposures of having this application installed, you can log into the root account using this interface, AND your credentials are transmitted through HTTP in clear text! This is just a lab running on a VM on your machine and is (hopefully) not exposed. The purpose is to show you how this application works and why some users may prefer this instead of the mysql CLI client. Do NOT leave it this way on any kind of deployments!

Using phpmyadmin Web App

Notice at the top, the actual SQL query that was sent to the DB is also displayed! You should now have a tester user who has a new empty database named tester that tester has full control over.

In case you need to delete the user or issue SQL queries such as FLUSH privileges;, you can use the SQL tab, use the text box, and hit Go to execute.

Restoring a DB via CLI

Download the employees-dump.sql database backup file from https://gorbehnare.github.io/COMP1071/employees-dump.sql to your server:

# For convenience:
sudo su

# Download the example DB dump file from the repo:
wget -O /root/employees-dump.sql https://gorbehnare.github.io/COMP1071//employees-dump.sql

# Restore the database backup into **tester** database:
mysql -u tester -p tester < /root/employees-dump.sql

# Connect to your mysql server, use the **tester** database: 
mysql -u tester -p
# For convenience:
sudo su

# Download the example DB dump file from the repo:
wget -O /root/employees-dump.sql https://gorbehnare.github.io/COMP1071//employees-dump.sql

# Restore the database backup into **tester** database:
mysql -u tester -p tester < /root/employees-dump.sql

# Connect to your mysql server, use the **tester** database: 
mysql -u tester -p

Using the MySQL CLI client, show tables to see what was restored:

use tester;
show tables;
use tester;
show tables;
mysqldump -u tester -p --result-file=/root/tester-backup.sql tester
mysqldump -u tester -p --result-file=/root/tester-backup.sql tester

Did it succeed? If not, use the MySQL root account to attempt the operation again!

Evaluate your server