Sure-fire MySQL Install on Windows

Due to firewalls, virus scans, corporate security restrictions, or just plain bad luck; there are times when MySQL just won’t install on a specific Windows server.  Here’s a sure-fire install method.   Relax, I won’t have you run the msi installer again.

The first step is to remove directories from your previous install attempts.  Uninstall from the control panel.  Manually, rename or delete “C:\Program Files\MySQL\MySQL Server 5.1″.  Next, delete (or rename) the data directory.  Warning! If you have previously entered data into MySQL, deleting the data directory will delete data.  The directory is located at “C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data”.   You may need to unhide the data directory.

Let’s get started.

#1.  Download the no-install MySQL Version for Windows.  Choose either “Windows (x86, 32-bit) ZIP Archive” or “Windows (x86, 64-bit) ZIP Archive” based on whether your system is 64 or 32-bit .

#2.  Unzip the files to “C:\Program Files\MySQL\MySQL Server 5.1″.

#3.  Use notepad or wordpad to create a my.ini file and save the file in the MySQL directory, “C:\Program Files\MySQL\MySQL Server 5.1″   Be careful that notepad doesn’t add a default “.txt” extension to the my.ini file ( http://support.microsoft.com/kb/253688 )

Add the following following to your my.ini file and save.

[mysqld]
##update basedir if you move the install location
basedir="C:/Program Files/MySQL/MySQL Server 5.1"

##update datadir if you move the data directory
datadir="C:/Program Files/MySQL/MySQL Server 5.1/data"

## increase to 40% of RAM if using MyISAM
key_buffer_size=32MB

## increase to 80% of available RAM for production usage
innodb_buffer_pool_size = 128MB

## may want to increase if write intensive
innodb_log_file_size = 64MB
innodb_log_buffer_size=8MB
table_cache=1024
thread_cache=16
query_cache_size=32M

Note the basedir and the datadir variables.  If you wish to install in a different location or put the data in a different location, update these values accordingly.

#3.  Create and start the service.    Open a command prompt, and enter the following:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"  --install "MySQL 5.1" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini"

This installs the server.  To start the service, type

net start "MySQL 5.1"

or start the service from the control panel.

You should be up and running.  If not, check your err log in “C:\Program Files\MySQL\MySQL Server 5.1\Data”

#4.  Connect from your client application and update your user accounts.  By default, there are 3 accounts: root@localhost, root@127.0.0.1 and an anonymous account.

The following commands log in with the mysql command-line client, remove the anonymous account and set the password for the root accounts.

At a command prompt, enter the following:

"c:\program files\mysql\mysql 5.1 server\bin\mysql" -uroot

This will log you into MySQL and you should have a mysql command prompt like mysql>

Here are the commands to delete your anonymous account and set passwords for your two root accounts.  When setting the password, substitute your desired password for “mypassword”.

mysql> DROP USER ""@localhost;
mysql> Set PASSWORD FOR "root"@"localhost" = password("mypassword");
mysql> Set PASSWORD FOR "root"@"127.0.0.1" = password("mypassword");
mysql> flush privileges;
mysql> exit

Feel free to post any questions/comments or issues to the comment section of this blog.  For more comprehensive instructions see http://dev.mysql.com/doc/refman/5.1/en/windows-install-archive.html

About these ads