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
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.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”.
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.23-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.23-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.23-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.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”.
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.
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”.
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.
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 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 button, you advance to the custom installation dialog, described in Section 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 button to the right of the displayed installation path.
After choosing your installation components and installation path, click the button to advance to 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 button. To change your settings, click the button. To exit the MySQL Installation Wizard without installing MySQL, click the 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.
Once you click the 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.23, the key contains a value of
5.0.23.
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 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 section in the menu.
The following entries are created within the new menu section:
: 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.
: This is a shortcut to the MySQL Configuration Wizard. Use this shortcut to configure a newly installed server, or to reconfigure an existing server.
: 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:\, where
Program
Files\MySQL\MySQL Server
5.0Program 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:\, where
Program
Files\MySQLProgram 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 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.
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.
The MySQL Configuration Wizard helps automate the process of
configuring your server under Windows. The MySQL Configuration
Wizard creates a custom my.ini file by
asking you a series of questions and then applying your
responses to a template to generate a
my.ini 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”.
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 entry in the section of the Windows menu.
Alternatively, you can navigate to the
bin directory of your MySQL installation
and launch the MySQLInstanceConfig.exe
file directly.
If the MySQL Configuration Wizard detects an existing
my.ini file, you have the option of
either reconfiguring your existing server, or removing the
server instance by deleting the my.ini
file and stopping and removing the MySQL service.
To reconfigure an existing server, choose the
option and
click the button. Your existing
my.ini file is renamed to
my,
where timestamp.ini.baktimestamp is the date and
time at which the existing my.ini file
was created. To remove the existing server instance, choose
the option and
click the button.
If you choose the
option, you advance to a confirmation window. Click the
button. The MySQL Configuration
Wizard stops and removes the MySQL service, and then deletes
the my.ini file. The server installation
and its data folder are not removed.
If you choose the option, you advance to the dialog where you can choose the type of installation that you wish to configure.
When you start the MySQL Configuration Wizard for a new MySQL installation, or choose the option for an existing installation, you advance to the dialog.
There are two configuration types available: and . The option is intended for new users who want to get started with MySQL quickly without having to make many decisions about server configuration. The 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 should suit your needs. Choosing the option causes the MySQL Configuration Wizard to set all configuration options automatically with the exception of and .
The 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 option is recommended.
To complete the , please refer to the sections on and in Section 2.3.4.11, “The Service Options Dialog”, and Section 2.3.4.12, “The Security Options Dialog”, respectively.
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.
: 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.
: 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.
: 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.
The dialog allows you to
indicate the storage engines that you expect to use when
creating MySQL tables. The option you choose determines
whether the InnoDB storage engine is
available and what percentage of the server resources are
available to InnoDB.
: This
option enables both the InnoDB and
MyISAM storage engines and divides
resources evenly between the two. This option is
recommended for users who use both storage engines on a
regular basis.
:
This option enables both the InnoDB and
MyISAM storage engines, but dedicates
most server resources to the InnoDB
storage engine. This option is recommended for users who
use InnoDB almost exclusively and make
only minimal use of MyISAM.
: This option disables the
InnoDB storage engine completely and
dedicates all server resources to the
MyISAM storage engine. This option is
recommended for users who do not use
InnoDB.
Some users may want to locate the InnoDB
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
InnoDB 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 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.
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 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.
: 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.
: Choose this option if your server requires a large number of concurrent connections. The maximum number of connections is set at 500.
: 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.
Use the 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 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 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”.
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 dialog to change the default character set of the MySQL server.
: Choose
this option if you want to use latin1
as the default server character set.
latin1 is used for English and many
Western European languages.
: Choose this option if you
want to use utf8 as the default server
character set. This is a Unicode character set that can
store characters from many different languages.
: 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.
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
MySQL. If you do not wish to install the
service, uncheck the box next to the 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 option.
It is strongly recommended that you set a
root 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
root password, uncheck the box next to the
option.
To set the root password, enter the desired
password into both the and
boxes. If you are reconfiguring an existing server, you need
to enter the existing root password into
the box.
To prevent root logins from across the
network, check the box next to the option. This increases
the security of your root account.
To create an anonymous user account, check the box next to the option. Creating an anonymous account can decrease server security and cause login and permission difficulties. For this reason, it is not recommended.
The final dialog in the MySQL Configuration Wizard is the . To start the configuration process, click the button. To return to a previous dialog, click the button. To exit the MySQL Configuration Wizard without configuring the server, click the button.
After you click the 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
my-template.ini and is located in your
server installation directory.
The MySQL Configuration Wizard then writes these options to a
my.ini file. The final location of the
my.ini 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 root password, the
MySQL Configuration Wizard connects to the server, sets your
new root 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 button to exit the MySQL Configuration Wizard.
The MySQL Configuration Wizard places the
my.ini 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
my.ini file, an argument similar to this
is passed to the MySQL server as part of the service
installation:
--defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"
Here, C:\Program Files\MySQL\MySQL Server
5.0 is replaced with the
installation path to the MySQL Server. The
--defaults-file option instructs the MySQL
server to read the specified file for configuration options
when it starts.
To modify the my.ini 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
my.ini file located in the server
installation directory. To configure the client and utility
applications, create a new my.ini file in
the C:\WINDOWS or
C:\WINNT directory (whichever is
applicable to your Windows version).
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:
Extract the archive to the desired install directory
Create an option file
Choose a MySQL server type
Start the MySQL server
Secure the default user accounts
This process is described in the sections that follow.
To install MySQL manually, do the following:
If you are upgrading from a previous version please refer to Section 2.3.14, “Upgrading MySQL on Windows”, before beginning the upgrade process.
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.
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.3.7, “Creating an Option File”.
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.
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:
Move the entire data directory and all
of its contents from C:\Program Files\MySQL\MySQL
Server 5.0\data to
E:\mydata.
Use a --datadir option to specify the new
data directory location each time you start the server.
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 InnoDB and
BDB support. |
| mysqld | Optimized binary with InnoDB support. |
| mysqld-nt | Optimized binary for Windows NT, 2000, and XP with support for named pipes. |
| mysqld-max | Optimized binary with InnoDB and
BDB 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 mysql-max-nt
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 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.
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.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:\> "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.23' 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.10, “Post-Installation Setup and Testing”.
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
Section E.1.2, “Creating Trace Files”.
Use mysqld --verbose --help to display all the options that mysqld understands.
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 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
Next select the tab from the menu that appears, and click the button.
Under System Variables, select , and then click the button. The 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 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=
to specify the name of an option file from which the server
should read options when it starts.
file_name
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.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.
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 mysqlC:\>"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" version status procC:\>"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 ceated 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.8.4, “Connecting to the MySQL Server”.
For more information about mysqlshow, see Section 8.15, “mysqlshow — Display Database, Table, and Column Information”.
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.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 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
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.
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.
This section lists some of the steps you should take when upgrading MySQL on Windows.
Review Section 2.11, “Upgrading MySQL”, for additional information on upgrading MySQL that is not specific to Windows.
You should always back up your current MySQL installation before performing an upgrade. See Section 5.10.1, “Database Backups”.
Download the latest Windows distribution of MySQL from http://dev.mysql.com/downloads/.
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.
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.
If you are using the MySQL Installation Wizard, start the wizard as described in Section 2.3.3, “Using the MySQL Installation Wizard”.
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.
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”.)
Restart the server. For example, use NET START MySQL if you run MySQL as a service, or invoke mysqld directly otherwise.
If you encounter errors, see Section 2.3.13, “Troubleshooting a MySQL Installation Under Windows”.
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:\>mysqlbinlogC:\>binary_log_file--result-file=/tmp/bin.sqlmysql --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.