A Complete Guide to Installing and Configuring MySQL for PHP Apps on Windows IIS 7

Many developers and administrators prefer to use MySQL server for their PHP applications on IIS 7. One of the advantages is it can also be used as an alternate database.

If you’re going to use MySQL server on Windows IIS 7, learn how to install and configure MySQL the proper way for PHP.

Below are the steps involved for installing and configuring MySQL for PHP apps on Windows IIS 7. It’s divided into two parts. First shows how to install and the second demonstrates how to configure the MySQL instance.

Installing MySQL on Windows

When installing MySQL for Windows IIS 7, you should note one thing. That is you should install it on a dedicated server and not on the server that’s running IIS. It’s crucial to keep the two separated to avoid the confusion between a database and process in the web server.

Here are the steps to start the installation process:

Download MySQL server and Windows Installer, if it isn’t available
Open Windows Installer
Start extracting the files and run Setup.exe. You can opt for either a Typical setup or a custom installation. Stick to the former for simplicity
Once installation is over, check the box that reads ‘Configure the MySQL Server now’

Configuring MySQL

The next part is configuring the MySQL instance. To begin the configuration process, run the ‘MySQL Server Instance Configuration Wizard.’ Here, you’d have to make appropriate settings as per your preference. Here are the steps to follow:

Open Instance Configuration Wizard, and select ‘Next’
Then, choose ‘Detailed Configuration’ and click on ‘Next’
Select a server that you deem best for the environment. When creating the MySQL server, select the type of server as ‘Dedicated MySQL Server Machine’
Select your preferred database and then click on ‘Next’.

Within this section, you can either a Multifunctional or a Transactional Database for storage engines like MyISAM or InnoDB. Select these databases if your web application works with multiple statement transactions. Other scenarios like when you need isolation or foreign constraints and ACID Feature (Atomic, Consistent, Isolated, and Durable), you’d have to select the databases
You can select ‘Non-Transactions Database Only’ for improved performance. This would also have a lower cost on memory

Next, you’d have to select the number of connections to your MySQL server
Adjust the settings to match the environment requirements and hit ‘Next’
On the Windows section, enable both the options and then click ‘Next’
You’d have to set a password. So input a password of your choice
Review for one last time and click on ‘Execute’ to apply the settings
Now click on ‘Finish’ and you’ll exit the Configuration Wizard

Now you’d have to head over to the php.ini file to make some changes. This is to make sure PHP is working fine with MySQL. Here are the things to take care of inside the file:

Ensure that ‘extensions_dir’ is pointing to exact PHP extensions location
Uncomment this line - ‘extension=php_mysql.dll’. It will enable the MySQL dynamic extension

Create a Secure MySQL

Before you start creating a secure MySQL, you need to check for any unsolicited database account that might exist on your system. Remove all such accounts since they can hamper security. Now open the MySQL command by navigating- All Programs>MySQL>MySQL Server 5.1>MySQL Command Line. Then follow the steps:

Enter the account password
Once you access MySQL, enter the following commands:

Code:

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)

Then, execute the following command to restrict root account:

Code:

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)

Now execute the following command for changing root user’s name:

Code:

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)

Provision a New User and Database

Lastly, you need to provision a user. To begin, execute this command:

Code:

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

The new user you just created doesn’t have any rights on MySQL. It’s time to grant the basic rights like ability to access the file. Use this command for this purpose:

Code:

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

Also, give user access to the database. Execute this command:

Code:

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';

Configuring PHP Application

The last step is to give access to the PHP application to the MySQL server. For this open the php.ini file by visiting c:\php\php.ini. Uncomment the following:

Code:

extension=php_mysqli.dll
extension=php_mbstring.dll
extension=php_mcrypt.dll

Once done, restart IIS. If you followed the steps correctly, then you’ll be able to see the MySQL section on the PHP page you created at this URL- http://localhost/phpinfo.php. You should see a table as the output.

So that’s how you install and configure MySQL for your PHP apps on IIS 7. For further assistance, contact the hosting support team.

A Complete Guide to Installing and Configuring MySQL for PHP Apps on Windows IIS 7

More resources from kumkumsharma

Share this resource