Lab 6: SQL Databases and Web Applications

Introduction

In this lab you will perform the following tasks:

  • Install MariaDB

  • Perform Basic MariaDB/MySQL Database Tasks from the Command Line

  • Install a GUI Database Management Application

  • Perform Basic MariaDB/MySQL Database Tasks from a GUI Management Application

  • Install and Test the Wordpress Content Management System Web Application

You will be introduced to the following commands:

  • mariadb

  • mysql

Preliminaries

  1. Open an SSH remote terminal session to your Linux server’s IP address

    1. Connect to ITCnet from the computer you will be using as your administrative PC. In most cases this means connecting to the ITC Student VPN (unless you are using the Netlab Windows Administrative PC).

    2. Run the PuTTY software on your computer (or the Windows Administrative PC) and enter in the IP address of your Linux server VM in the "Host Name" box and click the "Open" button.

      Remember that if you do not have a Windows computer to connect from you can either figure out how to SSH from your own computer over the VPN to your Linux server or you can use the Windows Administrative PC that is provided for you in Netlab.

  2. Login with your standard user’s username and password

Install MariaDB

  1. MySQL is widely popular relational database management system (RDMS) originally developed in the mid-1990s. MySQL gained prominence due to its open-source nature, reliability, and performance. It became the go-to choice for web applications, powering websites, content management systems, and countless other services. Its simplicity, speed, and scalability contributed to its widespread adoption. In 2009, Oracle Corporation acquired Sun Microsystems, which owned MySQL. This caused concerns in the community about the future of MySQL under Oracle’s stewardship and the development of MariaDB as a fork of MySQL by the community. Over time some new features have been added but there remains a committment to remain compatible as a drop-in replacement for MySQL and the terms MariaDB and MySQL are largely used interchangably.

  2. Most Linux distributions today favor MariaDB over MySQL. When users request MySQL installations from a package manager, they actually often end up with MariaDB. The transition is seamless because MariaDB remains mostly compatible with MySQL and typically includes links so that even if you use MySQL command names the MariaDB equivalents will run. As a result in this lab activity we’ll install and use MariaDB as the actual database backend but commands may still reference MySQL as that is typially what you see in application configuration instructions.

  3. Use apt to install the mariadb-server package on your Linux server.

  4. Because we know that we eventually will want to access our MariaDB database from web applications that are written in PHP we also need to install the connector module that allows PHP to interact with MySQL/MariaDB databases. Again, use apt to install the php-mysql package.

  5. If you run the command mysqladmin version on your Linux server with root permissions you should see that MariaDB is installed as well as the version of the server software and some additional information:

    ben@2480-Z:~$ sudo mysqladmin version
mysqladmin  Ver 9.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.11.6-MariaDB-0+deb12u1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /run/mysqld/mysqld.sock
Uptime:                 4 hours 22 min 42 sec

Threads: 1  Questions: 1864  Slow queries: 0  Opens: 136  Open tables: 112  Queries per second avg: 0.118

  6. If you check your PHP Info page at http://172.17.xx.xx/phptest.php you should now see that PHP has the MySQL PDO driver loaded.

Experiment with an SQL Database

  1. SQL is a standard language for interacting with a specific type of database called a relational database, we’ll see why it’s called a relational database in a minute. Each database is a collection of data stored in tables. You can think of a table as something like a spreadsheet with rows and columns, except we call the columns fields and the rows records (sometimes we actually call them rows too). The different tables are often related to one another by using identifer which are found in multiple tables to match the records.

  2. To jumpstart our exploration of databases we’ll download a pre-built sample database with a few tables and records already in it and import it into our MariaDB database server. Use the wget program to download the sample database from https://info.ihitc.net/icecream_db.tar.gz

  3. Unpack the compressed icecream_db.tar.gz file in your home directory

  4. Use a text file viewer to view the icecream_schema.sql file. This text file describes the format (schema) for the database. That includes a list of all the database tabes as well as what fields each table includes.

  5. Use a text file viewer to view the icecream_data.sql file. This text file includes all of the data records for the database which get stored into the tables.

  6. When restoring a database backup or loading an initial database for some application there are many times where the schema and some initial data are just included together in one SQL file you can import, that is what the icecream_combined.sql file has in it.

  7. We can interact with the MariaDB server in many different ways. The first way we’ll take a look at is through the command line mysql program. MariaDB has it’s own set of users which are separate from the Linux server’s system users but they still call the super user account the root user. Right now that MariaDB root user is the only account that exists. To log into it we need to run the mysql program as the system root user like sudo mysql.

    ben@2480-Z:~$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

    MySQL has it’s own command line language the you can use through the MariaDB [(none)]> prompt you are now seeing. For example, if we want to see a list of databases on the system we can run SHOW databases;. Run the command and note the databases that already exist by default and are used internally by MariaDB for storing data (like it’s own usernames and passwords).

    MariaDB [(none)]> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.001 sec)
    Each MySQL/MariaDB statement ends with a semicolon (;). If you do not put the semicolon at the end of the line the command will not execute when you press enter and you will be able to continue to add more commands to the statement before finishing with a semicolon. If you accidentally press enter without ending with a semicolon you can just enter a semicolon on the next line and press enter.

  8. Create a new database for our sample ice cream data using the CREATE DATABASE `icecream`; command. You should see a confirmation that this worked, but it’s always worth double checking!

    MariaDB [(none)]> CREATE DATABASE `icecream`;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| icecream           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)

  9. If we want to select a certain database for use we can use the USE `icecream`; command at the MariaDB prompt. You’ll see if you do this that the name of the active database shows up in the default prompt. It will change from [(none)] to [`icecream]` like this:

    MariaDB [(none)]> USE `icecream`;
Database changed
MariaDB [icecream]>

  10. There are a few ways that an SQL file can be imported into a MariaDB database. We’ll try two of them. First, if we’re already at a MariaDB prompt and set up to use the database we want to import into we can use a statement like source icecream_db/icecream_combined.sql;. If you do this you’ll see a bunch of lines with Query OK go by on your screen. You should have one of these for each SQL statement that is in the file you’re importing.

    Because when we started the mysql program we were in our home directory and the icecream_combined.sql file we want to import was in the icecream_db directory inside our home directory we needed to specify the path to the file as icecream_db/icecream_combined.sql in the source statement.

  11. To try creating a database and importing an SQL file from the standard Linux command shell we must first get rid of our existing database which we can do with the DROP DATABASE `icecream`; statement.

    Obviously, the DROP statement is one to be careful with as you can easily wipe out a huge database with one line!

  12. Next we’ll exit back to our Linux command shell using the exit; command.

  13. Try creating the icecream database using the mysql -e 'CREATE DATABASE icecream;' command.

    ben@2480-Z:~$ sudo mysql -e 'CREATE DATABASE icecream;'

  14. You can then import the SQL file from the command line as well using redirection and the command mysql icecream < icecream_db/icecream_combined.sql.

    ben@2480-Z:~$ sudo mysql icecream < icecream_db/icecream_combined.sql
    When we were working inside the MariaDB shell we were able to put backticks (`) around the database name. This is the preferred way of doing things with MariaDB as it lets us use special characters in the database or table names if we want and keeps identifiers separate from commands which is a security benefit. However, it’s not possible to use these with commands when issued from the Linux shell which might be a good argument for creating and working with databases from inside the MariaDB shell even though many instructions for loading databases on the Internet just have you do it from the Linux shell.

  15. Next, let’s try going back into the MariaDB shell and running a few SQL statements which can show us some of the power of our database.

    1. Access the MariaDB shell again and select the icecream database for use.

      ben@2480-Z:~$ sudo mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 60
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> USE `icecream`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [icecream]>

    2. List the tables in the database with the SHOW tables; statment:

      MariaDB [icecream]> SHOW tables;
+--------------------+
| Tables_in_icecream |
+--------------------+
| flavor_ingredients |
| flavors            |
| ingredients        |
+--------------------+
3 rows in set (0.001 sec)

    3. Here you can see that we have three tables in the database. Check what fields (columns) are in the flavors table with the SHOW FIELDS FROM `flavors`; command.

      MariaDB [icecream]> SHOW FIELDS FROM `flavors`;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| flavor_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| flavor_name | varchar(255)     | YES  |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.002 sec)

    4. Try getting a list of all information in the flavors table with the SELECT * FROM `flavors`; query.

    5. Whoa, that was too much information! The descriptions are pretty long so let’s just get the flavor_id and flavor_name using SELECT `flavor_id`, `flavor_name` FROM `flavors`;

    6. That’s more like it, but what if we’re just testing things and we only want to have the first five flavors in the table? Try using SELECT `flavor_id`, `flavor_name` FROM `flavors` LIMIT 5;

    7. OK, now let’s look at the power of a database over a spreadsheet by querying the database to only give us the flavor name if it includes the word chocolate with this query (you could again limit this to the first few reponses if you wanted too):

      SELECT `flavors`.`flavor_name`
FROM `flavors`
WHERE LOWER(`flavors`.`flavor_name`) LIKE '%chocolate%';
      You may have noticed there is a section in our query LOWER(). This turns all the text in that field temporarily into lower case so we don’t have to worry how it’s capitalized when we’re searching it.

    8. We can look for descriptions that have the word chocolate in them if we wanted too:

      SELECT `flavors`.`flavor_name`, `flavors`.`description`
FROM `flavors`
WHERE LOWER(`flavors`.`description`) LIKE '%chocolate%';
      You can leave off the `flavors`.`description` part in the SELECT line if you want to search the descriptions but not show them. You can also limit the results to the first few responses by adding a LIMIT statement as in the previous examples as well.

    9. What really makes a relational database special compared with a single table (think spreadsheet) is that even more advanced queires are possible. For example, we can search through the table of ingredients for an ingredients that include a certain word and get the ingredient ID, then look for all flavors that contain that ingreditent and get a list of them. An example of this type of query where we’re looking for all flavors that have cocoa as an ingredient looks like:

      SELECT DISTINCT `flavors`.`flavor_name`, `flavors`.`description`
FROM `flavors`
JOIN `flavor_ingredients` ON `flavors`.`flavor_id`=`flavor_ingredients`.`flavor_id`
JOIN `ingredients` ON `ingredients`.`ingredient_id`=`flavor_ingredients`.`ingredient_id`
WHERE LOWER(`ingredients`.`ingredient_name`) LIKE '%cocoa%';

    10. The same is also possible if we want to look for all flavors that do not have a specific ingredient. If somone had an allergy to eggs and we wanted to find all flavors without egg in them we could use:

      SELECT `flavors`.`flavor_name`
FROM `flavors`
WHERE `flavors`.`flavor_id`
NOT IN (
	SELECT `flavor_ingredients`.`flavor_id`
	FROM `flavor_ingredients`
	WHERE `flavor_ingredients`.`ingredient_id`
	IN (
		SELECT `ingredient_id` FROM `ingredients` WHERE LOWER(`ingredients`.`ingredient_name`) LIKE '%egg%'
		)
  );
      There are often many ways to create SQL queries that return the same results, especially with more complex queries. There are often advantages and disadvantages to each way you might create a query. For example, in some cases JOIN statements may be more efficient (faster) and so they are a better choice. In other cases it may be more desirable to use IN and NOT IN statements to tie multiple queries together because it’s easier to understand what’s happening when reading through the query. In still other cases, programmers may make multiple simple queires to the databse and then join the information from multiple queires together in a programming language instead. Whole careers can be made out of designing database queries so our goal here is not to make you and expert but to show you the types of things possible with a relational database such as MySQL or MariaDB.

Install a GUI Database Management Application and Connect to Your Database

  1. In many cases it is desirable to have a graphical environment to create, update, or query a database running on a remote server. The MariaDB server software can support connections from remote clients (such as your administrative PC) and allow those clients to be used to administer the database. There are many different graphical clients available but just as an example we’ll use Beekeeper Studio because it is available for Windows, Mac, and Linux and a free basic version is available.

  2. Start by downloading the latest community version of Beekeeper Studio Community Edition to your administrative PC. Note that you do not need to provide an email address, just click the "Skip to the download" link on the download page.

  3. Install the software as you normally would for your administrative PC platform. If you’re using a Windows administrative PC such as the one in Netlab or your own this will involve running a setup.exe file and completing the installation wizard.

  4. After completing the installation launch the Beekeeper Studio software on your administrative PC.

    Freshly installed copy of Beekeeper Studio Opening

  5. In the "New Connection" dialog on the right side of the screen click the "Connection Type" dropdown and choose "MariaDB".

    Selecting MariaDB connection type

  6. You should see an expanded dialog box to create a new connection to your database. For security reasons our database server is only set to allow connections from the localhost (the server itself) and not from other computers over the network. This is a best practice unless you have other security measures in place. However, we can still make a localhost connection to the server from itself if we SSH to the server first. This is a common workaround which retains better security while still allowing remote access for people with accounts on the system. To access the SSH settings move the slider to enable the "SSH Tunnel" section of the dialog box.

  7. Now you’ll see that there is a section for an SSH hostname, authentication method, and username as well as a database host, user, password, and default database. Because we are using SSH usernames and passwords and haven’t yet learned about other SSH authentication methods you should also change the "SSH Authentication" option to "Username & Password" which should add an SSH Password box to fill in.

    Blank new connection dialog

  8. Before we can actually connect to the database we’ll need to set up a user and password for the icecream database. As we previously mentioned these are separate for system users and passwords and so far we have just been using the root database user without a password which is only possible from the command line when using sudo to run the mysql command, which is not possible for a remote connection.

    1. Use your Linux shell SSH session to run the MariaDB shell as the administrative user again with the sudo mysql command.

    2. Use a MariaDB statement like CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; to create a new database user. In the example below we’ll use icdbuser as the username and icdbpassword as the password. The localhost part of the command here means the user will only be able to log on from the local system and not remotely (except if they are already connected to the local system such as with SSH). Be sure to make a note of the username and password you choose.

    3. Just creating the user is not enough. With MariaDB you can also be very specific about what databases (and even what tables inside of databases) the user can work with (in case you have many databases on the same server) and what things the user can do in the database (such as only read the database or also make various types of changes). In our case we’ll use a statement like GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'; with the icecream database listed. The .* after the database name means this applies to all of the tables inside of the database. This statement will allow the user listed to do anything they want inside of the icecream database as long as they are logged in through the localhost.

    4. In order to have the new account and privileges take effect we will finish with a FLUSH PRIVILEGES; statement

      MariaDB [(none)]> CREATE USER 'icdbuser'@'localhost' IDENTIFIED BY 'icdbpassword';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON icecream.* TO 'icdbuser'@'localhost';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit;
Bye

  9. Now that we have a database user created we can fill out the Beekeeper Studio connection form. Be sure to enter the MariaDB username and password as well as the database name in the database connection section. In the SSH Tunnel section you will use the IP address of your Linux server as the SSH Hostname, and your regular Linux server username and password in the SSH User and SSH Password boxes. Lastly, to make it convenient to re-connect in the future we can give the connection a name like "IceCream" and click the "Save" button at the bottom of the dialog. After saving the connection it should show up under the "Saved" part of the window on the left hand side.

    Filled new connection dialog

  10. Click the "Connect" button to connect to the database.

  11. You should now see the left side of your screen has changed to "Entities" and shows the three tables in your database. If you click the arrow to the left of each table name you can expand these to show the fields contained in each table.

    Expanded table list showing fields

  12. If you right-click on a table name and choose "View Data" the right side of your screen you should get a new tab at the top of your screen with the name of the table in it and be able to browse through the table. Note that not all records are shown on the screen if it’s a big table, there is a page forward/back button at the bottom of the screen you cna use to go to additional pages of data if they exist.

    Viewing data from flavors table

  13. You can also double click in a table cell and make a change to the table data. When you press enter the cell in the table will turn yellow and the "Apply" button in the lower right will turn dark. This means there is something different on your screen than is really in the database. To save your change to the database you can click the "Apply" button or to cancel the change press the "Refresh Table" button just to it’s right. To add a new record to the table press the "+" button even further to the right.

    Flavors table with pending change

  14. You can close the view of the table data by clicking the dot or "x" to the right of the table name in the tab at the top of the screen. A dot indicates there are unsaved changes in the table that you may want to apply before closing the table.

  15. Of course you can run the same type of queries we did from the command line too. Just use the "Query #1" tab and type or copy in your query statement and then click the "Run" button on the right hand side. Try running all of the same queries we did before from the command line. Here’s an example of one of them:

    Sample query in Beekeeper Studio

  16. Many times it can be helpful to have a graphical environment for building and testing database queries or making other changes to the database because you can do things like resize columns for easier viewing in an way that is not so flexible in a command line environment. You can also do things like easily copy and paste names of tables and fields into queries when building them.

Install and Test the Wordpress Content Management System

  1. Now let’s see how we can combine our web server running PHP and a database system to run one of the most popular web applications on servers. Wordpress is a content management system which was originally desinged as blogging software but now runs millions of websites of all kinds and allows people without HTML experience to write content, run web stores, and other activities which can have a template desinged by an HTML designer applied to them automatically creating an easy to use website.

  2. It is suggested you reath through this entire section before starting your installation as there is a hints and tips section at the end which may be helpful to see before you get started.

  3. Start by using wget to download the file https://wordpress.org/latest.tar.gz onto your Linux server

  4. Next, review the very well written Wordpress Installation Guide which contains both basic and detailed instructions.

  5. You goal is to install Wordpress in such a way that it is accessed by going to http://172.17.xx.xx/blog (obviously with your own server’s IP address). In other words it should not be the main page of your web server but need the /blog on the end of the URL. An example of a working front page is:

    Wordpress working front page

  6. Once you have completed the Wordpress installation be sure to log in to the admin interface through your web browser http://172.17.xx.xx/blog/wp-admin and pick a theme and create a few short blog posts to make sure they show up and everything is working correctly. An example of the Wordpess administrative interface:

    Wordpress administrative dashboard

  7. Here are a few hints, tips, and guidance that may assist you in combination with the official installation instructions.

    1. You will need to download and untar the Wordpress software.

    2. You will need to move the Wordpress files that you unpacked to the correct location on your server’s filesystem. The name of the directory you put them in will be important. Remember you want the software to work when users visit http://172.17.xx.xx/blog

    3. You will need to create a new MariaDB database, a MariaDB user/password, and give that user permissions to work in the database. Be sure to write down the database name, username, and password you create.

    4. You may need to create a copy of the wp-config-sample.php Wordpress configuration file and edit the file to put in your hostname (localhost), database name, database username, and password. Whether you need to do this depends on if the installation wizard works for you.

    5. You will need to make sure that the directory containing all your Wordpress files and all the files and subdirectories inside it are owned by the www-data user and group.

    6. The first time you access your Wordpress site from a browser it will run through an installation wizard where you will pick an administrative username and password for the Wordpress administration pages. Be sure to write these down so you can login!

  8. There are many other PHP web applications including discussion forums, photo sharing, web stores, etc. that can be installed on a web server with PHP and a database just like Wordpress. They mostly all install in a similar way (creating a database, database user, copying files, editing a configuration file) though there are some small variations and the instructions are often not quite as good as Wordpress has. If you’re interested you should be able to find plenty of free open-source PHP applications by searching for them online. Do note that PHP web applications frequently have security bugs that are found and so if you run one on the public internet it’s very important to keep it up to date or your server could be compromised!

Wrapping Up

  1. Close the SSH session

    1. Type exit to close the connection while leaving your Linux server VM running.

  2. If you are using the Administrative PC in Netlab instead of your own computer as the administrative computer you should also shut down that system in the usual way each time you are done with the Netlab system and then end your Netlab Reservation. You should do these steps each time you finish using the adminsitrative PC in future labs as well.

You can keep your Linux Server running, you do not need to shut it down.

Document Build Time: 2024-10-30 23:55:41 UTC
Page Version: 2024.01
Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License