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.
How to 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.
Want to start working as a DBA? Check out our SQL 2012 Administration Essentials videos, and sign up for a 3-day free trial to access all of our courses!
