Configuring Backward Compatibility for Password Handling | Mysql: the Definitive Guide to Using, Programming, and Administering Mysql 4.1 and 5.0

Arranging for MySQL Server Startup and Shutdown

One general goal that you have as a MySQL administrator is to make sure that the server, mysqld, is running as much of the time as possible so that your users can access it. Occasionally, however, it's necessary to stop the server. For example, if you're relocating a database, you can't have the server updating tables in that database at the same time, so you must shut it down. The tension between the desire to keep the server running and the need to stop it occasionally is something this book can't resolve for you. But we can at least discuss how to get the server started and stopped so that you have the ability to perform either operation as you see fit. Many aspects of the procedures for this are different for Unix and Windows, so the following discussion covers them separately.

Running the MySQL Server on Unix

On Unix, the MySQL server can be started either manually or automatically at system startup time. It's also possible to arrange for the server to run automatically at system boot time as part of the standard startup procedure. (This is in fact probably how you'll start the server under normal operating conditions after you get everything set up the way you want.) But before discussing how to start the server, let's consider which login account should be used to start it. On a multi-user operating system such as Unix, you have a choice about which login account to use for running the server. If you start the server manually, it runs as the Unix user you happen to be logged in as. For example, if I log in as paul and start the server, it runs as paul. If instead I use the su command to switch user to root and then start the server, it runs as root.

You should keep in mind two goals for your MySQL server startup procedures under Unix:

You want the server to run as some user other thanroot . To say the server runs "as" a given user means that the server process is associated with the user ID of that user's login account, and that it has that user's privileges for reading and writing files in the filesystem. This has certain security implications, particularly for processes that run as the root user, because root is allowed to do anything, however dangerous. (Some of the problems that can arise are described in Chapter 12.) One way to avoid these dangers is to have the server relinquish its special privileges. Processes that start as root have the capability to change their user ID to that of another account and thus give up root's privileges in exchange for those of a regular unprivileged user. This makes the process less dangerous. In general, you should limit the power of any process unless it really needs root access, and mysqld in particular does not. The server needs to access and manage the contents of the MySQL data directory, but little else. This means that if the server starts as root, you should tell it to change its user ID during startup to run as an unprivileged user. (An exception occurs on Solaris if you have trouble with the server being swapped out a lot and you want to force it to remain locked in memory by using the --memlock option. This option requires that you run the server as root.)
You want the server to run as the same user every time it executes. It's inconsistent for the server to run with one user's privileges sometimes and with another user's privileges other times. That leads to files and directories being created under the data directory with varying ownerships and results in the server not being able to access certain databases or tables, depending on who it runs as. By consistently running the server as the same user, you avoid this problem.

Running the Server Using an Unprivileged Login Account

To set up for running mysqld as an unprivileged non-root user, follow this procedure:

1.	Stop the server if it's running: % mysqladmin -p -u root shutdown
2.	Choose which login account to use for running `mysqld`. You can use any account, but it's cleaner conceptually and administratively to create a separate account that is devoted exclusively to MySQL activity. You can also designate a group name specifically for use with MySQL. I'll use `mysql` for both the user and group names. If you use different names, substitute them anywhere you see mysql used as a user or group name elsewhere in this book. For example, if you install MySQL under your own account because you have no special administrative privileges on your system, you'll probably run the server under your own user ID. In this case, substitute your own login name and group name for `mysql`.
3.	If necessary, create the login account for the name you've chosen, using your system's usual account-creation procedure. You'll need to do this as `root`. Should you elect to use the account named `mysql` for running the server, you might not need to create it yourself. If you install MySQL on Linux using an RPM file, the installation procedure creates the account automatically. Current versions of Mac OS X come with a `mysql` account already set up. Other systems might do the same.
4.	Modify the user and group ownership of the MySQL data directory and any subdirectories and files under it so that the `mysql` user owns them. For example, if the data directory is `/usr/local/mysql/data`, you can set up ownership for that directory and its contents as follows: # chown -R mysql /usr/local/mysql/data # chgrp -R mysql /usr/local/mysql/data Run those commands as `root`.
5.	It's a good security precaution to set the access mode of the data directory to keep other people out of it. To do this, modify its permissions so that it can be accessed only by the `mysql` user. If the data directory is `/usr/local/mysql/data`, you can set up everything in and under it to be accessible only to `mysql` by turning off all the "group" and "other" permissions as follows: # chmod -R go-rwx /usr/local/mysql/data

The last couple of steps actually are part of a more comprehensive lockdown procedure that is detailed in Chapter 12. Be sure to check that chapter for additional instructions on making ownership and mode assignments, particularly if your MySQL installation has a non-standard organization.

After completing the preceding procedure, you should make sure always to start the server with an option of --user=mysql so that it will switch its user ID to mysql if it's invoked by root. This is true both for when you run the server manually as root, and for setting up the server to be invoked during your system's startup procedure. Unix systems perform startup operations as the Unix root user, so any processes initiated as part of that procedure execute by default with root privileges.

The best way to ensure that the user is specified consistently is to list it in an option file. For example, put the following lines in /etc/my.cnf:

 [mysqld] user=mysql

For more information on option files, see "Specifying Server Startup Options."

If you happen to start the server while logged in as mysql, the presence of the user line in your option file will result in a warning to the effect that the option can be used only by root. This means that the server does not have the capability to change its user ID and will run as mysql. That's what you want anyway, so just ignore the warning.

Methods for Starting the Server

After deciding what login account to use for running the server, you have several choices about how to start it. It's possible to run the server manually from the command line or automatically during the system startup procedure. Methods for doing this include the following:

Invoke mysqld directly
This is probably the least common method. I won't discuss it further, except to say that mysqld --verbose --help is a useful command for finding out what startup options the server supports.
Invoke the mysqld_safe script
mysqld_safe invokes the server for you and then monitors it and restarts it if it terminates abnormally. mysqld_safe commonly is used on BSD-style versions of Unix, and it also is invoked by mysql.server on non-BSD systems and on Mac OS X.
mysqld_safe redirects error messages and other diagnostic output from the server into a file in the data directory to produce an error log. mysqld_safe sets the ownership of the error log so that it is owned by the user named by the --user option. This can lead to trouble if you to use different --user values at different times. The symptom is that mysqld_safe's attempt to write to the error log will fail with a "permission denied" error. This can be especially problematic because if you examine the error log to see what the difficulty is, it will contain no useful information related to the cause! If this problem occurs, remove the error log and invoke mysqld_safe again.
Invoke the mysql.server script
mysql.server starts the server by executing mysqld_safe. This script can be invoked with an argument of start or stop to indicate whether you want the server to start or stop. It serves as a wrapper around mysqld_safe and commonly is used on systems that employ the System V method of arranging startup and shutdown scripts into several directories. Each directory corresponds to a particular run level and contains scripts to be invoked when the machine enters or exits that run level.
To coordinate several servers, use the mysqld_multi script
This startup method is more complicated than the others, so I'll defer discussion to the section "Running Multiple Servers."

The mysqld_safe and mysqld_multi scripts are installed in the bin directory under the MySQL installation directory or can be found in the scripts directory of MySQL source distributions. The mysql.server script is installed under the share/mysql directory under the MySQL installation directory or can be found in the support-files directory of MySQL source distributions. If you want to use mysql.server, you might need to copy it to the proper run-level directory and make it executable. (Some installation methods install mysql.server for you. Linux RPM and Mac OS X DMG packages do so, for example.) If you use a MySQL RPM obtained from another vendor, a similar startup script might be installed under a different name, such as mysqld.

The arrangements that you'll need to make to have a startup script execute at system boot time depend on the type of system you have. Read through the following examples and use or adapt the instructions that most closely match the startup procedures for your system.

For BSD-style systems, it's common to have a few files in the /etc directory that initiate services at boot time. These files often have names that begin with rc, and it's likely that there will be a file named rc.local (or something similar) intended specifically for starting locally installed services. On an rc-based system, you might add lines such as the following to rc.local to start the server:

 if [ -x /usr/local/bin/mysqld_safe ]; then     /usr/local/bin/mysqld_safe & fi

Modify the lines appropriately if the pathname to mysqld_safe is different on your system.

For System V-style systems, you can install mysql.server. Copy it to the appropriate run-level directory under /etc. This may have been done for you already if you run Linux and installed MySQL from an RPM file. Otherwise, install the script in the main startup script directory with the name you want to use, make sure the script is executable, and place links to it in the appropriate run-level directory.

Note: Normally, you install mysql.server into the run-level directory under the name mysql, but I'll generally continue to discuss it as mysql.server to make it clear what I'm referring to.

The layout for run-level directories varies from system to system, so you'll need to check around to see how your system organizes them. For example, under Solaris, the general multi-user run level is 2. The main script directory is /etc/init.d, and the run-level directory is /etc/rc2.d, so the commands would look like this:

 # cp mysql.server /etc/init.d/mysql # cd /etc/init.d # chmod +x mysql # cd /etc/rc2.d # ln -s ../init.d/mysql S99mysql

At system startup time, the boot procedure automatically invokes the S99mysql script with an argument of start.

Many Linux variants have a similar set of directories, but organized under /etc/init.d and /etc/rc.d. Such Linux systems typically have a chkconfig command that is intended for startup script management. You can use it to help you install the mysql.server script instead of manually running commands like those just shown. The following instructions show how to install mysql.server into the startup directories using a name of mysql:

Copy the mysql.server script from wherever it's located into the init.d directory and make it executable:
```
 # cp mysql.server /etc/init.d/mysql # chmod +x /etc/init.d/mysql 
```
Register the script and enable it:
```
 # chkconfig --add mysql # chkconfig mysql on 
```
To verify that the script has been properly enabled, run chkconfig with the --list option:
```
 # chkconfig --list mysql mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off 
```
That output indicates that the script will execute automatically for run levels 3, 4, and 5.

If you don't have chkconfig, you can use a procedure similar to that used for Solaris, although the pathnames are slightly different. To enable the script for run level 3, use these commands:

 # cp mysql.server /etc/init.d/mysql # cd /etc/init.d # chmod +x mysql # cd /etc/rc.d/rc3.d # ln -s /etc/init.d/mysql S99mysql

Under Mac OS X, the startup procedure is different yet. The /Library/StartupItems and /System/Library/StartupItems directories contain subdirectories for the services that are initiated at system boot time. The DMG package provided at the MySQL AB Web site for Mac OS X contains an installer that places a startup item for the MySQL server into one of these directories.

Running the MySQL Server on Windows

MySQL distributions for Windows include several servers, each of which is built with different options. You can find a summary of the different servers in Appendix A. For this discussion, I'll use mysqld for examples that apply to any version of Windows on which MySQL runs, and mysqld-nt for examples that are more applicable to NT-based versions of Windows (NT, 2000, XP, and 2003).

You can start the server manually from the command line under any version of Windows. In addition, for NT-based systems, it's possible to install any MySQL server as a Windows service. You can set the service to run automatically when Windows starts and control it from the command line or by using the Windows Services Manager.

Windows servers allow two types of connections that Unix servers do not. First, you can configure a Windows server to allow client programs to connect using shared memory. Second, if you use one of the servers built specifically for NT, you can configure it so that clients can connect using named pipes.

Running the Server Manually

To start a server manually, invoke it from the command line in a console window:

 C:\> mysqld

If you want error messages to go to the console window rather than to the error log (the host_name.err file in the data directory), use the --console option:

 C:\> mysqld --console

When you run a MySQL server from the command line, you will not necessarily see another command prompt until the server exits. That's okay. It just means that you need to open another console window to use for running client programs.

If you add the --shared-memory option to the startup command, the server allows local clients to connect using shared memory. You can allow local clients to connect via named pipes if you use mysqld-nt on NT-based systems. However, named pipe support is disabled by default. To enable it, add the --enable-named-pipe option to the startup command.

Note: The reason named pipes are disabled by default is that they were found to cause problems at server shutdown time on some machines. If you use the --enable-named-pipe option, be sure to verify that your server shuts down properly.

To stop the server, use mysqladmin:

 C:\> mysqladmin -p -u root shutdown

Running the Server as a Windows Service

On NT-based versions of Windows, the MySQL server can be installed as a Windows service using this command:

 C:\> mysqld-nt --install

This command does not actually start mysqld. Instead, it causes mysqld to run automatically whenever Windows starts. If you prefer to use a service that does not run automatically, install the server as a "manual" service:

 C:\> mysqld-nt --install-manual

These examples use mysqld-nt, but you actually can install any MySQL server as a Windows service.

As a general rule, when you install a server as a service, you give no other options on the command line and list them in an option file instead. (See "Specifying Server Startup Options.") However, it is possible to specify a service name and option file as arguments, as described in the following discussion. This is especially useful when you install several Windows servers as services. For details, see "Running Multiple Servers."

When you install a MySQL server as a Windows service, the default service name is MySQL. (Service names are not case sensitive.) It's possible to specify a service name explicitly following the --install option:

 C:\> mysql-nt --install service_name

Windows services each must have a unique name, so one reason for choosing a service name other than MySQL is that it allows you to run multiple MySQL servers as services. The service name affects which option groups the server reads from option files when it starts. Each server reads the [mysqld] option group, and it may also read another group depending on the service name. The rules are as follows:

With no service_name argument or a service name of MySQL, the server uses the default service name (MySQL) and reads the [mysqld] group from option files.
With a service_name argument different from MySQL, the server uses that name as the service name and reads the [mysqld] and [service_name] groups from option files.

If you specify a service name, you can also specify a --defaults-file option as the final option on the command line when you install a server:

 C:\> mysqld-nt --install service_name --defaults-file=file_name

This gives you an alternative means of providing server-specific options. The name of the file will be remembered and used by the server whenever it starts, and it will read options from the [mysqld] group of the file. This syntax requires a service name to be specified; to use the default service name, use a service_name value of MySQL.

After the server has been installed as a service, you control it using the service name. This can be done from the command line, or from the Windows Services Manager if you prefer a graphical interface. Depending on your version of Windows, the Services Manager can be found as a Services item in the Windows Control Panel or in the Administrative Tools item in the Control Panel.

To start or stop the service from the command line, use the following commands:

 C:\> net start MySQL C:\> net stop MySQL

If you use the Services Manager, it presents a window that displays a list of the services it knows about, along with additional information such as whether each service is running and whether it is automatic or manual. To start or stop the MySQL server, select its entry in the services list and then choose the appropriate button or menu item.

You can also stop the server from the command line with mysqladmin shutdown.

Note: Although you can control services using either the Services Manager or commands at the command prompt, you should avoid interactions between the two approaches. Make sure to close the Services Manager whenever you invoke service-related commands from the prompt.

To remove the MySQL server from the list of services, start it if it is running, and then issue this command:

 C:\> mysqld-nt --remove

The command removes the MySQL service having the default service name of MySQL. To indicate explicitly which service to remove, specify its name following the --remove option:

 C:\> mysqld-nt --remove service_name

Specifying Server Startup Options

On any platform, there are two primary methods for specifying startup options when you invoke the server:

You can list them on the command line, in which case it's possible to use either the long or short forms of any option for which both forms are available. For example, you can use either --user=mysql or -u mysql.
You can list the options in an option file. For options specified this way, only the long option form can be used, and it's given without the leading dashes:
```
 [mysqld] user=mysql 
```
See "Option Files," in Appendix F for a general discussion of the format and syntax of option files.

It's generally easiest to use an option file. You can do so for any startup method, and after you put the options in the file, they take effect each time the server starts. Listing options on the command line works only when starting the server manually or by using mysqld_safe. It does not work for mysql.server, which is intended to support only start and stop options on the command line. Also, with limited exceptions, you cannot specify startup options on the command line if you use --install or --install-manual to install a Windows server as a service. (The exceptions are discussed in "Running the Server as a Windows Service.")

The usual files used for specifying server options under Unix are the /etc/my.cnf file and the my.cnf file in the data directory. Under Windows, you can use the my.ini file in the Windows directory and C:\my.cnf. If the file you want to use doesn't exist, create it as a plain text file.

Server startup options typically are placed in the [mysqld] option group. For example, to indicate that you want the server to run as mysql and to use a base directory location of /usr/local/mysql, you can put the following group of lines in the option file:

 [mysqld] user=mysql basedir=/usr/local/mysql

That is equivalent to launching the server as follows with the options on the command line:

 % mysqld --user=mysql --basedir=/usr/local/mysql

The standard list of option groups used by servers and the server startup programs is shown in the following table. The line for mysqld also applies to servers with variant names such as mysqld-nt and mysqld-max.

Program	Option Groups Used by Program
`mysqld`	`[mysqld]`, `[server]`, `[mysqld-X.Y]`
`mysqld_safe`	`[mysqld]`, `[server]`, `[mysqld_safe]`, `[safe_mysqld]`
`mysql.server`	`[mysqld]`, `[server]`, `[mysql_server]`, `[mysql.server]`
`libmysqld`	`[server]`, `[embedded]`, `[appname_server]`

The [mysqld-X.Y] notation for mysqld indicates that servers read the release series-specific group. MySQL 4.1 servers read [mysqld-4.1], MySQL 5.0 servers read [mysqld-5.0], and so on.

mysqld_safe reads the [safe_mysqld] group for compatibility reasons; mysqld_safe was known as safe_mysqld before MySQL 4.0.

mysql.server uses option files only to determine the --basedir, --datadir, and --pid-file options.

The line for libmysqld refers to the embedded server library that can be linked into programs to produce MySQL-based applications that do not require a separate standalone server. (Chapter 6, "Writing MySQL Programs Using C," describes how to write applications that use the embedded server.) The [appname_server] notation indicates the application-specific option group that is read for an application named appname that includes the embedded server. (This is only a convention. It must be enforced by the application itself.)

On Windows, if you install a MySQL server as a Windows service and do not use the default service name, that affects the option groups that the server reads. See "Running the Server as a Windows Service" for details.

When you place options in a group, choose the group that will be used in the context or contexts that you want. The [server] group can be used for options that apply to any server, whether standalone or embedded. The [mysqld] or [embedded] groups can be used for options that apply only to standalone servers or to embedded servers. Similarly, the [mysqld_safe] or [mysql.server] groups allow you to specify options that apply only to one startup script or the other.

If you launch the server by using the mysqld_safe or mysql.server script, a third way to specify options is to edit the script to pass those options directly to the server. I don't recommend this except as a last resort because it has a significant disadvantage: You must remember to redo your changes each time you install a new version of MySQL because that will wipe out your modified script with the new version.

Stopping the Server

To stop the server manually, use mysqladmin:

 % mysqladmin -p -u root shutdown

This works for both Unix and Windows. If you installed the server as a service under Windows, it's also possible to use the graphical interface offered by the Services Manager to select and stop the server, or to stop the server manually from the command line:

 C:\> net stop MySQL

If you have set up the server to start automatically when your system boots, you shouldn't need to do anything special to stop it automatically at system shutdown time. BSD Unix systems normally shut down services by sending a TERM signal to the service processes, which respond to the signal appropriately (or are killed unceremoniously if they fail to do so). mysqld responds by terminating when it receives this signal.

For System V-style Unix systems that start the server with mysql.server, the shutdown process invokes that script with an argument of stop to tell the server to shut down. You can also invoke the script yourself to stop the server manually. For example, if you've installed the mysql.server script as /etc/init.d/mysql, you can invoke it as follows (you'll need to be root to do this):

 # /etc/init.d/mysql stop

If you run the MySQL server as a Windows service, the service manager automatically tells the server to stop when Windows shuts down. If you do not run the server as a service, you should stop the server manually with mysqladmin shutdown at the command line before shutting down Windows.

Regaining Control of the Server When You Can't Connect to It

Under certain circumstances you might need to restart the server manually if you find that you no longer can connect to it. This presents something of a conundrum, because typically you stop the server by connecting to it so that you can tell it to shut down (for example, by executing a mysqladmin shutdown command). How can this situation arise?

First, the MySQL root password might have gotten set to a value that you don't know. This can happen when you change the passwordfor example, if you accidentally type an invisible control character when you enter the new password value. Or you may simply have forgotten the password.

Second, under Unix, connections to localhost by default are made through a Unix domain socket file such as /tmp/mysql.sock. If the socket file gets removed, local clients won't be able to use it to connect. This might happen if your system runs a cron job that removes temporary files in the /tmp directory now and then.

If the reason you can't connect to the server is that the Unix socket file has been removed, you can get it back simply by restarting the server. (The server will re-create the socket file when it comes back up.) The trick here is that because the socket file is gone, you can't use it to establish a connection for telling the server to stop. You must establish a TCP/IP connection instead. To do this, connect to the local server by specifying a host value of 127.0.0.1 rather than localhost:

 % mysqladmin -p -u root -h 127.0.0.1 shutdown

127.0.0.1 is an IP number (it refers to the local host's loopback interface), so it explicitly forces a TCP/IP connection to be used rather than a socket connection.

If it is the case that the Unix socket file is being removed by a cron job, the missing-socket problem will recur until you change the cron job or use a socket file located somewhere else. You can specify a different socket by naming it in a global option file. For example, if the MySQL base directory is /usr/local/mysql, you can move the socket file there by adding these lines to /etc/my.cnf:

 [mysqld] socket=/usr/local/mysql/mysql.sock [client] socket=/usr/local/mysql/mysql.sock

Restart the server after making the change so that it creates the socket file in the new location. It's necessary to specify the Unix socket file pathname both for the server and for client programs so that they all use the same file. If you set the pathname only for the server, client programs still will expect to find the file at the old location. A limitation of this method is that it works only for clients that read the option file; some third-party programs might not. If you recompile MySQL from source, you can reconfigure the distribution to use a different pathname by default both for the server and clients. This automatically affects third-party programs that use the client library, unless they have been statically linked with the old library. In that case, you must recompile them to use the new library.

If you can't connect because you can't remember or don't know the root password, you need to regain control of the server so that you can set the password again. To do this, use the following procedure:

Stop the server. Under Unix, if you can log in as root on the server host, you can terminate the server using the kill command. Find out the server's process ID by looking in the server's PID file (which is usually located in the data directory), or by using the ps command. Then try telling the server process to shut down normally by sending it a TERM signal:

 # kill -TERM PID

That way, tables and logs will be flushed properly. If the server is jammed and unresponsive to a normal termination signal, you can use kill -9 to forcibly terminate it.

 # kill -9 PID

kill -9 is a last resort because there might be unflushed modifications in memory, and you risk leaving tables in an inconsistent state.

Under Linux, ps might show several mysqld "processes." These are really threads of the same process, so you can kill any of them to kill them all.

If you start the server using mysqld_safe, it will be monitoring the server for abnormal termination. If you kill the server with kill -9, mysqld_safe will immediately restart it. To avoid this, determine the PID of the mysqld_safe process and kill it first before killing mysqld.

If you run the server as a service under Windows, you can bring it down normally without knowing any passwords by using the Services Manager or by issuing this command:

 C:\> net stop MySQL

To forcibly terminate the server on Windows, use the Task Manager (Alt-Ctrl-Del). Like kill -9 on Unix, this is a last resort.

Restart the server with the --skip-grant-tables option to disable use of the grant tables for verifying connections. That allows you to connect with no password and with all privileges. However, it also leaves your server wide open so that other people can connect the same way, so issue a FLUSH PRIVILEGES statement as soon as you connect:

 % mysql mysql> FLUSH PRIVILEGES;

The FLUSH statement tells the server to reread the grant tables, causing it to start using them again for access control. You will remain connected, but the server will require any subsequent connection attempts by other clients to be validated with the grant tables as usual. The FLUSH statement also re-enables the SET PASSWORD statement, which is disabled when the server is not using the grant tables. After reloading the tables, you can change the root password with SET PASSWORD or UPDATE, as shown in "Securing a New MySQL Installation." For example:

 mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('rootpass');

After changing the root password, shut down the server and restart it using your normal startup procedure. You now should be able to connect to it as root using the new password.

Should you be forced to terminate the server with kill -9 under Unix or with the Task Manager under Windows, the abrupt nature of the shutdown gives the server no chance to flush any unsaved changes to disk. To help deal with problems that may occur due to this kind of shutdown, it's a good idea to have the server's auto-recovery capabilities enabled. For details, see Chapter 13.