Howto setup a MySQL Connector/J 5.1 for Tomcat on Linux

Again, I'm not switching to Java. :) For clarity, I'm helping one of my online buddies to setup and use Ruby even as I write this. This work was something I had to do for a Rails project which used JSPs and stuff with a MySQL database over JDBC. The application setup was quite interesting calling JSPs to work with a Rails webapp.

Actually the following things are found on the Internet. I cannot remember all the sources I looked at, but one was the MySQLs own documentation and Apache Tomcat documentations. So if this works (which in my case did), credit should not be mine. :)

Here's the setup.

• GNU/Linux (in my case CentOS 5, although should work with any Linux distro)
• Apache Tomcat (5.5.25, should work with Tomcat 5.5 range)
• Sun JDK (1.6.0_04)
• MySQL (5.0.22)
• MySQL Connector/J (5.1)
  

1. I assume that Java is setup (See my previous post for more details on setting up Java manually), and your MySQL is running on the same host on port 3306. Please replace your actual settings if they are different.

2. First, lets set up Apache Tomcat 5.5.
If you already have Tomcat up and running, feel free to skip to step 2.
You can download it from (http://tomcat.apache.org/). For my case I downloaded Apache Tomcat version 5.5.25 (Eg: apache-tomcat-5.5.25.tar.gz).

Extract the downloaded archive to get the tomcat (Eg: tar xzvf apache-tomcat-5.5.25.tar.gz). This will give a directory with a name similar to apache-tomcat-5.5.25.

Move this directory to a place where you'd run it as the Tomcat server.
Eg: cp -R ~/apache-tomcat-5.5.25 /var/tomcat

Now set the variables, CATALINA_HOME, JAVA_HOME, JDK_HOME. One way of doing this is by adding those to /etc/profile file (Eg: sudo vi /etc/profile).
Eg: Add these lines to the end of /etc/profile:
CATALINA_HOME=/var/tomcat
JAVA_HOME=/usr/java/jdk1.6.0_04
JDK_HOME=/usr/java/jdk1.6.0_04
export CATALINA_HOME JAVA_HOME JDK_HOME

To make sure it takes effect, you can log out and log in, or just close the terminal and start a new thing.

Then you can start the Tomcat server by running the startup.sh script which comes with Tomcat. Shutdown script is called shutdown.sh. They are in the bin directory of your Tomcat directory.
Eg: $/var/tomcat/bin/startup.sh


2. Now lets move to Connector/J setup. Download MySQL Connector/J from (http://www.mysql.com/products/connector/j/). In my case I downloaded version 5.1. You'll get a .tar.gz file
Eg: mysql-connector-java-5.1.5.tar.gz

This writeup assumes this connector version. It is the latest as of this writing, but if the version differs, the following configuration instruction may not work.

3. Extract the connector.tar.gz archive to get a .jar file, which is the actual connector.
Eg: tar xzvf mysql-connector-java-5.1.5.tar.gz

This will most probably create a directory with a name something like mysql-connector-java-5.1.5. In that you'll find a directory structure where the .jar file (Eg: mysql-connector-java-5.1.5-bin.jar) will be in the topmost level.

4. Copy the .jar file (Eg: mysql-connector-java-5.1.5-bin.jar) to your $CATALINA_HOME/common/lib (i.e: common/lib directory within your Tomcat directory.)
Eg: sudo cp ~/mysql-connector-java-5.1.5/mysql-connector-java-5.1.5-bin.jar /var/tomcat/common/lib/

5. Create a context configuration file for Tomcat.
Create a configuration file in your $CATALINA_HOME/conf/ which has the file name apps-yourapp.xml
Eg: If your applications name is myapp then,
$ vi /var/tomcat/conf/apps-myapp.xml

6. Enter the things found here or in this file. When you copy and paste these code, please remember to replace values for Context path, docBase, Resource name, ResourceParams name, username, password and url with your values.

Eg: If your application is located under /var/tomcat/webapps/myapp and it's accesible via http://yourdomain.tld/myapp then your Context path would be "/myapp" (where it's accessible in URL) and docBase would be "webapps/myapp" (where it's available on file system).

7. Restart your Tomcat can now you are good to go. In your Java code you can use the JDBC connection in something like:
Connection conn = DriverManager.getConnection("jdbc:mysql://yourdomain.tld/my_database?" + "user=myuser&password=mypassword");