Real-World Windows 2000 Configuration: Getting Apache, PHP, MySQL, and phpMyAdmin to Work Together, Part 2

Real-World Windows 2000 Configuration: Getting Apache, PHP, MySQL, and phpMyAdmin to Work Together, Part 2


August 7, 2002

Part 1 of this two-part tutorial focuses on implementing Apache and PHP on Windows 2000. Here, we continue with our writer's quest to get his system up and running, by examining how to install and configure a database (MySQL) and database admin tool (phpMyAdmin) on the operating system.

The Database: MySQL - Installation and Configuration

According to the MySQL Web site, MySQL is the world's most popular open source database. Apache, PHP, and MySQL are often used together (usually with Linux -- which leads to the acronym LAMP -- and less often with FreeBSD, but in this article we are obviously using Windows 2000). Earlier this year, in performance testing of SQL databases, MySQL did very well. For normal internal use it is free, but if there is a question about the license you can read the MySQL Licensing Policy. Technical support can be purchased from MySQL AB. MySQL AB sponsors various mailing lists through which you can obtain free MySQL support, including one for users in a Win32 environment.

The current stable release of MySQL is 3.23.51. It can be downloaded at http://www.mysql.com/downloads/mysql-3.23.html. The next major release is 4.0.2, which is currently in development but can be downloaded at http://www.mysql.com/downloads/mysql-4.0.html. I used 4.0.1-alpha for a few months without incident. I am now using 4.0.2-alpha, which is the version I will write about in this article. Please take note, however, that MySQL 4.0.2 is still in the alpha stage, and should be used only for test purposes.

The online documentation for the installation of MySQL on Windows is at http://www.mysql.com/doc/W/i/Windows_installation.html. I installed MySQL by extracting the files and folders from the download, mysql-4.0.2-alpha-win.zip, to a temporary directory, and then double-clicking the setup.exe file and following the default prompts. The files will end up in C:\mysql.

This distribution of MySQL comes with four sample configuration files: my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. (Note: When using Windows Explorer, or the File | Open dialog box in some text editors, the .cnf extension is usually hidden, so all you'll see will be shortcut icons and filenames without an extension.) I don't know what the definitions of small, medium, large, and huge are (although you can see the settings in the sample config files), but for this article I will use my-small.cnf. Whichever sample config file you choose, copy it to the root of the C:\ drive, and rename it to my.cnf. (In other words, the result will be located at C:\my.cnf.) The online documentation for the config files is at http://www.mysql.com/doc/O/p/Option_files.html.

Start the MySQL service before starting Apache. (The commands above for starting and shutting down Apache are noted in Part 1 of this series.) Starting and stopping a service can be done from the Services console, which is found under Administrative Tools in the Start menu, or it can be done from a command line, using:

    net start mysql

and

    net stop mysql

Batch Files

For convenience, you might want to create two batch files, one for each of these commands, and then create an icon for each batch file on your Windows desktop. If you have never created a batch file, just use a text editor to create a file containing one of the commands, e.g., net start mysql, and then name the file with a .bat extension, e.g., startMySQL.bat. Put the batch file in the C:\mysql directory, and then right-click it and select "Create Shortcut," and move the resulting icon to your desktop. Double-click the icon to activate the command. You can do the same thing for the commands for starting and stopping Apache. Place the batch files in the C:\Apache directory, and the shortcuts on your desktop.

When MySQL is first installed, it contains a blank database titled "test," and it is configured for two users, "root" and "" (i.e., blank), with no password required for either one. Both users have full privileges. Also, both users can access databases from "localhost" and any other. If you are going to open your database to other users you are most likely going to want to change these default settings. The MySQL documentation explains how to do this from the command line at http://www.mysql.com/doc/W/i/Windows_running.html.

The next section of this article presents a tool that provides an alternate way to make changes to your databases, user privileges, passwords, and more. (Incidentally, these user, password, and privilege changes occur in the "mysql" database. In terms of files on your server's (or laptop's) hard drive, these changes are reflected in the C:\mysql\data\mysql directory.)

Web-Based MySQL Database Administration: phpMyAdmin - Installation and Configuration

phpMyAdmin is a set of PHP scripts that enable MySQL database administration via a browser. I use phpMyAdmin frequently to create queries and otherwise interact with and administer my databases and tables. The latest stable version of phpMyAdmin is 2.2.6. Currently, there is a release candidate, 2.3.0-rc4, available. The next stable version, 2.3.0, is due August 11, 2002.

phpMyAdmin can be downloaded at http://www.phpmyadmin.net/. The online documentation is at http://www.phpmyadmin.net/documentation/, and it is also included in the distribution (i.e., in the .zip file that you download). The user support forum for phpMyAdmin on SourceForge.net is at: http://sourceforge.net/forum/forum.php?forum_id=72909

phpMyAdmin can be configured for multiple users. For this article, I configured it for just one user. I will use phpMyAdmin version 2.3.0-rc4. A general rule is that when you upgrade, do not copy your old configuration file on top of the new one without at least first comparing them for differences. Alternatively, you can just edit the changes needed in the new configuration file.

To install phpMyAdmin, extract the files from phpMyAdmin-2.3.0-rc4-php.zip. I like to rename the top-level directory, which in this case is phpMyAdmin-2.3.0-rc4, to just phpMyAdmin. Put this directory in the C:\Apache\htdocs directory. Once Apache and MySQL are running, you can access phpMyAdmin through your browser with the following URL:

    http://localhost/phpmyadmin/

or

    https://localhost/phpmyadmin/

assuming you set up your configuration as shown in this article.

The configuration file for phpMyAdmin is C:\Apache\htdocs\phpMyAdmin\config.inc.php. I made the changes noted below.

Changes to config.inc.php

$cfg['PmaAbsoluteUri'] = 'https://localhost/phpMyAdmin/';

$cfg['Servers'][$i]['auth_type']     = 'http';

With this config file, and the one for Apache, I set up phpMyAdmin so that it uses SSL while connected.

phpMyAdmin has three authentication modes from which to choose to control access to itself and the databases: http, cookies, and config. They are explained in the documentation, in the "Installation" section. For this article, I chose http authentication. To use the http authentication mode for phpMyAdmin, PHP must be running as an Apache module (this article has Apache configured that way). With http authentication, the user password is not stored in a file. Rather, it is entered each time at login. This is good. What I am not sure about; however, is how well the input data to the login form is scrubbed for control characters and other hack attempt-related input before it is authenticated. Another question I have is: During http login, is the password sent by the browser unencrypted to the server before the SSL connection is established?

There is at least one other way to control access to phpMyAdmin, and that is by using PHP sessions. Controlling access this way would require adding or including some code to the phpMyAdmin scripts. This approach is beyond the scope of the present article, however.

When you enter the URL to access phpMyAdmin, as configured above, your browser will present a login dialog to you. Enter the User Name and Password of the root user of your databases. If you have other users defined, with different privileges, they can log in, too, and what they'll be able to see will be dictated by their privileges as set in MySQL. When you are done using phpMyAdmin, just close the browser window to effectively log out.

The way to set a user password is not by using phpMyAdmin to enter the password directly into the text editing field in the mysql database row corresponding to that user. Rather, passwords should be set or changed using an actual SQL query. An example of setting the password for the user named "root" is:

UPDATE `user` SET `Password` = PASSWORD('rootuserpassword') WHERE `User` = 'root';

This query can be entered at the mysql command line, or it can be entered through a tool, such as phpMyAdmin, that provides an interface to the database. In phpMyAdmin, when you click on a database name in the left frame, the right frame will contain a tab-like link to a page with a text entry field for running SQL queries for that particular database. The use of the PASSWORD() function within the query has the effect of encrypting the password before it is stored in the database. After this query successfully executes, you will be able to see the encrypted form of rootuserpassword when you browse the database with phpMyAdmin.

For versions of phpMyAdmin that are not release candidates, you can add some protection of your phpMyAdmin configuration file by moving it to your directory of include files that are located outside of the document root. If you move it, edit the line in the phpMyAdmin file C:\Apache\htdocs\phpMyAdmin\libraries\common.lib.php that is the PHP statement that includes the config file. For example, I would rename the config.inc.php to config.inc, move it to the C:\phpinc directory, and then edit the config file include statement in common.lib.php so it becomes:

include('config.inc');

Windows-Based MySQL Database Administration: Scheduling Dumps, and Other Admin Tools

To set up regularly scheduled dumps of database schema and data you can use the Scheduled Tasks tool. This is the Windows 2000 equivalent of the cron daemon. You can find it at Start | Programs | Accessories | System Tools.

First, create a batch file containing a mysqldump statement. For example, in the C:\mysql\bin directory you can create a file called dump.bat containing two lines of code:

    @echo off
    mysqldump --user username --password=userpassword --opt db > E:\dumps\dbdump.sql

The user specified in the mysqldump statement must have at least the SELECT privilege for the database to be dumped.

Then, use the Scheduled Tasks tool to run this batch file at the desired regular interval. The batch file creates a dump of database db in the dumps directory on the E:\ drive. The value for userpassword in the batch file is the unencrypted form of the password for that database. Since you will be storing a username-password combination to the database in a file, make sure that you control access to that file. Later in this article we will discuss controlling file access.

Be aware that that there are some characters, such as ^, that cannot be used in passwords that you will be putting in mysqldump statements in batch files.

If you are using Windows NT, there is the the AT command (for a GUI version, there is WinAT, from the Windows NT 4.0 Resource Kit). To use it, the Schedule service must be running. A reference for the AT command syntax is located at http://www.cotse.com/Ntcommands/ntcmdsuntitled00000004.html#000002ff. I have not tested using the AT command to schedule and execute mysqldump commands.

For more information on scheduling tasks in a Win32 environment see http://aspn.activestate.com//ASPN/Reference/Products/ActivePerl/faq/Windows/ActivePerl-Winfaq4.html#How_do_I_schedule_jobs_on_Win32_.

The online documentation for the mysqldump command is available at http://www.mysql.com/doc/m/y/mysqldump.html.

An alternative to mysqldump is mysqlhotcopy. The online documentation for MySQL database backups is at http://www.mysql.com/doc/B/a/Backup.html

In addition to phpMyAdmin, another MySQL administration tool I use is the Windows freeware utility MySQL-Front, available at http://my.anse.de/. It has many database administration capabilities, similar to phpMyAdmin. It is not browser-based; however, and instead is a Windows GUI application that requires access to the databases.

phpMyAdmin and MySQL-Front can both generate individual MySQL dumps.

MySQL comes with a variety of tools, such as WinMySQLadmin, which is located in the C:\mysql\bin directory. Also, a variety of other freeware and non-free programs are avDatabase Administration: phpMyAdminailable on the Internet that you can use to administer MySQL.

Discuss this article in the ServerWatch discussion forum

Log Files

Using the previously described configurations, Apache, PHP, and MySQL will generate the following log files:

C:\Apache\logs\error.log
C:\Apache\logs\access.log
C:\Apache\logs\ssl.log
C:\php_log\php_errors.log
C:\mysql\data\mysql.err

The Apache manual has a section titled Log Files.

Chapter 16 of the PHP documentation covers Error Handling.

Section 4.9 of the MySQL manual describes the MySQL log files and their maintenance.

Like the database dumps described above, you can create a batch file and use the Scheduled Tasks tool to run regular backups of your log files.

Once your site is online, one of the programs that can be used to analyze Apache log files is AWStats. It requires Perl.

Controlling File Access at the OS Level: Windows 2000 NTFS and File-Sharing Permissions

My hard drive partitions are formatted NTFS. This allows the setting of access permissions to folders (AKA directories) and files.

I gave the MySQL install directory, C:/mysql, the following permissions in Windows 2000:
     Administrator - Full Control
     Everyone - Full Control
But I did not share the folder.

The minimum permissions for Everyone that I could find that still allowed me to start the MySQL service are:
     Everyone - Special*:
                          - Traverse Folder / Execute Data
                          - List Folder / Read Data
                          - Read Attributes
                          - Read Extended Attributes
(*click the Advanced button on the Security tab in the mysql Properties dialog)
With just these permissions I was able to submit a SELECT query to a MySQL table, but I was not able to make changes, such as INSERT, to it (because it was read-only).

The batch file that I created for database dumps, as described earlier, is located in the C:\mysql\bin directory. It contains a username and password. This file is located in a directory that is not shared; however, for extra security, perhaps, individual permissions can be set for the file. I was able to run a scheduled task that executes this batch file with the only user granted access to this file being an administrator with Full Control. To set the security for the file, right-click it in Windows Explorer, select Properties, and then the Security tab.

Similarly, individual tasks listed in the Scheduled Tasks tool can also be assigned security settings. Right-click the task, select Properties, and then the Security tab. I was able to run a mysqldump batch file task with just an administrator having Full Control of the task.

I log in to Windows as an administrator to do development work, including working with MySQL via phpMyAdmin.

Regarding Apache, some factors to consider include the following settings and permissions for the document root, i.e., C:\Apache\htdocs (allow changes to propagate to its subfolders):

  • After you are done developing your site (otherwise you won't be able to edit the files) set it to read-only.
  • Do not share it.
  • Remove Everyone as a user. Set up an administrator as a user with Full Control.
  • Disable "Allow inheritable permissions from the parent to propagate to this object."

Regarding PHP and the directory where you keep your include files (e.g., C:\phpinc): consider the same settings and permissions for it as were used for Apache's document root.

In my opinion, this subject of file and directory permissions for running Apache, PHP, and MySQL on Windows 2000 (or NT) is not sufficiently documented. Please do not consider the information presented here as authoritative, but rather as explorative.

If this is the first time the subject has been brought to your attention, you can now explore it on your own, and find what works best for your situation.

As to general Windows security, in July 2002 the Center for Internet Security released benchmarks and a scoring tool for Windows 2000 and NT. They can be obtained at: http://www.cisecurity.org/bench_win2000.html. Also, a benchmark for the Apache Web Server is under development.

Closing Comments

The approach shared in this article is obviously not the only way to go, but it is one up-to-date way that works in Windows 2000 and is presented here for educational purposes. There is still much to learn. It is a challenge to put all the pieces together, and less information is available when doing it on Windows.

Also note that new versions of software come out, and they might bring changes that invalidate some of the information in this article. In addition, sometimes new software brings with it bugs that break things that were working before.

Discuss this article in the ServerWatch discussion forum