Startup and shutdown of MySQL database

MySQL database server usually refers to the mysqld, and command line MySQL is the MySQL client program, these two concepts are usually easy to confuse. Normally starting the MySQL server is starting the mysqld process, and after Mysqld is started, you can connect to the MySQL server via MySQL. This article mainly describes several ways to start MySQL server and how to shut down the MySQL server.

1, direct use mysqld start
MYSQLD is the MySQL server, you can call this command to start the MySQL server directly
Mysqld reads the contents of the [mysqld] and [Server] option groups from the configuration file, or you can follow the parameters directly at the command line.
The MYSQLD server reads the order of the configuration files for cases where multiple profiles exist with multiple values for the same parameter, whichever is the last read.
Command line follow parameter has highest precedence

#当前mysql服务器运行环境
[Email protected] ~]# cat/etc/issue
Red Hat Enterprise Linux Server release 6.4 (Santiago)

#查看mysqld启动时的缺省选项
[Email protected] ~]# mysqld--print-defaults
Mysqld would has been started with the following arguments:
--socket=/tmp/mysql3306.sock--port=3306--pid-file=/var/lib/mysql/my3306.pid--user=mysql--server-id=3306-- Federated
[[email protected] ~]# ps-ef|grep MySQL
Root 2963 2840 0 14:10 pts/0 00:00:00 grep mysql

# Author:leshami
# Blog: Http://blog.csdn.net/leshami

#查看mysqld启动配置文件的优先级
[[email protected] ~]# mysqld--verbose--help |grep-a 1 "Default Options"
Default options is read from the following files in the given order:
/ETC/MY.CNF/ETC/MYSQL/MY.CNF/USR/ETC/MY.CNF ~/.my.cnf

#查看当前的my. CNF configuration file
[Email protected] ~]# grep-v ^#/etc/my.cnf|head-n 7
[Mysqld]
Socket =/tmp/mysql3306.sock
Port = 3306
Pid-file =/var/lib/mysql/my3306.pid
user = MySQL
server-id=3306
Federated

#清空当前mysql服务器的错误日志文件
[Email protected] ~]# Cat/dev/null>/var/lib/mysql/rhel64a.ycdata.net.err
[Email protected] ~]# Mysqld &#启动mysqld服务器
[1] 3480
[Email protected] ~]# 2014-10-25 14:20:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
--explicit_defaults_for_timestamp server option (see documentation for more details).
2014-10-25 14:20:42 3480 [Note] innodb:using Atomics to ref count buffer pool pages
2014-10-25 14:20:42 3480 [Note] innodb:the InnoDB memory heap is disabled
2014-10-25 14:20:42 3480 [Note] innodb:mutexes and rw_locks use GCC atomic builtins
2014-10-25 14:20:42 3480 [Note] innodb:memory barrier is not used
2014-10-25 14:20:42 3480 [Note] innodb:compressed tables use zlib 1.2.3
2014-10-25 14:20:42 3480 [Note] innodb:using Linux native AIO
2014-10-25 14:20:42 3480 [Note] innodb:using CPU CRC32 Instructions
2014-10-25 14:20:42 3480 [Note] innodb:initializing buffer pool, size = 128.0M
2014-10-25 14:20:42 3480 [Note] innodb:completed initialization of buffer pool
2014-10-25 14:20:42 3480 [Note] innodb:highest supported file format is Barracuda.
2014-10-25 14:20:42 3480 [Note] innodb:128 rollback segment (s) is active.
2014-10-25 14:20:42 3480 [Note] innodb:waiting for purge to start
2014-10-25 14:20:42 3480 [Note] innodb:5.6.21 started; Log sequence Number 1626087
2014-10-25 14:20:42 3480 [Note] Server hostname (bind-address): ' * '; port:3306
2014-10-25 14:20:42 3480 [Note] IPv6 is available.
2014-10-25 14:20:42 3480 [note]-':: ' resolves to ':: ';
2014-10-25 14:20:42 3480 [Note] Server socket created on IP: ':: '.
2014-10-25 14:20:42 3480 [Note] Event scheduler:loaded 0 Events
2014-10-25 14:20:42 3480 [Note] mysqld:ready for connections.
Version: ' 5.6.21 ' socket: '/tmp/mysql3306.sock ' port:3306 MySQL Community Server (GPL) L

#下面可以查询到mysqld进程
[Email protected] ~]# Ps-ef|grep mysql|grep-v grep
MySQL 18240 2641 1 20:25 pts/2 00:00:00 mysqld
[Email protected] ~]# mysql-uroot-pxxx-p3306--protocol=tcp
[Email protected][(None)]> Show variables like ' version ';
+---------------+--------+
| variable_name | Value |
+---------------+--------+
| Version | 5.6.21 |
+---------------+--------+

2. mysql Secure boot (mysqld_safe)
Mysqld_safe is a shell script that calls Mysqld to start the MySQL server and listen to the server.
If the mysqld process terminates abnormally, Mysqld_safe will restart automatically mysqld
Mysql_safe reads the [mysqld],[server],[mysqld_safe] option from the configuration file, Mysql_safe also reads the [safe_mysql] option for backward compatibility.

#下面使用mysqladmin来关闭之前启动的mysql服务器
[Email protected] ~]# mysqladmin-uroot-pxxx shutdown-s/tmp/mysql3306.sock
[[email protected] ~]# ps-ef|grep MySQL
Root 4262 3418 0 14:40 pts/4 00:00:00 grep mysql

#下面使用mysqld_safe来启动实例
[Email protected] ~]# Mysqld_safe--user=mysql &
[1] 4273
[Email protected] ~]# 141025 14:40:34 mysqld_safe Logging to '/var/lib/mysql/rhel64a.ycdata.net.err '.
141025 14:40:34 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql

#查看启动后的后台进程
[[email protected] ~]# ps-ef|grep mysql|grep-v grep #mysqld的进程为4438, whose parent process is 4273, which is Mysqld_safe
Root 4273 3418 0 14:40 pts/4 00:00:00/bin/sh/usr/bin/mysqld_safe--user=mysql
MySQL 4438 4273 0 14:40 pts/4 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/rhel64a.ycdata.net.err
--pid-file=/var/lib/mysql/my3306.pid--socket=/tmp/mysql3306.sock--port=3306

[email protected] ~]# mysql-uroot-pxxx-p3306--protocol=tcp
[Email protected][(None)]> system kill-9 4438 #强制kill mysqld Process
  [Email protected][(None)]>/usr/bin/mysqld_safe:line 166:4438 killed #下面的nohup为mysql启动进程
Nohup/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql--plugin-dir=/usr/lib64/mysql/plugin
--user=mysql--log-error=/var/lib/mysql/rhel64a.ycdata.net.err--pid-file=/var/lib/mysql/my3306.pid
--socket=/tmp/mysql3306.sock--port=3306 </dev/null >>/var/lib/mysql/rhel64a.ycdata.net.err 2>&1
141025 14:42:29 mysqld_safe Number of processes running now:0
141025 14:42:29 mysqld_safe mysqld restarted #提示该进程已经自动重新启动

#校验mysqld是否已经成功重启
  [Email protected][(None)]> system Ps-ef|grep mysql|grep-v grep
Root 4273 3418 0 14:40 pts/4 00:00:00/bin/sh/usr/bin/mysqld_safe--user=mysql
Root 4517 3418 0 14:41 pts/4 00:00:00 mysql-uroot-px x-p3306--protocol=tcp
MySQL 4553 4273 0 14:42 pts/4 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/rhel64a.ycdata.net.err
--pid-file=/var/lib/mysql/my3306.pid--socket=/tmp/mysql3306.sock--port=3306

  [Email protected][(None)]> Select databases ();
ERROR (HY000): Lost connection to MySQL server during query #由于mysqld进程被杀掉, so the connection has been disconnected
  [Email protected][(None)]> Connect #重新连接
Connection id:1
Current database: * * * NONE * * *

[Email protected][(None)]> Select Database ();
+------------+
| Database () |
+------------+
| NULL |
+------------+

3, MySQL (mysql.server) Service mode start
  mysql.server is a shell script that calls Mysqld_safe, which reads the [Mysql.server] and [mysqld] options for the configuration file.
   is backwards compatible, and the [mysql_server] option is also read

   #首先使用mysqladmin关闭已存在的mysqld
   [email protected][(None )]> system mysqladmin-uroot-pxxx shutdown-s/tmp/mysql3306.sock
  warning:using a password on the CO Mmand line interface can is insecure.
  141025 14:47:18 mysqld_safe mysqld from PID File/var/lib/mysql/my3306.pid ended
   [email protected][(None   bye
  [1]+  done                     Mysqld_ Safe--user=mysql

  [[email protected] ~]# ls-hltr/etc/init.d/mysql              #/etc/ MySQL startup script has been configured in the INIT.D directory, add
  -rwxr-xr-x 1 root root 11K Sep one at the time of installation 22:18/etc/init.d/mysql & nbsp;     #如果为rpm方式安装会自动添加该启动脚本

  [[email protected] ~]#/etc/init.d/mysql--help                # Get help with MySQL startup scripts
  Usage:mysql  {start|stop|restart|reload|force-reload|status}  [ MySQL Server options]
  [[email protected] ~]#/etc/init.d/mysql start                 #启动mysqld
  starting MySQL. [  ok ]
  [[email protected] ~]#/etc/init.d/mysql stop                  #停止mysqld
   shutting down MySQL. [  ok ]

#缺省情况下mysql已经作为一个服务添加到系统目录已经配置了自启动, as follows
[Email protected] ~]# chkconfig--list |grep MySQL
MySQL 0:off 1:off 2:on 3:on 4:on 5:on 6:off

[Email protected][(None)]> system ps-ef|grep MySQL |grep-v grep
Root 5127 1 0 14:49 PTS/4 00:00:00/bin/sh/usr/bin/mysqld_safe
--datadir=/var/lib/mysql--pid-file=/var/lib/mysql/my3306.pid
MySQL 5306 5127 0 14:49 pts/4 00:00:07/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql
--plugin-dir=/usr/lib64/mysql/plugin--user=mysql--log-error=/var/lib/mysql/rhel64a.ycdata.net.err
--pid-file=/var/lib/mysql/my3306.pid--socket=/tmp/mysql3306.sock--port=3306
Root 5340 3418 0 14:49 pts/4 00:00:00 mysql-uroot-px x-p3306--protocol=tcp

#通过服务的方式启动mysql服务器, Mysql.server calls Mysqld_safe, and Mysql_safe calls Mysqld
#从上面的方式可知, Mysqld will eventually be called, regardless of the startup mode. Because MYSQLD is the MySQL server.

4. Close the MySQL server
In the previous demo, we have used the mysqladmin and service methods to close the instance. There are 2 ways to shut down the database. No more demonstrations.
#获取mysqladmin命令行的帮助信息, including the following shutdown
[Email protected] ~]# mysqladmin--help|grep shutdown |grep Server
Shutdown take server down

5. Summary
A, pay attention to understand mysqld and MySQL. Mysqld is a MySQL server or an instance on a server that provides client access. MySQL provides client access to Administrative tools.
b, you can start the MySQL server in three ways (mysqld,mysqld_safe,service MySQL start)
c, for Mysqld_safe,service MySQL start mode, always by calling Mysqld_safe to start the mysqld process, to prevent mysqld unplanned outage and provide restart services.
D, can terminate MySQL server through mysqladmin shutdown and service MySQL stop
E, for multi-instance start and close please refer to: Linux under the MySQL source installation full version of Linux 5 install MySQL 5.6 (RPM mode)
F, for MySQL installation Please refer to: MySQL multi-instance configuration (i) MySQL multi-instance configuration (ii)

Startup and shutdown of MySQL database