How To Install and Configure MySQL for PHP Applications on Windows IIS 7

Microsoft SQL Server is the most preferred database to use when you decide to host PHP applications on IIS 7 (Internet Information Services 7). MySQL can also be used as an alternative database.

Many PHP applications use MySQL Server for storage of data which makes it a great addition to IIS 7. This tutorial will show you how to install and configure MySQL on IIS 7.

Step 1- Installing MySQL on Windows

MySQL should be installed on a dedicated server instead of installing it on a similar server with IIS running. It is important to separate them to make the installation process secure and avoid confusion between database and process in the web server (click here for the best dedicated server hosting services).

For a start.

Download MySQL server. Get the Windows Installer.
Open the Window Installer and extract the files then run the Setup.exe
Depending on what suits your needs, choose either a Typical Setup or a custom installation.
After the installation process is complete, check the box â€œConfigure the MySQL Server now.â€

Step 2 â€“ Configuring MYSQL Instance

Now run the â€œMySQL Server Instance Configuration Wizard.â€ Remember to select the appropriate configuration settings that match what you need.

In the Instance Configuration Wizard, hit â€œNextâ€.
Choose â€œDetailed Configuration,â€ then hit â€œNextâ€.
Choose a server that best fits your environment. When setting up your MySQL server, remember to select the type of server as a â€œDedicated MySQL Server Machine,â€ and proceed to the next step.
Choose your preferred database, and click â€œNextâ€.
- In this section, you can either choose Multifunctional Database or Transactional Database options for MyISAM or InnoDB storage engines. This can be used if youâ€™re dealing with multiple statement transactions on your web applications or when there are other advanced levels that require isolation, or foreign constraints as well as ACID (Atomic, Consistent, Isolated, and Durable) Features.
- You can also opt to use â€œNon-Transactional Database Onlyâ€ option for improved performance with low cost on memory and disc space.
Select the number of connections needed in your system.
Adjust the settings for the desired network depending on the environment, then click â€œNextâ€.
Enable both options on the Windows section and click â€œNext.â€
Input the password of your choice for the account and proceed to the next step.
Click â€œExecuteâ€ to apply these changes.
Click â€œFinishâ€ to exit from the wizard.

To ensure PHP is working well with MySQL. Also, conduct some changes in the Php.ini file:

Make sure the file extension_dir is pointing to the exact location of the PHP extensions.
To enable MySQL dynamic extension, uncomment on the following line on MySQL extension: extension=php_mysql.dll
Save and exit from the Php.ini file.

Creating a secure MySQL

Start by removing any strange database account that exists in the system. Then go ahead and open the MySQL command and follow the process below: Start > All Programs > MySQL >MySQL Server 5.1 >MySQL Command Line Client.
Type your password for your account.
Now you should access MySQL. Run the command below:

mysql> use mysql;
Database changed
mysql> DELETE FROM user WHERE user = '';
Query OK, 2 rows affected (0.03 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)

Next, ensure the root account is restricted. This can be done from the localhost. In the MySQL command prompt, run the following commands:

mysql> use mysql;
Database changed
mysql> DELETE FROM user WHERE user = 'root' AND host = '%';
Query OK, 2 rows affected (0.03 sec) 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)

Use the command below to change the actual name of the root user:

mysql> USE mysql;
Database changed
mysql> UPDATE user SET user='johndoe' WHERE user='root';
Query OK, 1 row affected (0.19 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.23 sec)

Step 3 â€“ Provision a New User and Database.

To provision the user, run the command below:

mysql>CREATE USER 'some_username'  IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)

By default, the new user doesnâ€™t have any rights on MySQL. So, to ensure the user access the file, type the command below:

mysql>CREATE DATABASE IF NOT EXISTS some_database_name;
Query OK, 1 row affected (0.00 sec)

To ensure the user gains access to this database, run the command below:

mysql>   GRANT ALTER,
      -> ALTER ROUTINE,
      -> CREATE,
      -> CREATE ROUTINE,
      -> CREATE TEMPORARY TABLES,
      -> CREATE VIEW,
      -> DELETE,
      -> DROP,
      -> EXECUTE,
      -> INDEX,
      -> INSERT,
      -> LOCK TABLES,
      -> SELECT,
      -> UPDATE,
      -> SHOW VIEW
      ON some_database_name.* TO 'some_username';

Step 4 â€“ Configuring PHP application to Access MySQL

Go to c:phpphp.ini from your text editor

Ensure you uncomment the text below by omitting the semicolon

extension=php_mysqli.dll

extension=php_mbstring.dll

extension=php_mcrypt.dll

Now restart IIS by going to â€œStart,â€ select the â€œSearch Field,â€ type the word â€œiisreset.â€ and click ENTER.

If everything is successfully installed, the MySQL section will be visible on the PHP page you created earlier on http://localhost/phpinfo.php.

The output should look like this:

Conclusion

Congratulations! You now have your own IIS web server configured with MySQL and PHP application. We hope these simple steps will be helpful in getting you started.