Install and Configure MySQL for PHP Applications on IIS 7

  • Article
  • 5 minutes to read

past Ruslan Yakushev

Introduction

While Microsoft® SQL Server® 2008 is the recommended database to use when hosting PHP applications on an Internet Information Services vii (IIS 7) and above Web server, you lot can too apply MySQL as the database. Currently, many popular PHP applications utilize MySQL Server for data storage. Using MySQL requires hosting providers to include MySQL database support with the hosting packages.

MySQL cannot currently exist installed with the Microsoft® Web Platform Installer (Web PI). This article provides guidance for installing MySQL manually.

Install MySQL Server on Windows Server 2008 or Windows Server 2008 R2

It is recommended that you install MySQL on a dedicated server rather than installing MySQL on the aforementioned server that is running IIS. The separation of database server and Web server makes overall installation more secure and manageable and avoids resource contentions between the database and Spider web server processes.

  1. Download MySQL Community Server. We recommend downloading Windows® Installer.
  2. Get-go Windows Installer, or extract all the files from the annal, and then start Setup.exe.
  3. You tin can use a Typical Setup or customize the installation to suit your needs.
  4. Once the installation magician is completed, it is recommended that you leave the Configure the MySQL Server at present check box selected.

Configure a MySQL Case

  1. Run the MySQL Server Instance Configuration Magician, and and then cull the configurations options that nearly closely match your environment.
    For more information, see the Server Example Configuration Wizard.
    Best exercise recommendations are equally follows:

    • Click Next in the Instance Configuration Magician.

    • Select Detailed Configuration, and then click Next.

    • Select a server type that best suits your environment. It is recommended to ready a split up MySQL server; when prompted to select a server type, select Defended MySQL Server Machine, and so click Next.

    • Select a database selection, and then click Adjacent.

      • Select either the Multifunctional Database or Transactional Database Only options if y'all are using the InnoDB storage engine or the high-speed MyISAM storage engine (for example, if the Spider web applications on your server require multi-statement transactions, avant-garde isolation levels and row-level locking, foreign cardinal constraints, or atomic, consistent, isolated, and durable [ACID] features). These options provides fully ACID transactional capabilities, just at the price of more aggressive usage of disk space and retentiveness.
      • Otherwise, apply the Non-Transactional Database Only selection, which is optimized for loftier-performance SELECT operations. It has low overhead, in terms of memory usage and disk utilization, simply at the toll of not supporting transactions.

    • Choose the option that sets the number of concurrent connections you demand.

      Note

      Connections crave retentiveness; if the number you cull is as well big, your server may non have enough memory.

    • Yous may adjust networking settings to suit your environment or have defaults, and and then click Adjacent.

    • Select the default character set that all-time suits y'all, and and then click Adjacent.

    • We recommend enabling both Windows options here. Select both check boxes, and then click Next.

    • Type the password you want to apply for the root account, and so click Next.

    • Click Execute to employ your settings.

    • Click Finish to close the wizard.

  • For PHP to piece of work with MySQL, it is necessary to perform the following modifications to the Php.ini file:

    • Confirm that the extension_dir points to the folder where all PHP loadable extensions are located, frequently in the Ext folder (for example, extension_dir=".\ext").
    • Enable dynamic extension for MySQL by uncommenting the respective line for the MySQL extension: extension=php_mysql.dll
    • c. Save and close the Php.ini file.

Secure MySQL

  1. Remove the anonymous database account (if it exists). Open up the MySQL command prompt by clicking Start -> All Programs -> MySQL -> MySQL Server 5.i -> MySQL Command Line Client:

  2. Enter the password for the root account.

  3. Once logged on to MySQL, use the following sequence of commands:

                      mysql> utilise mysql; Database changed mysql> DELETE FROM user WHERE user = ''; Query OK, ii rows affected (0.03 sec) mysql> Flush PRIVILEGES; Query OK, 0 rows affected (0.05 sec)

  4. Side by side, restrict the root account to log on just from localhost. Open a MySQL command prompt, and use the following sequence of commands:

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

  5. Change the proper noun of the root user with the following sequence of commands from the command prompt:

                      mysql> Utilise mysql; Database changed mysql> UPDATE user Gear up user='johndoe' WHERE user='root'; Query OK, i row affected (0.19 sec) Rows matched: 1  Inverse: i  Warnings: 0 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.23 sec)

Provision the User and Database

  1. To provision a new user, type the following control from the MySQL command prompt:

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

  2. The newly created user does not have any privileges on the MySQL server past default. To create a new database, blazon the following command:

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

  3. To grant access to this database for a detail user, type the following command:

                      mysql>   GRANT ALTER,       -> Modify 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';

Configure PHP to Access MySQL

  1. Open up the c:\php\php.ini file with your favorite text editor.

  2. Uncomment the following lines by removing the semicolon:

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

  3. Restart the IIS service by clicking on Beginning, selecting the Search Field, typing iisreset, and and then pressing ENTER.

  4. If all went well, you should see the mysqli section on the PHP information page created before http://localhost/phpinfo.php.

    Table listing Mysqli version information.

    Figure 1: The mysqli section on the PHP information page

Best Practices for MySQL

  • Enable TCP/IP Networking — This is the default. Keep the TCP port that MySQL uses to listen at 3306. If the database volition be running on a split system from the Web server, select the Add firewall exception for this port cheque box.
  • Include Bin Directory in Windows PATH — This makes the MySQL utilities available from the command prompt or from Windows PowerShell™.
  • Create an Anonymous Account — The default is to keep this disabled. Adding anonymous user support may create a security take a chance for the database; additionally, enabling anonymous users causes the GRANT statements used to set up database to be unreliable.

Run into also

  • MySQL Server Web site
  • Using the MySQL Installation Wizard
  • Installing MySQL from a Noinstall Zip Archive
  • MySQL Windows Installation