DBA solves the problem of MySQL server startup and shutdown _ MySQL

As a MySQL administrator, a common goal is to ensure that the server is running as much as possible so that the client can access it at any time. However, it is best to disable the server sometimes (for example, if the database is being relocated, you do not want the server to update the table in the database ). This book does not address the need to maintain the relationship between server running and occasional shutdown. However, mysql automatically shuts down

As a MySQL administrator, a common goal is to ensure that the server is running as much as possible so that the client can access it at any time. However, it is best to disable the server sometimes (for example, if the database is being relocated, you do not want the server to update the table in the database ). This book does not address the need to maintain the relationship between server running and occasional shutdown. However, we can at least discuss how to start and stop the server so that you can perform these two operations.

This chapter only applies to UNIX systems. If you are running Windows, skip this chapter because the section "obtain and install software" in Appendix A contains all the required startup and shutdown commands.

Call the commands provided in this Chapter

For simplicity, in most cases, programs such as mysqladmin and mysqldump do not provide any-h,-u, or-p options in this chapter. I assume that you will call these programs with any options required to connect to the server.

Run the MySQL server with a non-privileged user account

Before discussing how to start the server, consider which account should be run when the server is started. The server can be started manually or automatically. If it is manually started, the server runs as a UNIX user (you just registered as this user ). That is, if the author registers with paul and starts the server, it will run as paul. If you use the s u command to switch the user to the root and then start the server, the server runs as root.

However, the server may not be started manually most of the time. You are likely to schedule the server to run automatically as part of the standard boot process during system boot. In UNIX, the startup process is executed by the system as the root user of UNIX. in this process, all processes started run with the root permission.

Note the two goals of the MySQL server startup process:

The server must be started as a non-root user. In general, unless the process really requires root access, and MySQL cannot do so, the capabilities of any process should be limited.

The server must always run as the same user. The server sometimes runs as a user and sometimes as another user. This will cause the file and directory to be created under the data with different ownership, and even cause the server to be unable to access the database or table. This problem can be avoided by running the server as the same user.

To run the database as a standard, unauthenticated user, follow these steps:

1) select the account used to run the server. Mysqld can run as any user, but obviously it only creates a separate account for the MySQL activity. You can also specify a group for MySQL. I name these users and groups called mysqladm and mysqlgrp. If you use other names, replace them with mysqladm and mysqlgrp in this book.

If you have installed MySQL under your account and the system does not have specific management permissions, you can run the server under your ID. In this case, replace mysqladm and mysqlgrp with your own registration name and group name.

If you use the RPM file to install MySQL in RedHat Linux, the installer automatically creates an account under mysql. Replace mysqladm with this name.

2) if necessary, you can use the account-creation process commonly used by the system to create a server account. This operation must be performed as root.

3) shut down the server (if it is running ).

4) modify the ownership of data directories and any subdirectories and files so that mysqladm users can have them. For example, if the data directory is/usr/local/var, you can set the ownership of the mysqladm user as follows:

# Move cd/usr/local/var to the data directory.

# Chown-r mysqladmin. mysqlgrp sets the ownership of all directories and files.

5) modify the permission of data directories and any subdirectories and files so that only mysqladm users can access them. Setting this method is a good security precaution to prevent access by others. If the data directory is/us r/l o c a l/v a r, you can use mysqladm to set everything you should do as follows (you need to run these commands as root):

# Move cd/usr/local/var to the data directory.

# Chmod-R go-rwx makes everything accessible only to mysqladm.

Observe the symbolic connection when setting the ownership and method of the data directory and its content. You need to track the symbolic connection and modify the ownership and method of the file or directory to which it points. If the directory to which these connection files are located does not belong to you, this may cause trouble. Therefore, you must be a root user.

After completing the preceding process, make sure that the server is always started, whether registered as mysqladm or as a root user. In the latter, make sure that the -- user = mysqladm option is specified so that the server can switch its user ID to mysqladm (this option can also be used during system startup ).

The -- user option is added to MySQL of MySQL3.22. If your version is earlier than MySQL3.22, you can run the su command to instruct the system to run the server under the specified account when starting the server and running it as the root user. You need to read the manual pages about su because the syntax for running commands as a specified user is changed.

How to start the server

If you have determined the account used to run the server, you can choose how to start the server. You can run the server manually from the command line or automatically during system startup. There are three main methods to start the server:

Call mysqld directly. This is perhaps the smallest command method. Except that mysqld -- help is a useful command (you can use it to find options you can use in other startup methods), I do not intend to discuss it further.

Call the safe_mysqld script. Safe_mysqld tries to determine the location of the server program and data directory, and then calls the server using the option that reflects these locations. Safe_mysqld redirects the server's standard error output to the error file in the data directory and appears as a record. After the server is started, safe_mysqld also monitors the server and restarts when it crashes. Safe_mysqld is usually used in BSD-style UNIX versions.

If you have started safe _ mysqld as root or in a system startup program, the error log will be owned by root. If you try to call safe _ mysqld as a non-privileged user, the error of "ownership denied" may occur. Delete the error file and try again.

Call the mysql. server script. Run safe _ mysqld. mysql. server to start the server. This script is recommended when System V is used to start or shut down the System. This system contains directories of scripts called when a machine logs on or exits a specific running level. It can be called using the start or stop parameters to specify whether to start or stop the server.

The safe_mysqld script is installed in the bin directory of the MySQL installation directory, or in the scripts directory of the MySQL source program distribution package. The mysql. server script is installed in the share/MySQL Directory of the mysql installation directory, or in the support-files directory of the MySQL source program distribution package. If you want to use it, copy it to the appropriate Startup directory.

For BSD-style systems, there are several files in the/etc directory that correspond to each other and they start service during boot. The names of these files usually start with 'rc ', so a file named rc. local (or similar name) is likely to start the local installation service. In such a system, you may need to add some rows to the rc as follows. to start the server in the local file (if the path is different from that in your system, you can change it to safe _ mysqld ):

if (-x /usr/local/bin/safe_mysqld);then /usr/local/bin/safe_mysqld & fi

For a System V System, you can install mysql. server by placing it in the appropriate Startup directory under/etc. If you run Linux and install MySQL from the RPM file, this operation may have been completed. Otherwise, you should install the script in the main startup script directory and set the connection to it in the appropriate running directory. You can also make the script executable only to the root user.

The layout of the startup file directory changes with the system, so a comprehensive check is required to figure out how the system organizes them. For example, in LinuxPPC, these directories are/etc/rc. d/init. d and/etc/rc. d/rc3.d. Install the script as follows:

# Cp mysql. server/etc/rc. d/init. d # cd/etc/init. d # chmod 500 mysql. server # cd/etc/rc. d/rc3.d # In-s .. /init. d/mysql. server S99mysql in Solaris, the main script directory is/etc/init. d. The run-level directory is/etc/rc2.d. Therefore, replace the preceding command with: # cp mysql. server/etc/init. d # cd/etc/init. d # chmod 500 mysql. server # cd/etc/rc2.d # In-s .. /init. d/mysql. during system startup of server s99mysql, the S99mysql script is automatically called using the start parameter.

If you have the chkconfig command (which is commonly used in Linux), you can use it to help install the mysql. server script instead of manually running the preceding command.

1. specify the startup options:

When starting a server, if you want to specify an additional startup option, you can use either of the following methods. You can modify the startup script used (safe_mysqld or mysql. server) and directly specify these options in the command line that calls the server. You can also specify options in the options file. I suggest you specify the server options in the global options file if possible. The file is usually located in/etc/my. cnf in UNIX and c: \ my. cnf in Windows.

Some types of information cannot be specified as server options. For these options, you may need to modify safe _ mysqld. For example, if the server cannot correctly pick up the local time zone and the returned time value in GMT, you can set the TZ environment variable to give a prompt for this variable. If you use safe_mysqld or mysql. server to start the server, you can add the time zone settings to safe_mysqld. Find the Command Line for starting the server, and add the following command before this line:

TZ=US/Central export TZ

This command sets TZ to the US Central time zone. Use the appropriate time zone. The syntax is Solaris, and your system may be different. For example, another common syntax for setting TZ variables is:

TZ=CST6CDT export TZ

If the startup script is modified, the modifications will be lost the next time you install MySQL (for example, upgrade to the latest version), unless you have copied the startup script elsewhere. After the new version is installed, compare your script with the newly installed script to see what changes need to be made to the new build.

2. checklist during startup:

In addition to arranging server startup during system boot, you can also install a script to run mysamchk and I s a m c h k to check the table before the server starts. You may want to restart after the server crashes, but the table may have been destroyed. Check these tables before starting the server. Chapter 13th contains details about writing and installing such scripts.

Disable the server

To manually shut down the server, use mysqladmin: % mysqladmin shutdown.

To automatically shut down the server, you do not need to perform any special operations. The BSD system usually disables the service by sending a TERM signal to the process. The process may respond to it or be canceled at will. When mysqld receives a signal, it will terminate the response. For mysql. server to start the System V-style System of the server. the shutdown process will call the script with the stop parameter to instruct the server to shut down-of course, it is assumed that you have installed mysql. server.

Reclaim server control when no connection is established

In some environments, because you cannot connect to the server, you need to manually restart it. Of course, this is a bit ridiculous, because it is usually manually disabled by connecting to the server and telling the server to terminate. So how does this happen?

First, the root password of MySQL may obtain a value that you do not know. This may occur when a password is changed-for example, if an invisible control character is entered when a new password value is entered. It is also possible that the password is completely forgotten.

Second, the localhost connection is usually performed through a socket file in the UNIX domain, which is generally/tmp/mysql. sock. If the socket file is deleted, the local client cannot connect. If the system occasionally runs a cron job to delete temporary files in/tmp, this may happen.

If you cannot connect because the socket file is lost, you can simply restart the server to recover it, because the server re-establishes the file during startup. It should be noted that you cannot establish a connection with this socket (because it does not exist) and you must establish a TCP/IP connection. For example, if the server host is pit-viper. snke.net, you can connect as follows: % mysqladmin-p-uroot-h pit-viper.snake.net shutdown.

If this socket file is deleted by a cron job, the problem will recur until you modify the cron job or use another socket file. You can use the global options file to specify another socket file. For example, if the data directory is/usr/local/var, you can move the socket file to/etc/my. cnf by adding the following lines:

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

The path name is specified by the server and the client program so that they can use the same socket file. If you only set the path name for the server, the client will still find the socket file in the old location. After this modification, restart the server to create a socket file in a new location.

If you forget the root password or change it to a value you do not know and cannot connect, you need to revoke the control of the server to reset the password:

Disable the server. If you log on to the server host as the root user, you can use the kill command to terminate the server. You can find the ID process of the server by using the ps command or by viewing the server's PID file (usually in the data directory.

It is best to first try to use the standard kill command to cancel the server. this command sends a TERM signal to the server to check whether the server responds by closing the signal. That is to say, the table and log will be properly refreshed. If the server is blocked and does not respond to a normal termination signal, use kill-9 to forcibly terminate it. This is the final method, because there may be unrefreshed changes, and the risk of retaining the table in a non-consistent state is required. If kill-9 is used to terminate the server, ensure that the myisamchk and isamchk are used to check the table before the server is restarted.

Use the -- skip-grant-tables option to restart the server. This operation tells the server not to use an authorized table to check the connection. This allows you to connect as a root user without entering a password. After the connection, modify the root password.

Tell the server to use mysqladmin flush-privileges to start with the authorization table. If your mysqladmin version does not recognize flush-privileges, try to reload it.