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:\php\php.ini from your text editor
Ensure you uncomment the text below by omitting the semicolon
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:
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.