innodb_data_file_path = ibdata1:10M:autoextend
set-variable = innodb_buffer_pool_size=32M
set-variable = innodb_additional_mem_pool_size=4M

set-variable = innodb_log_file_size=8M
set-variable = innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit=1

enable-named-pipe
socket=MySQL

D:\>cd mysql-4.0.14b-win-noinstall (Hit Enter)
D:\mysql-4.0.14b-win-noinstall>cd bin (Hit Enter)
D:\mysql-4.0.14b-win-noinstall\bin>

D:\mysql-4.0.14b-win-noinstall\bin>mysqld-nt --console

D:\mysql-4.0.14b-win-noinstall\bin>mysql -u root

mysql> use mysql;

The records for users are kept in a table called 'user' in the 'mysql' database. So if we want to change/set the password for our root account, we'll have to issue an UPDATE SQL command. This is how we do it:

mysql> UPDATE user SET password = PASSWORD('secret') WHERE user = 'root';

Above command will set the password for the user 'root' to the given password. We have given our password as an argument to a function called Password() because we don't want to store our password as plain text, we want it to store it in an encrypted format so that's why we used Password() function. You'd obviously put something other than the 'secret' text that I entered, something you can remember later on when you try to log in again.

After that UPDATE command was issued, you should see a message that something like 2 rows were affected.

Command Prompt - Setting Password

One other thing we'd want to do at this stage is to remove a "no user", "no password" account from the 'user' table. This is a user account that is created for you so that you can play around in the 'test' database without having to log in as root. But it can be a potential security risk so we'd want to remove this account. To see all accounts in the 'user' table, run a command like this at mysql command prompt:

mysql> SELECT host, user, password FROM user;

Command Prompt - Viewing User Accounts

Notice the last 2 rows where there is no user or password name shown. This is the ghost account I am talking about. Also notice the 'host' column. This column's value should be the host name/IP address of the host the given user is allowed to log in from. To be on the safe side (at least for now), don't mess with it ;). To remove this account, issue following command:

mysql> DELETE FROM user WHERE user = '';

Command Prompt - Removing Ghost Account

To confirm, issue following command again:

mysql> SELECT host, user, password FROM user;

Command Prompt - Viewing User Accounts

No more ghost account, only root account is there now. To log out of MySQL, use following command:

mysql> quit

We have learned how to change/set the password for root account. Let's move on!

How to install MySQL as a Windows service on NT based Windows systems?
What you have learned about MySQL till now is valid for all Windows operating systems from Windows 98 to Windows Server 2003. But what you are going to do now can only happen on NT based Windows systems, that is, Windows NT, Windows 2000, Windows XP, Windows Server 2003.

Installing MySQL as a service is not only convenient, it is essential in production systems. So what we'll do now is to install, remove and re-install MySQL as a service. This way you'll learn to install and uninstall MySQL service.

Make sure MySQL is not already running, if it is then open a new command prompt window and issue following command to shut it down:

D:\mysql-4.0.14b-win-noinstall\bin>mysqladmin -u root -p shutdown (Hit Enter)

Command Prompt - Shutting down MySQL

Services are installed in 3 modes; automatic, manual and disabled. Automatic services start and stop when operating system boots and shuts down. Manual services are started and stopped manually. While disabled services don't start at all.

Now to install MySQL as an 'Automatic' mode service, issue the following command:

D:\mysql-4.0.14b-win-noinstall\bin>mysqld-nt --install

Command Prompt - Installing as an Automatic mode service

To remove MySQL as a service, issue the following command:

D:\mysql-4.0.14b-win-noinstall\bin>mysqld-nt --remove

Command Prompt - Removing MySQL service

To install MySQL as a 'Manual' mode service, issue the following command:

D:\mysql-4.0.14b-win-noinstall\bin>mysqld-max-nt --install-manual

Command Prompt - Installing as a Manual mode service

To remove it you'll issue the same "mysqld-max-nt --remove" command you used to remove automatic mode service earlier.

In production systems, you should install MySQL in 'Automatic' mode, and on development systems in 'Manual' mode.

We are now done with obtaining, installing, configuring and running MySQL database server. Let us now focus our attention on Connector/J MySQL JDBC driver.

Obtaining Connector/J MySQL JDBC Driver
Go to MySQL.com's JDBC section and click the 'Production Release' link for Connector/J:

Click the 'production release' link for Connector/J JDBC Driver

A list of mirrors will be displayed to you. Pick a mirror and download Connector/J driver. Once you are done with downloading the .zip or .gz file, unzip or untar the file to extract the files in a temporary folder.

Installing Connector/J MySQL JDBC Driver
Among the extracted files will be a JAR file with a name like 'mysql-connector-java-3.0.8-stable-bin.jar'. Copy this file to your %JAVA_HOME%/jre/lib/ext folder which on my system happens to be D:\j2sdk1.4.2\jre\lib\ext>.

You should also add the complete path to this JAR file in your CLASSPATH environment variable. In case if you don't know how to do that, go to Start -> Settings -> Control Panel -> System -> Advanced (tab) -> Environment Variables. Double-click 'CLASSPATH' if it is already listed there or click 'New' to create one with the name of 'CLASSPATH'. If you already had a 'CLASSPATH environment variable listed, simply type in (or paste) the complete path including file name in the window that opens up *at the end of* existing CLASSPATH values. If you are creating a new 'CLASSPATH' environment variable then simple enter '.;' and then append the complete path to Connector/J's JAR file.

Window - Environment Variables

Window - Edit System Variable

If you've correctly added the path to Connector/J JDBC driver's JAR file in your CLASSPATH environment variable then you are done with installing MySQL Connector/J JDBC driver.

How to test if Connector/J has been installed and configured correctly?
We will now create a simple Java program which will try to connect to our MySQL database server using Connector/J JDBC driver. Create a new JdbcExample1.java file and copy/paste following code in it:

package com.stardeveloper.example;

import java.sql.*;

public class JdbcExample1 {

  public static void main(String args[]) {
    Connection con = null;

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql:///test", "root", "secret");

      if(!con.isClosed())
        System.out.println("Successfully connected to MySQL server...");

    } catch(Exception e) {
      System.err.println("Exception: " + e.getMessage());
    } finally {
      try {
        if(con != null)
          con.close();
      } catch(SQLException e) {}
    }
  }
}

I won't be explaining any JDBC code in this article. All this code does is to connect to our MySQL database server (if it is running) using the account 'root', password 'secret' and database 'test'. Once connected it prints a success message on the console.

Compile this class but before running it make sure that MySQL database server is running on your system. If not already running, you can start it by issueing a command like "mysqld-nt --console" or if you've installed it as a service then "net start mysql".

On my system running this program gave following success message:

Command Prompt - java com.stardeveloper.example.JdbcExample1

If you get the same message then congratulations you've correctly setup Connector/J JDBC driver.

How to configure Connector/J to use named pipes to connect to MySQL Database server?
The above Java program used TCP/IP to connect to MySQL database. We'll now make just one change and this time it will use name pipe to connect to our MySQL server instance.

Simply convert this line in JdbcExample1.java:

      con = DriverManager.getConnection("jdbc:mysql:///test", "root", "secret");

To this one:

      con = DriverManager.getConnection("jdbc:mysql:///test" +
        "?socketFactory=com.mysql.jdbc.NamedPipeSocketFactory",
        "root", "secret");

Compile and run this class, this time it'll use named pipe to connect to MySQL database. If you again get a success message then congratulations you've setup named pipe on both sides (server and client) correctly.

Why and when to use named pipes in place of TCP/IP with MySQL/Connector/J?
Named pipes are a high performane alternative to TCP/IP on a Windows NT/2000/XP/2003 only environment. The Connector/J JDBC driver's documentation suggests an increase in data access speed of 30%-50% as compared to TCP/IP. You can always use named pipes in an intranet environment. So to keep it short, use named pipes when you can and TCP/IP when you cannot.

Summary
In this tutorial we obtained, installed, configured and ran MySQL database server on Microsoft Windows. We then obtained and installed Connector/J JDBC driver to access MySQL database from our JDBC applications. We tested both MySQL server and JDBC driver and found everything to be setup correctly.

I deliberately didn't discuss any JDBC code in this article. This tutorial should provide a base for future JDBC articles which will focus on various data access techniques available to JDBC developers.