This article needed to be written from a long time and since it was not, I almost suffered the same hiccups during the installing of MySQL on Windows XP. Every single time I had to search the internet for the solutions which I often forgot over a period of time.

So today without any procrastination, I intend to put down the simplest of the steps to configure and run MySQL on Windows XP in the quickest of the time. First and foremost, as I always tend to follow a course is, break down the task into 3 steps. They are:

Step 1: Getting the software

Download the latest version of MySQL from the community site. Unpack it into your desired folder. It is always a good practice to download the zipped archive of MySQL binary distribution since using the one click installer, you might sometimes lose the power of configuration manually.

Its also a better way to understand configuring manually since if you switch to a Unix based environment later on, you will have to carry out a similar procedure and there is usually no one click installer in such environments.

I unpacked MySQL in C:\Program Files\mysql-5.0.45-win32

Step 2 : Configuring the environment

While configuring in the Windows environment you have to do two things. One, putting a file having a name my.ini in the Windows folder of the operating system. Two, setting the path in your system environment variable. Here’s what you have to do:

Creating Option File (my.ini)

Go to your installation path of MySQL (In my case it is: C:\Program Files\mysql-5.0.45-win32) and rename the file my-medium.ini to my.ini. Open the renamed file in notepad and add the following lines, just beneath the line which says, # The MySQL server and above the line port = 3306:

[mysqld]
# set basedir to your installation path
basedir=C:/Program Files/mysql-5.0.45-win32
# set datadir to the location of your data directory
datadir=D:/workspace/data

The second line as shown above is, configuring your database directory. This means, the path which you assign to datadir will be the path where your databases will be created. You have to make sure that the path exists before you assign it. There will a data directory in the MySQL installed path. If you provide a different path to the data directory than this default one, you must copy the entire contents of the data directory in that path.

Finally, drop the my.ini file into your Windows directory. In my case, I copied the file into the path C:/Windows

Setting the environment variable

After completing the above step, goto

My Computer > Properties > Advanced >Environment Variables > System variables > Path > Edit

In the Value box, goto the end of the line and put a semi colon and the path of the MySQL bin folder. In my case I have put it as:

;C:\Program Files\mysql-5.0.45-win32\bin

Click OK 3 times and thats it with the configuration of MySQL. You are all set to test the installation.

Step 3: Final Configurations and Running MySQL

You are actually good to go for running mysql. For this, open up your command prompt and type in the command mysqld –console. This will start the mysql database server. Whenever you run this command henceforth you will see these lines at the end:

[Note] mysqld: ready for connections.
Version: ‘5.0.45-community-nt-log’ socket: ” port: 3306 MySQL Community Edit
ion (GPL)

When you see these lines, it means that mysql has started successfully. Its time to party!

Leave the command prompt open and start up another command prompt. For the first time you should do this:

C:\>mysql -u root -p
Enter password:

When it will ask for a password, just hit Enter. It will allow access to you without a password and will show up a prompt as:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Note that your database has anonymous access. It is not a secure thing to let it like that. You should always access the database with a password. So for setting up the password you will have to do the below steps. Put in the commands which are highlighted as shown:

mysql> update user set password=PASSWORD(”admin”) where User=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Once you have done this, try logging into the server again without a password. It must show the below error:

C:\Documents and Settings\xpuser>mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)

This means, now your database is secure.You should be relieved that your mysql setup has got completed. Now every time you want to start in your database start the server in one command prompt and in the second command prompt login to the database server by doing:

C:\>mysql -u root -p
Enter password: admin

Thats it. Phew! I hope I remember the steps myself and now for my cup of cappuccino.