Arranging for MySQL Server Startup and Shutdown | High Performance MySQL: Optimization, Backups, Replication, and More

One general goal that you will have as a MySQL administrator is to make sure the server is running as much of the time as possible so that your users can access it. Occasionally, however, it's necessary to bring down the server. For example, if you're relocating a database, you don't want the server updating tables in that database at the same time. The tension between the desire to keep the server running and the need to shut it down 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. (In fact, this is 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 it should be run under when it does start. On a multiuser operating system such as UNIX, you have a choice about which login account to use for running the server. For example, if you start it manually, the server runs as the UNIX user you happen to be logged in as. That is, 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 than root. 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 file system. 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 during its startup procedure 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 running the server as root.)
You want the server to run as the same user all the time. It's inconsistent for the server to run as one user sometimes and as another user other times. That leads to files and directories being created under the data directory with different ownerships and can even result in the server not being able to access certain databases or tables. Consistently running the server as the same user enables you to avoid this problem.

Running the Server Using an Unprivileged Login Account

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

Shut down the server if it's running:
```
 % mysqladmin -p -u root shutdown  
```
Choose a 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 call these user and group names mysqladm and mysqlgrp. If you use different names, substitute them anywhere you see mysqladm and mysqlgrp throughout this book. For example, if you have installed MySQL under your own account and 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 mysqladm and mysqlgrp. If you installed MySQL on Linux using an RPM file, the installation procedure may have created an account automatically, using mysql for both the user and group names. Substitute that name for mysqladm and mysqlgrp.
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.
Modify the user and group ownership of the data directory and any subdirectories and files under it so that the mysqladm 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 (you'll need to run this command as root):
```
 # chown -R mysqladm.mysqlgrp /usr/local/mysql/data  
```
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 only mysqladm can use it. If the data directory is /usr/local/mysql/data, you can set up everything in and under it to be accessible only to mysqladm 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 to always start the server with an option of --user=mysqladm so that if it's invoked by root, it will switch its user ID to mysqladm. (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=mysqladm

For more information on option files, see the "Specifying Startup Options" section later in this chapter.

If you start the server while logged in as mysqladm, 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 the server does not have not have the ability to change its user ID and will run as mysqladm. That's what you want anyway, so just ignore the warning.

The --user option was added to mysqld in MySQL 3.22. If you have an older version, use the su command to tell the system to run the server under a particular account when you start it while running as root. You'll need to read your system's manual page for su because different versions of su vary in their invocation syntax.

Methods for Starting the Server

After you've decided what account to use for running the server, you have several choices about how to start it up. 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--help is a useful command for finding out what startup options the server supports.
Invoke the mysqld_safe script
mysqld_safe invokes the server and then monitors it and restarts it if it dies. mysqld_safe is commonly used on BSD-style versions of UNIX, and it is also used by mysql.server on non-BSD systems. (mysqld_safe is called safe_mysqld prior to MySQL 4, which you'll need to take into account for any instructions given in this chapter relating to mysqld_safe.)

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 with 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 up 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 up or shut down. It serves as a wrapper around mysqld_safe for use on systems that use 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 "Running Multiple Servers" section later in this chapter.

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 the MySQL source distribution. 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 the MySQL source distribution. You'll need to copy it to the proper startup directory and make it executable if you want to use it. If you install MySQL using an RPM file obtained from the MySQL Web site, the mysql.server script is installed under the name mysql; you'll find it in the /etc/rc.d/init.d directory. If you use a MySQL RPM obtained from RedHat, a similar startup script is installed under the name 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 such a system, you might add lines like the following to rc.local to start up the server:

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

Modify the lines appropriately if the pathname to your MySQL bin directory is different on your system.

For System V-style systems, you can install mysql.server. Copy it to the appropriate startup 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: I'll assume here that mysql.server gets installed into the startup 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 startup file 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 multiuser 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 will invoke the S99mysql script with an argument of start.

Linux has a similar set of directories, but they are organized under /etc/rc.d (for example, /etc/rc.d/init.d and /etc/rc.d/rc3.d). 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/rc.d/init.d/mysql  # chmod +x /etc/rc.d/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 during startup 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 the following commands:
```
 # cp mysql.server /etc/rc.d/init.d/mysql  # cd /etc/rc.d/init.d # chmod +x mysql # cd /etc/rc.d/rc3.d # ln -s ../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. You can set up MySQL using existing services as a model, or, more simply, you can just visit http://www.entropy.ch/software/macosx/mysql/ for a startup item package. After installing the package, you may need to modify its main script a bit to match the location where you have your server installed.

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, and XP).

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 server as a service. You can set the MySQL service to run automatically when Windows starts up, and control it from the command line or by using the Windows Services Manager. If you use one of the servers built specifically for NT, you can set it up so that clients can connect using named pipes.

Running the Server Manually

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

 C:\> mysqld

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

 C:\> mysqld --console

Use mysqld-nt on NT-based systems if you want to allow connections via named pipes. Named pipe support is enabled by default for mysqld-nt up through MySQL 3.23.49. This has now been changed; to take advantage of named pipe support for MySQL 3.23.50 and up, add the --enable-named-pipe option to the startup command. (It's not necessarily a good idea to do this! The reason named pipes now are disabled by default is that they were found to cause problems at server shutdown time on many machines. If you use this option, be sure to check whether or not your server shuts down properly.)

To stop the server, use mysqladmin:

 C:\> mysqladmin -p -u root shutdown

Running the Server as a Service

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

 C:\> mysqld-nt --install

This will cause the server 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 can actually install any server as a service. You might install mysqld instead if you don't care about named pipe support, for example.

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 the "Specifying Startup Options" section later in this chapter.) An exception to this rule occurs if you install several Windows servers as services. For details, see the "Running Multiple Servers" section later in this chapter.

After the server has been installed as a service, you can control it using the service name, which is MySql. This can be done from the command line or from the Windows Services Manager if you prefer a graphical interface. 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, depending on your version of Windows.

To start or stop the service from the command line, use the following commands (the service name actually can be given in any lettercase because it is not case sensitive):

 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 shut down the server from the command line with mysqladmin shutdown.

To remove the server from the list of services, shut it down if it is running, and then issue the following command:

 C:\> mysqld-nt --remove

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

Specifying 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=mysqladm or -u mysqladm.
You can list the options in an option file. When specifying an option this way, only its long option form can be used, and it's given without the leading dashes:
```
 user=mysqladm  
```

It's generally easiest to use an option file. You can do so for any startup method, and once you put the options there, they'll take effect each time the server starts. Listing options on the command line works only when starting up 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 the "Running Multiple Servers" section later in this chapter.)

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 system directory, C:\my.cnf, and the my.cnf file in the data directory. If the file you want to use it doesn't exist, create it.

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

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

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

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

The complete list of option groups used by servers and the server startup programs is shown in the following table:

Program	Option Groups Used By Program
`mysqld`	`[mysqld]`, `[server]`
`mysqld_safe`	`[mysqld]`, `[server]`, `[mysqld_safe]`, `[safe_mysqld]`
`safe_mysqld`	`[mysqld]`, `[server]`, `[safe_mysqld]`
`mysql.server`	`[mysqld]`, `[mysql_server]`
`libmysqld`	`[embedded]`, `[server]`

The line for libmysqld refers to the embedded server that can be linked into programs to produce MySQL-based applications that do not require a separate standalone server. (Chapter 6, "The MySQL C API," describes how to write applications that use the embedded server.)

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 are used only when you invoke one startup script or the other.

Prior to MySQL 4, mysqld_safe was called safe_mysqld. Instructions in this book that refer to the [mysqld_safe] option group that is used by mysqld_safe should be read as references to the [safe_mysqld] group if you're using safe_mysqld instead.

See Appendix E for more information on the format and syntax of option files.

If you launch the server by using a startup script, a third way to specify options is to modify the script to pass those options directly to the server. I don't recommend this except as a last resort. It has the significant disadvantage that you'll have to remember to redo your changes each time you install a new version of MySQL, which will wipe out your modified script with the new version.

Shutting Down the Server

To shut down 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 stop the server manually from the command line:

 C:\> net stop MySql

Or you can use the graphical interface offered by the Services Manager to select and stop the server.

If you have set the server to start up 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 processes a TERM signal. They either respond to the signal appropriately or are killed unceremoniously. 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 will invoke that script with an argument of stop to tell the server to shut down. You can also invoke the script yourself to shut down the server manually. For example, if you've installed the mysql.server script as /etc/rc.d/init.d/mysql, you can invoke it as follows (you'll need to be root to do this):

 # /etc/rc.d/init.d/mysql stop

If you run the MySQL server as a service on a Windows NT-based system, the service manager automatically will tell the server to stop at system shutdown time. Under other versions of Windows, or if you do not run the server as a service, you should bring down 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 may need to restart the server manually due to an inability to connect to it. Of course, this is somewhat paradoxical because typically when you manually shut down the server, you do so by connecting to it with mysqladmin shutdown, which tells it to terminate. How then 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 password for 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 are made through a UNIX domain socket file (for example, /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 /tmp now and then.

If the reason you can't connect is that the 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 shut down. 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 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 the following lines to /etc/my.cnf:

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

It's necessary to specify the pathname both for the server and for client programs so that they all use the same socket file. If you set the pathname only for the server, client programs will still expect to find the socket at the old location. Restart the server after making the change so that it creates the socket in the new location. Unfortunately, this method works only for clients that read the option file; many do, but some may not. If you recompile MySQL from source, you can reconfigure the distribution to use a different pathname by default for the server and clients both. This will also automatically affect third-party programs that use the client 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, perform the following procedure:

Shut down 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  
```
That is a last resort because there may be unflushed modifications, and you risk leaving tables in an inconsistent state.

Under Linux, ps may 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 and will immediately restart it after you kill 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. You will remain connected, but the server will require any subsequent connection attempts by other clients to be validated with the tables as usual. The FLUSH statement also re-enables the GRANT statement, which is disabled when the server is not using the grant tables. After reloading the tables, you can change the root password, as shown in the "Securing a New MySQL Installation" section earlier in this chapter.
After changing the root password, you may want to shut down the server again and bring it back up using your normal startup procedure.

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. It's possible that this may result in table corruption. 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.