2.6.1.3 Running MySQL on Windows

MySQL supports TCP/IP on all Windows platforms. The mysqld-nt and mysql-max-nt servers support named pipes on NT, 2000, and XP. The default is to use TCP/IP regardless of the platform, because named pipes are actually slower than TCP/IP, and because some users have experienced problems shutting down the MySQL server when named pipes are used. Starting from 3.23.50, named pipes are only enabled for mysqld-nt and mysql-max-nt if they are started with the --enable-named-pipe option.

You can force a MySQL client to use named pipes by specifying the --pipe option or by specifying . as the host name. Use the --socket option to specify the name of the pipe. In MySQL 4.1, you should use the --protocol=PIPE option.

You can test whether the MySQL server is working by executing any of the following commands:

C:\> C:\mysql\bin\mysqlshow
C:\> C:\mysql\bin\mysqlshow -u root mysql
C:\> C:\mysql\bin\mysqladmin version status proc
C:\> C:\mysql\bin\mysql test

If mysqld is slow to answer to connections on Windows 9x/Me, there is probably a problem with your DNS. In this case, start mysqld with the --skip-name-resolve option and use only localhost and IP numbers in the Host column of the MySQL grant tables.

There are two versions of the MySQL command-line tool:

If you want to use mysqlc, you must have a copy of the `cygwinb19.dll' library installed somewhere that mysqlc can find it. If your distribution of MySQL doesn't have this library installed in the same directory as mysqlc (the `bin' directory under the base directory of your MySQL installation, look in the lib directory to find it and copy it to your Windows system directory (`\windows\system' or similar place).

The default privileges on Windows give all local users full privileges to all databases without specifying a password. To make MySQL more secure, you should set a password for all users and remove the row in the mysql.user table that has Host='localhost' and User=''.

You should also add a password for the root user. The following example starts by removing the anonymous user that has all privileges, then sets a root user password:

C:\> C:\mysql\bin\mysql mysql
mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> QUIT
C:\> C:\mysql\bin\mysqladmin reload
C:\> C:\mysql\bin\mysqladmin -u root password your_password

After you've set the password, if you want to shut down the mysqld server, you can do so using this command:

C:\> mysqladmin --user=root --password=your_password shutdown

If you are using the old shareware version of MySQL Version 3.21 under Windows, the above command will fail with an error: parse error near 'SET password'. The solution to this problem is to upgrade to a newer version of MySQL.

With the current MySQL versions you can easily add new users and change privileges with GRANT and REVOKE commands. See section 4.3.1 GRANT and REVOKE Syntax.

User Comments

Actually, my.ini file "should" be searched on path specified by %windir%. mine, in not event set on winnt at all.

anyway, i place a copy in my installation dir, i created a winnt and put a copy there, and put a my.cfn file in c:\ as well - and i still get the same error 1067.

On Windows XP. I had to put my.ini into c:\windows\system32. I also had to add the lines from the InnoDB section.

No matter what I do or how I attempt to do it, I can't change the root password.

The command give (mysqladmin --user=root --password=pass
give me a command unknown error, but just using -p prompts me for the password.

After running \mysqladmin -u root password 'newpadd' there is no command line showing it was done or it failed and the pass of my choice never goes into effect.

found a partial resolution
if you use v3.x of mySQL and change the password and then upgrade to v4 the problem is corrected.
However, I can't change the password once it is set in v3..

nothing in v4 for passwords seems to work to change/edit/create (windows bins)

To change the password on V4.

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Omar Ordonez
Unica Solutions

I think the above is a good entry point but I have also added some helpful information about actually starting and stopping the MySQL service under Windows at http://www.digitalscores.com/featured-articles/03/stopping-and-starting-mysql-under-nt.html

Regards
Julian Cochran
DigitalScores
http://www.digitalscores.com

How to change the root password for windows XP, use the following code once you've logged in:

shell> mysql -u root -p
shell> Enter password: (Don't enter anything here)
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

I've 3 MySQL books and they're all wrong, this seems to work.

I found another reason for 1067 error. I start to run DHCP to get leases on an IP address. I tried to start MySQL and got the error. When I check the my.ini I found the bind-address to be different than my current address. It would be nice to have mysqld find the current address, but not a big deal because if it is going to be a network server then it would need a fixed ip address anyway. Hope this helps others.

Instead of...

"removing the anonymous user that has all privileges"

Why not just remove all accounts that do not have a password.

mysql> DELETE user FROM mysql.user WHERE password="";

http://www.devside.net/web/server/win32/mysql-bin
http://www.devside.net

Add your own comment.