MySQL Setup and Configuration for CS320

This document describes a step-by-step procedure to install a MySQL 5.0 database server under Windows 2000. If you already have an older version of MySQL installed, you do not have to upgrade it, since we are not going to use any feature that is specific to version 5.0. However, if you do want to upgrade to the latest version, please make sure to uninstall the old version first.

[Download and Installation]

Download MySQL 5.0.15 binary release from the mysql.com. There are several packages under the "Windows downloads" section. It's recommended that you download the one called "Windows Essentials". This package comes in as a single file mysql-essential-5.0.15-win32.msi. Once you have the file, you may start the installation by double-clicking on the file, and follow the on-screen instructions. During the installation process, you may leave all the options to default. After the installation is completed, the MySQL configuration wizard will walk you through the configuration of the database server:

In the 1st screen, choose Standard Configuration
In the 2nd screen, check both Install As Windows Service and Include Bin Directory in Windows PATH.
In the 3rd screen, enter a new root password, and leave both Enable root access from remote machines and Create An Anonymous Account unchecked.

You may leave all other options to default.

[Post Installation Setup]

Suppose that MySQL is installed under C:\Program Files\MySQL\MySQL Server 5.0, the installer would have added C:\Program Files\MySQL\MySQL Server 5.0\bin to your PATH environment variable during the second step of the configuration (Include Bin Directory in Windows PATH). On Windows 2000 you may need to log off then log in again for the environment variable to take effect.

Open a DOS prompt and execute the command

mysql -u root -p

Type in the root password you supplied during the installation process, and your should be able to see the mysql prompt:

mysql>

which means that you are now connected to the MySQL database server.

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

Create a new database cs320stu31.
Create a new user cs320stu31 with password abcd, and grant the user full privileges on the database cs320stu31.
Create a table items with two records in the cs320stu31 database.

You may want to change the user name, db name, and password to match your account information on the CS server, so you can develop your web applications on the local machine, and deploy them on the CS server without any changes.

[Test Run]

If you have set up Tomcat as described in Tomcat Setup and Configuration, you would already have the MySQL JDBC driver mysql-connector-java-3.0.15-ga-bin.jar under %CATALINA_HOME%\common\lib.

Download servlet HelloJDBC.java. Modify the source code so the user name, db name, and password match your setup. Compile and run it, and you should see following output:

milk 3.89 2.0
beer 6.99 1.0

Download HelloSQL.jsp. Again, modify user name, db name, and password if necessary. Run the page, and you should see:

name milk

price 3.89

quantity 2

name beer

price 6.99

quantity 1

If you reach here without getting any error, congratulations! You are now ready to suffer the second part of the course.

[FAQ]

Q: During test run I got an error message "javax.servlet.ServletException: javax.servlet.jsp.JspException: SELECT * FROM items: Unknown type '246 in column 1 of 3 in binary-encoded result set.".

A: Update your MySQL JDBC driver to the latest version (3.1.11 at the time of writing) should solve the problem.

THE END