Tomcat in AWS – Part 4 – Install MariaDB / MySQL

Uncannier Software April 17, 2019

In this article we add MariaDB (MySQL) to our Tomcat Ubuntu 18.04 LTS server in AWS EC2. I cover the basics of how to integrate it with your Tomcat web applications. If you’ve been playing along at home, you know this is part 4 of a series that started here.

Installation

Installing MariaDB in Ubuntu is a bit of a doddle if you use the Ubuntu package manager. Life’s too short to make harder work of it, unless you really need to.

sudo apt install mariadb-server

This installs it to /var/lib/mysql

You can check the version using the following command.

mysql --version

Secure The Installation

The mysql_secure_installation script is a thing of wonder and beauty. Run it, pay attention to what it’s telling you, accept all defaults, and set a root password you won’t forget.

sudo mysql_secure_installation

Create Users & Databases

Start the mysql client using the root user, and the password created in the last step.

sudo mysql -u root -p

Execute commands similar to the following to create a database user or users, and databases that can be managed by that user. In my case, a single database named foobar that is managed by user foobaruser.

CREATE DATABASE foobar;
CREATE USER 'foobaruser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON foobar.* TO 'foobaruser'@'localhost' IDENTIFIED BY 'password';

Create Data

You can obviously then create data as you see fit, or import data that has been exported from other databases. In my case I make one table, named foobar_stuff, with just one column and one row, containing an integer value of 42.

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 67
Server version: 10.1.38-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
 
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 foobar
Database changed
 
MariaDB [foobar]> create table foobar_stuff(value INT);
Query OK, 0 rows affected (0.11 sec)
 
MariaDB [foobar]> insert into foobar_stuff values(42);
Query OK, 1 row affected (0.00 sec)
 
MariaDB [foobar]> select * from foobar_stuff;
+-------+
| value |
+-------+
|    42 |
+-------+
1 row in set (0.00 sec)
 
MariaDB [foobar]>

Integration With Tomcat

With the most minimal database in place, I now make the most minimal Tomcat web application that uses it. Being so minimal, it’s just a single JavaServer Page (JSP) with no Java Servlets or other Java code.

Web App Structure

The Tomcat web application we are about to create has just 5 files in 3 directories, and is deployed as the ROOT web application.

Context

The context.xml file can be used to define a data source for a web application. In this case I give the data source a name of jdbc/foobar and it refers to the MariaDB SQL foobar database we created earlier. The web application will access the foobar database using the credentials we created in this post and the org.mariadb.jdbc.Driver will be the JDBC driver used.

<Context path="" docBase="ROOT">
    <Resource name="jdbc/foobar" auth="Container" type="javax.sql.DataSource"
                username="foobaruser" password="password" driverClassName="org.mariadb.jdbc.Driver"
                url="jdbc:mysql://localhost/foobar"/>
</Context>

Refer to Tomcat’s documentation for more information on JDBC Data Sources: https://tomcat.apache.org/tomcat-8.5-doc/jndi-resources-howto.html#JDBC_Data_Sources

Libraries

I require 3 libraries for this miniature web application.

javax.servlet.jsp.jstl-api.jar	JavaServer Pages Standard Tag Library (JSTL) API
javax.servlet.jsp.jstl.jar	JavaServer Pages Standard Tag Library (JSTL) Core
mariadb-java-client-2.4.0.jar	MariaDB JDBC Driver (org.mariadb.jdbc.Driver)

The JSTL libraries are because I want to use a few JSTL tags in my JSP. The MariaDB JDBC Driver is needed for the JDBC Data Source defined in context.xml.

JSP File

I define an index.jsp file. By using that name, it is served automatically as the base document so that no URI is needed in the request.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
 
<html>
 
<head>
<title>foobar.com</title>
</head>
 
<body>
 
<sql:query dataSource="jdbc/foobar" var="result">
     SELECT * from foobar_stuff;
</sql:query>
 
<p>Welcome to foobar.com!</p>
<p>The MariaDB database <strong>foobar</strong> has a table <strong>foobar_stuff</strong> 
containing value: <c:out value="${result.rows[0].value}"/></p>
</body>
 
</html>

The JSP file uses the defined Data Source to access the foobar_stuff table and display the value contained in its sole cell. JSTL tags are used to connect to the data source, and display the value.

And here’s the final result!

Security Hardening

There are of course various security hardening guides on the web. For example:

If you installed MariaDB from Ubuntu’s repositories as I described, and ran the mysql_secure_installation script, then you have already satisfied most of the common tips. Here I comment on a few others of particular interest.

Port 3306

It’s often recommended to change the default TCP port of 3306. However, in this series, we are building an AWS server in which MariaDB is intended to be accessed only by the Tomcat instance running on the same server. The AWS Security Group we created in part 1 did not open port 3306, and hence there is no possibility for that port to be accessed by any other server. Thus I leave the default alone.

TLS

As we intend for MariaDB to only be used by Tomcat on the same server, TLS is also of no interest.

Shell History

If you’ve followed my steps, your server has just the ubuntu user for server administration. This user can only login with a PEM file, thus preventing brute force password attacks. And your AWS Security Group should whitelist your IP address as the only one that can access the SSH port. Thus, no-one but you, is ever going to see the MySQL shell history. In other words, there’s no need to disable the shell history

Tagged aws, web

Uncannier Software