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.
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.
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.