2.4.8. Installing MySQL on Windows

A native Windows distribution of MySQL has been available from MySQL AB since version 3.21 and represents a sizable percentage of the daily downloads of MySQL. This section describes the process for installing MySQL on Windows.

Note: If you are upgrading MySQL from an existing installation older than MySQL 4.1.5, you must first perform the procedure described in Section 2.4.8.14, “Upgrading MySQL on Windows”.

To run MySQL on Windows, you need the following:

• A 32-bit Windows operating system such as 9x, Me, NT, 2000, XP, or Windows Server 2003.

  A Windows NT-based operating system (NT, 2000, XP, 2003) permits you to run the MySQL server as a service. The use of a Windows NT-based operating system is strongly recommended. See Section 2.4.8.11, “Starting MySQL as a Windows Service”.

  Generally, you should install MySQL on Windows using an account that has administrator rights. Otherwise, you may encounter problems with certain operations such as editing the PATH environment variable or accessing the Service Control Manager.

• TCP/IP protocol support.

• Enough space on the hard drive to unpack, install, and create the databases in accordance with your requirements (generally a minimum of 200 megabytes is recommended.)

There may also be other requirements, depending on how you plan to use MySQL:

• If you plan to connect to the MySQL server via ODBC, you need a Connector/ODBC driver. See Chapter 23, Connectors.

• If you need tables with a size larger than 4GB, install MySQL on an NTFS or newer filesystem. Don't forget to use MAX_ROWS and AVG_ROW_LENGTH when you create tables. See Section 13.1.5, “CREATE TABLE Syntax”.

MySQL for Windows is available in several distribution formats:

• Binary distributions are available that contain a setup program that installs everything you need so that you can start the server immediately. Another binary distribution format contains an archive that you simply unpack in the installation location and then configure yourself. For details, see Section 2.4.8.1, “Choosing An Installation Package”.

• The source distribution contains all the code and support files for building the executables using the Visual Studio compiler system.

Generally speaking, you should use a binary distribution that includes an installer. It is simpler to use than the others, and you need no additional tools to get MySQL up and running. The installer for the Windows version of MySQL, combined with a GUI Configuration Wizard, automatically installs MySQL, creates an option file, starts the server, and secures the default user accounts.

The following section describes how to install MySQL on Windows using a binary distribution. To use an installation package that does not include an installer, follow the procedure described in Section 2.4.8.5, “Installing MySQL from a Noinstall Zip Archive”. To install using a source distribution, see Section 2.4.14.6, “Installing MySQL from Source on Windows”.

MySQL distributions for Windows can be downloaded from http://dev.mysql.com/downloads/. See Section 2.4.4, “How to Get MySQL”.

2.4.8.1. Choosing An Installation Package

This section does not apply to MySQL Enterprise Server users.

For MySQL 5.0, there are three installation packages to choose from when installing MySQL on Windows:

• The Essentials Package: This package has a filename similar to mysql-essential-5.0.38-win32.msi and contains the minimum set of files needed to install MySQL on Windows, including the Configuration Wizard. This package does not include optional components such as the embedded server and benchmark suite.

• The Complete Package: This package has a filename similar to mysql-5.0.38-win32.zip and contains all files needed for a complete Windows installation, including the Configuration Wizard. This package includes optional components such as the embedded server and benchmark suite.

• The Noinstall Archive: This package has a filename similar to mysql-noinstall-5.0.38-win32.zip and contains all the files found in the Complete install package, with the exception of the Configuration Wizard. This package does not include an automated installer, and must be manually installed and configured.

The Essentials package is recommended for most users. It is provided as an .msi file for use with the Windows Installer. The Complete and Noinstall distributions are packaged as Zip archives. To use them, you must have a tool that can unpack .zip files.

Your choice of install package affects the installation process you must follow. If you choose to install either the Essentials or Complete install packages, see Section 2.4.8.2, “Installing MySQL with the Automated Installer”. If you choose to install MySQL from the Noinstall archive, see Section 2.4.8.5, “Installing MySQL from a Noinstall Zip Archive”.

2.4.8.2. Installing MySQL with the Automated Installer

New MySQL users can use the MySQL Installation Wizard and MySQL Configuration Wizard to install MySQL on Windows. These are designed to install and configure MySQL in such a way that new users can immediately get started using MySQL.

The MySQL Installation Wizard and MySQL Configuration Wizard are available in the Essentials and Complete install packages. They are recommended for most standard MySQL installations. Exceptions include users who need to install multiple instances of MySQL on a single server host and advanced users who want complete control of server configuration.

2.4.8.3. Using the MySQL Installation Wizard

2.4.8.3.1. Introduction to the Installation Wizard

MySQL Installation Wizard is an installer for the MySQL server that uses the latest installer technologies for Microsoft Windows. The MySQL Installation Wizard, in combination with the MySQL Configuration Wizard, allows a user to install and configure a MySQL server that is ready for use immediately after installation.

The MySQL Installation Wizard is the standard installer for all MySQL server distributions, version 4.1.5 and higher. Users of previous versions of MySQL need to shut down and remove their existing MySQL installations manually before installing MySQL with the MySQL Installation Wizard. See Section 2.4.8.3.7, “Upgrading MySQL with the Installation Wizard”, for more information on upgrading from a previous version.

Microsoft has included an improved version of their Microsoft Windows Installer (MSI) in the recent versions of Windows. MSI has become the de-facto standard for application installations on Windows 2000, Windows XP, and Windows Server 2003. The MySQL Installation Wizard makes use of this technology to provide a smoother and more flexible installation process.

The Microsoft Windows Installer Engine was updated with the release of Windows XP; those using a previous version of Windows can reference this Microsoft Knowledge Base article for information on upgrading to the latest version of the Windows Installer Engine.

In addition, Microsoft has introduced the WiX (Windows Installer XML) toolkit recently. This is the first highly acknowledged Open Source project from Microsoft. We have switched to WiX because it is an Open Source project and it allows us to handle the complete Windows installation process in a flexible manner using scripts.

Improving the MySQL Installation Wizard depends on the support and feedback of users like you. If you find that the MySQL Installation Wizard is lacking some feature important to you, or if you discover a bug, please report it in our bugs database using the instructions given in Section 1.8, “How to Report Bugs or Problems”.

2.4.8.3.2. Downloading and Starting the MySQL Installation Wizard

This section does not apply to MySQL Enterprise Server users.

The MySQL installation packages can be downloaded from http://dev.mysql.com/downloads/. If the package you download is contained within a Zip archive, you need to extract the archive first.

The process for starting the wizard depends on the contents of the installation package you download. If there is a setup.exe file present, double-click it to start the installation process. If there is an .msi file present, double-click it to start the installation process.

2.4.8.3.3. Choosing an Install Type

This section does not apply to MySQL Enterprise Server users.

There are three installation types available: Typical, Complete, and Custom.

The Typical installation type installs the MySQL server, the mysql command-line client, and the command-line utilities. The command-line clients and utilities include mysqldump, myisamchk, and several other tools to help you manage the MySQL server.

The Complete installation type installs all components included in the installation package. The full installation package includes components such as the embedded server library, the benchmark suite, support scripts, and documentation.

The Custom installation type gives you complete control over which packages you wish to install and the installation path that is used. See Section 2.4.8.3.4, “The Custom Install Dialog”, for more information on performing a custom install.

If you choose the Typical or Complete installation types and click the Next button, you advance to the confirmation screen to verify your choices and begin the installation. If you choose the Custom installation type and click the Next button, you advance to the custom installation dialog, described in Section 2.4.8.3.4, “The Custom Install Dialog”.

2.4.8.3.4. The Custom Install Dialog

This section does not apply to MySQL Enterprise Server users.

If you wish to change the installation path or the specific components that are installed by the MySQL Installation Wizard, choose the Custom installation type.

A tree view on the left side of the custom install dialog lists all available components. Components that are not installed have a red X icon; components that are installed have a gray icon. To change whether a component is installed, click on that component's icon and choose a new option from the drop-down list that appears.

You can change the default installation path by clicking the Change... button to the right of the displayed installation path.

After choosing your installation components and installation path, click the Next button to advance to the confirmation dialog.

2.4.8.3.5. The Confirmation Dialog

This section does not apply to MySQL Enterprise Server users.

Once you choose an installation type and optionally choose your installation components, you advance to the confirmation dialog. Your installation type and installation path are displayed for you to review.

To install MySQL if you are satisfied with your settings, click the Install button. To change your settings, click the Back button. To exit the MySQL Installation Wizard without installing MySQL, click the Cancel button.

After installation is complete, you have the option of registering with the MySQL web site. Registration gives you access to post in the MySQL forums at forums.mysql.com, along with the ability to report bugs at bugs.mysql.com and to subscribe to our newsletter. The final screen of the installer provides a summary of the installation and gives you the option to launch the MySQL Configuration Wizard, which you can use to create a configuration file, install the MySQL service, and configure security settings.

2.4.8.3.6. Changes Made by MySQL Installation Wizard

This section does not apply to MySQL Enterprise Server users.

Once you click the Install button, the MySQL Installation Wizard begins the installation process and makes certain changes to your system which are described in the sections that follow.

Changes to the Registry

The MySQL Installation Wizard creates one Windows registry key in a typical install situation, located in HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB.

The MySQL Installation Wizard creates a key named after the major version of the server that is being installed, such as MySQL Server 5.0. It contains two string values, Location and Version. The Location string contains the path to the installation directory. In a default installation it contains C:\Program Files\MySQL\MySQL Server 5.0\. The Version string contains the release number. For example, for an installation of MySQL Server 5.0.38, the key contains a value of 5.0.38.

These registry keys are used to help external tools identify the installed location of the MySQL server, preventing a complete scan of the hard-disk to determine the installation path of the MySQL server. The registry keys are not required to run the server, and if you install MySQL using the noinstall Zip archive, the registry keys are not created.

Changes to the Start Menu

The MySQL Installation Wizard creates a new entry in the Windows Start menu under a common MySQL menu heading named after the major version of MySQL that you have installed. For example, if you install MySQL 5.0, the MySQL Installation Wizard creates a MySQL Server 5.0 section in the Start menu.

The following entries are created within the new Start menu section:

• MySQL Command Line Client: This is a shortcut to the mysql command-line client and is configured to connect as the root user. The shortcut prompts for a root user password when you connect.

• MySQL Server Instance Config Wizard: This is a shortcut to the MySQL Configuration Wizard. Use this shortcut to configure a newly installed server, or to reconfigure an existing server.

• MySQL Documentation: This is a link to the MySQL server documentation that is stored locally in the MySQL server installation directory. This option is not available when the MySQL server is installed using the Essentials installation package.

Changes to the File System

The MySQL Installation Wizard by default installs the MySQL 5.0 server to C:\Program Files\MySQL\MySQL Server 5.0, where Program Files is the default location for applications in your system, and 5.0 is the major version of your MySQL server. This is the recommended location for the MySQL server, replacing the former default location C:\mysql.

By default, all MySQL applications are stored in a common directory at C:\Program Files\MySQL, where Program Files is the default location for applications in your Windows installation. A typical MySQL installation on a developer machine might look like this:

C:\Program Files\MySQL\MySQL Server 5.0
C:\Program Files\MySQL\MySQL Administrator 1.0

2.4.8.5. Installing MySQL from a Noinstall Zip Archive

This section does not apply to MySQL Enterprise Server users.

Users who are installing from the Noinstall package can use the instructions in this section to manually install MySQL. The process for installing MySQL from a Zip archive is as follows:

1. Extract the archive to the desired install directory

2. Create an option file

3. Choose a MySQL server type

4. Start the MySQL server

5. Secure the default user accounts

This process is described in the sections that follow.

C:\Program Files\MySQL\MySQL Query Browser 1.0

This approach makes it easier to manage and maintain all MySQL applications installed on a particular system.

2.4.8.3.7. Upgrading MySQL with the Installation Wizard

This section does not apply to MySQL Enterprise Server users.

The MySQL Installation Wizard can perform server upgrades automatically using the upgrade capabilities of MSI. That means you do not need to remove a previous installation manually before installing a new release. The installer automatically shuts down and removes the previous MySQL service before installing the new version.

Automatic upgrades are available only when upgrading between installations that have the same major and minor version numbers. For example, you can upgrade automatically from MySQL 4.1.5 to MySQL 4.1.6, but not from MySQL 4.1 to MySQL 5.0.

2.4.8.6. Extracting the Install Archive

This section does not apply to MySQL Enterprise Server users.

To install MySQL manually, do the following:

1. If you are upgrading from a previous version please refer to Section 2.4.8.14, “Upgrading MySQL on Windows”, before beginning the upgrade process.

2. If you are using a Windows NT-based operating system such as Windows NT, Windows 2000, Windows XP, or Windows Server 2003, make sure that you are logged in as a user with administrator privileges.

3. Choose an installation location. Traditionally, the MySQL server is installed in C:\mysql. The MySQL Installation Wizard installs MySQL under C:\Program Files\MySQL. If you do not install MySQL at C:\mysql, you must specify the path to the install directory during startup or in an option file. See Section 2.4.8.7, “Creating an Option File”.

4. Extract the install archive to the chosen installation location using your preferred Zip archive tool. Some tools may extract the archive to a folder within your chosen installation location. If this occurs, you can move the contents of the subfolder into the chosen installation location.

2.4.8.7. Creating an Option File

If you need to specify startup options when you run the server, you can indicate them on the command line or place them in an option file. For options that are used every time the server starts, you may find it most convenient to use an option file to specify your MySQL configuration. This is particularly true under the following circumstances:

• The installation or data directory locations are different from the default locations (C:\Program Files\MySQL\MySQL Server 5.0 and C:\Program Files\MySQL\MySQL Server 5.0\data).

• You need to tune the server settings.

When the MySQL server starts on Windows, it looks for options in two files: the my.ini file in the Windows directory, and the C:\my.cnf file. The Windows directory typically is named something like C:\WINDOWS or C:\WINNT. You can determine its exact location from the value of the WINDIR environment variable using the following command:

C:\> echo %WINDIR% 

MySQL looks for options first in the my.ini file, and then in the my.cnf file. However, to avoid confusion, it's best if you use only one file. If your PC uses a boot loader where C: is not the boot drive, your only option is to use the my.ini file. Whichever option file you use, it must be a plain text file.

You can also make use of the example option files included with your MySQL distribution; see Section 4.3.2.1, “Preconfigured Option Files”.

An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is in E:\mydata\data, you can create an option file containing a [mysqld] section to specify values for the basedir and datadir parameters:

[mysqld]
# set basedir to your installation path
basedir=E:/mysql
# set datadir to the location of your data directory
datadir=E:/mydata/data

Note that Windows pathnames are specified in option files using (forward) slashes rather than backslashes. If you do use backslashes, you must double them:

[mysqld]
# set basedir to your installation path
basedir=E:\\mysql
# set datadir to the location of your data directory

datadir=E:\\mydata\\data

On Windows, the MySQL installer places the data directory directly under the directory where you install MySQL. If you would like to use a data directory in a different location, you should copy the entire contents of the data directory to the new location. For example, if MySQL is installed in C:\Program Files\MySQL\MySQL Server 5.0, the data directory is by default in C:\Program Files\MySQL\MySQL Server 5.0\data. If you want to use E:\mydata as the data directory instead, you must do two things:

1. Move the entire data directory and all of its contents from C:\Program Files\MySQL\MySQL Server 5.0\data to E:\mydata.

2. Use a --datadir option to specify the new data directory location each time you start the server.

2.4.8.8. Selecting a MySQL Server Type

This section does not apply to MySQL Enterprise Server users.

The following table shows the available servers for Windows in MySQL 5.0.

Binary	Description
mysqld-nt	Optimized binary for Windows NT, 2000, and XP with support for named pipes
mysqld	Optimized binary without named pipe support (for Windows 95/ME)
mysqld-debug	Like mysqld-nt, but compiled with full debugging and automatic memory allocation checking

All of the preceding binaries are optimized for modern Intel processors, but should work on any Intel i386-class or higher processor.

Each of the servers in a distribution support the same set of storage engines. The SHOW ENGINES statement displays which engines a given server supports.

All Windows MySQL 5.0 servers have support for symbolic linking of database directories.

MySQL supports TCP/IP on all Windows platforms. The mysqld-nt and mysql-debug servers support named pipes on Windows NT, 2000, XP, and 2003. However, the default is to use TCP/IP regardless of platform. (Named pipes are slower than TCP/IP in many Windows configurations.)

Use of named pipes is subject to these conditions:

• Named pipes are enabled only if you start the server with the --enable-named-pipe option. It is necessary to use this option explicitly because some users have experienced problems with shutting down the MySQL server when named pipes were used.

• Named-pipe connections are allowed only by the mysqld-nt and mysqld-debug servers, and only if the server is run on a version of Windows that supports named pipes (NT, 2000, XP, 2003).

• These servers can be run on Windows 98 or Me, but only if TCP/IP is installed; named-pipe connections cannot be used.

• These servers cannot be run on Windows 95.

Note: Most of the examples in this manual use mysqld as the server name. If you choose to use a different server, such as mysqld-nt, make the appropriate substitutions in the commands that are shown in the examples.

2.4.8.9. Starting the Server for the First Time

This section gives a general overview of starting the MySQL server. The following sections provide more specific information for starting the MySQL server from the command line or as a Windows service.

The information here applies primarily if you installed MySQL using the Noinstall version, or if you wish to configure and test MySQL manually rather than with the GUI tools.

The examples in these sections assume that MySQL is installed under the default location of C:\Program Files\MySQL\MySQL Server 5.0. Adjust the pathnames shown in the examples if you have MySQL installed in a different location.

On NT-based systems such as Windows NT, 2000, XP, or 2003, clients have two options. They can use TCP/IP, or they can use a named pipe if the server supports named-pipe connections. For MySQL to work with TCP/IP on Windows NT 4, you must install service pack 3 (or newer).

On Windows 95, 98, or Me, MySQL clients always connect to the server using TCP/IP. (This allows any machine on your network to connect to your MySQL server.) Because of this, you must make sure that TCP/IP support is installed on your machine before starting MySQL. You can find TCP/IP on your Windows CD-ROM.

Note that if you are using an old Windows 95 release (for example, OSR2), it is likely that you have an old Winsock package; MySQL requires Winsock 2. You can get the newest Winsock from http://www.microsoft.com/. Windows 98 has the new Winsock 2 library, so it is unnecessary to update the library.

MySQL for Windows also supports shared-memory connections if the server is started with the --shared-memory option. Clients can connect through shared memory by using the --protocol=memory option.

For information about which server binary to run, see Section 2.4.8.8, “Selecting a MySQL Server Type”.

Testing is best done from a command prompt in a console window (or “DOS window”). In this way you can have the server display status messages in the window where they are easy to see. If something is wrong with your configuration, these messages make it easier for you to identify and fix any problems.

To start the server, enter this command:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld" --console 

For a server that includes InnoDB support, you should see the messages similar to those following as it starts (the pathnames and sizes may differ):

InnoDB: The first specified datafile c:\ibdata\ibdata1 did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file c:\ibdata\ibdata1 size to 209715200
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file c:\iblogs\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be created
InnoDB: Setting log file c:\iblogs\ib_logfile2 size to 31457280
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: creating foreign key constraint system tables
InnoDB: foreign key constraint system tables created
011024 10:58:25  InnoDB: Started

When the server finishes its startup sequence, you should see something like this, which indicates that the server is ready to service client connections:

mysqld: ready for connections


Version: '5.0.38'  socket: ''  port: 3306

The server continues to write to the console any further diagnostic output it produces. You can open a new console window in which to run client programs.

If you omit the --console option, the server writes diagnostic output to the error log in the data directory (C:\Program Files\MySQL\MySQL Server 5.0\data by default). The error log is the file with the .err extension.

Note: The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords for them using the instructions in Section 2.4.15, “Post-Installation Setup and Testing”.

2.4.8.10. Starting MySQL from the Windows Command Line

The MySQL server can be started manually from the command line. This can be done on any version of Windows.

To start the mysqld server from the command line, you should start a console window (or “DOS window”) and enter this command:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld" 

The path to mysqld may vary depending on the install location of MySQL on your system.

On non-NT versions of Windows, this command starts mysqld in the background. That is, after the server starts, you should see another command prompt. If you start the server this way on Windows NT, 2000, XP, or 2003, the server runs in the foreground and no command prompt appears until the server exits. Because of this, you should open another console window to run client programs while the server is running.

You can stop the MySQL server by executing this command:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" -u root shutdown 

Note: If the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted.

This command invokes the MySQL administrative utility mysqladmin to connect to the server and tell it to shut down. The command connects as the MySQL root user, which is the default administrative account in the MySQL grant system. Note that users in the MySQL grant system are wholly independent from any login users under Windows.

If mysqld doesn't start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the C:\Program Files\MySQL\MySQL Server 5.0\data directory. It is the file with a suffix of .err. You can also try to start the server as mysqld --console; in this case, you may get some useful information on the screen that may help solve the problem.

The last option is to start mysqld with the --standalone and --debug options. In this case, mysqld writes a log file C:\mysqld.trace that should contain the reason why mysqld doesn't start. See Creating Trace Files.

Use mysqld --verbose --help to display all the options that mysqld understands.

2.4.8.11. Starting MySQL as a Windows Service

On the NT family (Windows NT, 2000, XP, 2003), the recommended way to run MySQL is to install it as a Windows service, whereby MySQL starts and stops automatically when Windows starts and stops. A MySQL server installed as a service can also be controlled from the command line using NET commands, or with the graphical Services utility.

The Services utility (the Windows Service Control Manager) can be found in the Windows Control Panel (under Administrative Tools on Windows 2000, XP, and Server 2003). To avoid conflicts, it is advisable to close the Services utility while performing server installation or removal operations from the command line.

Before installing MySQL as a Windows service, you should first stop the current server if it is running by using the following command:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" -u root shutdown 

Note: If the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted.

This command invokes the MySQL administrative utility mysqladmin to connect to the server and tell it to shut down. The command connects as the MySQL root user, which is the default administrative account in the MySQL grant system. Note that users in the MySQL grant system are wholly independent from any login users under Windows.

Install the server as a service using this command:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld" --install 

The service-installation command does not start the server. Instructions for that are given later in this section.

To make it easier to invoke MySQL programs, you can add the pathname of the MySQL bin directory to your Windows system PATH environment variable:

• On the Windows desktop, right-click on the My Computer icon, and select Properties

• Next select the Advanced tab from the System Properties menu that appears, and click the Environment Variables button.

• Under System Variables, select Path, and then click the Edit button. The Edit System Variable dialogue should appear.

• Place your cursor at the end of the text appearing in the space marked Variable Value. (Use the End key to ensure that your cursor is positioned at the very end of the text in this space.) Then enter the complete pathname of your MySQL bin directory (for example, C:\Program Files\MySQL\MySQL Server 5.0\bin), Note that there should be a semicolon separating this path from any values present in this field. Dismiss this dialogue, and each dialogue in turn, by clicking OK until all of the dialogues that were opened have been dismissed. You should now be able to invoke any MySQL executable program by typing its name at the DOS prompt from any directory on the system, without having to supply the path. This includes the servers, the mysql client, and all MySQL command-line utilities such as mysqladmin and mysqldump.

  You should not add the MySQL bin directory to your Windows PATH if you are running multiple MySQL servers on the same machine.

Warning: You must exercise great care when editing your system PATH by hand; accidental deletion or modification of any portion of the existing PATH value can leave you with a malfunctioning or even unusable system.

The following additional arguments can be used in MySQL 5.0 when installing the service:

• You can specify a service name immediately following the --install option. The default service name is MySQL.

• If a service name is given, it can be followed by a single option. By convention, this should be --defaults-file=file_name to specify the name of an option file from which the server should read options when it starts.

  It is possible to use a single option other than --defaults-file, but this is discouraged. --defaults-file is more flexible because it enables you to specify multiple startup options for the server by placing them in the named option file. Also, in MySQL 5.0, use of an option different from --defaults-file is not supported until 5.0.3.

• As of MySQL 5.0.1, you can also specify a --local-service option following the service name. This causes the server to run using the LocalService Windows account that has limited system privileges. This account is available only for Windows XP or newer. If both --defaults-file and --local-service are given following the service name, they can be in any order.

For a MySQL server that is installed as a Windows service, the following rules determine the service name and option files that the server uses:

• If the service-installation command specifies no service name or the default service name (MySQL) following the --install option, the server uses the a service name of MySQL and reads options from the [mysqld] group in the standard option files.

• If the service-installation command specifies a service name other than MySQL following the --install option, the server uses that service name. It reads options from the group that has the same name as the service, and reads options from the standard option files.

  The server also reads options from the [mysqld] group from the standard option files. This allows you to use the [mysqld] group for options that should be used by all MySQL services, and an option group with the same name as a service for use by the server installed with that service name.

• If the service-installation command specifies a --defaults-file option after the service name, the server reads options only from the [mysqld] group of the named file and ignores the standard option files.

As a more complex example, consider the following command:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld"
         --install MySQL --defaults-file=C:\my-opts.cnf 

Here, the default service name (MySQL) is given after the --install option. If no --defaults-file option had been given, this command would have the effect of causing the server to read the [mysqld] group from the standard option files. However, because the --defaults-file option is present, the server reads options from the [mysqld] option group, and only from the named file.

You can also specify options as Start parameters in the Windows Services utility before you start the MySQL service.

Once a MySQL server has been installed as a service, Windows starts the service automatically whenever Windows starts. The service also can be started immediately from the Services utility, or by using a NET START MySQL command. The NET command is not case sensitive.

When run as a service, mysqld has no access to a console window, so no messages can be seen there. If mysqld does not start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the MySQL data directory (for example, C:\Program Files\MySQL\MySQL Server 5.0\data). It is the file with a suffix of .err.

When a MySQL server has been installed as a service, and the service is running, Windows stops the service automatically when Windows shuts down. The server also can be stopped manually by using the Services utility, the NET STOP MySQL command, or the mysqladmin shutdown command.

You also have the choice of installing the server as a manual service if you do not wish for the service to be started automatically during the boot process. To do this, use the --install-manual option rather than the --install option:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld" --install-manual 

To remove a server that is installed as a service, first stop it if it is running by executing NET STOP MySQL. Then use the --remove option to remove it:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld" --remove 

If mysqld is not running as a service, you can start it from the command line. For instructions, see Section 2.4.8.10, “Starting MySQL from the Windows Command Line”.

Please see Section 2.4.8.13, “Troubleshooting a MySQL Installation Under Windows”, if you encounter difficulties during installation.

2.4.8.12. Testing The MySQL Installation

You can test whether the MySQL server is working by executing any of the following commands:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqlshow"
C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqlshow" -u root mysql
C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" version status proc
C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql" test 

If mysqld is slow to respond to TCP/IP connections from client programs, there is probably a problem with your DNS. In this case, start mysqld with the --skip-name-resolve option and use only localhost and IP numbers in the Host column of the MySQL grant tables.

You can force a MySQL client to use a named-pipe connection rather than TCP/IP by specifying the --pipe or --protocol=PIPE option, or by specifying . (period) as the host name. Use the --socket option to specify the name of the pipe if you do not want to use the default pipe name.

Note that if you have set a password for the root account, deleted the anonymous account, or created a new user account, then you must use the appropriate -u and -p options with the commands shown above in order to connect with the MySQL Server. See Section 5.7.4, “Connecting to the MySQL Server”.

For more information about mysqlshow, see Section 8.16, “mysqlshow — Display Database, Table, and Column Information”.

2.4.8.13. Troubleshooting a MySQL Installation Under Windows

When installing and running MySQL for the first time, you may encounter certain errors that prevent the MySQL server from starting. The purpose of this section is to help you diagnose and correct some of these errors.

Your first resource when troubleshooting server issues is the error log. The MySQL server uses the error log to record information relevant to the error that prevents the server from starting. The error log is located in the data directory specified in your my.ini file. The default data directory location is C:\Program Files\MySQL\MySQL Server 5.0\data. See Section 5.11.1, “The Error Log”.

Another source of information regarding possible errors is the console messages displayed when the MySQL service is starting. Use the NET START MySQL command from the command line after installing mysqld as a service to see any error messages regarding the starting of the MySQL server as a service. See Section 2.4.8.11, “Starting MySQL as a Windows Service”.

The following examples show other common error messages you may encounter when installing MySQL and starting the server for the first time:

• If the MySQL server cannot find the mysql privileges database or other critical files, you may see these messsages:

  System error 1067 has occurred.
  Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist
  

  These messages often occur when the MySQL base or data directories are installed in different locations than the default locations (C:\Program Files\MySQL\MySQL Server 5.0 and C:\Program Files\MySQL\MySQL Server 5.0\data, respectively).

  This situation may occur when MySQL is upgraded and installed to a new location, but the configuration file is not updated to reflect the new location. In addition, there may be old and new configuration files that conflict. Be sure to delete or rename any old configuration files when upgrading MySQL.

  If you have installed MySQL to a directory other than C:\Program Files\MySQL\MySQL Server 5.0, you need to ensure that the MySQL server is aware of this through the use of a configuration (my.ini) file. The my.ini file needs to be located in your Windows directory, typically C:\WINDOWS or C:\WINNT. You can determine its exact location from the value of the WINDIR environment variable by issuing the following command from the command prompt:

  C:\> echo %WINDIR% 

  An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is D:\MySQLdata, you can create the option file and set up a [mysqld] section to specify values for the basedir and datadir parameters:

  [mysqld]
  # set basedir to your installation path
  basedir=E:/mysql
  # set datadir to the location of your data directory
  datadir=D:/MySQLdata
  

  Note that Windows pathnames are specified in option files using (forward) slashes rather than backslashes. If you do use backslashes, you must double them:

  [mysqld]
  # set basedir to your installation path
  basedir=C:\\Program Files\\MySQL\\MySQL Server 5.0
  # set datadir to the location of your data directory
  datadir=D:\\MySQLdata
  

  MySQL Enterprise. For expert advice on the start-up options appropriate to your circumstances, subscribe to the MySQL Network Monitoring and Advisory Service. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

  If you change the datadir value in your MySQL configuration file, you must move the contents of the existing MySQL data directory before restarting the MySQL server.

  See Section 2.4.8.7, “Creating an Option File”.

• If you reinstall or upgrade MySQL without first stopping and removing the existing MySQL service and install MySQL using the MySQL Configuration Wizard, you may see this error:

  Error: Cannot create Windows service for MySql. Error: 0
  

  This occurs when the Configuration Wizard tries to install the service and finds an existing service with the same name.

  One solution to this problem is to choose a service name other than mysql when using the configuration wizard. This allows the new service to be installed correctly, but leaves the outdated service in place. Although this is harmless, it is best to remove old services that are no longer in use.

  To permanently remove the old mysql service, execute the following command as a user with administrative privileges, on the command-line:

  C:\> sc delete mysql
  [SC] DeleteService SUCCESS
  

  If the sc utility is not available for your version of Windows, download the delsrv utility from http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/delsrv-o.asp and use the delsrv mysql syntax.

2.4.8.14. Upgrading MySQL on Windows

This section does not apply to MySQL Enterprise Server users.

This section lists some of the steps you should take when upgrading MySQL on Windows.

1. Review Section 2.4.16, “Upgrading MySQL”, for additional information on upgrading MySQL that is not specific to Windows.

2. You should always back up your current MySQL installation before performing an upgrade. See Section 5.9.1, “Database Backups”.

3. Download the latest Windows distribution of MySQL from http://dev.mysql.com/downloads/.

4. Before upgrading MySQL, you must stop the server. If the server is installed as a service, stop the service with the following command from the command prompt:

   C:\> NET STOP MySQL 

   If you are not running the MySQL server as a service, use the following command to stop it:

   C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" -u root shutdown 

   Note: If the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted.

5. When upgrading to MySQL 5.0 from a version previous to 4.1.5, or when upgrading from a version of MySQL installed from a Zip archive to a version of MySQL installed with the MySQL Installation Wizard, you must manually remove the previous installation and MySQL service (if the server is installed as a service).

   To remove the MySQL service, use the following command:

   C:\> C:\mysql\bin\mysqld --remove 

   If you do not remove the existing service, the MySQL Installation Wizard may fail to properly install the new MySQL service.

6. If you are using the MySQL Installation Wizard, start the wizard as described in Section 2.4.8.3, “Using the MySQL Installation Wizard”.

7. If you are installing MySQL from a Zip archive, extract the archive. You may either overwrite your existing MySQL installation (usually located at C:\mysql), or install it into a different directory, such as C:\mysql5. Overwriting the existing installation is recommended.

8. If you were running MySQL as a Windows service and you had to remove the service earlier in this procedure, reinstall the service. (See Section 2.4.8.11, “Starting MySQL as a Windows Service”.)

9. Restart the server. For example, use NET START MySQL if you run MySQL as a service, or invoke mysqld directly otherwise.

10. If you encounter errors, see Section 2.4.8.13, “Troubleshooting a MySQL Installation Under Windows”.

2.4.8.15. MySQL on Windows Compared to MySQL on Unix

MySQL for Windows has proven itself to be very stable. The Windows version of MySQL has the same features as the corresponding Unix version, with the following exceptions:

• Windows 95 and threads

  Windows 95 leaks about 200 bytes of main memory for each thread creation. Each connection in MySQL creates a new thread, so you shouldn't run mysqld for an extended time on Windows 95 if your server handles many connections! Newer versions of Windows don't suffer from this bug.

• Limited number of ports

  Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Note that ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower.

  For more information about this problem, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271.

• Concurrent reads

  MySQL depends on the pread() and pwrite() system calls to be able to mix INSERT and SELECT. Currently, we use mutexes to emulate pread() and pwrite(). We intend to replace the file level interface with a virtual interface in the future so that we can use the readfile()/writefile() interface on NT, 2000, and XP to get more speed. The current implementation limits the number of open files that MySQL 5.0 can use to 2,048, which means that you cannot run as many concurrent threads on Windows NT, 2000, XP, and 2003 as on Unix.

• Blocking read

  MySQL uses a blocking read for each connection. That has the following implications if named-pipe connections are enabled:

  • A connection is not disconnected automatically after eight hours, as happens with the Unix version of MySQL.

  • If a connection hangs, it is not possible to break it without killing MySQL.

  • mysqladmin kill does not work on a sleeping connection.

  • mysqladmin shutdown cannot abort as long as there are sleeping connections.

  We plan to fix this problem in the future.

• ALTER TABLE

  While you are executing an ALTER TABLE statement, the table is locked from being used by other threads. This has to do with the fact that on Windows, you can't delete a file that is in use by another thread. In the future, we may find some way to work around this problem.

• DROP TABLE

  DROP TABLE on a table that is in use by a MERGE table does not work on Windows because the MERGE handler does the table mapping hidden from the upper layer of MySQL. Because Windows does not allow dropping files that are open, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table.

• DATA DIRECTORY and INDEX DIRECTORY

  The DATA DIRECTORY and INDEX DIRECTORY options for CREATE TABLE are ignored on Windows, because Windows doesn't support symbolic links. These options also are ignored on systems that have a non-functional realpath() call.

• DROP DATABASE

  You cannot drop a database that is in use by some thread.

• Killing MySQL from the Task Manager

  On Windows 95, you cannot kill MySQL from the Task Manager or with the shutdown utility. You must stop it with mysqladmin shutdown.

• Case-insensitive names

  Filenames are not case sensitive on Windows, so MySQL database and table names are also not case sensitive on Windows. The only restriction is that database and table names must be specified using the same case throughout a given statement. See Section 9.2.2, “Identifier Case Sensitivity”.

• The ‘\’ pathname separator character

  Pathname components in Windows are separated by the ‘\’ character, which is also the escape character in MySQL. If you are using LOAD DATA INFILE or SELECT ... INTO OUTFILE, use Unix-style filenames with ‘/’ characters:

  mysql> LOAD DATA INFILE 'C:/tmp/skr.txt' INTO TABLE skr;
  mysql> SELECT * INTO OUTFILE 'C:/tmp/skr.txt' FROM skr; 

  Alternatively, you must double the ‘\’ character:

  mysql> LOAD DATA INFILE 'C:\\tmp\\skr.txt' INTO TABLE skr;
  mysql> SELECT * INTO OUTFILE 'C:\\tmp\\skr.txt' FROM skr; 

• Problems with pipes

  Pipes do not work reliably from the Windows command-line prompt. If the pipe includes the character ^Z / CHAR(24), Windows thinks that it has encountered end-of-file and aborts the program.

  This is mainly a problem when you try to apply a binary log as follows:

  C:\> mysqlbinlog binary_log_file | mysql --user=root 

  If you have a problem applying the log and suspect that it is because of a ^Z / CHAR(24) character, you can use the following workaround:

  C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
  C:\> mysql --user=root --execute "source /tmp/bin.sql" 

  The latter command also can be used to reliably read in any SQL file that may contain binary data.

• Access denied for user error

  If MySQL cannot resolve your hostname properly, you may get the following error when you attempt to run a MySQL client program to connect to a server running on the same machine:

  Access denied for user 'some_user'@'unknown'
  to database 'mysql'
  

  To fix this problem, you should create a file named \windows\hosts containing the following information:

  127.0.0.1       localhost
  

Here are some open issues for anyone who might want to help us improve MySQL on Windows:

• Add macros to use the faster thread-safe increment/decrement methods provided by Windows.