dcsimg

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

By Scott Beatty (Send Email)
Posted Aug 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

This two-part tutorial covers nearly every detail of one writer's quest to install and configure a Web server, server-side scripting language, database, and data administration application on Windows 2000. Part 2 examines ways to install and configure MySQL and phpMyAdmin on the operating system.

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

Page 1 of 2


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.