18.2 Installing and Configuring MySQL

MySQL is a popular and free open source database available for Unix (Solaris, Linux, etc.), MacOS, and Windows. When this book went to press, Version 4.0 was the current stable version of MySQL. Version 4.0 of MySQL does not support stored procedures and a few other advanced database features, but it is free and has surprisingly high performance.

Below are details for downloading and installing MySQL on Windows. These instructions provide the minimal installation for MySQL. For security issues (e.g., setting the root password) and postconfiguration guidelines, see the instructions at http://www.mysql.com/documentation/mysql/bychapter/. The online documentation also provides installation instructions for Unix (including Linux) and MacOS.

To use MySQL, you must install the product, set up a database, and configure users' rights. Here, we outline the four steps required to set up MySQL, followed by a detailed description of the steps.

1. Download and install MySQL. Download MySQL from http://www.mysql.com/downloads/ and install as a service.

2. Create a database. Add a new database to MySQL by entering a simple CREATE DATABASE command.

3. Create a user . To create a user, use GRANT to assign database privileges to the user.

4. Install the JDBC driver. Download the appropriate driver for MySQL, bundled as a JAR file. During development, include the JAR file in your CLASSPATH . For deployment, place the JAR file in the WEB-INF/lib directory of your Web application.

Download and Install MySQL

You can download MySQL from http://www.mysql.com/downloads/. Download mysql-4.0. xx -win.zip (or later), unzip, and run the setup.exe program to install MySQL. We recommend installing MySQL in the C:\mysql directory. Note that before installing MySQL on Windows, you must log in to the computer with administrative rights.

Core Warning

To install MySQL on Windows NT/2000/XP, you must have local administrator rights on the machine.

On Windows NT/2000/XP, to configure MySQL as a service, run the following command in DOS from the C:\mysql\bin directory.

 C:\mysql\bin>  mysqld-max-nt --install  

For more details, see the online documentation at http://www.mysql.com/documentation/.

Create a Database

Before creating a database, you must start the MySQL server. You can start the service from the command line by entering the net start command as shown.

 C:\mysql\bin>  net start MySql  

If the server is already running, you will receive a warning message.

Next, to create a new database, start the MySQL monitor as the root user by using the following command.

 C:\mysql\bin>  mysql.exe --user=root  

Then create the database by entering the CREATE DATABASE command as follows .

 mysql>  CREATE DATABASE   database_name   ;  

where database_name is the name of the database you want to create. For the code in this chapter, we created a database named csajsp . To see a listing of the current databases, enter the following command.

 mysql>  SHOW DATABASES;  

If you prefer graphical interfaces over command-line utilities, use MySQL Control Center for managing your server. MySQL Control Center is available at http://www.mysql.com/downloads/ mysqlcc .html.

Create a User

You can create a user at the same time you grant privileges to that user. To grant a user access to the database from the local host, use the command

 mysql>  GRANT ALL PRIVILEGES ON   database   .* TO   user   @localhost   IDENTIFIED BY '   password   ';  

where database is the name of the database and user is the name of the new user. To grant the user rights to the database from other client machines, use the command

 mysql>  GRANT ALL PRIVILEGES ON   database   .* TO   user   @"%"   IDENTIFIED BY '   password   ';  

where @"%" acts as a wildcard for access to the database from any client machine. If you have problems creating new users, check that you started the MySQL monitor as the root user.

Install the JDBC Driver

Two JDBC drivers are commonly used to access MySQL: MySQL Connector/J and the Caucho Resin driver.

MySQL recommends the MySQL Connector/J driver, which is available at http://www.mysql.com/products/connector-j/. In our examples, we use version 3.0 of the Connector/J driver. The driver is bundled in a JAR file named mysql-connector-java-3.0.6-stable-bin.jar with a class name of com.mysql.jdbc.Driver . The URL to use with the MySQL Connector/J driver is jdbc:mysql://host:3306/ dbName , where dbName is the name of the database on the MySQL server.

Caucho Resin also provides a MySQL driver at http://www.caucho.com/projects/jdbc-mysql/index.xtp. The driver is bundled in the JAR file named caucho- jdbc-mysql-2.1.0.jar with a class name of com.caucho.jdbc.mysql.Driver . The URL to use with the Caucho Resin driver is jdbc:mysql-caucho://host:3306/ dbName , where, again, dbName is the name of the database on the MySQL server.

Neither driver is fully JDBC 2.0 compliant since MySQL is not fully ANSI SQL-92 compliant.

For development, place the JAR file in your CLASSPATH ; for deployment, place the JAR file in the Web application's WEB-INF/lib directory. However, if multiple applications on the server are using MySQL databases, the Web administrator may choose to move the JAR file to a common lib directory on the container. For example, with Tomcat 4.x, JAR files used by multiple applications can be placed in the install_dir /common/lib directory.