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,
This distribution of MySQL comes with four sample configuration files:
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
net stop mysql
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
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
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
assuming you set up your configuration as shown in this article.
The configuration file for phpMyAdmin is
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:
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
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
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
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
First, create a batch file containing a
@echo off mysqldump --user username --password=userpassword --opt db > E:\dumps\dbdump.sql
The user specified in the
Then, use the Scheduled Tasks tool to run this batch file at the desired regular interval. The batch file creates a dump of database
Be aware that that there are some characters, such as
If you are using Windows NT, there is the the
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
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
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.
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,
The minimum permissions for Everyone that I could find that still allowed me to start the MySQL service are:
The batch file that I created for database dumps, as described earlier, is located in the
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
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.,
Regarding PHP and the directory where you keep your include files (e.g.,
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.
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.