3 Easy Steps to Install MySQL on Windows XP

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.

logo_mysql_sun

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.

70 thoughts on “3 Easy Steps to Install MySQL on Windows XP

  1. this article is very useful for installing mysql on windows xp.
    do u have any idea how can i configure wordpress on my local computer.

  2. @ajay – For configuring wordpress on your computer, you would need to install Apache server and also PHP for it. You would also have to configure PHP work with MySQL by putting in the required libraries.

  3. you can download package from http://www.devside.net/ , which include WAMP(Windows, Apache, MySQL and PHP), if you only wish to install, you can select option as mysql. I’ve tried it once for windows purpose. Otherwise for me most of the linux flavors come with pre-installed, latest and stable version of mysql.

  4. How about using WampServer? I wanted better control so didnt go this way but could be convenient.

  5. @Arvind,

    If you want to copy the data from datadir, you must need to stop mysql server. Otherwise, you would not get the proper data.
    However, this is not good solution.
    The better one is take your data backup using mysqldump utility. Infact you can also do backup using replication.

    – Jignesh

  6. Hey.. Now i have Mysql running… Also, have jdk6 installed..

    I have to connect the both.. Please Help me…. with JDBC Connection & DSN Settup.

  7. Please help…

    I’ve followed the instructions fine up to step three, but when I type mysqld -console in the command prompt, I just get command prompt not recognised – can’t figure out what I’ve done wrong though as I’m totally new the MySQL it could be anything.

    Thanks in advance.

    Adam.

  8. Sorry, should clarify….I get the following…

    ‘mysqld’ is not recognized as an internal or external command,
    operable program or batch file.

  9. @Adam,

    Please set the path to your MySQL bin directory in the system environment variables. Open up a new command prompt and then try. It will work

  10. One more correction: The path set in the environment variables should be enclosed in quotes. For example,

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

  11. I don?t usually reply to posts but I will in this case. I?ve been experiencing this very same problem with a new WordPress installation of mine. I?ve spent weeks calibrating and getting it ready when all of a sudden? I cannot delete any content. It?s a workaround that, although isn?t perfect, does the trick so thanks! I really hope this problem gets solved properly asap.

  12. It worked very well for me. I just noticed only once error when assigning a password to the user ‘root’. It didn’t recognize the database. So I changed the query to include the DB name.

    update user set password=PASSWORD(”admin”) where User=’root’;

  13. I forgot to copy the modifed query

    update mysql.user set password=PASSWORD(”admin”) where User=’root’;

  14. Guys,

    I need to install MySQL (no-install package) on Windows in the user’s home directory. How can I configure that in my.ini file? The entries basedir and datadir do not take relative path and also cannot use environment variables like %USERHOME%, etc.

    Please help!

    Thnx
    Rajesh

  15. You may need to select the ‘mysql’ database before setting the password…

    mysql>use mysql

    mysql> update user set password=PASSWORD(”admin”)…

  16. I have followed your blog’s instructions and finally when I type the mysqld command at the prompt I get the following message

    ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)

    What would anyone suggest I do ? Thanks in advance !

  17. hi guys new here…
    just a line for everyone you can use XAMPP/EasyPHP/WAMP on windows to begin with i have used all three and currently prefer XAMPP for control and near linux like handling. Mercury mail and webalizwer help to so do try XAMPP

  18. @techknowl – I have used VertrigoServer. Its too the one click installer for all WAMP applications. Haven’t yet used Wampserver

  19. I got the same error as Nick:
    I have followed your blog’s instructions and finally when I type the mysqld command at the prompt I get the following message

    ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)

    What would anyone suggest I do ? Thanks in advance !

    Regards,
    Tux

  20. Hi,

    First time i was successfully connected to mysql on windows XP using command :
    mysql –console

    Now that i’m agai trying to connect, it gives me error as:
    mysql: unknown option ‘–console’

  21. The program runs as a server providing multi-user access to a number of databases. This cannot be installed client-side. It must be installed by someone who has administrative rights on the server side…

  22. a good article

    ihave used mysql with win2000 i have now changed to win xp pro oem setup cant install mysql i get mysql needs IIS CANT INSTALL IIS no install disks and no i386 folder HELP!!!

    REGARDS
    LAWRIE NEAL

  23. Error comes after run mysqld -console in command prompt.
    Error :
    mysqld: unknown option ‘-c’
    Aborting

  24. In step 2 it tells that rename the file my-medium.ini to my.ini… but i cant rename that because that file (my.ini) is already exist in that..

  25. Personally I swear by ApacheFriend’s XAMPP. Its a heavy distrobution, but it has *EVERYTHING*. When I need to trim it down, that is so much easier than finding all the things you need, and then finding a compatible DLL for something you want to do.

  26. Generally, you should install MySQL on Windows using an account that has administrator rights. Otherwise, you may encounter problems with certain operations such as editing the PATH environment variable or accessing the Service Control Manager.

  27. when i am trying to set the password as you hav directed for installing MySql 5.0 it is showing the following error..plz help me out what to do…
    ERROR 1046:No database selected

  28. MySQL is a relational database management system (RDBMS)which has more than 11 million installations. The program runs as a server providing multi-user access to a number of databases. This cannot be installed client-side. It must be installed by someone who has administrative rights on the server side.

  29. i am performing the steps
    1)copy the folder of mysql
    2) and paste in to the c:program files
    3)then in this folder iam copy the path of bin folder
    4) then my computer right click advance environmental
    system variables ok ok iam got it only two times not three times
    5) then iam open cmd & type mysqld
    but it gives an error
    it is not recogniszed as internal & external coomand

    please help me solve this query

  30. there is no my-medium.ini file … in 5.0.92, 5.1.56 or in the latest 5.5.9 … download all three archives, extracted … went to configure the my-medium.ini and none can be found … searched each folder and nothing … i assume this “how-to” pertains to the version mentioned, 5.0.45 … sooo, going to use the installer version.

  31. Helpful resource ! only thing is now we are moving with MySQL 5.5 with MSI windows Installer. Are all these steps still required

  32. Hello, I have troubles, All the commands work but when I put into a .bat it doesn’t work!!! Any idea???

    Thanks

  33. I followed the steps and on step 3 When I enter ‘mysqld -console’ it pauses for sec and give me my usual prompt.

    I did have the path wrong at first witch gave me an error. How ever I hear only silence.

  34. The nutrition and workout program from the Truth About Washboard Abs could be used by both gender at any age. Even though it might look like the eBook is geared in the direction of men, the program can be performed by anyone.

  35. hey.. everything worked until your last step .. i create workspace folder .. n when i run mysql n set a password.. it says ERROR 1046 (3D000): No database selected.. how can i solve this out ? help me!! thanks in advance..

  36. Hi,

    In Configuration steps, since I had already my.ini I did the steps after that but when I, “Start->Run->mysqld” it opened a console didnt display any message, waited and immediately closed, Is there any problem with the installation or configuration

  37. Howdy are using WordPress for your blog platform? I’m new to the blog world but
    I’m trying to get started and create my own. Do you need any coding knowledge to
    make your own blog? Any help would be greatly appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *