MySQL最全安装教程[2021-06-10]

MySQL安装

一、MySQL5.7安装

MySQL官网下载地址：https://dev.mysql.com/downloads/mysql/

先以LinuxMySQL5.7版本安装为例。

官网安装步骤如下：https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

附注：linux基本环境安装

yum install gcc gcc-c++ autoconf automake make libtool bzr git net-tools vim -y

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql --datadir=/mdata/mysql_test_data  ## 可以声明在这，若不声明则使用my.cnf中的默认配置。注意：初始化时要保证该路径存在，且没有文件，否则报错。
shell> bin/mysql_ssl_rsa_setup        ## 生成秘钥
shell> bin/mysqld_safe --user=mysql &
# Next command is optional  将mysql服务启动文件存放到/etc/init.d/目录下
shell> cp support-files/mysql.server /etc/init.d/mysql.server

• step1：创建用户组

[root@k8s001 ~]# groupadd mysql
[root@k8s001 ~]# useradd -r -g mysql -s /bin/false mysql

• step2：下载好安装包。如下:（5.6、5.7、8.0三个版本这里都下载了）

[root@k8s001 ~]# pwd
/root
[root@k8s001 ~]# ll
总用量 1406488
-rw-------. 1 root root      1271 5月  31 09:44 anaconda-ks.cfg
-rw-r--r--. 1 root root 307507810 6月   6 08:14 mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 root root 641798603 6月   6 08:14 mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
-rw-r--r--. 1 root root 490922012 6月   6 08:14 mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

• step3：解压安装包到/usr/local目录下。

[root@k8s001 ~]# cd /usr/local/
[root@k8s001 local]# tar -xvf /root/mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz

• step4：创建软链（类似于用mysql-5.7.21-linux-glibc2.12-x86_64文件夹创建一个快捷方式）

[root@k8s001 local]# ln -s mysql-5.7.21-linux-glibc2.12-x86_64 mysql
[root@k8s001 local]# ll
总用量 0
drwxr-xr-x.  2 root root   6 4月  11 2018 bin
drwxr-xr-x.  2 root root   6 4月  11 2018 etc
drwxr-xr-x.  2 root root   6 4月  11 2018 games
drwxr-xr-x.  2 root root   6 4月  11 2018 include
drwxr-xr-x.  2 root root   6 4月  11 2018 lib
drwxr-xr-x.  2 root root   6 4月  11 2018 lib64
drwxr-xr-x.  2 root root   6 4月  11 2018 libexec
lrwxrwxrwx.  1 root root  35 6月   6 22:02 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x. 13 root root 213 6月   6 21:58 mysql-5.6.20-linux-glibc2.5-x86_64
drwxr-xr-x. 10 root root 148 6月   6 21:32 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root 129 6月   6 22:02 mysql-8.0.20-linux-glibc2.12-x86_64
drwxr-xr-x.  2 root root   6 4月  11 2018 sbin
drwxr-xr-x.  5 root root  49 5月  31 09:34 share
drwxr-xr-x.  2 root root   6 4月  11 2018 src

• step5：创建文件夹mysql-files

[root@k8s001 ~]# cd mysql
[root@k8s001 ~]# mkdir mysql-files
[root@k8s001 ~]# chown mysql:mysql mysql-files
[root@k8s001 ~]# chmod 750 mysql-files

• step6：修改配置文件my.cnf（配置文件如果存在，先备份，然后删除，重新创建文件my.cnf）

[root@k8s001 mysql]# vi /etc/my.cnf

文件内容如下：（或者根据自己需要配置如下内容）

附注：这个文件很重要！！！

[mysqld]
port=3306
user=mysql
## 提前创建好文件目录
datadir=/mdata/data57
log_error=error.log

• step7：初始化安装

[root@k8s001 ~]# cd /usr/local/mysql
[root@k8s001 mysql]# bin/mysqld --initialize --user=mysql
[root@k8s001 mysql]# bin/mysql_ssl_rsa_setup
[root@k8s001 mysql]# bin/mysqld_safe --user=mysql &
[1] 38167
[root@k8s001 mysql]# 2021-06-07T04:10:09.076603Z mysqld_safe Logging to '/mdata/mysql_test_data/error.log'.
2021-06-07T04:10:09.105330Z mysqld_safe Starting mysqld daemon with databases from /mdata/mysql_test_data
# 按下enter键
[1]+  退出 1                bin/mysqld_safe --user=mysql

• step8：查找初始登录密码（如下，初始密码为：6jqSs6iH2v>D）

[root@k8s001 mysql]# cat /mdata/mysql_test_data/error.log 
2021-06-07T01:35:22.568697Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-07T01:35:22.815399Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-06-07T01:35:22.867947Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-06-07T01:35:22.933465Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a0755815-c730-11eb-b0fe-000c2955194b.
2021-06-07T01:35:22.934546Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-06-07T01:35:22.935335Z 1 [Note] A temporary password is generated for root@localhost: 6jqSs6iH2v>D

• step9：二进制mysql和mysqld文件

配置环境变量或者直接拷贝二进制文件，使我们可以使用这些命令

配置环境变量

vim /etc/profile
## 在文件最后添加
export PATH=$PATH:/usr/local/mysql/bin
## :wq 保存退出

## 让配置生效
source /etc/profile

或者直接拷贝二进制文件

 ## mysqld 服务器端命令
[root@k8s001 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server
 ##mysql 客户端命令
[root@k8s001 ~]# cp /usr/local/mysql/bin/mysql /usr/bin/

• step10：服务端启动，并设置开启启动

 ## 服务端启动
[root@k8s001 ~]# /etc/init.d/mysql.server start
Starting MySQL SUCCESS! 
 ## 设置开机启动
[root@k8s001 ~]# chmod 755 /etc/init.d/mysql.server
[root@k8s001 ~]# chkconfig --add mysql.server
[root@k8s001 ~]# chkconfig --level 345 mysql.server on
## 查看开机启动项
[root@k8s001 ~]# chkconfig --list

• 客户端登录，并修改登录密码

[root@k8s001 mysql]# mysql -uroot -p
Enter password:6jqSs6iH2v>D   ## 初始化密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.21 MySQL Community Server (GPL)    ## mysql版本 5.7.21登录成功

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password='123456';
Query OK, 0 rows affected (0.00 sec)

• mysql 5.7 安装完成。

二、MySQL5.6安装

mysql 5.6 与 mysql 5.7 安装过程大致一样。不同的地方有三处：

1.初始化脚本不一样。（scripts/mysql_install_db --user=mysql）
2.第一次登录没有初始密码。
3.mysql5.6有test数据库。

官网安装步骤如下：https://dev.mysql.com/doc/refman/5.6/en/binary-installation.html

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql:mysql .
shell> scripts/mysql_install_db --user=mysql --datadir=/mdata/data56
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

在已经安装好MySQL 5.7版本的情况下，安装MySQL5.6版本，具体步骤如下（安装包已经解压到/usr/local/）：

• step1：重新创建文件软链（原先mysql指向MySQl5.7，修改为指向MySQL5.6），如下：

[root@k8s001 local]# cd /usr/local
[root@k8s001 local]# unlink mysql
[root@k8s001 local]# ln -s mysql-5.6.20-linux-glibc2.5-x86_64 mysql
[root@k8s001 local]# ll
总用量 0
drwxr-xr-x.  2 root root   6 4月  11 2018 bin
drwxr-xr-x.  2 root root   6 4月  11 2018 etc
drwxr-xr-x.  2 root root   6 4月  11 2018 games
drwxr-xr-x.  2 root root   6 4月  11 2018 include
drwxr-xr-x.  2 root root   6 4月  11 2018 lib
drwxr-xr-x.  2 root root   6 4月  11 2018 lib64
drwxr-xr-x.  2 root root   6 4月  11 2018 libexec
lrwxrwxrwx.  1 root root  34 6月   6 22:41 mysql -> mysql-5.6.20-linux-glibc2.5-x86_64
drwxr-xr-x. 13 root root 213 6月   6 21:58 mysql-5.6.20-linux-glibc2.5-x86_64
drwxr-xr-x. 10 root root 148 6月   6 21:32 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root 129 6月   6 22:02 mysql-8.0.20-linux-glibc2.12-x86_64
drwxr-xr-x.  2 root root   6 4月  11 2018 sbin
drwxr-xr-x.  5 root root  49 5月  31 09:34 share
drwxr-xr-x.  2 root root   6 4月  11 2018 src

• step2：脚本初始化MySQL5.6（已经添加mysql用户组）

[root@k8s001 local]# groupadd mysql
groupadd：“mysql”组已存在
[root@k8s001 local]# useradd -r -g mysql -s /bin/false mysql
useradd：用户“mysql”已存在
[root@k8s001 local]# cd mysql
## 如果开启了MySQL5.7 则先关闭Mysql5.7
[root@k8s001 mysql]# /etc/init.d/mysqld stop
 ## 先安装autoconf库 
[root@k8s001 mysql]# yum -y install autoconf
 ## 初始化脚本
[root@k8s001 mysql]# scripts/mysql_install_db --user=mysql
Installing MySQL system tables...2021-06-06 22:49:01 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK
Filling help tables...2021-06-06 22:49:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK
...
 ## 初始化
[root@k8s001 mysql]# bin/mysqld_safe --user=mysql &
[1] 29420
[root@k8s001 mysql]# 210606 22:55:58 mysqld_safe Logging to '/mdata/mysql_test_data/error.log'.
210606 22:55:58 mysqld_safe Starting mysqld daemon with databases from /mdata/mysql_test_data
210606 22:55:59 mysqld_safe mysqld from pid file /mdata/mysql_test_data/k8s001.pid ended
## 按下enter键
[1]+  完成                  bin/mysqld_safe --user=mysql

• step3：二进制mysql和mysqld文件（同MySQL5.7安装）
• step4：服务端启动，并设置开启启动（同MySQL5.7安装）

## 如果启动时出先以下错误
[root@k8s001 ~]# /etc/init.d/mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/mdata/mysql_test_data/k8s001.pid).
## 则新建文件/mdata/mysql_test_data/k8s001.pid
[root@k8s001 ~]# touch /mdata/mysql_test_data/k8s001.pid
[root@k8s001 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!

• step5：客户端登录，并修改登录密码（第一次登录不需要密码）
• mysql 5.6 安装完成。

三、MySQL8.0安装

MySQL8.0是一次大的升级。它在竭尽全力摒弃除innodb存储引擎的其它存储引擎。比如已经彻底废弃了MyIASM存储引擎。

MySQL8.0的安装和MySQL5.7的安装完全一致，它也完全兼容MySQL5.7。

官网安装步骤如下：https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar xvf /path/to/mysql-VERSION-OS.tar.xz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql  --datadir=/mdata/data80
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

四、MySQL升级

以更新MySQL5.6到MySQL5.7为例。

官网升级安装步骤：https://dev.mysql.com/doc/refman/5.6/en/upgrading.html

大致可以可分两步：

step1：更新升级服务器端。

step2：更新mysql数据库（最好先备份原来的数据库文件）

五、多实例安装

演示额外安装四个实例：两个mysql5.7、1个mysql5.6、1个mysql8.0。（这里称多实例1，多实例2，多实例56、多实例80）

特点：

• 一台服务器上可以安装多个MySQL实例（云厂商都是这么做的）
• 充分利用硬件资源
• 通过mysqld_multi程序即可

## mysqld1、mysqld2、mysqld56、mysqld80四个实例 配置在/etc/my.cnf（mysqld也是一个实例，所以安装完成后一共有五个数据库实例）
##查看
[root@k8s001 local]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld56 is not running
MySQL server from group: mysqld80 is not running
##启动一个实例 $num即实例号，即mysqld后面的数字（即1、2、56、80）
[root@k8s001 local]# mysqld_multi satrt $num
##停止一个实例 $num即实例号
[root@k8s001 local]# mysqld_multi stop  $num

• 准备工作1：将各个解压包按照mysql5.7安装步骤解压好（做好软链），并确保已经有mysql用户组。

[root@k8s001 local]# pwd
/usr/local
[root@k8s001 local]# ll
总用量 0
drwxr-xr-x.  2 root root    6 4月  11 2018 bin
drwxr-xr-x.  2 root root    6 4月  11 2018 etc
drwxr-xr-x.  2 root root    6 4月  11 2018 games
drwxr-xr-x.  2 root root    6 4月  11 2018 include
drwxr-xr-x.  2 root root    6 4月  11 2018 lib
drwxr-xr-x.  2 root root    6 4月  11 2018 lib64
drwxr-xr-x.  2 root root    6 4月  11 2018 libexec
lrwxrwxrwx.  1 root root   34 6月   9 23:01 mysql -> mysql-5.7.16-linux-glibc2.5-x86_64
lrwxrwxrwx.  1 root root   34 6月   9 23:19 mysql56 -> mysql-5.6.28-linux-glibc2.5-x86_64
drwxr-xr-x. 13 root mysql 245 6月   9 23:27 mysql-5.6.28-linux-glibc2.5-x86_64
drwxr-xr-x. 10 root root  172 6月   9 23:22 mysql-5.7.16-linux-glibc2.5-x86_64
lrwxrwxrwx.  1 root root   38 6月   9 23:19 mysql80 -> mysql-8.0.0-dmr-linux-glibc2.12-x86_64
drwxr-xr-x. 10 root root  148 6月  10 01:35 mysql-8.0.0-dmr-linux-glibc2.12-x86_64
drwxr-xr-x.  2 root root    6 4月  11 2018 sbin
drwxr-xr-x.  5 root root   49 5月  31 09:34 share
drwxr-xr-x.  2 root root    6 4月  11 2018 src
[root@k8s001 local]# groupadd mysql
groupadd：“mysql”组已存在
[root@k8s001 local]# useradd -r -g mysql -s /bin/false mysql
useradd：用户“mysql”已存在

• 准备工作2：配置好/etc/my.cnf文件（极其重要）

配置文件内容如下：

[client]
user=root    
password=Cmss@2021         ##免密登录设置
port=3306
socket=/tmp/mysql.sock 

##表示登录mysql后的设置
[mysql]
prompt=(\\u@\\h)[\\d]>\\_   ##显示登录用户名+ip+库

##mysql服务端设置（mysqld开头的都是服务端设置）
[mysqld]
server-id=1      
port=3306
user=mysql
datadir=/mdata/mysql_test_data
log_error=error.log
plugin-load=validate_password.so
#default_password_lifetime=0
#skip-grant-tables

## 多实例配置
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe    #用来启动mysqld
mysqladmin=/usr/local/mysql/bin/mysqladmin #用来关闭mysqld，它需要用户名和密码。如不配置，则默认使用[client]的用户名和密码。如配置，请注意注意密码配置为pass（踩坑）
log=/usr/local/mysql/mysqld_multi.log
#user=root       #mysqladmin
#pass=Cmss@2021  #注意密码配置

##单机多实例1（版本5.7）
[mysqld1]
server-id=11
innodb_buffer_pool_size=32M
port=3307
datadir=/mdata/data1
socket=/tmp/mysql.sock1

##单机多实例2（版本5.7）
[mysqld2]
server-id=12
innodb_buffer_pool_size=32M
port=3308
datadir=/mdata/data2
socket=/tmp/mysql.sock2

##单机多实例3（版本5.6）
[mysqld56]
server-id=56
innodb_buffer_pool_size=32M
port=3356
basedir=/usr/local/mysql56    #声明mysql56安装包目录
datadir=/mdata/data56
socket=/tmp/mysql.sock3

##单机多实例4（版本8.0）
[mysqld80]
server-id=80
innodb_buffer_pool_size=32M
port=3380
basedir=/usr/local/mysql80
datadir=/mdata/data80
socket=/tmp/mysql.sock4

1.单机多实例1和2（版本5.7）

• step1：初始化目录，启动数据库多实例1和多实例2

## 初始化目录/mdata/data1
[root@k8s001 mysql]# bin/mysqld --initialize --user=mysql --datadir=/mdata/data1
## 启动多实例1
[root@k8s001 mysql]# mysqld_multi start 1
[root@k8s001 mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld56 is not running
MySQL server from group: mysqld80 is not running

## 初始化目录/mdata/data2
[root@k8s001 mysql]# bin/mysqld --initialize --user=mysql --datadir=/mdata/data2
## 启动多实例2
[root@k8s001 mysql]# mysqld_multi start 2
[root@k8s001 mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld56 is not running
MySQL server from group: mysqld80 is not running

• step2：安装成功，登录验证。

[root@k8s001 mysql]# mysql -uroot -p -S /tmp/mysql.sock1
Enter password: ## 密码在/mdata/data1/error.log日志中
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.16

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost)[(none)]> set password='Cmss@2021';   ##修改登录密码
Query OK, 0 rows affected (0.00 sec)

上面演示的是登录多实例1，多实例2登录一样。

2.单机多实例3（版本5.6）

• step1：初始化目录，启动数据库多实例56

## 初始化目录
[root@k8s001 local]# cd /usr/local/mysql56/
[root@k8s001 mysql56]# chown -R mysql:mysql .
[root@k8s001 mysql56]# scripts/mysql_install_db --user=mysql --datadir=/mdata/data56
[root@k8s001 mysql56]# chown -R root .
[root@k8s001 mysql56]# chown -R mysql data
## 启动多实例56
[root@k8s001 local]# mysqld_multi start 56
[root@k8s001 local]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld56 is running
MySQL server from group: mysqld80 is not running

• step2：安装成功，登录验证。

[root@k8s001 mysql80]# mysql -uroot -p -S /tmp/mysql.sock3
Enter password: ## mysql5.6版本不用输入密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.28 MySQL Community Server (GPL)     ## 版本5.6登录成功标志

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost)[(none)]>

3.单机多实例4（版本8.0）

• step1：初始化目录，启动数据库多实例80

## 初始化目录
[root@k8s001 mysql80]# cd /usr/local/mysql80/
[root@k8s001 mysql80]# mkdir mysql-files
[root@k8s001 mysql80]# chown mysql:mysql mysql-files
[root@k8s001 mysql80]# chmod 750 mysql-files
[root@k8s001 mysql80]# bin/mysqld --initialize --user=mysql  --datadir=/mdata/data80
## 启动多实例56
[root@k8s001 local]# mysqld_multi start 80
[root@k8s001 local]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld56 is running
MySQL server from group: mysqld80 is running

• step2：安装成功，登录验证。

[root@k8s001 mysql80]# mysql -uroot -p -S /tmp/mysql.sock4
Enter password: ## 密码在/mdata/data80/error.log日志中
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.0-dmr                ## 版本8.0登录成功标志

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost)[(none)]> set password='Cmss@2021';   ##修改登录密码

4.验证所有数据库实例

如下，一共启动成功了5个mysql实例。1个默认实例+4个多实例。它们都有两个进程：mysqld_safe和mysqld。

[root@k8s001 mysql80]# ps -ef|grep mysqld
root      31343  32498  0 02:30 pts/5    00:00:00 grep --color=auto mysqld
root      33821      1  0 00:35 ?        00:00:00 /bin/sh bin/mysqld_safe --user=mysql
## 默认mysql实例
root      33821      1  0 00:35 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mdata/mysql_test_data --pid-file=/mdata/mysql_test_data/k8s001.pid
mysql     34006  33821  0 00:35 ?        00:00:05 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mdata/mysql_test_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mdata/mysql_test_data/error.log --pid-file=/mdata/mysql_test_data/k8s001.pid --port=3306
## 多实例80
root      37507      1  0 01:36 pts/5    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --server-id=80 --innodb_buffer_pool_size=32M --port=3380 --basedir=/usr/local/mysql80 --datadir=/mdata/data80 --socket=/tmp/mysql.sock4
mysql     37735  37507  0 01:36 pts/5    00:00:02 /usr/local/mysql80/bin/mysqld --basedir=/usr/local/mysql80 --datadir=/mdata/data80 --plugin-dir=/usr/local/mysql80/lib/plugin --user=mysql --server-id=80 --innodb-buffer-pool-size=32M --log-error=/mdata/data80/error.log --pid-file=/mdata/data80/k8s001.pid --socket=/tmp/mysql.sock4 --port=3380
## 多实例56
root      91670      1  0 01:00 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --server-id=56 --innodb_buffer_pool_size=32M --port=3356 --basedir=/usr/local/mysql56 --datadir=/mdata/data56 --socket=/tmp/mysql.sock3
mysql     91924  91670  0 01:00 ?        00:00:04 /usr/local/mysql-5.6.28-linux-glibc2.5-x86_64/bin/mysqld --basedir=/usr/local/mysql56 --datadir=/mdata/data56 --plugin-dir=/usr/local/mysql56/lib/plugin --user=mysql --server-id=56 --innodb-buffer-pool-size=32M --log-error=/mdata/data56/error.log --pid-file=/mdata/data56/k8s001.pid --socket=/tmp/mysql.sock3 --port=3356
## 多实例1
root     119414      1  0 02:11 pts/5    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --server-id=11 --innodb_buffer_pool_size=32M --port=3307 --datadir=/mdata/data1 --socket=/tmp/mysql.sock1
mysql    119630 119414  0 02:11 pts/5    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mdata/data1 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=11 --innodb-buffer-pool-size=32M --log-error=/mdata/data1/error.log --pid-file=/mdata/data1/k8s001.pid --socket=/tmp/mysql.sock1 --port=3307
## 多实例2
root     121654      1  0 02:12 pts/5    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --server-id=12 --innodb_buffer_pool_size=32M --port=3308 --datadir=/mdata/data2 --socket=/tmp/mysql.sock2
mysql    121898 121654  0 02:12 pts/5    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mdata/data2 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=12 --innodb-buffer-pool-size=32M --log-error=/mdata/data2/error.log --pid-file=/mdata/data2/k8s001.pid --socket=/tmp/mysql.sock2 --port=3308

5.数据库实例启动与关闭流程

• 启动命令

## 常规启动方法1
/etc/init.d/mysql.server start
## 常规启动方法2
mysqld --defaults-file=/etc/my.cnf &
## 常规启动方法3
mysqld_safe --defaults-file=/etc/my.cnf &
## 多实例启动
mysqld_multi start &num

对于常规方法1，它是一个启动脚本。
原理：通过常规方法1，会启动两个进程mysqld_safe和mysqld，即常规启动方法3和常规启动方法2。这里mysqld_safe是mysqld的守护进程，它会在mysqld挂掉的时候，会重新拉起一个mysqld进程，在一定程度上更加安全，所以建议使用mysqld_safe启动，多实例情况下也建议使用mysqld_safe启动。如下：

[root@k8s001 ~]# /etc/init.d/mysql.server start
Starting MySQL SUCCESS! 
[root@k8s001 ~]# ps -ef|grep mysqld
root      43022      1  0 03:22 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mdata/mysql_test_data --pid-file=/mdata/mysql_test_data/k8s001.pid
mysql     43195  43022  4 03:22 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mdata/mysql_test_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mdata/mysql_test_data/error.log --pid-file=/mdata/mysql_test_data/k8s001.pid --port=3306
root      43312  41464  0 03:22 pts/2    00:00:00 grep --color=auto mysqld
[root@k8s001 ~]# kill -9 43195
[root@k8s001 ~]# /usr/local/mysql/bin/mysqld_safe: 行 169: 43195 已杀死               nohup /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mdata/mysql_test_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mdata/mysql_test_data/error.log --pid-file=/mdata/mysql_test_data/k8s001.pid --port=3306 < /dev/null >> /mdata/mysql_test_data/error.log 2>&1
## 可以看出：mysqld的进程是杀不掉的。
[root@k8s001 ~]# ps -ef|grep mysqld
root      43022      1  0 03:22 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mdata/mysql_test_data --pid-file=/mdata/mysql_test_data/k8s001.pid
mysql     43479  43022  7 03:22 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mdata/mysql_test_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mdata/mysql_test_data/error.log --pid-file=/mdata/mysql_test_data/k8s001.pid --port=3306
root      43568  41464  0 03:22 pts/2    00:00:00 grep --color=auto mysqld

• 关闭命令

## 常规关闭方法1
/etc/init.d/mysql.server stop
## 常规关闭方法2
mysqladmin -uroot -pCmss@2021 -S /tmp/mysql.sock shutdown
## 非常规关闭方法3（不建议）
kill -9 &进程号
## 多实例关闭
mysqld_multi start &num

对于常规关闭方法1，它是一个启动脚本。
原理：通过执行常规方法1，会发送一个kill -0信号给mysqd的pid，通知mysqld的pid进程准备关闭，随后kill掉pid。所以这也是个安全关闭，建议使用。
对于常规关闭方法2，它是一个命令，是多实例关闭的常用的方法。这就是为什么需要在my.cnf文件里 [mysqld_multi] 配置用户名和密码的原因，如下：

[client]
user=root    
password=Cmss@2021         ##免密登录设置
port=3306
socket=/tmp/mysql.sock 
## 多实例配置
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe    #用来启动mysqld
mysqladmin=/usr/local/mysql/bin/mysqladmin #用来关闭mysqld，它需要用户名和密码。如不配置，则默认使用[client]的用户名和密码。如配置，请注意注意密码配置为pass（踩坑）
log=/usr/local/mysql/mysqld_multi.log
#user=root       #mysqladmin
#pass=Cmss@2021  #注意密码配置键为pass

六、Percona Server for MySQL安装

Percona 下载地址：https://www.percona.com/downloads/

step1：选择下载对应版本的Percona。

step2：解压安装包（和之前一样）

step3：创建软链

step4：重启mysql

shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> unlink mysql
shell> ln -s full-path-to-Percona-VERSION-OS mysql
shell> /etc/init.d/mysql.server restart

登录验证（Percona完全兼容mysql，所以还是之前的登录方式）
登录成功标识：Server version: 5.7.34-37-log Percona Server (GPL), Release 37, Revision 7c516e9

[root@k8s101 local]# mysql -uroot -pCmss@2021
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.34-37-log Percona Server (GPL), Release 37, Revision 7c516e9

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost)[(none)]> 

七、mysql_utilities安装

mysql_utilities下载地址：https://downloads.mysql.com/archives/utilities/

下载→解压→安装

[root@k8s001 ~]# tar -xvf mysql-utilities-1.6.4.tar.gz
[root@k8s001 ~]# ll
总用量 648
-rw-------. 1 root root    1271 5月  31 09:44 anaconda-ks.cfg
drwxr-xr-x. 7 7161 31415    196 6月  10 05:00 mysql-utilities-1.6.4
-rw-r--r--. 1 root root  659331 6月  10 05:00 mysql-utilities-1.6.4.tar.gz
[root@k8s001 ~]# cd mysql-utilities-1.6.4
[root@k8s001 mysql-utilities-1.6.4]# python setup.py install

## 使用mysqlfrm命令查看*.frm文件的结构
[root@k8s001 mysql]# mysqlfrm --diagnostic time_zone.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for time_zone.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `Use_leap_seconds` enum('Y','N') NOT NULL, 
PRIMARY KEY `PRIMARY` (`Time_zone_id`)
) ENGINE=InnoDB COMMENT 'Time zones';

#...done.