Install MySQL on IIS7 Server 2008

Now that you have installed PHP on your server, most of the popular PHP applications like WordPress & Joomla will require a database on the backend to support them.

While there are other options, MySQL is usually the database of choice to use for these applications. It is one of the most widely supported databases so you can usually find help if you run into a snag.

In this article we will install MySQL 5 on a Windows Server 2008 running IIS7.

Install MySQL on IIS7

As of this writing the current production version of MySQL is 5.0 Community, so that is what we are going to use for this example. We are also going to use the installer package, which can be downloaded at the following location:

• MySQL Windows 32-Bit — http://dev.mysql.com/downloads/mysql/5.0.html#win32
• MySQL Windows 64-Bit — http://dev.mysql.com/downloads/mysql/5.0.html#winx64

In this case we are going to use the 32-bit "Essential" binary which is recommended for most users, so I am going to go ahead and have that downloaded onto our test machine and then we will get going.

1. Find where you downloaded the file and click on the .msi file.

2. At the setup wizard click Next.

3. I am going to choose the Typical install and then click Next.

4. Review your chosen options then click Install.

5. Next you will see an advertisement to upgrade to MySQL Enterprise version. For some that is a good option as you get quite a bit of benefit from having the service relationship in place compared to the community addition.

You can read the ad or click for more information, but if you want to continue installing the software click Next

6. Now you will see an advertisement for their monitoring service to keep a professional eye on your database. If you have a need for such service I encourage you to click the More button, but otherwise click Next.

7. You should now arrive at the Wizard Completed window. Leave the box Configure the MySQL Server now checked and click on Finish.

8. Now we will see the MySQL Server Instance Configuration Wizard, which will walk you through configuring your MySQL installation. Go ahead and click Next.

9. In the next screen we will see two different options Detailed & Standard, I am going to go ahead and select the Detailed and click Next.

10. The MySQL Instance Configuration screen is next, you have three options:

• Developer Machine — Many other apps will be running on this machine and MySQL should use a minimal amount of memory.




• Server Machine — Server apps will be running, this is the setting to use for web servers, MySQL will use a medium amount of memory.




• Dedicated MySQL Server Machine – The big papa of installation choices, MySQL will be the only application on this server and will utilize all available memory.

We are going to go ahead and choose Server Machine for this demo, then click Next.

11. Now we have to select the database usage. There is a lot that can be said for this but I am going to give you a quick summary:

The choices here boil down to what you are going to be using the database for and what feature set you need.

The main difference between the MyISAM and InnoDB is speed and reliability. If you need transactional support you should know it and you would know to select Transactional Database to use InnoDB and use that.

In our demo we are going to use the Multifunctional Database to give us the greatest flexibility. After selecting that click Next.

12. On the next windows you choose the place to put the InnoDB datafile.

If you have a raid or array storage you may want to move it there for performance/reliability reasons, but for this demo we will use the default and click Next.

13. The next decision to make is how many connections to set the server up for.

Since this is going to be on a web server I am going to set it up to use 500 active connections at one time by selecting Online Transaction Processing (OLTP), but note that you can also setup a custom number by using the Manual Setting.

If I was working on a development box I would have probably selected Decision Support as that assumes 20 connections and would be ideal for just test purposes. After you make your decision click Next.

14. This next screen is very important because it sets how other applications will connect to your MySQL instance.

The default port is 3306 and it is recommended that you leave this as is because it is the assigned port and most 3rd party applications you use will try to connect on that port.

Now if you are coding your own application and can customize the port the application will use, then you could change the port.

You can also add a firewall exception by checking the box next to the port number. This is tricky depending on how your environment is going to be setup. I am going to add the exception for this demo but you may want to be more restrictive and only open the exception to the servers that would be connecting to the MySQL apps.

Enable Strict Mode is another added feature to MySQL 5 and it is recommended to leave it on. This basically does some data checks and forces applications to use correct formats.

Note: This may break some legacy apps that used older MySQL versions, but it is more preferable to patch those apps and keep this mode enabled.

After making your selections click Next.

15. Now we will set the default character set that the database will use.

Again this will really be dependent on what your environment is, but for this demo we are going to use Best Support for Multilingualism as that supports most languages. If you know you are going to be using a specific set than you can also set that by using the Manual option.

After you are done go ahead and click Next.

16. The next screen will setup MySQL to run as a service so if there are any reboots the database will come back up automatically. You can also set the Service name which is useful if you have more than 1 instance of MySQL running.

You can also select Include Bin Directory in Windows Path to add the server/client variables into the PATH variable. This is useful if you will be working from the command line for any reason.

For this demo we are going to select it and then click Next.

17. Now let’s set a root password for the MySQL server instance. Type it in twice to confirm it, but leave Enable root access from remote machines unchecked unless you know you will need that.

Also leave Create An Anonymous Account unchecked as that could cause security issues.

18. Finally we arrive at the final configuration window. All that is left is to click on Execute to setup the MySQL instance. Go ahead and click … you know you want to!

19. You will see a brief summary at the end of the install that will let you know everything completed successfully and ask you to click Finish.

Now that you have a powerful database like MySQL installed on you server it gives you quite a few options for some great open source apps when combined with PHP and other programming languages.

My next article will be how to use some graphical applications to manage your MySQL databases.

Related Posts:

• Install PHPMyAdmin on IIS7 and Server 2008
• IIS 7: Install FastCGI & PHP on Server 2008
• Microsoft Web Application Installer
• Installing WordPress on IIS 7 – Part 1
• IIS7: URL Rewrite Extension on Windows Server 2008
• Install DHCP Role on Windows Server 2008
• Server 2008: Install Active Directory Certificate Services

16 Responses to “Install MySQL on IIS7 Server 2008”

• Darren M Says:

  September 11th, 2008 at 6:04 pm

  Thank’s Dave
  this and the preceding article on installing FastCGI and PHP have been very informative.
  Hope your next article is on installing Joomla :)

• David Lawlor Says:

  September 12th, 2008 at 1:29 pm

  Darren -

  Glad you liked the article, the next article I am going to be working on is the URL Rewrite extension that was released GoLive on 9/11. With that installed I will see about writing an article so that Joomla and Wordpress can have “Pretty” URLS which is really important if you want your content to rank in the major search engines.

  Keep an eye out for them :)

  Dave

• How to get MySQL and PHPMyAdmin working with IIS7 and Windows 2008: BillS IIS Blog Says:

  October 14th, 2008 at 11:56 am

  [...] First, learn how to get MySQL going on Windows 2008 by reading this tutorial: Install MySQL on IIS7 Server 2008  [...]

• Kevin Says:

  October 24th, 2008 at 12:21 pm

  I am so glad I found these Train Signal RSS feeds. I have read some very helpful and informative information.

  Thanks a bunch.

  Kevin

• John Says:

  November 18th, 2008 at 7:39 pm

  what’s different here from any other windows box installation that ya need to call it – Install MySQL on IIS7 Server 2008.

• Ben Says:

  November 24th, 2008 at 4:35 pm

  Dave,

  Your articles are awesome. Clear, concise, and best of all, they actually work. Thank you for the time you’ve taken to write this article and the previous on getting PHP to work on Server 2008. I was about ready to pull my hair out before I found your articles.

  Thanks again,
  Ben

• David Lawlor Says:

  November 24th, 2008 at 6:10 pm

  Ben-

  Im glad the articles helped you out!

  John-
  Specifically it was named that because I did the install on Server 2008. It will probably almost be exactly the same for older server installs, but I like to be specific, plus it helps people find exactly what matches their problem when searching.

  Dave

• IT_Architect Says:

  February 18th, 2009 at 8:19 pm

  I doubt that this works anymore. At the end of the instance config after you hit the execute button, you will get the error message:

  “Could not start the service mysq. Error: 0″

  However, the service IS started when you check on it, so it had no problem starting the service. However, you cannot fnish the install. Moreover it leaves things half configured. It does not backup out. Some say you can delete the instance and uninstall to get a clean start, but that is false. You must go into programdata and delete it from there after you uninstall or it will modify the behavior of the install.

  I don’t have a solution yet. There are a lot of people gasing about fixes on the web, but I haven’t seen them work. What I can say is it revolves around InnoDB. If you select the options so you don’t install InnoDB, it will install fine. I already tried changing the InnoDB location to one of the suggested locations off root, updated the Windows installer to 4.5. They did not help. If I were a betting man, I’d bet it has to do with an MS security update.

  This will not install:
  Windows 2008 Server Web Edition x64 (TechNet)
  Updates install, PowerShell installed, inbound ping enabled, with or without updated 4.5 installer.
  MySQL 5.1 x64

• IT_Architect Says:

  February 18th, 2009 at 9:30 pm

  I uninstalled all updates and powershell and rebooted. Back to a bare default install. It didn’t help. I appears he install program will not install InnoDB in with this combination. The most logical culprit is a rights issue, but I have not nailed it down.

• Levi Blaney Says:

  March 9th, 2009 at 1:18 pm

  IT_Architect,

  The problem your experiencing is from already having the mysql service installed. This may have happened if you have tried to install mysql more than once and failed. Open up services and stop and delete the mysql service. This will allow the installer to continue.

  Thanks,
  Levi

• tien113 Says:

  March 9th, 2009 at 4:42 pm

  i can not start mysql service in configuration wizard…

  how can i fix it ???

• Russ Says:

  March 10th, 2009 at 3:24 am

  Had the same “service wont run” issue.
  I can confirm that deleting the MySQL service and running the final steps of installing MySQL Server again works :)

  Was done on SBS 2008 STD 64-bit.

• David Potter Says:

  March 10th, 2009 at 5:16 pm

  Great tutorial. I’m writing one as well – mostly so I can remember how to set it up the next time I need to :-). I’m setting up a WordPress blog for my company and they are a little paranoid about access and security issues. Is enabling network access to MySQL necessary if the web server is running on the same machine as MySQL? I can’t seem to find the answer anywhere.

  Thanks,
  David

• Installing MySQL | David’s Technical Musings Says:

  March 11th, 2009 at 3:33 pm

  [...] Install MySQL on IIS7 Server 2008 by Dave Lawlor [...]

• Wally Says:

  April 3rd, 2009 at 2:35 pm

  Hello david,
  first thanks for the great steps, I was able to get everything working from inside however, when browse from outside I just get text no graphic and also I am not able to get to login prompt,
  any suggestion will be apreciated..
  Thank you
  Wally

• sivani Says:

  April 23rd, 2009 at 8:36 pm

  hi Dave ,
  I have installed 32 bit essential and installed well till da end but i encountered with an error during the execute process in Apply security settings. it says
  Error no: 1045 Access denied for user’root@localhost’If a personal firewall is running on ur machine plz make sure u have opened the TCP port 3306 for connections. Otherwise no client appilication can connect to the server.

  So can u plz suggest me wat am i supposed to do

  Thank You
  Sivani

Leave A Comment:

Name (required) Mail (will not be published) (required) Website