(Source: Dominic Ryan (2007) at http://www.iis-aid.com/articles/how_to_guides/install_and_configure_mysql_5_on_windows?page=0%2C2)
MySQL Server is one of the most popular database servers in the world, especially for use with dynamic, content rich websites. in fact the website you are looking at now is powered by MySQL. This guide will walk you through all the steps needed to both install and configure your MySQL 5 server on your Windows machine. Make sure you read the steps carefully as they explain why you should and shouldn’t enable some options, and if your get stuck you can always get help right here in our forums. Lets get on with the guide…
First step is to download the latest MySQL install package from the MySQL site. There are several packages to chose from, but for general usage I recommend you use the Windows Essentials package. Once you have downloaded the installer package double click on it to initilize the installation wizard as shown below in figure 1.
The first step in the installation wizard will be to choose the setup type. Here I recommend using the Custom option so that you can define your installation path. Once you have this selected press the Next button as shown below in figure 2.
Use the Change botton in the lower right hand side of this window to specify your installation path, and I generally recommend using a path without spaces. Leave the installtion values as the default and click the Next button as shown below in figure 3.
You do not need to have or create a MySQL account to use MySQL, so unless you wish to do otherwise I recommend you select the Skip Sign-Up option and click the Next button as shown below in figure 4.
This completes the installation phase of your MySQL server setup. Ensure that the Configure the MySQL Server now option is checked and click the Finish button to complete the installtion wizard and start the configuration wizard.
With the installation complete you should now be presented with the MySQL Server Instance Configuration Wizard as shown below in figure 6. Click the Next button at the bottom to continue.
When asked what configuration type you wish to use, select the Detailed Configuration option and click the Next button at the bottom as shown below in figure 7.
The next step is choosing the server type. If you will be using MySQL on a server that is hosting other applications as well I suggest you use the Server Machine option. Once you’ve made your selection click the Next button to continue as shown below in figure 8.
Selecting your database usage is an option you should give some consideration as this determines what database types are enabled. For general purpose I would recommend using the Multifunctional Database option as then both the MyISAM and InnoDB database types are enabled. Once you’ve made your selection click the Next button at the bottom as shown below in figure 9.
If you selected an option in the previous step that enables the InnoDB database engine, then you will be prompted to set a path for use by the InnoDB datafile. Leave this as the default (uses the installation path) unless you require otherwise and click the Next button at the bottom as shown below in figure 10.
The next step is to set the option that best suites your estimated conccurrent connection usage. This really depends on what applications you’ll be using, and how many. You need to be mindful of this limit as many MySQL buffer/cache settings are per connection based, so setting this too high may leave your server open for a memory usage blow out. As a general purpose database server for web applications I suggest using the Decision Support (DSS)/OLAP option which sets the concurrent connection limit to 20 which is pretty safe. Once you have made your selection click the Next button at the bottom as shown below in figure 11 to continue.
The next page in the configuration wizard allows you to enable/disable networking support, and enable/disable the MySQL strict mode. Ensure that you have the Enable TCP/IP Networking option checked, but I actually recommend that your uncheck the Enable Strict Mode option even though it is on by default and recommended in the configuration wizard. This is because a lot of web applications do not work with MySQL running in strict mode (just do a Google search on it), so unless you have a specific reason for enabling it I suggest leaving it unchecked. Once you have made your selection click the Next button at the bottom to continue as shown below in figure 12.
Here you can set the default character set used by MySQL. Leave it as the default Standard Character Set unless you require otherwise. This does not disabled other chracter sets, and most applications that require a specifc character set will define it in their installtion process. Click the Next button to continue as shown below in figure 13.
In this step we set the Windows specific options which include running MySQL as a service and adding MySQL to the system path. Ensure that the Install As Windows Service and Launch MySQL Server Automatically options are checked. I also recommend that you check the Include Bin Directory in Windows PATH option so that you can execute the MySQL tools from anywhere when using the command line. This can be handy when creating automated scripts for backups, etc. Once you’ve made your selection click the Next button at the bottom as shown below in figure 14.
This step is very important as here you set the root password. The root account is equivalent to the Windows administrator account, so be sure to set a strong password (alphanumeric & atleast 6 characters) that you are going to remember. I don’t recommend checking the Enable root access for remote machines option if this server is going to be exposed to the Internet, nor to I recommend checking the Create An Anonymous Account option. Click Next to continue on to complete the configuration wizard as shown below in figure 15.
After setting the password you’ll be presented with a page confriming your settings. Click the Execute button at the bottom and the configuration wizard will go ahead and implement your settings. Once completed you should be presented with a page as shown below in figure 16 showing that everything was sucessful and that the MySQL service is now using the new settings. Press Finish to complete this configuration wizard and exit.
That’s it, all done! You now have your MySQL server up and running and you can start using MySQL based applications. All your MySQL settings are stored in a file called my.ini in the root of your MySQL installation, and you can reconfigure your server at any time by editing this file directly or running the configuration wizard again. If you are going to use PHP applications with MySQL, be sure to check out my guide on installing PHP 5 on IIS in 5 simple steps. Be sure to check back for new articles on how to create & delete users and databases, assign permissions and other general MySQL administration task. Also keep an eye out for future articles that will cover the basics of MySQL performance tuning. As always, if you need any help please feel free to ask questions in the forums (registration required).