Installing and Configuring MySQL Database and Connector/J JDBC Driver on Microsoft Windows

Overview
Following topics will be covered in this tutorial:

• How to obtain, install and configure MySQL Database?
• How to configure MySQL to use InnoDB tables? (for transaction support)
• How to configure MySQL to use TCP/IP as well as named pipes on NT based Windows systems?
• How to start MySQL Database server?
• How to setup root password and remove ghost account?
• How to install MySQL as a Windows service on NT based Windows systems?
• How to obtain and install Connector/J MySQL JDBC Driver?
• How to test if Connector/J has been installed and configured correctly?
• How to configure Connector/J to use named pipes to connect to MySQL Database server?
• Why and when to use named pipes in place of TCP/IP with MySQL/Connector/J?

Obtaining MySQL
You should have a working installation of MySQL on your system. If you don't then follow these Instructions:

1. Go to MySQL.com's download section and look for the heading 'MySQL database server & standard clients'. Under this heading look for the latest MySQL database version (currently it is 4.0) for production release. Click that link as shown below:
   
   
   
   Click the 'Production release' link
   
   
2. Following above link will take you to a page which will be listing different MySQL database downloads for different operating systems. Follow the 'Pick a mirror' link under the heading "Windows".
   
   
   
   Click 'Download' to start download
   
   
3. Select one of the mirrors and start downloading the zip file. Wait until the complete file gets downloaded, then move over to the next section to install MySQL.

Installing MySQL
This step is going to be simple. The name of the zip file that we've downloaded will be something like 'mysql-4.0.14b-win-noinstall.zip'. It doesn't contain a setup.exe file. The installation is so simple that we don't actually need any setup files. All we have to do is to unzip this file to a folder, say 'D:\mysql-4.0.14b-win-noinstall' and we are done with installation.

Configuring MySQL
Now don't get too excited, we have yet to configure MySQL. MySQL reads it's configuration directives from a text file called my.ini. This file should be placed in the main Windows folder which will be /WINNT on Windows NT/2000 and /WINDOWS on Windows 98/XP/2003. You can find this folder by running following command at the command prompt:

C:\>echo %WINDIR%

Command Prompt - Windows Installation Folder

In my case the Windows (Win2k) is installed in the /WINNT folder in my D: drive. You will have to place my.ini file in this Windows folder so that the complete path to my.ini is something like D:\WINNT\my.ini.

Now let's see what should your my.ini file look like? Create a new my.ini file in your Windows installation folder and copy/paste following text in it:

[client]
port=3306

[mysqld]
port=3306
enable-named-pipe
socket=MySQL

skip-locking
set-variable	= max_connections=64
set-variable	= read_buffer_size=1M
set-variable	= key_buffer=8M
set-variable	= max_allowed_packet=1M
set-variable	= table_cache=64
set-variable	= sort_buffer=512K
set-variable	= net_buffer_length=8K
set-variable	= myisam_sort_buffer_size=4M
server-id	= 1

basedir = D:/mysql-4.0.14b-win-noinstall/
datadir = D:/mysql-4.0.14b-win-noinstall/data/

skip-bdb

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

[mysqldump]
quick
set-variable	= max_allowed_packet=8M

[mysql]
no-auto-rehash

[isamchk]
set-variable	= key_buffer=10M
set-variable	= sort_buffer=10M
set-variable	= read_buffer=2M
set-variable	= write_buffer=2M

[myisamchk]
set-variable	= key_buffer=10M
set-variable	= sort_buffer=10M
set-variable	= read_buffer=2M
set-variable	= write_buffer=2M

[mysqlhotcopy]
interactive-timeout

Explanation
The my.ini file should at least contain the path to the MySQL installation and data files folder. Since I am assuming you installed MySQL in D:\mysql-4.0.14b-win-noinstall folder, this folder's path has been put in my.ini file. If for example you have installed (extracted the zip file) MySQL in your C: drive then replace the 'D:' with 'C:' in your my.ini file.

You can view the details of all the configuration directives that can be placed in my.ini file in the documentation that comes with MySQL. I'll only briefly explain the important ones here.