HOWTO: Installing MySQL, PHP and PHPMyAdmin on IIS - Part 1 MySQL Server

Getting yourself a LAMP environment when you only have a Windows server to play with, can be a little daunting.  However, if you have a Windows 2003 Server (or even Windows 2000) with IIS Installed, then getting PHP and MySQL installed and ready for use is not that difficult.

This guide will also work for installations on Windows Vista and Windows XP, however it should be noted that for best results you will be required to disable UAC on Windows Vista.

In this short tutorial, we’ll be going through the entire install process, starting with MySQL, and the PHP.

We’ll then install PHPMyAdmin a web-based tool for administrating MySQL that’ll confirm that PHP and MySQL are talking, all within the framework of Microsoft’s Internet Information Server.

This HOWTO (and it’s three corresponding sections) are all in response to the fact that PHP is now available for IIS (in a somewhat more reliable fashion)

I’ve split this tutorial/HOWTO into four separate articles so that they can be easily digested, this is the first part which focuses on the installation and configuration of the MySQL Database Server.

(This HOWTO was written with MySQL Version 5.0 in mind and indeed the screens and steps for installation and configuration it shows refer to the MySQL 5.0 installer.  However the install for other versions is not that different, and through using this HOWTO the installation for other versions can be straightforward, it should be noted however, that MySQL 4.1 doesn’t support Strict Mode (see further down for what that is, and why is important), and MySQL 6.0 is currently in Beta, as a result of which I won’t recommend it in a production or development environment, but only for experimentation with the new features and evaluation purposes.)

The four sections are as follows:

1. Installing and Configuring MySQL Server
2. Installing, Configuring and Using MySQL Client Tools
3. Installing and Configuring PHP for IIS
4. Installing and Configuring PHPMyAdmin on IIS

The fourth part, will also serve as a complete test of the entire process, and will show that using MySQL with PHP on IIS is entirely possible and indeed quite reliable.

Without further ado, let’s get moving:

Part 1: Installing and Configuring MySQL Server

Planning:

• A typical install of MySQL 5.X on Windows takes about 90MB plus space for the Data.  The following is a rough split of the default installation’s space utilization:
• 60MB : Actual Software
• 30MB : Default Database Skeleton
• The software is installed to C:\Program Files\MySQL by default
• The data files are installed to C:\Program Files\MySQL\MySQL Server 5.0\data by default.  This can naturally be changed using the Server Configuration Wizard, as explained later.
  (It is important to note, that the folder depends on which version you download and install if you install 5.0, that is what the folder will be, if you choose 5.1, then 5.1 will be the name of the folder.  The current recommended version by MySQL themselves is 5.0, that that is the version we will be using here)
• The amount of space required to store a given amount of data depends on the table type used as well as other factors.  InnoDB uses the most storage while archive uses the least.

It is difficult to define generic guidelines for storage/capacity planning since every project is different and also the variations with the use of different storage engines (InnoDB, MyISAM, Archive, etc) are quite significant.  The level of concurrency that a given application requires is also a factor in this regard.

However, a baseline rule of thumb is that if you are working with a large dataset(s) and/or expect high concurrency, you will want to carefully consider where you want to put the InnoDB tablespace.  If this installation is for a small dataset, with little (if any) concurrency, then this can be left as is the default.  For more information on MySQL Storage Engine Architecture, see the series of articles by Arjen Lentz at the MySQL Development Centre:

1. Part 1: MySQL Storage Engine Architecture - An Overview
2. Part 2: MySQL Storage Engine Architecture - An In-Depth Look
3. Part 3: MySQL Storage Engine Architecture - Details and Comparisons

Installation and Configuration:

You need to first download the MySQL Installer from the Open Source MySQL Website.  There are currently four major versions of MySQL that can be installed on the Windows Platform, they are (starting with the oldest, and moving to the latest):

1. MySQL Version 4.1 (List of Changes / Release Notes)
2. MySQL Version 5.0 (List of Changes / Release Notes)
   MySQL 5.0 is the Currently Recommended Version, this HOWTO covers MySQL 5.0, other versions may differ.
3. MySQL Version 5.1 (List of Changes / Release Notes)
4. MySQL Version 6.0 (List of Changes / Release Notes)

Once you have downloaded the installer (be sure to download the item that is referred to as Windows Essentials, whether you choose 32bit (x86) or 64bit (EMT64/AMD64) is entirely dependent on your own hardware architecture.

While you’re downloading, it might also be prudent to download the corresponding version of the MySQL GUI Tools (currently only Version 5.0 of the GUI Tools is available, so I would suggest that you download that one!)

If you need to download a connector (ODBC Connector, JDBC/Java DB Connector, etc), then this can be downloaded from the MySQL Connectors Download page, however this HOWTO will NOT cover the installation of a connector (it’s pretty straightforward so don’t worry, and if you need a connector you should know enough about how to install a connector.  If you’re really stuck, email me or leave a comment and I’ll try to help you out!!).

Right, so let’s download MySQL Community Server 5.0 and get a move on:

Once the download has completed, you need to double click the installer.  This invokes a standard Windows Installation Wizard.  The screens are all pretty much self-explanatory, and no configuration information is required here.

It should be noted, that selecting to install the Typical component set is perfectly acceptable.

When the software has completed it’s installation process, a configuration wizard will run, this will require a little more explanation.

Configuration Wizard

At this step simply press the Next > button to move onto the next screen.

Over here you will have to select, which configuration mode you wish to use. 

There are two configuration modes, one is the typical configuration, and the other which is the Detailed Configuration.  I have chosen to focus on the Detailed configuration as it allows for the most flexibility.

On selecting the Detailed Configuration, you will be presented with the following page:

There are three options that you are now faced with:

1. Developer Machine
   This option should be selected if the machine you’re installing MySQL on is a development workstation, and a plethora of other applications will also be installed on the machine.  MySQL with this configuration will use a minimal amount of memory, but consequently will run a little slower than it would in it’s most optimized configuration. 

   (Having said that, with today’s hardware as it is, I doubt if the difference will be noticed during the development phase, and it should be noted that it will run quite efficiently and swiftly)

2. Server Machine
   This option should be selected if the machine you’re installing on has a number of server applications running on it - for example an application server (such as Windows Sharepoint Services), a Web Server (such as IIS or Apache), a Mail Server (such as the Windows SMTP/POP3 Engine, or MailEnable Standard), or any other application server.

   In this configuration, MySQL will use a Medium memory footprint.

3. Dedicated MySQL Server Machine
   This is a situation where the machine that MySQL is being installed on is a dedicated MySQL Database Server, and has no other (apart from basic Operating System Services) running on it.

   In this configuration, MySQL will use up all available memory, and will generally provide the best performance.

For the purposes of this HOWTO, I have elected to select the second option, that is “Server Machine”, you might have to make minor modifications to your configuration depending on exactly what your requirements are, but all of this is pretty standard, and generally the defaults can be accepted following this stage.

So without further ado, select the option you want (in our case here the second option “Server Machine”), and click the Next > button:

This is where it gets a little interesting.  You should consider your choice of Database Engine very carefully.  The links that I have provided above for the MySQL Storage Engine Architecture make for interesting and educational reading regarding this, and I strongly recommend that you at least read through the overview at the very least.  However, for the sake of completeness;

The main table types supported under Windows on MySQL are MyISAM and InnoDB.  Each of these use fundamentally different techniques for maintaining concurrent data and have their own respective strengths and weaknesses:

•  MyISAM was the first major table type that MySQL supported.  It has NO TRANSACTIONAL ROLLBACK capability and concurrency is controlled using table locks.  It is optimized for rapid searches, the trade-in being substantial data integrity controls for this performance gain. 

  MyISAM DOES NOT support Foreign Key Constraints (so if your applications has a requirement for these, then you cannot use the MyISAM table type).  MyISAM is generally the preferred choice for basic Content Management Systems, Blog Systems and such applications (however as always, there are exceptions)

• InnoDB (now licensed from Oracle) provides a Multi-Version Concurrency Control based storage engine.  This is similar in many ways to PostgreSQL.  InnoDB is considerably slower than MyISAM for simple data retrieval, but would be expected to perform much better when large numbers of INSERTs, UPDATEs and SELECTs must happen concurrently.

  The reason for this is because locking is not generally required to manage concurrency.  However, it should be noted that Full Text Indexing IS NOT supported on InnoDB tables.

Other table types exist (e.g. HEAP, Merge, BDB, etc) but they are nowhere near as widely used as MyISAM and InnoDB, and what follows now is a very brief explanation of all the table types that are available when running MySQL on the Windows Platform:

• ARCHIVE
  This storage engine uses a compressed flat file to store large amounts of information.  Indexes are NOT SUPPORTED, NOR ARE TRANSACTIONS.
• BDB
  This storage engine uses the Berkeley Database Engine, developed by Sleepycat Software (recently acquired by Oracle) as a back-end.  It does not perform as well as InnoDB when placed under load, but appears to take up less disc space.  It may be considered an option when concurrency is expected to be low, but space is at a premium.
• FEDERATED
  This storage engine allows for the management of external data on other MySQL Servers.  NO DATA IS STORED IN A FEDERATED TABLE ITSELF.  There is NO TRANSACTION SUPPORT.
• InnoDB
  This table uses a row-versioning in order to allow for transactional consistency.  The back-end for this table was originally developed by Innobase (acquired recently by Oracle).  It is licensed under the GPL and MySQL AB purchases additional licenses for their enterprise customers.  It requires more space.
• MERGE
  This storage engine allows for partitioning of data across MyISAM tables.  The MERGE is just the parent table (where NO DATA IS ACTUALLY STORED), while identical tables hold the partitions.
• MEMORY
  This storage engine forces an entire table to be stored entirely in memory and NEVER WRITTEN TO DISC.  It is therefore not ACID compliant, but is used sometimes as a useful developer tool (consider it a sort of SQL interface to shared memory segments in RAM)
• MyISAM
  This table type uses a flat file to store the data, and auxiliary files for indexes and metadata.  Because there are no provisions for keeping data around after it is updated, MyISAM does NOT SUPPORT transactional operations.  It is the oldest table type still supported.

It is important to note that currently, the only engines that are transaction-safe are the InnoDB and BDB Engines.  Furthermore, if an engine is NOT RECOGNIZED by the CREATE TABLE statement or it is NOT USABLE, the system will automatically fall-back to MyISAM tables; as a result of this behavior, support for foreign key constraints and transaction rollback can easily be lost of attention is not paid to the details.  As they say - the devil is in the detail!

Since we are building a MySQL Server that will be as versatile as possible, the second option should be selected which is “Transactional Database Only“, this includes both MyISAM and InnoDB, as does the first option “Multifunctional Database”, however, it also has the added advantage of supporting transactions, and as a result is generally preferred (at least in my opinion) to the straight forward “Multifunctional Database”.

Only use the third option “Non-Transactional Database Only” if you are building very simple web applications, or for monitoring or logging applications.  If you’re building a system for storing System Logs in a MySQL structure, and this is the database server that you’ll be doing the storage on, then using this option seems acceptable, however, the best way is the middle way, which is to use the “Transactional Database Only” option.

So without further ado, let’s select the “Non-Transactional Database Only” option, and move on.  You will now be asked for a location to store your InnoDB tables, since InnoDB is available as part of the option we’ve selected.

The default location is the Data Directory, if you have a RAID Array, or a SAN type infrastructure then you might want to change the location to those, for the purposes of this HOWTO, I will leave things as they are.

Select the appropriate drive/directory combination, and then click the Next > button to continue.

Now this screen is a little interesting, and requires some explanation, as well as thought and planning during the installation.  There are three options on it; and I’ll go through all three in some details, so as to help the reader make a more informed and better decision.

1. Decision Support (DSS) / OLAP
   This option should be selected if your database application will not require a high number of concurrent connections.  A number of 20 concurrent connections is used, and assumed by the configuration tool.
2. Online Transaction Processing (OLTP)
   This option should be chosen if you have applications that require a high degree of concurrency, this option allows for upto 500 concurrent connections, and if this option is selected the configuration tool selects 500 concurrent connections.
3. Manual Setting
   Generally it is a good idea to not use this setting unless you know exactly what you’re doing.  This option allows you to select yourself how many concurrent connections you would like the server to handle.

For the purposes of this HOWTO, we will keep the default option selected “Decision Support (DSS) / OLAP“, and click the Next > button to move onto the next page.

This is a very important screen, and I will try to discuss it in some depth.

The first part of the screen talks about enabling TCP/IP networking.  If you disable TCP/IP networking, then while it may be possible to access the database on the local machine, any machine on your LAN that tries to access it will be unable to do so.  Even some applications that are local to your MySQL server might have issues accessing it, since TCP/IP is the de facto standard for accessing services, both local as well as remote.

The default port that MySQL listens to is 3306, this can be changed if you require, or if you have another service running on the same port (but strictly speaking you shouldn’t since that port is reserved for MySQL TCP/IP Connectivity).

Strict mode is a new and important feature in MySQL 5.0.  In earlier versions, MySQL tried very hard to commit a transaction without aborting it.  Numbers would be truncated silently, and dates like February 31, 2007 would be considered valid. 

Naturally, since this behavior would create incoherent data, it was one of the main reasons that MySQL was dismissed from inclusion in the list of what were called “Serious RDBMS (Relational Database Management Systems)”.  As a result, “Strict Mode” was added to the MySQL feature-set. 

This feature tightens down the validity checks on entered data but if you are using it with an old or legacy MySQL application, there is a good chance that without any code reviews the older application might get broken by this new (and indeed very welcomed) feature.  It is for this reason that the configuration tool allows for this to be disabled.

It is however, very highly recommended that this option be enabled, and that if you have any legacy application it is fixed or patched to cater for Strict Mode and trap any potential errors that may arise.

For our example installation, we will keep the TCP/IP port at it’s default value of 3306, and enable TCP/IP Networking (by keeping the checkbox checked), and we will enable Strict Mode (again by keeping the checkbox checked).

Now, we press the Next > to get to the next screen in the MySQL Server Configuration Wizard:

This section covers the character sets supported by your MySQL Database Server, there are three basic options, of which, in my opinion the safest and most sensible one is the second:

1. Standard Character Set
   Selecting the first option makes the Latin1 character set the default for the server.  This character is best suited for English and other Western European languages, but if you decide to move to Arabic, Persian or other eastern or Cyrillic language types, then it could cause you problems and even data corruption or loss.
2. Best Support For Multilingualism
   This option makes UTF-8 the default character set for the server.  This is the recommended character set for storing text in many different languages, eastern as well as western.
3. Manual Selected Default Character Set / Collation
   This option allows you to select a custom character set as the default for your database server.  Only use this option if you know exactly what you are doing.

While this selection of default character set doesn’t prohibit you from having tables that use a different character set, for example if you select the default character set for your database server, then decide to have a table that has Arabic text for example, you can do this by manually adding the character set when creating the table, however, this can be omitted or missed out unintentionally, as a result, it is far safer and considered best-practice to choose a default that makes sense, and caters for your content/data types.

For the purposes of this HOWTO, I will select the second option, which is Best Support for Multilingualism.

Select the option that you feel best suited to your server installation, and click the Next > button to continue on to the next screen:

This screen allows you the option of running MySQL as a Windows Service (and configuring it’s name - useful if you have multiple MySQL Servers on the same machine), it also allows you to include the Bin Directory of the MySQL installation in the Windows Path.  This can be very useful when you are working at the command line within Windows, for table creation, interrogation and maintenance tasks.

The default option is to install MySQL as a Windows Service with the Name “MySQL”, and to set the service to start automatically.  For this HOWTO, we will also modify the Windows PATH environment variable and add the MySQL installation path to the Windows PATH.  To do this, select the second checkbox and make sure that it is checked.

Once you have completed that, click the Next > to continue:

This option requires you to set the root password for the MySQL installation.  This will be the super-user password for the database server, and the password for the user that will be used to create all new users and/or databases. 

It is recommended that you choose a strong password for this, as with this password, a potential hacker will have full access to all the data stored within your database!!

This screen also allows you to allow remote root access to the MySQL Server should you wish to do so, that is a choice that you have and a decision that you will have to make, unless it is expressly required, I would advise against allowing remote access.  For the purposes of our example, we will set a password and NOT ALLOW remote access for root. 

If there is a user that wishes to use the database on a remote machine, it is very straightforward for you to set up a user account for that user and allow that user (with it’s appropriate restrictions) remote access, remote access for root is generally a BAD IDEA.

There is also an option to create an anonymous user.  While in development this might be a useful option or some very quirky and strange live deployment scenarios, generally this is a bad idea, since whether it is justified or not, it WILL lead to AN INSECURE SYSTEM.

Type your password for the root user and then confirm it, once you have done that, press the Next > button to continue:

Now, before you press the Execute button on this stage, be sure to review everything you’ve done.  It’s generally a good idea I’ve found to make notes of what you’re doing throughout the configuration process - this prevents mistakes and omissions on account of distraction or indeed carelessness.

If you want to change anything, you can always click the < Back button and go back to the appropriate page and review what you’d entered.

Once you’re sure that everything is okay, click the Execute button.  The configuration tool will then apply the configuration that you’ve selected to the MySQL Database Server you’ve just installed:

Prior to receiving this completed screen, you might get a warning message with Error 10061 where the Configuration tool complains that a firewall is preventing connection to the MySQL Server on ”localhost” port 3306.  Be sure to click the Retry button.  This will only really happen if you have Windows Firewall enabled, if you don’t have it enabled, the system will continue and you will get the summary screen presented above.

That’s all there is to it.  You now have a configured and ready to go MySQL Server on your Windows 2000 or 2003 installation.  It should be noted, that a full installation of MySQL will give you the MySQL Query Browser and the MySQL Administrator client applications also, but in the next part of this HOWTO we will look at how to install these themselves, as well as configure and use them.

Congratulations!

The next part will talk about the Installation, Configuration and Use of the MySQL Client Access tools.  This post will be modified with the appropriate links as the parts are completed and written up. 

Warm Regards,

Shabbir