linux 下mysql的启动、调试、排错

2022-09-21 07:15:13

Linux 下 MySQL 启动与关闭说明

一.启动

1.1 MySQL 进程

可以用ps 命令查看进程：

[root@rac2 ~]# ps -ef|grep mysql

root 2161 1 0 09:38 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe--datadir=/var/lib/mysql --pid-file=/var/lib/mysql/rac2.pid

mysql 2418 2161 0 09:38 ? 00:00:00 /usr/sbin/mysqld--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin--user=mysql --log-error=/var/lib/mysql/rac2.err--pid-file=/var/lib/mysql/rac2.pid --socket=/var/lib/mysql/mysql.sock--port=3306

root 3628 3596 0 09:57 pts/1 00:00:00 grep mysql

1.1.1 mysqld — The MySQL Server

mysqld, also known as MySQL Server, is the main program that does most of the work in aMySQL installation. MySQL Server manages access to the MySQL data directorythat contains databases and tables. The data directory is also the defaultlocation for other information such as log files and status files.

When MySQLserver starts, it listens for network connections from client programs andmanages access to databases on behalf of those clients.

The mysqld programhas many options that can be specified at startup. For a complete list ofoptions, run this command:

shell> mysqld--verbose --help

MySQL Serveralso has a set of system variables that affect its operation as it runs. Systemvariables can be set at server startup, and many of them can be changed atruntime to effect dynamic server reconfiguration. MySQL Server also has a setof status variables that provide information about its operation. You canmonitor these status variables to access runtime performance characteristics.

-- mysqld 进程是MySQL Server 最核心的进程。mysqld 进程crash 或者出现异常，MySQLServer 基本上也就无法正常提供服务了。

1.1.2 mysqld_safe — MySQL Server Startup Script

mysqld_safe is the recommended way to start a mysqld server on Unix. mysqld_safe adds somesafety features such as restarting the server when an error occurs and loggingruntime information to an error log file. A description of error logging isgiven later in this section.

mysqld_safe triesto start an executable named mysqld. To override the default behavior andspecify explicitly the name of the server you want to run, specify a --mysqld or--mysqld-version option to mysqld_safe. You can also use --ledir to indicate thedirectory where mysqld_safe should look for the server.

mysqld_safe readsall options from the [mysqld], [server], and [mysqld_safe] sections in optionfiles. For example, if you specify a [mysqld] section like this, mysqld_safe willfind and use the --log-error option:

[mysqld]

log-error=error.log

For backwardcompatibility, mysqld_safe also reads [safe_mysqld] sections, although youshould rename such sections to [mysqld_safe] in MySQL 5.5 installations.

--mysqld_safe 会帮助我们来监控mysqld 进程的状态，当mysqld 进程crash 之后，mysqld_safe会马上帮助我们重启mysqld 进程。但前提是我们必须通过mysqld_safe 来启动MySQLServer，这也是MySQL AB 强烈推荐的做法。

1.1.3 mysql.server — MySQL Server Startup Script

MySQLdistributions on Unix include a script named mysql.server. It can be used onsystems such as Linux and Solaris that use System V-style run directories tostart and stop system services. It is also used by the Mac OS X Startup Itemfor MySQL.

mysql.server canbe found in the support-files directory under your MySQL installation directoryor in a MySQL source distribution.

--mysql.server 脚本放在support-files 目录下。即：/usr/share/mysql

If you use theLinux server RPM package (MySQL-server-VERSION.rpm), the mysql.server scriptwill be installed in the /etc/init.d directory with the name mysql. You neednot install it manually. See Section 2.5.1, “Installing MySQL from RPM Packages on Linux”, for more information onthe Linux RPM packages.

--如果我们使用RPM 包来安装的MySQL，那么mysql.server 脚本会自动安装到/etc/init.d目录下，并且重名为mysql。我们不需要手工的来安装

[root@rac2 mysql]# ls /etc/init.d/mysql

/etc/init.d/mysql

Some vendorsprovide RPM packages that install a startup script under a different name suchas mysqld.

If you installMySQL from a source distribution or using a binary distribution format thatdoes not install mysql.server automatically, you can install it manually.Instructions are provided in Section 2.10.1.2, “Starting and Stopping MySQLAutomatically”.

mysql.server readsoptions from the [mysql.server] and [mysqld] sections of option files. Forbackward compatibility, it also reads [mysql_server] sections, although youshould rename such sections to [mysql.server] when using MySQL 5.5.

--mysql.server 从参数文件里读取[mysql.server]和[mysqld]段的的信息。

1.1.4 mysqld_multi — Manage Multiple MySQL Servers

mysqld_multi is designedto manage several mysqld processes that listen for connections on differentUnix socket files and TCP/IP ports. It can start or stop servers, or reporttheir current status.

--如果我们的一个节点上有多个mysql，可以使用mysqld_multi 来管理。

mysqld_multi searchesfor groups named [mysqldN] in my.cnf (or in the file named by the --config-fileoption). N can be any positive integer. This number is referred to in thefollowing discussion as the option group number, or GNR. Group numbersdistinguish option groups from one another and are used as arguments to mysqld_multito specify which servers you want to start, stop, or obtain a status reportfor. Options listed in these groups are the same that you would use in the [mysqld]group used for starting mysqld. (See, for example, Section 2.10.1.2, “Startingand Stopping MySQL Automatically”.) However, when using multiple servers, it isnecessary that each one use its own value for options such as the Unix socketfile and TCP/IP port number. For more informationon which options must beunique per server in a multiple-server environment, see Section 5.6, “RunningMultiple MySQL Instances on One Machine”.

-- mysqld_multi 会在my.cnf 里搜索mysqldN的参数配置。

以上都是相关进程的大概内容，详细内容可以参考MySQL 5.5的官方文档的4.3 小节：MySQLServer and Server-Startup Programs

1.2 启动进程的参数

The MySQL servermaintains many system variables that indicate how it is configured. Each systemvariable has a default value. System variables can be set at server startupusing options on the command line or in an option file. Most of them can bechanged dynamically while the server is running by means of the SET statement,which enables you to modify operation of the server without having to stop andrestart it. You can refer to system variable values in expressions

在启动MySQL 时，我们可以手工的在命令后面指定相关的参数，如上面ps 的命令显示的一样：

/usr/sbin/mysqld --basedir=/usr--datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql--log-error=/var/lib/mysql/rac2.err --pid-file=/var/lib/mysql/rac2.pid--socket=/var/lib/mysql/mysql.sock --port=3306

这里是我们的默认的一个参数值，我们可以通过如下命令来查看MySQL的默认参数的具体值：

（1）To see the values that a serverwill use based on its compiled-in defaults and any option files that it reads,use this command:

mysqld --verbose--help

（2）To see the values that a serverwill use based on its compiled-in defaults, ignoring the settings in any optionfiles, use this command:

mysqld --no-defaults --verbose --help

如果每次启动都手工的指定参数比较麻烦，我们可以把相关的参数配置到参数文件里，在MySQL启动时会读取这些文件，参数文件的位置：

On Unix, Linux and Mac OS X, MySQL programsread startup options from the following files, in the specified order (topitems are used first).

注意上表中参数文件的顺序，MySQL 会按照从上往下的顺序优先使用上面的参数文件，这个就类似与Oracle的SPFILE,PFILE和 init 文件的读取顺序一样。

~ represents the current user's home directory (the value of $HOME).

SYSCONFDIR representsthe directory specified with the SYSCONFDIR option to CMake when MySQL wasbuilt. By default, this is the etc directory located under the compiled-ininstallation directory.

MYSQL_HOME is anenvironment variable containing the path to the directory in which theserver-specific my.cnf file resides. If MYSQL_HOME is not set and you start theserver using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME asfollows:

（1）Let BASEDIR and DATADIR representthe path names of the MySQL base directory and data directory, respectively.

（2）If there is a my.cnf file in DATADIRbut not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.

（3）Otherwise, if MYSQL_HOME is not setand there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.

In MySQL 5.5,use of DATADIR as the location for my.cnf is deprecated.

--在MySQL 5.5中，my.cnf 文件已经不放在DATADIR目录下。

Typically, DATADIRis /usr/local/mysql/data for a binary installation or /usr/local/var for asource installation. Note that this is the data directory location that wasspecified at configuration time, not the one specified with the --datadir optionwhen mysqld starts. Use of --datadir at runtime has no effect on where theserver looks for option files, because it looks for them before processing anyoptions.

我的测试版本：

mysql> selectversion(),current_user();

+------------+----------------+

| version() | current_user() |

+------------+----------------+

| 5.5.15-log |root@localhost |

+------------+----------------+

1 row in set(0.02 sec)

里并没有my.cnf 文件，只在/usr/share/mysql目录下面有几个类似的文件：

[root@rac2 mysql]# pwd

/usr/share/mysql

[root@rac2 mysql]# ls my*

my-huge.cnf my-small.cnf mysql_system_tables_data.sql

my-innodb-heavy-4G.cnf mysqld_multi.server mysql_system_tables.sql

my-large.cnf mysql-log-rotate mysql_test_data_timezone.sql

my-medium.cnf mysql.server

我们copy 一个到/etc 目录下：

[root@rac2 mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf

从上面了解到，MySQL 在启动时会先使用/etc/my.cnf的配置文件，如果没有在使用其他的文件，所以我们修改该配置文件里的相关参数即可。

二.关闭

2.1 官网对shutdown的说明

The server shutdown process takes place asfollows:

1. The shutdown processis initiated.

This can occurinitiated several ways. For example, a user with the SHUTDOWN privilege canexecute a mysqladmin shutdown command. mysqladmin can be used on any platformsupported by MySQL. Other operating system-specific shutdown initiation methodsare possible as well: The server shuts down on Unix when it receives a SIGTERM signal.A server running as a service on Windows shuts down when the services managertells it to.

2. The server creates ashutdown thread if necessary.

Depending on howshutdown was initiated, the server might create a thread to handle the shutdownprocess. If shutdown was requested by a client, a shutdown thread is created.If shutdown is the result of receiving a SIGTERM signal, the signal threadmight handle shutdown itself, or it might create a separate thread to do so. Ifthe server tries to create a shutdown thread and cannot (for example, if memoryis exhausted), it issues a diagnostic message that appears in the error log:

Error: Can't create thread to kill server

3. The server stopsaccepting new connections.

To prevent newactivity from being initiated during shutdown, the server stops accepting newclient connections by closing the handlers for the network interfaces to whichit normally listens for connections: the TCP/IP port, the Unix socket file, theWindows named pipe, and shared memory on Windows.

4. The server terminatescurrent activity.

For each threadassociated with a client connection, the server breaks the connection to theclient and marks the thread as killed. Threads die when they notice that theyare so marked. Threads for idle connections die quickly. Threads that currentlyare processing statements check their state periodically and take longer todie. For additional information about thread termination, see Section 12.7.6.4,“KILL Syntax”, in particular for the instructions about killed REPAIR TABLE or OPTIMIZETABLE operations on MyISAM tables.

For threads thathave an open transaction, the transaction is rolled back. Note that if a threadis updating a nontransactional table, an operation such as a multiple-row UPDATEor INSERT may leave the table partially updated because the operation canterminate before completion. If the server is a master replication server, ittreats threads associated with currently connected slaves like other clientthreads. That is, each one is marked as killed and exits when it next checksits state.

If the server isa slave replication server, it stops the the I/O and SQL threads, if they areactive, before marking client threads as killed. The SQL thread is permitted tofinish its current statement (to avoid causing replication problems), and thenstops. If the SQL thread was in the middle of a transaction at this point, thetransaction is rolled back.

If the slave isupdating a non-transactional table when it is forcibly killed, the slave's datamay become inconsistent with the master.

5. The server shuts downor closes storage engines.

At this stage,the server flushes the table cache and closes all open tables. Each storageengine performs any actions necessary for tables that it manages. For example, MyISAMflushes any pending index writes for a table. InnoDB flushes its buffer pool todisk (unless innodb_fast_shutdown is 2), writes the current LSN to the tablespace,and terminates its own internal threads.

6. The server exits.

三.相关示例

3.1 部署MySQL自启动

To start andstop MySQL automatically on your server, you need to add start and stopcommands to the appropriate places in your /etc/rc* files.

--如果配置MySQL的自动启用和关闭，需要讲相关命令添加到/etc/rc*目录里。

If you use theLinux server RPM package (MySQL-server-VERSION.rpm), or a native Linux packageinstallation, the mysql.server script may be installed in the /etc/init.d directorywith the name mysql. See Section 2.5.1, “Installing MySQL from RPM Packages onLinux”, for more information on the Linux RPM packages.Some vendors provide RPMpackages that install a startup script under a different name such as mysqld.

--如果是使用RPM包来安装的，已经部署到/etc/init.d/mysql了脚本，不需要其他配置。

If you installMySQL from a source distribution or using a binary distribution format thatdoes not install mysql.server automatically, you can install it manually. Thescript can be found in the support-files directory under the MySQL installationdirectory or in a MySQL source tree.

To install mysql.servermanually, copy it to the /etc/init.d directory with the name mysql, and thenmake it executable. Do this by changing location into the appropriate directorywhere mysql.server is located and executing these commands:

--如果手工的部署启动启动脚本，执行如下2个命令：

shell> cp mysql.server /etc/init.d/mysql

shell> chmod +x /etc/init.d/mysql

Note：

Older Red Hatsystems use the /etc/rc.d/init.d directory rather than /etc/init.d. Adjust thepreceding commands accordingly.

--老版本的Redhat 使用/etc/rc.d/init.d 代替/etc/init.d,所以需要创建一个link。

Alternatively,first create /etc/init.d as a symbolic link that points to /etc/rc.d/init.d:

shell> cd /etc

shell> ln -s rc.d/init.d .

After installingthe script, the commands needed to activate it to run at system startup dependon your operating system.

--脚本安装完之后，需要激活自启动。

On Linux, you can use chkconfig:

shell> chkconfig --add mysql

On some Linux systems, the followingcommand also seems to be necessary to fully enable the mysql script:

shell> chkconfig --level 345 mysql on

3.2 手工启动和关闭MySQL 示例

[root@rac2 /]#/usr/share/mysql/mysql.server stop

Shutting down MySQL. [ OK ]

[root@rac2 /]#/usr/share/mysql/mysql.server start

Starting MySQL.... [ OK ]

[root@rac2 /]# service mysql stop

Shutting down MySQL. [ OK ]

[root@rac2 /]# service mysql start

Starting MySQL...... [ OK ]

mysql.server stop stops the server by sending a signal to it. You can also stop the servermanually by executing mysqladmin shutdown.

[root@rac2 run]# mysqladmin shutdown

[root@rac2 run]# ps -ef|grep mysql

root 30997 3596 0 14:51 pts/1 00:00:00 grep mysql

Mysqladmin 不能用来启动mysql。只能用来关闭。没有对应的启动语法。这个具体可以通过如下命令查看：

[root@rac2 run]# mysqladmin --help

另一个需要注意的问题，/var/lib/mysql/mysql.sock 文件仅在mysql 进程启动时才有，一旦mysql关闭，改文件也就自动清除。

[root@rac2 run]# ls /var/lib/mysql/mysql.sock

ls: /var/lib/mysql/mysql.sock: No such fileor directory

[root@rac2 run]# service mysql start

Starting MySQL..... [ OK ]

[root@rac2 run]# ls/var/lib/mysql/mysql.sock

/var/lib/mysql/mysql.sock下 MySQL 启动与关闭说明