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

MySQL Windows Install Troubleshooting

If you’re having trouble installing MySQL 5.1 on a Windows server, I’d recommend the following process.  Don’t skip the first steps that describe the uninstall and removal of the previous install as this seems to clear up most issues.   For the uninstall, I’d recommend manually deleting data files.  Caution: deleting the data files will delete any data previously entered into MySQL.

1.  Remove previous MySQL installs (and remnants)

a.  Stop the MySQL service ( Start | Control Panel | Administrative Tools | Services)

b.  Remove MySQL using Windows Add or Remove Programs (Start | Control Panel | Add or Remove Programs)

c.  Clean up (delete) the data directory  (assuming that you don’t have data that you need) c:\documents and settings\all users\application data\mysql
 

 

d.  Clean up (delete) any remaining directories under program files c:\program files\MySQL\MySQL Server 5.1 (or whatever version you’re using)

 

————–

That’s it for the removal and clean up.  Now, we’ll begin the install.

 

2.  The MySQL install is a two step process: running the installer (.MSI) and then running the MySQL configuration utility (MySQLInstanceConfig.exe).  Theoretically, the first step should install the data files and program files and the second step should set up the service, set the root password and start the service.  The installer is designed to call the configuration utility automatically.   Instead, I recommend running each process separately.   

 

a.  Launch the installer from a command-line.  Here’s an example shell>msiexec /log c:\install.txt /i mysql-5.1.34-win32.msi 

 

Note: You’ll need to change the name of the .msi file to match the name of the version that you’re installing.  If you’re not used to working at the command-line, you’ll either need to change the directory to the location of the mysql installation file or specify the full path.  For example, you may need to execute:  msiexec /log c:\install.txt /i “c:\Documents and Settings\Administrator\Desktop\mysql-5.1.34-win32.msi”

 

Continue through the wizard until you reach the final screen which says, “Wizard Completed”  There’s a checkbox to “Configure the MySQL Server now”.  Uncheck the box and select Finish.

 

 

Uncheck the Configure box.

 When the install has finished, please check c:\documents and settings\all users\application data\mysql\mysql server 5.1\data .  You should see a mysql and test directory.  

 

Also, verify that files have been installed in c:\program files\MySQL\MySQL Server 5.1 

You should have a bin, Docs and share folder.  

 

b.  Navigate to c:\program files\MySQL\MySQL Server 5.1\bin and launch the MySQLInstanceConfig.exe from a command-line.  Here’s an example.

shell>MySQLInstanceConfig.exe “-lC:\mysql_install_log.txt” “

 

I suggest launching it from a command-line so that you can pass the -l parameter, which will create an install log in case you need to troubleshoot the problem.

 

Step through the wizard.  Make sure to remember the password that you assign in the wizard.

 

The install should complete successfully.

 

TROUBLESHOOTING

What if it didn’t work?

 

1.  Check the service from the control panel.  Is it running?  If so, try to log into MySQL.  I’ve seen instances where an error was received, but it completed successfully.  If it’s not running, try to start it and please post the complete error message.

 

2.  Check the error log.  It’s in C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data   It has an .err extension and you should be able to open it with notepad.  Ignore any errors about the innodb plugin not loading.  It’s a symptom, not the problem.  Look for an error about missing data files or a mismatch in file sizes.

 

3.  1045 error?  It should indicate Password: No or Password:yes.  If “yes”, it’s not a port issue, not a firewall issue — it’s a password issue.  It’s usually caused by not deleting out the data files from an old install.  The password is kept in the data directory and it’s likely that the password you entered during the previous install doesn’t match what you’re entering now.  You can either re-install after deleting all the files or try to reset your root password.  (resetting root password: http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html )

 

4.  Still stuck?   In the instructions above, I specified c:\install.txt and c:\mysql_install_log.txt  Please check those and post the contents.