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
This distribution of MySQL comes with four sample configuration files:
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
net stop mysql
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.,
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
.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:
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:
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, move it to the
C:\phpinc directory, and then edit the config file include statement in
common.lib.php so it becomes:
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
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.
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.