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:
Running the Server Using an Unprivileged Login Account
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:
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:
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
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:
# 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
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.
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:
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
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.
mysqld_safe reads the [safe_mysqld] group for compatibility reasons; mysqld_safe was known as safe_mysqld before MySQL 4.0.
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:
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.