MySQL database start and end _ MySQL

MySQL database startup and termination because the MySQL server has a variety of installation and distribution capabilities and can run on a variety of operating platforms, it also has a variety of methods to start and stop. You can use one of them according to your actual situation. When you install, upgrade, or maintain the system, you may need to start and terminate the server multiple times. you need to understand all aspects of starting and terminating the server.

Run the daemon directly

1. methods you can use

Generally, you can have the following options to start the MySQL server. their functions and usage are almost the same, so we will introduce them here:

Directly use the MySQL daemon mysqld to start the database system, especially the distribution on the Win32 Platform. this is because mysql. server and other server scripts are not available on the Win32 Platform.

By calling the safe_mysqld script, it accepts the same parameters as mysqld, tries to determine the correct options for mysqld, and then chooses to run it with those.

2. storage location of scripts or daemon

For binary distribution installation, the mysqld daemon is installed in the bin directory of the MySQL installation directory, or can be found in the libexec directory of MySQL source code distribution, the default value is/usr/local/libexec /. For rpm distribution, mysqld should be located in the program search PATH determined by the PATH variable, so it can be directly referenced.

Safe_mysqld is still a script and only exists in distribution on Unix platforms. 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. For rpm distribution, the script should be located in the program search PATH determined by the PATH variable, so it can be directly referenced.

3. Why use the safe_mysqld script?

Safe_mysqld accepts the same parameters as mysqld, tries to determine the location of the server program and Database Directory, and then uses these locations to call the server. Safe_mysqld redirects the server's standard error output to the error file in the database directory, and the file exists 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 for root or in a system startup program, the error log will be owned by root. If safe_mysqld is called with a non-privileged user identity, the error of "Access Denied" (that is, "ownership Denied") may occur. In this case, you can delete the error file and try again.

Because of the safe_mysqld script function, using the safe_mysqld script is obviously more effective than directly starting the mysqld daemon.

4. Complete Server startup process

For distribution on Unix platforms, if you start sqfe_mysqld as root or during system boot, error logs are 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. Therefore, we recommend that you switch to a dedicated mysql User before starting the server.

The specific method is as follows:

Unix platform

$ Su mysql

$ Safe_mysql & (or mysqld &, not recommended)

Win32 Platform

C:/mysql/bin> mysqld -- standalone

Or C:/mysql/bin> mysqld-nt-standalone

If you use mysqld without installing mysql in a standard location, you usually need to provide the-basedir option for your database installation location.

$ Safe_mysqld -- basedir = "/path/to/mysql" & (Unix platform)

C:/mysql/bin> mysqld -- basedir = "x:/path/to/mysql" (Win32 Platform)

5. use the safe_mysqld script to automatically start the server

You can also use safe_mysqld and mysqld to automatically start the server with the operating system. For Linux systems and BSD-style systems (FreeBSD, OpenBSD, etc.), several files under the/etc directory are usually Initialized during boot, these files usually have names starting with "rc" and 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

As a result, the database will be started as root during boot, which may cause problems and troubles in some cases. You can specify the -- user option, so you can modify the above code:

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

/Usr/local/bin/safe_mysqld -- user = mysql -- datadir =/path/to/data &

Fi

Use the script mysql. server to start and close the database

For distribution on Unix platforms, a better way is to use the database script mysql. server to start and close the database.

1. storage location of mysql. server scripts

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. For rpm distribution, the script named mysql is located in the/etc/rc. d/init. d Directory .... There is a copy of mysql. server. The following discussions are completed in the installation process for RPM distribution. If you want to use them, you need to copy them to the appropriate directory.

2. how to use the mysql. server script to start and stop the server

The mysql. server script can be used to start or stop a server. it is called by using the start or stop parameters:

$ Mysql. server start

$ Mysql. server stop

3. Functions of mysql. server

Before mysql. server starts the server, it changes the directory to the MySQL installation directory and then calls safe_mysqld. If you have binary distribution installed in a non-standard location, you may need to edit mysql. server. Modify it and cd to the correct directory before running safe_mysqld.

4. let mysql. server start the server with a specific user

If you want to run the server as a specific user, you can change mysql_daemon_user = root to use other users. you can also modify mysql. server to pass other options to safe_mysqld.

You can also use the option file to provide parameters for the mysql. server script.
You can also add the mysql. server option in a global "/etc/my. cnf" file. A typical "/etc/my. cnf" file may look like this:

[Mysqld]
Datadir =/usr/local/mysql/var
Socket =/tmp/mysqld. sock
Port = 3306

[Mysql. server]
User = mysql
Basedir =/usr/local/mysql

The mysql. server script uses the following variables: user, datadir, basedir, bindir, and pid-file.

5. use the mysql. server script to automatically start the server.

The importance of mysql. server script is that you can use it to configure a database that is automatically started with the operating system for installation. this is a common method in the actual system.

Copy mysql. server to the/etc/rc. d/init. d Directory:

# Cd/etc/rc. d/init. d
# Cp/usr/local/mysql/support-files/mysql. server mysql

Change its attribute to "x" (executable, executable)

# Chmod + x mysql

Finally, run chkconfig to add MySQL to the startup service group of your system.

#/Sbin/chkconfig -- del mysql
#/Sbin/chkconfig -- add mysql

You can also do this by manually creating a link:

# Cd/etc/rc. d/rc3.d (depending on your running level, it can be rc5.d)
# Ln-s ../init. d/mysql S99mysql

During system startup, the S99mysql script is automatically started using the start parameter.

Use the mysqladmin utility to close and restart the database.

Routine database maintenance requires you to shut down or restart the database server. Mysql. server stop can only be used to shut down the database system. it is not convenient, and the mysql. server script starts the server as a specific user. Therefore, the superuser permission is required to use this tutorial. In contrast, mysqladmin is much more convenient and suitable for installation of all MySQL types and platforms.

Disable database server

Mysqladmin shutdown

Restart database server

Mysqladmin reload

Get help

Mysqladmin-help

The mysqladmin utility is very useful. read the help output carefully and you will get more usage.

If the following error occurs:

Mysqladmin: connect to server at 'localhost' failed
Error: 'Access denied for user: 'root @ localhost' (Using password: YES )'

Indicates that you need a user that can be connected normally. please specify the-u-p option. for example, you can:

Shell> mysqladmin-u root-p shutdown
Enter Password :***********

Enter your modified password.

Start or stop system services on the NT platform

The preceding sections describe how to enable the database server automatically on the Unix platform. to enable the MySQL database to automatically start on the windows (NT) platform, you need to install the MySQL server as a service of the NT system.

1. install MySQL as a system service

For NT, the server name is mysqld-nt.

C:/mysql/bin> mysqld-nt -- install

(You can use the mysqld or mysqld-opt server on NT, but those cannot start or use named pipes as a service .)

2. modify the option file

If your mysql instance does not have the default location c:/mysql installed, the database cannot be started because the database cannot determine the location of the Database Directory. In this case, you need to provide a global option file c:/my. cnf. Copy the my-example.cnf file from the installation directory to the c root directory. Modify or add:

[Mysqld]
Basedir = x:/path/to/mysql/

3. how to start or stop a server

Run the following command to start and stop the MySQL service:

Net start mysql
Net stop mysql

Note that in this case, you cannot use any other options for mysqld-nt! You need to use the option file to provide parameters. You can provide the appropriate parameters in the option file.

Use the option file to provide server parameters

This section describes the important options required by the Daemon.

For the global option File (windows: c:/my. cnf or unix:/etc/my. cnf), the important options include:

If you use mysql. server and specify user in [mysql. server], this option is unnecessary.

Log-update = file: updates the log name, which is important for restoring the database. the database server will generate an update log file named file. n.

-L, -- log [= file] specifies the regular log name

-- Log-bin [= file] specifies the binary log file used for Server replication.

-Sg, -- skip-grant-tables do not load the authorization table at startup, which is used for maintenance.

-U, -- user = user_name start the Unix account used by the server

-- Socket =... specifies the Unix socket file name used to run multiple servers at the same time.

-P, -- port =... specifies the port used by the server, which is used when multiple servers are running at the same time.

Summary

As the MySQL administrator, your usual criticism is to ensure that the server runs as normal as possible, so that the client can access it normally. This chapter discusses how to start, restart, and shut down the server. if you have this capability, you can better maintain the server installation. Most of the methods described in this chapter are suitable for Unix systems.