Overview
A majority of popular PHP applications use MySQL Server as a back end data storage. This requires hosting providers to include MySQL database support into the hosting packages. This section provides a set of basic instructions on how to setup and configure MySQL Server on Windows Server® 2008 for use in shared hosting environments. For detailed documentation about MySQL Server, refer to MySQL documentation.
This article contains:
Installing MySQL Server on Windows Server 2008
It is recommended to install MySQL server on a dedicated server machine rather than having it on the same server with IIS 7.0. This separation of database server and web server makes overall installation more secure and manageable and avoids resource contentions between database and web server processes.
MySQL Server binaries and installation package can be downloaded from the official MySQL web site. For the purposes of this guide, the MySQL Community Server v 5.0.45 was used. MySQL can be installed either by using the installer package or by manually copying binaries onto a server file system. For detailed set of instructions on how to install MySQL on Windows, refer to these articles: Using the MySQL Installation Wizard or Installing MySQL from a Noinstall Zip Archive.
Configuring MySQL Instance
Once MySQL installer completes, it will offer an option of launching a MySQL Server Configuration Wizard.
Note: If you plan to enable network access to your database server, then you must open a TCP port in Windows Firewall before running the configuration wizard.
By default MySQL uses TCP port 3306, but it can be changed via Configuration Wizard or manually in my.ini file.
To create a Firewall rule for the default MySQL TCP port, run this command from Windows command line prompt:
C:\>netsh advfirewall firewall add rule name="MySQL Server" action=allow protocol=TCP dir=in localport=3306
Once the Firewall port has been opened, run the MySQL Server Configuration Wizard and choose the configurations options that most closely match your environment.
Some of the configuration options that you may want to set when configuring MySQL for shared hosting are described below. For a detailed list of all settings provided in Configuration Wizard, refer to MySQL Server Configuration Wizard.
Server Type
When choosing a server type, select either “Server Machine” or “Dedicated MySQL Server Machine”, depending on what kind of server setup you have. In general, it is recommended to have a database server setup on a separate machine and use “Dedicated MySQL Server Machine” option.
Database Usage
Database usage options control what kind of database storage engine is used on the server:
- MyISAM – Optimized for high performance SELECT operations. It has low overhead in terms of memory usage and disk utilization, but at the cost of not supporting transactions
- InnoDB – Provides fully ACID transactional capabilities, but at the cost of more aggressive usage of disk space and memory
For an in-depth comparison of these database engines, refer to MySQL Storage Engine Architecture. As a general recommendation – if web applications on your server require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features -- use InnoDB. Otherwise, use MyISAM.
InnoDB Data File Location
For the InnoDB storage engine, it is possible to change the location of the database data files. Usually, this is recommended when your system has higher capacity or higher performance storage devices available, such as the RAID storage system.
This option is not available for MyISAM storage engine.
Concurrent Connections
Choose the option that sets the number of concurrent connection you need.
Note: Connections take memory, so if the number you choose is too big, then the server may not be able to handle too many connections.
Modifying my.ini File
My.ini file, located in %ProgramFiles%\MySQL\MySQL Server 5.0\ contains the startup variables for the MySQL service. Modify settings in that file to fine tune server performance. In the same folder, you may find several other .ini files, including these two:
- my-large.ini – Contains recommended configuration settings for running MySQL service on dedicated server with 512 MB of RAM
- my-huge.ini – Contains recommended configuration settings for running MySQL service on dedicated server with 1 to 2 GB or RAM
Depending on your server configuration, use some of the recommended settings from those files.
Configuring PHP to Work with MySQL Server
In order for PHP to work with MySQL it is necessary to perform the following modifications to php.ini file:
- Set extension_dir to point to the folder where all PHP loadable extensions are located, frequently in the ext folder, e.g. extension_dir=”.\ext”.
- Enable dynamic extension for MySQL by un-commenting the corresponding line, e.g. extension=mysql.dll.
Securing MySQL Server on Windows Server 2008
The following guidelines describe how to tighten the security of MySQL Server on Windows Server 2008.
Change the MySQL Service Account
By default, MySQL service is installed to run as a highly privileged Local System account. However, it is recommended to run the service under a more restricted service account. You can create a dedicated account for it or you can use a Windows built-in “Local Service” account. To change the MySQL service account follow these steps (you must be logged in as Windows Server Administrator):
1. Stop MySQL service by typing this command:
C:\>net stop mysql
2. Grant new service account permissions to MySQL installation folder.
C:\>icacls "c:\Program Files\MySQL\MySQL Server 5.0" /grant "NT AUTHORITY\LOCAL SERVICE":(OI)(CI)(M)
3. Configure MySQL service to use a more restricted service account by going to Start Administrative Tools Services and opening properties of MySQL service. Open the Log On Tab and enter “Local Service” in the “This account” text box. Leave the Password and Confirm password fields empty.
4. Start MySQL service by typing this command:
C:\>net start mysql
Remove Anonymous Database Account If It Exists
1. Open the MySQL command prompt by typing:
mysql –u root –p
2. Once logged on to MySQL, use the following sequence of commands:
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)
Restrict Root Account to Login Only from localhost
1. Open MySQL command prompt by typing:
mysql –u root –p
2. Once logged on to MySQL 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> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)
Change the Name of the Root User
1. Open MySQL command prompt by typing:
mysql –u root –p
2. Once logged on to MySQL use the following sequence of commands:
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)
User and Database Provisioning
The following examples demonstrate how to provision new user and database on MySQL Server.
User Provisioning
To provision a new user to MySQL server database follow these steps:
1. Open MySQL command prompt by typing:
Mysql –u root –p
2. Once logged on to MySQL use the following command:
mysql>CREATE USER ‘some_username’ IDENTIFIED BY ‘some_password’;
Query OK, 0 rows affected (0.00 sec)
Note: The newly created user by default does not have any privileges on MySQL Server.
Database Provisioning
To create a new database and grant user access to it follow these steps:
1. Open MySQL command prompt by typing:
Mysql –u root –p
2. Once logged on to MySQL use the following command:
mysql>CREATE DATABASE IF NOT EXISTS some_database_name;
Query OK, 1 row affected (0.00 sec)
To grant access to this database for a particular user, use this command:
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';
Comments