2.3. Installing MySQL on Windows

MySQL 5.0

2.3. 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 the procedure described in Section 2.3.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.3.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 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 and when you create tables. See Section 13.1.5, “ 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.3.1, “Choosing An Installation Package”.

  • The source distribution contains all the code and support files for building the executables using the Visual Studio 7.1 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.3.5, “Installing MySQL from a Noinstall Zip Archive”. To install using a source distribution, see Section 2.9.6, “Installing MySQL from Source on Windows”.

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

2.3.1. Choosing An Installation Package

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 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 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 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 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 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.3.2, “Installing MySQL with the Automated Installer”. If you choose to install MySQL from the Noinstall archive, see Section 2.3.5, “Installing MySQL from a Noinstall Zip Archive”.

2.3.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.3.3. Using the MySQL Installation Wizard

2.3.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.3.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.3.3.2. Downloading and Starting the MySQL Installation Wizard

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 file present, double-click it to start the installation process. If there is an file present, double-click it to start the installation process.

2.3.3.3. Choosing an Install Type

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.3.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.3.3.4, “The Custom Install Dialog”.

2.3.3.4. The Custom Install Dialog

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.3.3.5. The Confirmation Dialog

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.3.3.6. Changes Made by MySQL Installation Wizard

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 .

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

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 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 user. The shortcut prompts for a 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 \MySQL\MySQL Server , where is the default location for applications in your system, and is the major version of your MySQL server. This is the recommended location for the MySQL server, replacing the former default location .

By default, all MySQL applications are stored in a common directory at \MySQL, where 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
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.3.3.7. Upgrading MySQL with the Installation Wizard

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.

See Section 2.3.14, “Upgrading MySQL on Windows”.

2.3.4. Using the Configuration Wizard

2.3.4.1. Introduction to the Configuration Wizard

The MySQL Configuration Wizard helps automate the process of configuring your server under Windows. The MySQL Configuration Wizard creates a custom file by asking you a series of questions and then applying your responses to a template to generate a file that is tuned to your installation.

The MySQL Configuration Wizard is included with the MySQL 5.0 server, and is currently available for Windows users only.

The MySQL Configuration Wizard is to a large extent the result of feedback that MySQL AB has received from many users over a period of several years. However, if you find that it lacks some feature important to you, please report it in our bugs database using the instructions given in Section 1.8, “How to Report Bugs or Problems”.

2.3.4.2. Starting the MySQL Configuration Wizard

The MySQL Configuration Wizard is typically launched from the MySQL Installation Wizard, as the MySQL Installation Wizard exits. You can also launch the MySQL Configuration Wizard by clicking the MySQL Server Instance Config Wizard entry in the MySQL section of the Windows Start menu.

Alternatively, you can navigate to the directory of your MySQL installation and launch the file directly.

2.3.4.3. Choosing a Maintenance Option

If the MySQL Configuration Wizard detects an existing file, you have the option of either reconfiguring your existing server, or removing the server instance by deleting the file and stopping and removing the MySQL service.

To reconfigure an existing server, choose the Re-configure Instance option and click the Next button. Your existing file is renamed to .ini.bak, where is the date and time at which the existing file was created. To remove the existing server instance, choose the Remove Instance option and click the Next button.

If you choose the Remove Instance option, you advance to a confirmation window. Click the Execute button. The MySQL Configuration Wizard stops and removes the MySQL service, and then deletes the file. The server installation and its folder are not removed.

If you choose the Re-configure Instance option, you advance to the Configuration Type dialog where you can choose the type of installation that you wish to configure.

2.3.4.4. Choosing a Configuration Type

When you start the MySQL Configuration Wizard for a new MySQL installation, or choose the Re-configure Instance option for an existing installation, you advance to the Configuration Type dialog.

There are two configuration types available: Detailed Configuration and Standard Configuration. The Standard Configuration option is intended for new users who want to get started with MySQL quickly without having to make many decisions about server configuration. The Detailed Configuration option is intended for advanced users who want more fine-grained control over server configuration.

If you are new to MySQL and need a server configured as a single-user developer machine, the Standard Configuration should suit your needs. Choosing the Standard Configuration option causes the MySQL Configuration Wizard to set all configuration options automatically with the exception of Service Options and Security Options.

The Standard Configuration sets options that may be incompatible with systems where there are existing MySQL installations. If you have an existing MySQL installation on your system in addition to the installation you wish to configure, the Detailed Configuration option is recommended.

To complete the Standard Configuration, please refer to the sections on Service Options and Security Options in Section 2.3.4.11, “The Service Options Dialog”, and Section 2.3.4.12, “The Security Options Dialog”, respectively.

2.3.4.5. The Server Type Dialog

There are three different server types available to choose from. The server type that you choose affects the decisions that the MySQL Configuration Wizard makes with regard to memory, disk, and processor usage.

  • Developer Machine: Choose this option for a typical desktop workstation where MySQL is intended only for personal use. It is assumed that many other desktop applications are running. The MySQL server is configured to use minimal system resources.

  • Server Machine: Choose this option for a server machine where the MySQL server is running alongside other server applications such as FTP, email, and Web servers. The MySQL server is configured to use a moderate portion of the system resources.

  • Dedicated MySQL Server Machine: Choose this option for a server machine that is intended to run only the MySQL server. It is assumed that no other applications are running. The MySQL server is configured to use all available system resources.

2.3.4.6. The Database Usage Dialog

The Database Usage dialog allows you to indicate the storage engines that you expect to use when creating MySQL tables. The option you choose determines whether the storage engine is available and what percentage of the server resources are available to .

  • Multifunctional Database: This option enables both the and storage engines and divides resources evenly between the two. This option is recommended for users who use both storage engines on a regular basis.

  • Transactional Database Only: This option enables both the and storage engines, but dedicates most server resources to the storage engine. This option is recommended for users who use almost exclusively and make only minimal use of .

  • Non-Transactional Database Only: This option disables the storage engine completely and dedicates all server resources to the storage engine. This option is recommended for users who do not use .

2.3.4.7. The InnoDB Tablespace Dialog

Some users may want to locate the tablespace files in a different location than the MySQL server data directory. Placing the tablespace files in a separate location can be desirable if your system has a higher capacity or higher performance storage device available, such as a RAID storage system.

To change the default location for the tablespace files, choose a new drive from the drop-down list of drive letters and choose a new path from the drop-down list of paths. To create a custom path, click the ... button.

If you are modifying the configuration of an existing server, you must click the Modify button before you change the path. In this situation you must move the existing tablespace files to the new location manually before starting the server.

2.3.4.8. The Concurrent Connections Dialog

To prevent the server from running out of resources, it is important to limit the number of concurrent connections to the MySQL server that can be established. The Concurrent Connections dialog allows you to choose the expected usage of your server, and sets the limit for concurrent connections accordingly. It is also possible to set the concurrent connection limit manually.

  • Decision Support (DSS)/OLAP: Choose this option if your server does not require a large number of concurrent connections. The maximum number of connections is set at 100, with an average of 20 concurrent connections assumed.

  • Online Transaction Processing (OLTP): Choose this option if your server requires a large number of concurrent connections. The maximum number of connections is set at 500.

  • Manual Setting: Choose this option to set the maximum number of concurrent connections to the server manually. Choose the number of concurrent connections from the drop-down box provided, or enter the maximum number of connections into the drop-down box if the number you desire is not listed.

2.3.4.9. The Networking and Strict Mode Options Dialog

Use the Networking Options dialog to enable or disable TCP/IP networking and to configure the port number that is used to connect to the MySQL server.

TCP/IP networking is enabled by default. To disable TCP/IP networking, uncheck the box next to the Enable TCP/IP Networking option.

Port 3306 is used by default. To change the port used to access MySQL, choose a new port number from the drop-down box or type a new port number directly into the drop-down box. If the port number you choose is in use, you are prompted to confirm your choice of port number.

Set the Server SQL Mode to either enable or disable strict mode. Enabling strict mode (default) makes MySQL behave more like other database management systems. If you run applications that rely on MySQL's old “forgiving” behavior, make sure to either adapt those applications or to disable strict mode. For more information about strict mode, see Section 5.2.5, “The Server SQL Mode”.

2.3.4.10. The Character Set Dialog

The MySQL server supports multiple character sets and it is possible to set a default server character set that is applied to all tables, columns, and databases unless overridden. Use the Character Set dialog to change the default character set of the MySQL server.

  • Standard Character Set: Choose this option if you want to use as the default server character set. is used for English and many Western European languages.

  • Best Support For Multilingualism: Choose this option if you want to use as the default server character set. This is a Unicode character set that can store characters from many different languages.

  • Manual Selected Default Character Set / Collation: Choose this option if you want to pick the server's default character set manually. Choose the desired character set from the provided drop-down list.

2.3.4.11. The Service Options Dialog

On Windows NT-based platforms, the MySQL server can be installed as a Windows service. When installed this way, the MySQL server can be started automatically during system startup, and even restarted automatically by Windows in the event of a service failure.

The MySQL Configuration Wizard installs the MySQL server as a service by default, using the service name . If you do not wish to install the service, uncheck the box next to the Install As Windows Service option. You can change the service name by picking a new service name from the drop-down box provided or by entering a new service name into the drop-down box.

To install the MySQL server as a service but not have it started automatically at startup, uncheck the box next to the Launch the MySQL Server Automatically option.

2.3.4.12. The Security Options Dialog

It is strongly recommended that you set a password for your MySQL server, and the MySQL Configuration Wizard requires by default that you do so. If you do not wish to set a password, uncheck the box next to the Modify Security Settings option.

To set the password, enter the desired password into both the New root password and Confirm boxes. If you are reconfiguring an existing server, you need to enter the existing password into the Current root password box.

To prevent logins from across the network, check the box next to the Root may only connect from localhost option. This increases the security of your account.

To create an anonymous user account, check the box next to the Create An Anonymous Account option. Creating an anonymous account can decrease server security and cause login and permission difficulties. For this reason, it is not recommended.

2.3.4.13. The Confirmation Dialog

The final dialog in the MySQL Configuration Wizard is the Confirmation Dialog. To start the configuration process, click the Execute button. To return to a previous dialog, click the Back button. To exit the MySQL Configuration Wizard without configuring the server, click the Cancel button.

After you click the Execute button, the MySQL Configuration Wizard performs a series of tasks and displays the progress onscreen as the tasks are performed.

The MySQL Configuration Wizard first determines configuration file options based on your choices using a template prepared by MySQL AB developers and engineers. This template is named and is located in your server installation directory.

The MySQL Configuration Wizard then writes these options to a file. The final location of the file is displayed next to the Write configuration file task.

If you chose to create a service for the MySQL server, the MySQL Configuration Wizard creates and starts the service. If you are reconfiguring an existing service, the MySQL Configuration Wizard restarts the service to apply your configuration changes.

If you chose to set a password, the MySQL Configuration Wizard connects to the server, sets your new password and applies any other security settings you may have selected.

After the MySQL Configuration Wizard has completed its tasks, it displays a summary. Click the Finish button to exit the MySQL Configuration Wizard.

2.3.4.14. The Location of the my.ini File

The MySQL Configuration Wizard places the file in the installation directory for the MySQL server. This helps associate configuration files with particular server instances.

To ensure that the MySQL server knows where to look for the file, an argument similar to this is passed to the MySQL server as part of the service installation:

--defaults-file="\my.ini"

Here, is replaced with the installation path to the MySQL Server. The option instructs the MySQL server to read the specified file for configuration options when it starts.

2.3.4.15. Editing the my.ini File

To modify the file, open it with a text editor and make any necessary changes. You can also modify the server configuration with the MySQL Administrator utility.

MySQL clients and utilities such as the mysql and mysqldump command-line clients are not able to locate the file located in the server installation directory. To configure the client and utility applications, create a new file in the or directory (whichever is applicable to your Windows version).

2.3.5. Installing MySQL from a Noinstall Zip Archive

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.

2.3.6. Extracting the Install Archive

To install MySQL manually, do the following:

  1. If you are upgrading from a previous version please refer to Section 2.3.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 . The MySQL Installation Wizard installs MySQL under . If you do not install MySQL at , you must specify the path to the install directory during startup or in an option file. See Section 2.3.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.3.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 ( and ).

  • You need to tune the server settings.

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

C:\> 

MySQL looks for options first in the file, and then in the file. However, to avoid confusion, it's best if you use only one file. If your PC uses a boot loader where is not the boot drive, your only option is to use the 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 and the data directory is in , you can create an option file containing a section to specify values for the and 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 directory to the new location. For example, if MySQL is installed in , the data directory is by default in . If you want to use as the data directory instead, you must do two things:

  1. Move the entire directory and all of its contents from to .

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

2.3.8. Selecting a MySQL Server type

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

Binary Description
mysqld-debug Compiled with full debugging and automatic memory allocation checking, as well as and support.
mysqld Optimized binary with support.
mysqld-nt Optimized binary for Windows NT, 2000, and XP with support for named pipes.
mysqld-max Optimized binary with and support.
mysqld-max-nt Like mysqld-max, but compiled with support for named pipes.

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

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 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 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 or mysqld-max-nt 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.3.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 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 . 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 option. Clients can connect through shared memory by using the option.

For information about which server binary to run, see Section 2.3.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:\> 

For a server that includes 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.25'  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 option, the server writes diagnostic output to the error log in the data directory ( by default). The error log is the file with the 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.10, “Post-Installation Setup and Testing”.

2.3.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:\> 

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:\> 

Note: If the MySQL user account has a password, you need to invoke mysqladmin with the 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 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 directory. It is the file with a suffix of . 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 and options. In this case, mysqld writes a log file that should contain the reason why mysqld doesn't start. See Section E.1.2, “Creating Trace Files”.

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

2.3.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:\> 

Note: If the MySQL user account has a password, you need to invoke mysqladmin with the 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 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:\> 

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 directory to your Windows system 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 directory (for example, ), 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 directory to your Windows if you are running multiple MySQL servers on the same machine.

Warning: You must exercise great care when editing your system by hand; accidental deletion or modification of any portion of the existing 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 option. The default service name is .

  • If a service name is given, it can be followed by a single option. By convention, this should be 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 , but this is discouraged. 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 is not supported until 5.0.3.

  • As of MySQL 5.0.1, you can also specify a option following the service name. This causes the server to run using the Windows account that has limited system privileges. This account is available only for Windows XP or newer. If both and 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 () following the option, the server uses the a service name of and reads options from the group in the standard option files.

  • If the service-installation command specifies a service name other than following the 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 group from the standard option files. This allows you to use the 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 option after the service name, the server reads options only from the group of the named file and ignores the standard option files.

As a more complex example, consider the following command:

C:\> 
          

Here, the default service name () is given after the option. If no option had been given, this command would have the effect of causing the server to read the group from the standard option files. However, because the option is present, the server reads options from the 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, ). It is the file with a suffix of .

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 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 option rather than the option:

C:\> 

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 option to remove it:

C:\> 

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

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

2.3.12. Testing The MySQL Installation

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

C:\> 
C:\> 
C:\> 
C:\> 

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 option and use only and IP numbers in the 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 or option, or by specifying (period) as the host name. Use the 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 account, deleted the anonymous account, or ceated a new user account, then you must use the appropriate and options with the commands shown above in order to connect with the MySQL Server. See Section 5.8.4, “Connecting to the MySQL Server”.

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

2.3.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 file. The default data directory location is . See Section 5.12.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.3.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 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 ( and , 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 , you need to ensure that the MySQL server is aware of this through the use of a configuration () file. The file needs to be located in your Windows directory, typically or . You can determine its exact location from the value of the environment variable by issuing the following command from the command prompt:

    C:\> 
    

    An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in and the data directory is , you can create the option file and set up a section to specify values for the and 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
    

    If you change the 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.3.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 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 service, execute the following command as a user with administrative privileges, on the command-line:

    C:\> 
    [SC] DeleteService SUCCESS
    

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

2.3.14. Upgrading MySQL on Windows

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

  1. Review Section 2.11, “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.10.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:\> 
    

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

    C:\> 
    

    Note: If the MySQL user account has a password, you need to invoke mysqladmin with the 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:\> 
    

    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.3.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 ), or install it into a different directory, such as . 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.3.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.3.13, “Troubleshooting a MySQL Installation Under Windows”.

2.3.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 and system calls to be able to mix and . Currently, we use mutexes to emulate and . We intend to replace the file level interface with a virtual interface in the future so that we can use the / 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.

  • While you are executing an 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.

  • on a table that is in use by a table does not work on Windows because the 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 tables (with ) or drop the table before dropping the table.

  • and

    The and options for are ignored on Windows, because Windows doesn't support symbolic links. These options also are ignored on systems that have a non-functional call.

  • 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 or , use Unix-style filenames with ‘’ characters:

    mysql> 
    mysql> 
    

    Alternatively, you must double the ‘’ character:

    mysql> 
    mysql> 
    
  • Problems with pipes

    Pipes do not work reliably from the Windows command-line prompt. If the pipe includes the character / , 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:\>  | mysql --user=root
    

    If you have a problem applying the log and suspect that it is because of a / character, you can use the following workaround:

    C:\>  --result-file=/tmp/bin.sql
    C:\> 
    

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

  • 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 ''@'unknown'
    to database 'mysql'
    

    To fix this problem, you should create a file named 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.