How to start and stop a MySQL Server

1. Run the MySQL server as a non-privileged user

Before discussing how to start the MySQL server, let's take a look at the user identity to run the MySQL server. The server can be started manually or automatically. If you manually start it, the server is started as the user you log on to Unix (Linux). That is, if you log on to Unix with paul and start the server, it runs with paul; if you use the su command to switch to the root, and then run the server, it runs as root. However, in most cases, you may not want to manually start the server. Most likely, you have to schedule the MySQL server to be automatically started during system boot, as part of the standard boot process, in Unix, this pilot process is run by the system's Unix user root, and any processes running in this process are run with the root permission.

You should keep in mind the two goals of the MySQL server startup process:

You want the server to run as a non-root user. Generally, you want to limit the capabilities of any running process unless you really need the root permission, which is not required by MySQL.

It is inconvenient for you to make the server run with the same user at all times, this causes the file and directory to be created under a data directory with different owners, and may cause the server to be unable to access the database or table, depending on which user you are running. You can avoid this problem by using the same user to run the server.

To run the server as a normal non-privileged user, follow these steps:

Select a user for running the server. mysqld can be run by any user. However, it is clear in concept that a separate user is created for MySQL operations. You can also select a user group for MySQL. This document uses mysqladm and mysqlgrp as user names and user group names respectively.

If you have installed MySQL under your account and do not have special management permissions on the system, you may run the server under your own user ID. In this case, replace mysqladm and mysqlgrp with your login name and user group.

If you use the rpm file to install MySQL on RedHat Linux, this installation will automatically create an account named mysql and replace mysqladm with this account.

If necessary, use the system's usual process of creating a user to create a server account, you need to use root for it.

If the server is running, stop it.

Modify the data directory and the owner of 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 owner of mysqladm as follows (you need to run these commands as root ):

# Cd/usr/local/var
# Chown-R mysqladm. mysqlgrp

Modify the permissions of Data Directories and any subdirectories and files so that they can only be accessed by mysqladm users. If the data directory is/usr/local/var, you can set anything owned by mysqladm:

# Cd/usr/local/var
# Chmod-R go-rwx

 

When setting the owner and mode of the data directory and its content, pay attention to the symbolic connection. You need to follow them and change the owner and mode of the files or directories they direct. If the directory of the connection file does not belong to you, it may be troublesome. You may need the root identity.

After completing the preceding process, make sure that the server is always started during mysqladm or root logon. In the latter, specify the -- user = mysqladm option, enables the server to switch its user ID to mysqladm (and also applies to the system startup process ).

-- The user option is introduced in MySQL 3.22. If you have an old version, you can use the su command to tell the system to run the server under a specific user during the root operation.

Ii. How to start the server

After we confirm the account used to run the server, you can choose how to schedule the server to start. You can run it manually or automatically during system boot from the command line. There are three main methods for starting a server:

Call mysqld directly.

This may be the least commonly used method. We recommend that you do not use it more. Therefore, this article will not detail it.

Call the safe_mysqld script.

Safe_mysqld tries to determine the location of the server program and data directory. Then, call the server with the option that reflects these values. Safe_mysqld migrates a standard error device from the server to an error file in the data directory, so that it has a record. After the server is started, safe_mysqld also monitors it and restarts it if it dies. Safe_mysqld is often used in BSD Unix systems.

If you start sqfe_mysqld as root or during system boot, the error log is owned by root, this may cause a "permission denied" error when you attempt to call safe_mysqld with a non-authorized user in the future. Delete the error log and try again.

Call the mysql. server script.

This script is intentionally used to start and stop the safe_mysqld.mysql.server on the System V to start the server, the system contains several script directories that are used when the machine enters or exits a given running level. It can use a start or stop parameter to indicate whether you want to start or stop the server.

The safe_mysqld script is installed in the bin directory of the MySQL installation directory, or can be found in the scripts directory of the MySQL source code distribution. The mysql. server script is installed in the share/mysqld directory under the MySQL installation directory or in the support_files directory distributed by MySQL source code. If you want to use them, you need to copy them to the appropriate directory.

For BSD-style systems (FreeBSD, OpenBSD, etc.), there are usually several files in the/etc directory that initialize the service during boot. These files usually have names starting with "rc, and it may be named "rc. local file (or something similar), specifically used to start locally installed services. On such a system, you may add rows similar to the following to the rc. local file to start the server (if the directory of safe_mysqld is different from that of your system, modify it ):

If [-x/usr/local/bin/safe_mysqld]; then/usr/local/bin/safe_mysqld & fi

For a System V System, you can install mysql. server in the appropriate startup directory under/etc. If you run Linux and install MySQL from an RPM file, you have done this for you. Otherwise, install the script in the main startup directory, and place the connection pointing to it in the appropriate running level directory. You can also enable the script to be started only by the root user.

The directory layout of startup files varies with systems, so you need to check how your system organizes them. For example, on Linux PPC, the directory is/etc/rc. d and/etc/rc. d/rc3.d. you can install the script like this:

# Cp mysql. server/etc/rc. d/init. d # cd/etc/init. d # chmod 500 mysql. server # cd/etc/rc. d/rc3.d # ln-s .. /init. d/mysql. server S99mysql

On solaris, the main Script directory is/etc/init. d, and the run-level directory is/etc/rd2.d, so the command looks like this:

# Cp mysql. server/etc/rc. d/init. d # cd/etc/init. d # chmod 500 mysql. server # cd/etc/rc2.d # ln-s .. /init. d/mysql. server S99mysql

When the system starts, the S99mysql script is automatically called with a start parameter. If you have the chkconfig command (available on Linux), you can help install the mysql. server script instead of manually running the command as above.

2.1 specify startup options

If you want to specify additional startup options when the server is started, you can use either of the following methods. You can modify the startup script (safe_mysqld or mysql. server) You are using and specify options directly on the line that calls the server, or in an option file. We recommend that you specify an option in a Global Options file, which is usually located in/etc/my. cnf (Unix) or c:/my. cnf (Windows ).

Some types of information cannot be specified with server options. You may need to modify safe_mysqld. For example, if your server cannot correctly select the local time zone and returns the time value in GMT, you can set the TZ environment variable to give it an indication. If you use safe_mysqld or MySQL. server to start the server, you can add a time zone to safe_mysqld. Find the line for starting the server and add the following command before the line:

TZ = US/centralexport TZ

The above command syntax is Solaris. For other system syntaxes may be different, please refer to the relevant manual. If you have modified your startup script, remember that the next time you install MySQL (such as upgrading to a new version), your modifications will be lost unless you have copied the startup script elsewhere. After the new version is installed, compare the scripts of the new and old versions to see what changes need to be rebuilt.

2.2 check your table at startup

In addition to arranging that your server be started during system boot, you may need to install the myisamchk and isamchk scripts to check your table before the server is started. You may restart after a crash, and the table may have been damaged. It is a good way to detect the problem before starting.

3. Stop the server

To manually start the server, use mysqladmin:

% Mysqladmin Shutdown

To automatically stop the server, you do not need to do anything special. The BSD system generally stops the service by sending a term signal to the process. They either respond to it correctly or are roughly killed. When it receives this signal, mysqld uses termination as the response. For a system V-style system that uses MySQL. server to start the server, the stop process uses a stop parameter to call the script and tell the server to terminate the process. Assume that you have installed mysql. server.

4. If you cannot connect to the server, how can you regain control over the server?

In some cases, you may manually restart the server because you cannot connect to it. Of course, this is a bit contradictory. Because you usually turn it off manually by connecting to the server, how can this happen.

First, the MySQL root password can be set to a value that you do not know. This may happen when you change the password, for example, if you accidentally enter an invisible control character when entering a new password. You may also forget the password.

Second, connecting to localhost is usually done through a Unix domain socket file, usually/tmp/mysql. sock. If the socket file is deleted, the local client cannot connect. This may occur when your system runs a cron task to delete temporary files under/tmp.

If you cannot connect because of the loss of the socket file, you can simply restart the server and recreate it. Because the server re-creates it at startup. The scam here is that you cannot establish a connection with a socket because it is gone, you must establish a TCP/IP connection, for example, if the server host is pit.snke.net, you can connect like this:

% Mysqladmin-p-u root-h pit.snke.net shutdown

If the socket file is deleted by a cron task, the problem repeats unless you modify the cron task or use one or more different Socket files, you can use the global options file to specify a different Socket. For example, if the data directory is/usr/local/var, you can add the following lines to/etc/my. in cnf, move the socket file there:

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

Specify the path name for both the server and the customer so that they all use the same socket file. If you only set the path for the server, the client program will still expect to execute the socket in the original location, restart the server after modification, and create a socket in the new location.

If you forget the root password or have set it to a different value and cannot connect, You need to regain control of the server. You can set the password again:

Server interruption

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

It is best to first try a normal kill that sends a TERM signal to the server to see if it will terminate the response normally. In this way, tables and logs are correctly cleared. If the server is blocked and does not respond to a normal termination signal, you can use kill-9 to force it to terminate. This is the final method, because there may be uncleared modifications, and you are at risk of putting the table in an inconsistent state.

If you use kill-9 to terminate the server, make sure that you use myisamchk and isamchk to check your table before starting the server.

Use the -- skip-grant-table option to restart the server.

This tells the server not to use the authorization table to verify the connection, which allows you to connect to the server as root without a password. After you have connected, change the root password.

Use mysqladmin flush-privileges to tell the server to start with the authorization table again

If you do not know flash-privileges in mysqladmin, try reload.

5. run multiple servers

Most of them run a single MySQL server on a given machine, but in many cases, it is very useful to run multiple servers:

You may want to test a new version of a server and keep the production server you are running. In this case, you will run different server code.

The operating system generally limits the number of opened file handles for each process. If your system is difficult to raise this limit, running multiple servers is a way to solve the limit. In this case, you may run multiple instances of the unified server.

ISP often provides its customers with their own MySQL installation, and it is necessary to involve a separate server. In this case, you may run multiple instances of the same version or different versions, if different customers want different versions of MySQL.

Naturally, running multiple servers is much more complex than running only one server. If you install multiple versions, you cannot install everything in the same place. When the server is running, some parameters must or may be unique to each server, they include the Server installation path, the path name of its data directory, the TCP/IP Port and the path name of the UNIX domain socket, and the UNIX account used to run the server (if you no longer run all servers under the same account). If you decide to run multiple servers, you must pay attention to the parameters you use, so that you will not lose traces of what happened.

5.1 configure and Install multiple servers

If you want to run servers of different versions instead of multiple instances of the same version, you must install them in different locations. If you install binary distribution (without RPM), they will be installed under directories containing different versions. If you install from the source code, the easiest way is to use the -- with-prefix option to separate different distributions during the configure configuration MySQL installation process in each version, this will make everything installed in a separate directory. You can associate the distribution version number of the Directory domain. For example, you can configure a MySQL distribution, where the version is the MySQL version number:

%. Configure -- with-prefix =/usr/local/mysql-version

The -- with-prefix option also determines a unique data directory on the server. You may want to add other server-specific options, such as the TCP/IP port Number and socket path name (-- with-TCP-port and -- with-unix-socket ).

If you want to run multiple instances of the same version of server, any option that must be set based on one server will need to be specified at runtime.

Startup of more than 5.2 servers

Starting multiple servers is more complex than using one server. Because both safe_mysqld and mysql. server work best on a single server. It is recommended that you carefully study safe_mysqld and use it as the basis for your startup process. With your modified version, you can crop it more accurately based on your own needs.

One problem you have to deal with is how to specify options in the option file (my. cnf. For multiple servers, you cannot use/etc/my. cnf for different server settings. You can only use this file for the same settings of all servers. If the server has a different location of the compiled data directory, you can find my. in cnf, specify the settings to be used by all servers, and use DATADIR/my. cnf specifies the specific settings of the server. DATADIR varies with the server.

Another way to specify server options is to use -- default-file = path_name as the first option of the command line, telling the server to read the option from the file named path_name, in this way, you can place a server option in a file that is unique to the server, and then tell the server to read the file at startup. Note: If you specify this option, you will not use any of the common option files such as/etc/my. cnf.