24.2. Starting and Stopping MySQL Server on Windows

This section discusses the prerequisites that you should check before running the server on Windows. It also describes how to run the server manually from the command line or automatically as a Windows service.

24.2.1. Server Startup Prerequisites on Windows

You should know where MySQL is installed because the installation directory contains several important subdirectories. If you install MySQL by using an Essentials or Complete distribution, the installer by default uses an installation location of C:\Program Files\MySQL\MySQL Server 5.0 for MySQL 5.0. If you use a No-install archive, the installation location is wherever you happen to place the unpacked archive.

Under the installation location, you'll find a directory named bin that contains the MySQL server and client programs, and a directory named data where the server stores databases.

For MySQL installations on Windows, the data directory is preconfigured and ready to use. For example, it includes a mysql directory for the mysql database that contains the grant tables, and a test directory for a database that can be used for test purposes. After the server has been started, you should set up passwords for the initial accounts listed in the grant tables, as described in Section 35.5.1, "Securing the Initial MySQL Accounts."

Windows MySQL distributions include several servers, which you can find in the bin directory under the MySQL installation directory:

• mysqld is the standard server. It includes both the MyISAM and InnoDB storage engines.

• mysqld-nt is like mysqld, but includes support for named pipes on NT-based systems such as Windows NT, 2000, XP, and 2003.

• mysqld-max and mysql-max-nt are like mysqld and mysql-nt, but with extra features such as support for additional storage engines that are not included in the non-max servers.

• mysqld-debug contains support for debugging. Normally, you don't choose this server for production use because it has a larger runtime image and uses more memory.

Essentials distributions include only the mysqld and mysqld-nt servers.

The example commands in the following sections use mysqld for the server name. To use a different server, make the appropriate substitutions.

When you start the MySQL server on Windows, it assumes by default that the installation directory is C:\mysql. You can install MySQL elsewhere, but if you do, the server must be told what the location is. One way to do this is to set up an option file that specifies the location, using the instructions in Section 24.4, "Runtime MySQL Configuration." The Configuration Wizard included with Essentials and Complete distributions sets up an option file for you using the following procedure:

• The Wizard uses an installation directory of C:\Program Files\MySQL\MySQL Server 5.0, and it creates an option file named my.ini in that directory. The option file contains a [mysqld] group that includes a basedir setting that names the installation directory:

   [mysqld] basedir="C:/Program Files/MySQL/MySQL Server 5.0" 

  
  

  Note that backslashes in Windows pathnames are written using forward slashes in option files.

• The Wizard installs the server to run as a Windows service, using a command that includes a --defaults-file option to specify the my.ini file pathname:

   mysqld --install MySQL     --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini" 

  
  

In this way, when the MySQL service starts, the server knows where to find the option file, and that option file specifies where the installation directory is located.

If you use the Configuration Wizard to install MySQL but want to use a server different from mysqld for the MySQL service, you'll need to remove the service and install it again using a different server name. Service removal and installation is discussed in Section 24.2.3, "Running MySQL Server as a Windows Service." Substitute a different server name for mysqld in the commands shown there.

24.2.2. Running MySQL Server Manually on Windows

To run a Windows MySQL server manually from the command line of a console window, change location into the bin directory under the MySQL installation directory. Then invoke the server as follows:

 shell> mysqld 

By default, Windows servers write error messages to the file host_name.err in the data directory, where host_name is the MySQL server hostname. If the server does not start properly, check the error log in the data directory to see why. Alternatively, to display diagnostic output in the console window instead, invoke the server with the --console option:

 shell> mysqld --console 

Other server options may be specified on the command line or in option files. See Section 24.4, "Runtime MySQL Configuration."

When you invoke the server at the command prompt, the command interpreter might not display another prompt until the server exits. If that happens, open a new console window so that you can invoke other MySQL programs while the server is running.

To stop the server, use mysqladmin from the command line:

 shell> mysqladmin shutdown 

It's also possible to use the Windows Task Manager, although you should avoid that if you can; the Task Manager terminates the server forcibly without giving it a chance to perform a clean shutdown. The result might be data corruption requiring table repairs.

24.2.3. Running MySQL Server as a Windows Service

The previous section describes how to run the MySQL server by starting it manually. Another way to run MySQL is to install it as a Windows service, so that Windows itself starts and stops the MySQL server when Windows starts and stops.

If you installed MySQL using an Essentials or Complete distribution, you may have used the Configuration Wizard to install the MySQL service and start the server. Otherwise, you'll need to install the server as a Windows service yourself. To do so, change location into the bin directory under the MySQL installation directory. Then invoke the server with the --install option as follows:

 shell> mysqld --install 

If the MySQL installation directory or data directory are not at the built-in default locations assumed by the server (C:\mysql and C:\mysql\data, respectively), you'll need to specify their locations in the [mysqld] group of an option file. This can be done in one of the standard option files. (See Section 24.4, "Runtime MySQL Configuration.") It's necessary to use an option file because a MySQL server that runs as a Windows service reads options only from option files, not from the command line.

It's also possible to put options in a file of your choice and tell the server specifically to read that file. For example, to use C:\server-opts for server options, the --install command looks like this:

 shell> mysqld --install MySQL --defaults-file=C:\server-opts 

When you name a file with the --defaults-file option, the server reads options only from the [mysqld] option group of the named file when it starts, and ignores the standard option files.

The --install command does not actually start the server. It only tells Windows to handle the server as a service, so that when Windows starts up and shuts down, it starts and stops mysqld automatically. The service also can be started or stopped manually from the command line. To do so, use these commands:

 shell> net start MySQL shell> net stop MySQL 

MySQL is the service name for MySQL. It can be given in any lettercase.

To control the MySQL service using a graphical interface, use the Windows Services Manager. It displays a window that lists all known services and has controls for starting and stopping them.

You can also shut down the server manually from the command line using mysqladmin shutdown.

If the server does not start properly when run as a service, check the error log in the data directory or run the server manually with the --console option as described in Section 24.2.2, "Running MySQL Server Manually on Windows." With the --console option, error messages will appear in the console window.

To remove the MySQL service, stop the server if it's running (using any of the means just described), and then issue the following command:

 shell> mysqld --remove 

The preceding commands assume the use of the default MySQL service name of MySQL. To use a different service name, use commands that specify the name explicitly:

 shell> mysqld --install my_service shell> mysqld --install my_service --defaults-file=C:\server-opts shell> mysqld --remove my_service shell> net start my_service shell> net stop my_service 

If you install a server using a service name other than MySQL and do not specify a --defaults-file option, the server reads options in the standard option files from the [my_service] group in addition to options from the [mysqld] group.

Another way to install or remove MySQL servers as Windows services, or to start and stop MySQL services is to use MySQL Administrator. See Chapter 26, "MySQL Administrator."