MySQL启动停止

启动和关闭

1.启动

1.1配置文件加载路径

查看系统下都有哪些配置文件

[root@test data]# mysqld --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

/etc/my.cnf		/etc/mysql/my.cnf		/usr/local/mysql/etc/my.cnf		~/.my.cnf
该配置文件的读取顺序是是固定的，后面的参数会覆盖前面的前面的参数。

1.2指定配置文件启动

指定参数：

--defaults-file
指定只读该配置文件，不在读取其他配置文件
--defaults-extra-file
指定mysqld在读取指定的配置的配置文件后，还需要用户指定的特殊配置文件
--print-defaults
输出现在mysqld启动的配置文件参数

1.3启动方式

日常启停脚本调用流程

1.mysql.server ---> mysqld_safe ---> mysqld 
第一种：mysql.server start方式启动先调用mysqld_safe，在调用mysqld

2.mysqld_safe ---> mysqld  
第一种：mysql.service方式启动直接启动mysqld

3.mysqld
直接启动mysqld
发现最终都是调用mysqld

进程解释：
mysqld：该进程是mysql正常运转的进程，不能挂掉，一挂掉mysql无法启动连接
mysqld_safe进程：该进程是属于守护进程，当mysqld挂掉时自动拉起mysqld进程

以上三种方式，都可以单独启动MySQL服务
mysqld_safe和mysqld一般是在临时维护时使用。
另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库

启动方式：

1.mysql.server启动

准备：
将mysql启动文件拷到/etc/init.d目录下
cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
启动
/app/mysql/support-files/mysql.server start
或
[root@test init.d]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@test ~]# ps -ef|grep mysql
root      17365      1  0 00:33 ?        00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/test.pid
mysql     17617  17365  0 00:33 ?        00:00:10 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysql/data --plugin-dir=/app/mysql/lib/plugin --user=mysql --log-error=test.err --pid-file=/data/mysql/data/test.pid --socket=/tmp/mysql.sock --port=3306
root      17900  17884  0 02:40 pts/2    00:00:00 grep --color=auto mysql
检查发现/app/mysql/bin/mysqld_safe和/app/mysql/bin/mysqld都已经启动
注意/app/mysql/bin/mysqld_safe是root用户启动的，/app/mysql/bin/mysqld是mysql用户启动的
测试：
直接杀掉mysqld进程
[root@test ~]# kill -9 17617
[root@test ~]# ps -ef|grep mysql
root      17365      1  0 00:33 ?        00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/test.pid
mysql     17918  17365 29 02:42 ?        00:00:00 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysql/data --plugin-dir=/app/mysql/lib/plugin --user=mysql --log-error=test.err --pid-file=/data/mysql/data/test.pid --socket=/tmp/mysql.sock --port=3306
root      17948  17884  0 02:42 pts/2    00:00:00 grep --color=auto mysql
检查发现进程号由17617变成了17618

2.mysqld_safe启动

[root@test init.d]# mysqld_safe --defaults-file=/etc/my.cnf &
&是后台运行
[1] 17029
[root@test init.d]# 2021-05-09T16:17:10.784241Z mysqld_safe Logging to '/data/mysql/data/test.err'.
2021-05-09T16:17:10.808533Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

[root@test init.d]#  ps -ef |grep mysql
root      17029  16328  0 00:17 pts/0    00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql     17254  17029  1 00:17 pts/0    00:00:00 /app/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/app/mysql --datadir=/data/mysql/data --plugin-dir=/app/mysql/lib/plugin --user=mysql --log-error=test.err --pid-file=test.pid --socket=/tmp/mysql.sock --port=3306
root      17284  16328  0 00:17 pts/0    00:00:00 grep --color=auto mysql
检查发现/app/mysql/bin/mysqld_safe和/app/mysql/bin/mysqld都已经启动

3.mysqld启动

[root@test init.d]# mysqld --defaults-file=/etc/my.cnf &
[1] 17314
[root@test init.d]# 2021-05-09T16:20:16.639737Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-09T16:20:16.639895Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2021-05-09T16:20:16.639918Z 0 [Note] mysqld (mysqld 5.7.28-log) starting as process 17314 ...
2021-05-09T16:20:16.643867Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-05-09T16:20:16.643891Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-05-09T16:20:16.643895Z 0 [Note] InnoDB: Uses event mutexes
2021-05-09T16:20:16.643897Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2021-05-09T16:20:16.643900Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-05-09T16:20:16.643903Z 0 [Note] InnoDB: Using Linux native AIO
2021-05-09T16:20:16.644051Z 0 [Note] InnoDB: Number of pools: 1
2021-05-09T16:20:16.644104Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-05-09T16:20:16.645150Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-05-09T16:20:16.654131Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-05-09T16:20:16.657015Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-05-09T16:20:16.669699Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-05-09T16:20:16.675434Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-05-09T16:20:16.675578Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-05-09T16:20:16.686999Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-05-09T16:20:16.687490Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-05-09T16:20:16.687496Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-05-09T16:20:16.688126Z 0 [Note] InnoDB: Waiting for purge to start
2021-05-09T16:20:16.738461Z 0 [Note] InnoDB: 5.7.28 started; log sequence number 2666746
2021-05-09T16:20:16.738939Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/data/ib_buffer_pool
2021-05-09T16:20:16.738948Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-05-09T16:20:16.740644Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210510  0:20:16
2021-05-09T16:20:16.751585Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-05-09T16:20:16.751606Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-05-09T16:20:16.752434Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-09T16:20:16.752521Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-05-09T16:20:16.752671Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-05-09T16:20:16.752797Z 0 [Note] IPv6 is available.
2021-05-09T16:20:16.752804Z 0 [Note]   - '::' resolves to '::';
2021-05-09T16:20:16.752823Z 0 [Note] Server socket created on IP: '::'.
2021-05-09T16:20:16.755725Z 0 [Note] Failed to start slave threads for channel ''
2021-05-09T16:20:16.758911Z 0 [Note] Event Scheduler: Loaded 0 events
2021-05-09T16:20:16.759265Z 0 [Note] mysqld: ready for connections.
Version: '5.7.28-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

[root@test init.d]#  ps -ef |grep mysql
mysql     17314  16328  2 00:20 pts/0    00:00:00 mysqld --defaults-file=/etc/my.cnf
root      17343  16328  0 00:20 pts/0    00:00:00 grep --color=auto mysql
该方式启动会将启动的过程打印出来，启动后发现/app/mysql/bin/mysqld_safe进程为启动，关闭时也会打印出来。

1.4mysqld_multi管理启动

mysqld_multi是用于管理多实例的一个脚本，它会读取配置文件的[mysqld_multi]和[mysqldN]（N自己定义，一般写为端口号方便记忆）[mysqldN]中的内容会覆盖[mysqld]中的部分内容。

注意：mysqld_multi启动读取的/etc/my.cnf文件，mysqld_multi的配置信息要写在该配置文件中。具体配置查看安装升级中的mysqld_multi多实例

启动方式：

查看：
[root@test ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
启动：
[root@test ~]# mysqld_multi start 3307
[root@test ~]# mysqld_multi start 3308
[root@test ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is not running
[root@test ~]# ps -ef|grep mysql
root      18126      1  0 03:21 pts/2    00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --server-id=1 --user=mysql --port=3307 --datadir=/data/3307/data/ --socket=/tmp/mysql.sock3307
mysql     18415  18126  1 03:21 pts/2    00:00:01 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/3307/data --plugin-dir=/app/mysql/lib/plugin --user=mysql --server-id=1 --log-error=test.err --pid-file=test.pid --socket=/tmp/mysql.sock3307 --port=3307
root      18464      1  1 03:22 pts/2    00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --server-id=2 --user=mysql --port=3308 --datadir=/data/3308/data/ --socket=/tmp/mysql.sock3308
mysql     18753  18464 27 03:22 pts/2    00:00:01 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/3308/data --plugin-dir=/app/mysql/lib/plugin --user=mysql --server-id=2 --log-error=test.err --pid-file=test.pid --socket=/tmp/mysql.sock3308 --port=3308
root      18793  17884  0 03:22 pts/2    00:00:00 grep --color=auto mysql
停止：
[root@test ~]# mysqld_multi stop 3307
[root@test ~]# mysqld_multi stop 3308
[root@test ~]# ps -ef|grep mysql
root      18817  17884  0 03:23 pts/2    00:00:00 grep --color=auto mysql

2不同系统启动方式

2.1linux6

在Linux6下使用的是init.d方式管理服务进程

前提：cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@test ~]# /etc/init.d/mysqld start

2.2linux7

在Linux7下使用的是systemctl方式管理服务进程

Linux7下的MySQL启动文件：

cat >/etc/systemd/system/mysqld3306.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

注意：ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
ExecStart是取MySQL命令的路径，--defaults-file指定启动的配置文件

启动：

systemctl start mysqld3306.service

3.关闭

3.1数据库中执行shutdown

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
[root@test ~]# ps -ef|grep mysql
root      19491  17884  0 03:45 pts/2    00:00:00 grep --color=auto mysql

3.2使用mysqladmin

[root@test ~]# mysqladmin -uroot -p -S /tmp/mysql.sock3307 shutdown
Enter password: 
[root@test ~]# ps -ef|grep mysql
root      19833  17884  0 03:48 pts/2    00:00:00 grep --color=auto mysql

4.选择启动方式

4.1正常启动

/etc/init.d/mysqld start
或
systemctl start mysqld3306.service

4.2维护任务时

mysqld_safe --skip-grant-tables --skip-networking &
或
mysqld --defaults-file=/etc/my.cnf &

我们一般会将我们需要的参数临时加到命令行.

也会读取/etc/my.cnf的内容,但是如果冲突,命令行优先级最高