Setting Up MySQL 8 for CS3220

This document describes how to set up a MySQL 8 database server on your local computer for development, and how to use the MySQL 8 server on CS3.

Note that installing MySQL on your own computer is not required as you can use the one on CS3, but you may find it more convenient to have a local database setup, and it also allows you experiment with some database management functionality (e.g. security) that require superuser privilege.

1. Software Installation

Install the MySQL database server as described in MySQL Database Server Installation.

2. Post Installation Setup

The purpose of this step is to create a local database setup that matches the one on the CS3 server, so you can run the same servlet/JSP code on both your local computer and on CS3 without any changes.

Download the mysql-postinst.sql script. This script performs the following operations:

• Create a new user cs3220stu31 with password abcd.
• Create a new database cs3220stu31.
• Grant full privileges on the database cs3220stu31 to the user cs3220stu31.
• Create a table items with two records in the cs3220stu31 database for testing purposes.

Edit the script so that the database name, the username, and the password match the ones for your account on the CS3 server, then run the script on your local MySQL.

3. Testing the Local Database Setup

If you have set up your development environment as described in Web Development with Eclipse and Tomcat, you would already have the MySQL JDBC driver installed; otherwise you will need to install the driver by adding the JAR file to the /WEB-INF/lib folder of your project.

Create a servlet HelloJDBC.java. Use HelloJDBC.java as a template, and modify the source code so the database name, username, and password match your setup. Compile and run the servlet, and you should see following output:

milk 3.89 2.0
beer 6.99 1.0

4. Testing Database Access on CS3

Connect to your MySQL database on CS3.

Create a table items, then insert two rows into the table as follows:

create table items (

    name varchar(32),

    price decimal(8,2),

    quantity int

);

insert into items values ('milk', 3.89, 2);

insert into items values ('beer', 6.99, 1);

Deploy your HelloJDBC servlet on CS3, and they should produce the same results as in the previous step.