网站导航
> 文章列表 > MySQL安装和配置(保姆级别和全网最详细教程)

MySQL安装和配置(保姆级别和全网最详细教程)

网友: 文章列表 2024-03-22 01:43:40

MySQL安装和配置(保姆级别和全网最详细教程)

前言

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。具体可以在【数据库原理 • 一】数据库系统概念进行了解。

在这里插入图片描述

MySQL安装和配置）

  • 一、版本介绍
    • 1.1 安装开发版本还是通用版本（GA)
    • 1.2 mysql命名方案
  • 二、mysql安装方式和下载
    • 2.1 mysql安装方式
    • 2.2 mysql下载
  • 三、mysql安装
    • 3.1 前期准备工作
      • 3.1.1 操作系统配置
      • 3.1.2 修改主机名
      • 3.1.3 安装常用的软件
      • 3.1.4 关闭防火墙
    • 3.2 rpm包按装mysql
      • 3.2.1 解压上传服务器的rpm包
      • 3.2.2 安装mysql
      • 3.2.2 卸载mysql
    • 3.3 二进制安装mysql
      • 3.3.1 安装mysql规范
      • 3.3.2 解压上传服务器的rpm包
      • 3.3.3 安装mysql
      • 3.3.4 使用service控制mysql启动或关闭
      • 3.3.5 卸载mysql
    • 3.4 源码安装mysql（了解）
      • 3.4.1 安装依赖包
      • 3.4.2 生成源码包
      • 3.4.3 编译安装mysql
      • 3.4.4 卸载mysql
  • 四、多实例部署
    • 4.1 多实例概念
    • 4.2 多实例安装（二进制）
    • 4.3 mysqld_multi（多实例第二种安装方式）
    • 4.4 使用service 控制实例的启停
    • 4.5 卸载mysql多实例

一、版本介绍

1.1 安装开发版本还是通用版本（GA)

  • 开发版本具有最新功能，但生产环境不能使用
  • GA版本，也称为生产版本或稳定版本。

1.2 mysql命名方案

mysql命名方案使用的发行版本名称由三个数字和一个可选的后缀组成。例如mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar。版本名称中的数字解释如下：

  • 第一个数字（5）： 是主版本号。

  • 第二个数字（7）： 是次要版本号。总而言之，主要和次要数字构成发行版本号。

  • 第三个数字（39）： 是发行系列中的版本号。对于每个新的错误修正版本，此值均递增。在通常最新版本是最佳选择。

  • 里程碑： 后缀用于指示版本的稳定性。可能的后缀是：m N（例如， m1， m2， m3，…）表示里程碑编号。rc表示发布候选（RC）。通过mysql的所有内部测试后，发布候选版本被认为是稳定的。没有后缀表示正式版。生产环境中的主要的选择类型。如图：

MySQL安装和配置(保姆级别和全网最详细教程)

二、mysql安装方式和下载

2.1 mysql安装方式

首先mysql有以下三种安装方式

  • rpm包形式
  • 通用二进制形式 （首选）
  • 源码编译（了解）

2.2 mysql下载

https://dev.mysql.com/downloads/mysql/mysql官网，点击Archives，选择对应的产品版本、操作系统（安装方式）和操作系统版本，点击Down进行下载

MySQL安装和配置(保姆级别和全网最详细教程)

MySQL安装和配置(保姆级别和全网最详细教程)

MySQL安装和配置(保姆级别和全网最详细教程)

三、mysql安装

3.1 前期准备工作

3.1.1 操作系统配置

[root@loclhost ~]# hostnamectl Static hostname: mysql2.x88.comIcon name: computer-vmChassis: vmMachine ID: 28cae06eae1740acbdfe4f88be6ffeb0Boot ID: 1f9fe175b6a54d2dab41033927790634Virtualization: vmwareOperating System: CentOS Linux 7 (Core)CPE OS Name: cpe:/o:centos:centos:7Kernel: Linux 3.10.0-1160.71.1.el7.x86_64Architecture: x86-64

3.1.2 修改主机名

[root@loclhost ~]# hostnamectl set-hostname mysql2.x88.com

3.1.3 安装常用的软件

[root@loclhost ~]# yum install lrzsz sysstat elinks wget net-tools bash-completion -y

3.1.4 关闭防火墙

查询防火墙状态

[root@loclhost ~]# systemctl status firewalld

MySQL安装和配置(保姆级别和全网最详细教程)

关防火墙

[root@loclhost ~]# systemctl stop firewalld #关闭防火墙
[root@loclhost ~]# systemctl disable firewalld #开机不启动防火墙
[root@loclhost ~]# systemctl status firewalld #查询防火墙状态

MySQL安装和配置(保姆级别和全网最详细教程)

查询SELINUX状态

[root@loclhost ~]# getenforce

MySQL安装和配置(保姆级别和全网最详细教程)

关闭SELINUX

[root@loclhost ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config #永久关闭SELINUX
[root@loclhost ~]# setenforce 0 #临时关闭
[root@loclhost ~]# getenforce

MySQL安装和配置(保姆级别和全网最详细教程)

重启服务器后，查看SELINUX状态

[root@loclhost ~]# init 6 #重启服务器
[root@mysql2 ~]# getenforce

MySQL安装和配置(保姆级别和全网最详细教程)

3.2 rpm包按装mysql

3.2.1 解压上传服务器的rpm包

我们创建一个目录，用来保存上传或者下载的rpm包，使用df -h命令查询磁盘的大小和挂载点

[root@mysql2 ~]# df -h #查询磁盘容量

MySQL安装和配置(保姆级别和全网最详细教程)
可以看出当前服务 / 目录下有183G，只使用了3%

[root@mysql2 ~]# mkdir -p /data/install
[root@mysql2 ~]# cd /data/install/

这里使用使用lrzsz 上传rpm安装包，也可以使用sftp等工具

安装包下载参考本文章 2.2章节
如服务器有外网的情况下可以使用wget进行下载
[root@mysql2 install]# wget -O /data/install/mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar

[root@mysql2 install]# rz

MySQL安装和配置(保姆级别和全网最详细教程)
解压我们刚才上传rpm安装包

[root@mysql2 install]# ll
总用量 543132
-rw-r--r-- 1 root root 556165120 4月  12 12:37 mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[root@mysql2 install]# tar -xvf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar #解压rpm安装包
mysql-community-client-5.7.39-1.el7.x86_64.rpm
mysql-community-common-5.7.39-1.el7.x86_64.rpm
mysql-community-devel-5.7.39-1.el7.x86_64.rpm
mysql-community-embedded-5.7.39-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.39-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.39-1.el7.x86_64.rpm
mysql-community-libs-5.7.39-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.39-1.el7.x86_64.rpm
mysql-community-server-5.7.39-1.el7.x86_64.rpm
mysql-community-test-5.7.39-1.el7.x86_64.rpm

3.2.2 安装mysql

首先我们卸载CentOS自带的Mariadb

[root@mysql2 install]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@mysql2 install]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64  #--nodeps 参数是忽略依赖关系

安装 rpm 安装mysql需要的依赖包

[root@mysql2 install]# yum install net-tools prel -y

按照以下顺序进行mysql的安装

  1. mysql-community-common-5.7.39-1.el7.x86_64.rpm
  2. mysql-community-libs-5.7.39-1.el7.x86_64.rpm
  3. mysql-community-client-5.7.39-1.el7.x86_64.rpm
  4. mysql-community-server-5.7.39-1.el7.x86_64.rpm
[root@mysql2 install]# rpm -ivh mysql-community-common-5.7.39-1.el7.x86_64.rpm
警告：mysql-community-common-5.7.39-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...1:mysql-community-common-5.7.39-1.e################################# [100%]
[root@mysql2 install]# rpm -ivh mysql-community-libs-5.7.39-1.el7.x86_64.rpm
警告：mysql-community-libs-5.7.39-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...1:mysql-community-libs-5.7.39-1.el7################################# [100%]
[root@mysql2 install]# rpm -ivh mysql-community-client-5.7.39-1.el7.x86_64.rpm
警告：mysql-community-client-5.7.39-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...1:mysql-community-client-5.7.39-1.e################################# [100%]
[root@mysql2 install]# rpm -ivh mysql-community-server-5.7.39-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.39-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...1:mysql-community-server-5.7.39-1.e################################# [100%]

数据库安装完成，实际坏境中，需要掌握以下命令来管理mysql的启停

• systemctl start mysqld 启动
• systemctl stop mysqld 停止
• systemctl status mysqld 状态查询
• systemctl restart mysqld 重启
• systemctl enable mysqld 开机自动启动
• systemctl disable mysqld 关闭开机自动启动

启动mysql

[root@mysql2 install]# systemctl status mysqld
● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: inactive (dead)Docs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.html

MySQL安装和配置(保姆级别和全网最详细教程)

[root@mysql2 install]# systemctl start mysqld
[root@mysql2 install]# lsof -i:3306 #也可以使用systemctl status mysqld 查询mysql状态
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2938 mysql   21u  IPv6  40870      0t0  TCP *:mysql (LISTEN)

修改初始化密码

安装mysql的时候，会把初始密码报错在日志里面，日志的位置在/var/log目录下的mysqld.log文件里面，使用如下命令查询初始化密码

[root@mysql2 install]# cat /var/log/mysqld.log | grep 'password is generated'

查询到密码后，我们使用mysql -uroot -p命令输入查询到初始化密码登陆数据库

[root@mysql2 install]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 2
Server version: 5.7.39Copyright (c) 2000, 2022, 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.mysql>

登陆数据库成功后，使用如下命令修改root账户密码

mysql> alter user 'root'@'localhost' identified by 'Cjlr2023!';
Query OK, 0 rows affected (0.00 sec)

授权root账户所有资源，并且可以远程登陆

mysql> grant all privileges on *.*  to  'root'@'%'  identified by 'Cjlr2023!'  with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

远程登陆测试

MySQL安装和配置(保姆级别和全网最详细教程)

3.2.2 卸载mysql

查看当前mysql安装情况

[root@mysql2 install]# rpm -qa | grep mysql
mysql-community-common-5.7.39-1.el7.x86_64
mysql-community-client-5.7.39-1.el7.x86_64
mysql-community-server-5.7.39-1.el7.x86_64
mysql-community-libs-5.7.39-1.el7.x86_64

停止mysql服务

[root@mysql2 install]# systemctl stop mysqld

卸载之前安装的mysql

[root@mysql2 install]# rpm -evh --nodeps mysql-community-server-5.7.39-1.el7.x86_64 mysql-community-client-5.7.39-1.el7.x86_64 mysql-community-libs-5.7.39-1.el7.x86_64 mysql-community-common-5.7.39-1.el7.x86_64
准备中...                          ################################# [100%]
正在清理/删除...1:mysql-community-server-5.7.39-1.e################################# [ 25%]2:mysql-community-client-5.7.39-1.e################################# [ 50%]3:mysql-community-libs-5.7.39-1.el7################################# [ 75%]4:mysql-community-common-5.7.39-1.e################################# [100%]

删除安装mysq产生的目录

[root@mysql2 install]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/share/mysql
[root@mysql2 install]# rm -rf /var/lib/mysql /etc/selinux/targeted/active/modules/100/mysql/ /usr/share/mysql/

删除mysql用户及用户组

[root@mysql2 install]# userdel -r mysql
[root@mysql2 install]# groupdel mysql

3.3 二进制安装mysql

3.3.1 安装mysql规范

首先我们mysql创建用户规范，mysql:用户建议虚拟用户，只用于mysql进程运行使用，不允许登录、不创建家目录。

useradd -s /sbin/nologin -M mysql

mysql目录规范

  • 系统目录 /opt/mysql/my-xx.xx
  • 软连接 ln -s /opt/mysql/my-xx.xx /usr/local/mysql
  • 数据目录 /data/mysql/my+port/{data,logs}
  • 配置文件/data/mysql/my+port/my+port.cnf

3.3.2 解压上传服务器的rpm包

安装包下载参考本文章 2.2章节
如服务器有外网的情况下可以使用wget进行下载
[root@mysql2 install]# wget -O /data/install/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

上传mysql二进制包

[root@mysql2 install]# rz

MySQL安装和配置(保姆级别和全网最详细教程)

解压二进制包

[root@mysql2 install]# tar -xvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

3.3.3 安装mysql

创建用户

[root@mysql2 install]# useradd -s /sbin/nologin -M mysql

创建目录

[root@mysql2 install]# mkdir -p /data/mysql/my3306/{data,logs}

更改用户和组

[root@mysql2 install]# chown -R mysql:mysql /data/mysql/
[root@mysql2 install]# chmod -R 750 /data/mysql/

将软件部分移动到指定目录

[root@mysql2 install]# mkdir -p /opt/mysql
[root@mysql2 install]# mv mysql-5.7.39-linux-glibc2.12-x86_64 /opt/mysql/

创建软连接

[root@mysql2 install]# ln -s /opt/mysql/mysql-5.7.39-linux-glibc2.12-x86_64/ /usr/local/mysql

删除系统自带的mariadb

[root@mysql2 install]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@mysql2 install]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

配置mysql配置文件

[root@mysql2 install]# cat <<EOF>> /data/mysql/my3306/my3306.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3306/data
socket = /data/mysql/my3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/my3306/logs/error.log
log_bin=/data/mysql/my3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
EOF[root@mysql2 install]# more /data/mysql/my3306/my3306.cnf

安装二进制安装mysql所需要的依赖包

[root@mysql2 install]# yum install libaio-devel numactl -y

配置环境变量

[root@mysql2 install]# cat <<EOF>> ~/.bash_profile
export PATH=/usr/local/mysql/bin:$PATH
EOF
[root@mysql2 install]# source ~/.bash_profile

初始化数据库

[root@mysql2 install]# mysqld --defaults-file=/data/mysql/my3306/my3306.cnf --initialize --user=mysql

--defaults-file: 如果参数文件没有在默认的位置，需要手工指定my.cnf的位置
--initialize：相对应的参数是---initialize-insecure。 “ 默认安全 ”安装（即包括生成随机初始的 root密码）。在这种情况下，密码被标记为已过期，您将需要选择一个新密码。使用--initialize-insecure，不会root生成密码。这是不安全的；可以直接登录进入。
--basedir=/usr/local/mysql  可选参数，如果my.cnf当中没有指定路径，需要手工指定,mysql软件位置。
--datadir=/opt/mysql/mysql/data 可选参数，如果my.cnf当中没有指定路径，需要手工指定，mysql数据目录位置。
--user=mysql  数据初始化后，所属的用户

查看密码

[root@mysql2 install]# grep -i 'password' /data/mysql/my3306/logs/error.log

后台启动mysql

  • nohup：结果默认会输出到nohup.out
  • &：后台运行
[root@mysql2 install]#nohup mysqld --defaults-file=/data/mysql/my3306/my3306.cnf  &
[root@mysql2 install]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  7562 mysql   23u  IPv6  97311      0t0  TCP *:mysql (LISTEN)

重置root密码

除了重置初始root密码的动作以外，其它全部默认即可。初始的root密码在错误日志当中

[root@mysql2 install]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3306/mysql.sock
#输入日志里密码
Enter password:  Securing the MySQL server deployment.The existing password for the user account root has expired. Please set a new password.
#设置新密码
New password: 
#确认新密码
Re-enter new password: #是否设置验证密码插件
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?Press y|Y for Yes, any other key for No: 
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : ... skipping.# 是否删除匿名账户
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.Remove anonymous users? (Press y|Y for Yes, any other key for No) : ... skipping.# 是否禁止远程登录root用户
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.Disallow root login remotely? (Press y|Y for Yes, any other key for No) : ... skipping.# 是否删除test 数据库
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.Remove test database and access to it? (Press y|Y for Yes, any other key for No) : ... skipping.
# 是否重新加载特权表
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.Reload privilege tables now? (Press y|Y for Yes, any other key for No) : ... skipping.
All done!

登陆mysql

[root@mysql2 install]# mysql -uroot -p --socket=/data/mysql/my3306/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 4
Server version: 5.7.39-log MySQL Community Server (GPL)Copyright (c) 2000, 2022, 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.mysql>

授权root远程登陆的权限

mysql> grant all privileges on *.*  to  'root'@'%'  identified by 'Cjlr2023!'  with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

远程登陆测试

MySQL安装和配置(保姆级别和全网最详细教程)
停止mysql

[root@mysql2 install]# mysqladmin -uroot -p123456 shutdown --socket=/data/mysql/my3306/mysql.sock

3.3.4 使用service控制mysql启动或关闭

修改46、47、207、266行如下

[root@mysql2 support-files]# vim /usr/local/mysql/support-files/mysql.server46 basedir=/usr/local/mysql #软件位置
47 datadir=/data/mysql/my3306/data #数据目录
207  conf=/data/mysql/my3306/my3306.cnf #参数文件
266  $bindir/mysqld_safe --datadir="$datadir" --socket=/data/mysql/my3306/mysql.sock --pid-file="$mysqld_pid_file_path" $other_args >/dev/nu    ll & #添加socket文件位置
[root@mysql2 support-files]# ./mysql.server 
Usage: mysql.server  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]
[root@mysql2 support-files]# ./mysql.server stop
Shutting down MySQL.. SUCCESS! 
[root@mysql2 support-files]# ./mysql.server start
Starting MySQL. SUCCESS! 
[root@mysql2 support-files]# ./mysql.server statusSUCCESS! MySQL running (3807)
[root@mysql2 support-files]# ./mysql.server reloadSUCCESS! Reloading service MySQL

添加到mysqld到service

[root@mysql2 support-files]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql2 support-files]# chkconfig /etc/init.d/mysqld on
[root@mysql2 support-files]# chkconfig mysqld --list注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 要列出 systemd 服务，请执行 'systemctl list-unit-files'。查看在具体 target 启用的服务请执行'systemctl list-dependencies [target]'。mysqld         	0:关	1:关	2:开	3:开	4:开	5:开	6:关
[root@mysql2 support-files]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@mysql2 support-files]# service mysqld start
Starting MySQL. SUCCESS! 
[root@mysql2 support-files]# service mysqld statusSUCCESS! MySQL running (4080)

3.3.5 卸载mysql

查询数据库状态

[root@mysql2 ~]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4080 mysql   29u  IPv6  40197      0t0  TCP *:mysql (LISTEN)

关闭数据库

[root@mysql2 install]# mysqladmin -uroot -p123456 shutdown --socket=/data/mysql/my3306/mysql.sock

如果配置了使用service控制mysql启动或关闭，删除service服务和脚本

[root@mysql2 ~]# chkconfig --del mysqld
[root@mysql2 ~]# rm -rf /etc/init.d/mysqld

删除软件目录

[root@mysql2 ~]# rm -rf /opt/mysql/
[root@mysql2 ~]# rm -rf /usr/local/mysql

删除数据目录

[root@mysql2 ~]# rm -rf /data/mysql/

删除mysql所有的目录

[root@mysql2 ~]# find / -name mysql
/run/lock/subsys/mysql
/var/spool/mail/mysql
[root@mysql2 ~]# rm -rf /run/lock/subsys/mysql 
[root@mysql2 ~]# rm -rf /var/spool/mail/mysql

删除mysql用户及用户组

[root@mysql2 install]# userdel -r mysql
[root@mysql2 install]# groupdel mysql

3.4 源码安装mysql（了解）

源码安装的方式时间非常久，不推荐使用这种方式，如果是想了解mysql引擎源码，可以使用这种方式进行安装。

3.4.1 安装依赖包

[root@mysql2 install]# yum install gcc gcc-c++ openssl openssl-devel ncurses ncurses-devel bison make cmake -y

3.4.2 生成源码包

安装包下载参考本文章 2.2章节
如服务器有外网的情况下可以使用wget进行下载
[root@mysql2 install]# wget -O /data/install/mysql-community-5.7.39-1.el7.src.rpm https://cdn.mysql.com/archives/mysql-5.7/mysql-community-5.7.39-1.el7.src.rpm

上传mysql二进制包

[root@mysql2 install]# rz

MySQL安装和配置(保姆级别和全网最详细教程)

[root@mysql2 install]# rpm -ivh mysql-community-5.7.39-1.el7.src.rpm 
[root@mysql2 install]# ll ~/rpmbuild/SOURCES/
总用量 155596
-rw-r--r-- 1 root root 70389425 6月   8 2022 boost_1_59_0.tar.bz2
-rwxr-xr-x 1 root root      160 6月   8 2022 filter-provides.sh
-rwxr-xr-x 1 root root      176 6月   8 2022 filter-requires.sh
-rw-r--r-- 1 root root 32411131 6月   8 2022 mysql-5.6.51.tar.gz
-rw-r--r-- 1 root root 56517250 6月   8 2022 mysql-5.7.39.tar.gz

3.4.3 编译安装mysql

创建用户

[root@mysql2 install]# useradd -s /sbin/nologin -M mysql

删除系统自带的mariadb

[root@mysql2 install]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@mysql2 install]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

编译安装

[root@mysql2 install]# cd ~/rpmbuild/SOURCES/
[root@mysql2 SOURCES]#  tar -xvf mysql-5.7.39.tar.gz
[root@mysql2 SOURCES]# cd mysql-5.7.39/
[root@mysql2 mysql-5.7.39]# cp ../boost_1_59_0.tar.bz2 ./
[root@mysql2 mysql-5.7.39]# tar -xvf boost_1_59_0.tar.bz2 
[root@mysql2 mysql-5.7.39]# cmake ./ -DWITH_BOOST=boost_1_59_0/ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/usr/local/mysql/data -DINSTALL_MANDIR=/usr/share/man -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DEXTRA_CHARSETS=all -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1

MySQL安装和配置(保姆级别和全网最详细教程)
如果缓存失败，清除缓存，重新编译

[root@mysql2 mysql-5.7.39]# make clean
[root@mysql2 mysql-5.7.39]# rm CMakeCache.txt

安装

[root@mysql2 mysql-5.7.39]# make && make install

配置mysql配置文件

[root@mysql2 mysql-5.7.39]# cat <<EOF>> /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket = /tmp/mysql.sock
server_id = 1
port = 3306
log_error=/usr/local/mysql/data/error.log
log_bin=/usr/local/mysql/data/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/usr/local/mysql/data/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
EOF
[root@mysql2 mysql-5.7.39]# more /etc/my.cnf

配置环境变量

[root@mysql2 install]# cat <<EOF>> ~/.bash_profile
export PATH=/usr/local/mysql/bin:$PATH
EOF
[root@mysql2 install]# source ~/.bash_profile

初始化数据库

[root@mysql2 mysql-5.7.39]# mysqld --initialize --user=mysql  //使用/etc/my.cnf ，则不需要指定参数文件

查看密码

[root@mysql2 mysql-5.7.39]# grep -i 'password' /usr/local/mysql/data/error.log 
2023-04-13T02:08:03.308852Z 1 [Note] A temporary password is generated for root@localhost: Y;_cFdha-4Lm

启动mysql

[root@mysql2 mysql-5.7.39]## nohup mysqld & 	    //使用/etc/my.cnf ，则不需要指定参数文件

查看是否启动成功

[root@mysql2 mysql-5.7.39]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  89895 mysql   23u  IPv6 183192      0t0  TCP *:mysql (LISTEN)

除了重置初始root密码的动作以外，其它全部默认即可。初始的root密码在错误日志当中

[root@mysql2 mysql-5.7.39]# mysql_secure_installation -uroot -p   //sock如果在/tmp/mysql.sock 则不需要指定#输入日志里密码
Enter password:  Securing the MySQL server deployment.The existing password for the user account root has expired. Please set a new password.
#设置新密码
New password: 
#确认新密码
Re-enter new password: #是否设置验证密码插件
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?Press y|Y for Yes, any other key for No: 
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : ... skipping.# 是否删除匿名账户
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.Remove anonymous users? (Press y|Y for Yes, any other key for No) : ... skipping.# 是否禁止远程登录root用户
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.Disallow root login remotely? (Press y|Y for Yes, any other key for No) : ... skipping.# 是否删除test 数据库
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.Remove test database and access to it? (Press y|Y for Yes, any other key for No) : ... skipping.
# 是否重新加载特权表
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.Reload privilege tables now? (Press y|Y for Yes, any other key for No) : ... skipping.
All done!

连接mysql

[root@mysql2 mysql-5.7.39]# mysql -uroot -p123456
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.39-log Source distributionCopyright (c) 2000, 2022, 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.mysql>

授权root远程登陆的权限

mysql> grant all privileges on *.*  to  'root'@'%'  identified by 'Cjlr2023!'  with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

远程登陆测试

MySQL安装和配置(保姆级别和全网最详细教程)

3.4.4 卸载mysql

查询数据库状态

[root@mysql2 mysql-5.7.39]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  89895 mysql   23u  IPv6 183192      0t0  TCP *:mysql (LISTEN)
[root@mysql2 mysql-5.7.39]# kill -9 89895

删除所有安装目录

[root@mysql2 mysql-5.7.39]# find / -name mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/include/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/rapid/plugin/group_replication/include/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/rapid/plugin/group_replication/libmysqlgcs/include/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-api/src/main/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-bindings/src/main/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-bindings/src/main/resources/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-core/src/main/resources/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-jdbc/src/main/antlr3/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-jdbc/src/main/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-jdbc/src/main/resources/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-jpatest/src/main/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-openjpa/src/main/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-openjpa/src/main/resources/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-openjpa/src/test/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-tie/src/main/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-tie/src/main/resources/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/clusterj/clusterj-tie/src/test/java/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/nodejs/Adapter/impl/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/src/ndbjtie/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/src/ndbjtie/com/mysql/ndbjtie/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/src/ndbjtie/jtie/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/test/crund/schema_driven_load_unfinished/tws_java/src/com/mysql
/root/rpmbuild/SOURCES/mysql-5.7.39/storage/ndb/test/crund/src/com/mysql
/var/spool/mail/mysql
/usr/local/mysql
/usr/local/mysql/include/mysql
/usr/local/mysql/bin/mysql
/usr/local/mysql/data/mysql
[root@mysql2 mysql-5.7.39]# rm -rf /root/rpmbuild/ /var/spool/mail/mysql /usr/local/mysql
[root@mysql2 mysql-5.7.39]# find / -name mysql
[root@mysql2 mysql-5.7.39]# rm -rf /etc/my.cnf

删除mysql用户及用户组

[root@mysql2 mysql-5.7.39]# userdel -r mysql
[root@mysql2 mysql-5.7.39]# groupdel mysql

四、多实例部署

4.1 多实例概念

如果一个系统有多个数据库端口（例如有3306 ，也有3307 ）则这个服务器上是多实例。

MySQL实例构成

由进程+线程+预分配内存组成，mysql是单进程多线程的结构

  • 进程：正在运行的程序
  • 查看mysql进程：ps -ef |grep mysql
  • 杀死mysql进程：kill 进程号、 pkill 进程名称
  • 线程：Master thread、purge thread 、page cleaner thread、io thread
  • 预分配内存：buffer pool、insert buffer、change buffer、double write

什么是多实例

  • 在一台机器上由多个实例（进程+线程+预分配内存）构成

注意事项

  • 系统共用，实例之间具有不同的datadir、server_id、port、日志文件、socket文件

  • innodb_buffer_pool_size分配的总量要小于物理内存的80%

4.2 多实例安装（二进制）

这里就不详细讲解，直接上手

安装依赖包

[root@mysql2 install]# yum install libaio-devel numactl -y

创建用户

[root@mysql2 install]# useradd -s /sbin/nologin -M mysql

创建目录

[root@mysql2 install]# mkdir -p /data/mysql/{my3306,my3307}/{data,logs}

更改用户和组

[root@mysql2 install]# chown -R mysql:mysql /data/mysql/
[root@mysql2 install]# chmod -R 750 /data/mysql/

解压包

[root@mysql2 install]# tar -xvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

将软件部分移动到指定目录

[root@mysql2 install]# mkdir -p /opt/mysql
[root@mysql2 install]# mv mysql-5.7.39-linux-glibc2.12-x86_64 /opt/mysql/

创建软连接

[root@mysql2 install]# ln -s /opt/mysql/mysql-5.7.39-linux-glibc2.12-x86_64/ /usr/local/mysql

删除系统自带的mariadb

[root@mysql2 install]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@mysql2 install]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

创建配置文件

  • 实例之间具有不同的datadir、server_id、port、日志文件、socket文件
[root@mysql2 install]# cat <<EOF>> /data/mysql/my3306/my3306.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3306/data
socket = /data/mysql/my3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/my3306/logs/error.log
log_bin=/data/mysql/my3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
EOF[root@mysql2 install]# cat <<EOF>> /data/mysql/my3307/my3307.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3307/data
socket = /data/mysql/my3307/mysql.sock
server_id = 2
port = 3307
log_error=/data/mysql/my3307/logs/error.log
log_bin=/data/mysql/my3307/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3307/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
EOF

配置环境变量

[root@mysql2 install]# cat <<EOF>> ~/.bash_profile
export PATH=/usr/local/mysql/bin:$PATH
EOF
[root@mysql2 install]# source ~/.bash_profile

初始化数据库

[root@mysql2 install]# mysqld --defaults-file=/data/mysql/my3306/my3306.cnf --initialize --user=mysql
[root@mysql2 install]# mysqld --defaults-file=/data/mysql/my3307/my3307.cnf --initialize --user=mysql

查看密码

[root@mysql2 install]# grep -i 'password' /data/mysql/my3306/logs/error.log
[root@mysql2 install]# grep -i 'password' /data/mysql/my3307/logs/error.log

启动mysql

[root@mysql2 install]# nohup mysqld --defaults-file=/data/mysql/my3306/my3306.cnf  &
[1] 94286
[root@mysql2 install]# nohup: 忽略输入并把输出追加到"nohup.out"[root@mysql2 install]# nohup mysqld --defaults-file=/data/mysql/my3307/my3307.cnf  &
[2] 94329
[root@mysql2 install]# nohup: 忽略输入并把输出追加到"nohup.out"

查看是否启动成功

[root@mysql2 install]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  94286 mysql   23u  IPv6 214546      0t0  TCP *:mysql (LISTEN)
[root@mysql2 install]# lsof -i:3307
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  94329 mysql   23u  IPv6 195569      0t0  TCP *:opsession-prxy (LISTEN)

重置root密码

[root@mysql2 install]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3306/mysql.sock
[root@mysql2 install]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3307/mysql.sock

连接

[root@mysql2 install]# mysql -uroot -p123456 --socket=/data/mysql/my3306/mysql.sock
[root@mysql2 install]# mysql -uroot -p123456 --socket=/data/mysql/my3307/mysql.sock

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

数据库卸载、启停和远程连接参考本文 3.3.3 和 3.3.5 章节

4.3 mysqld_multi（多实例第二种安装方式）

安装依赖包

[root@mysql2 install]# yum install libaio-devel numactl -y

创建用户

[root@mysql2 install]# useradd -s /sbin/nologin -M mysql

创建目录

[root@mysql2 install]# mkdir -p /data/mysql/{my3306,my3307}/{data,logs}

更改用户和组

[root@mysql2 install]# touch /data/mysql/{my3306,my3307}/logs/error.log
[root@mysql2 install]# chown -R mysql:mysql /data/mysql/
[root@mysql2 install]# chmod -R 750 /data/mysql/

解压包（二进制包）

[root@mysql2 install]# tar -xvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

将软件部分移动到指定目录

[root@mysql2 install]# mkdir -p /opt/mysql
[root@mysql2 install]# mv mysql-5.7.39-linux-glibc2.12-x86_64 /opt/mysql/

创建软连接

[root@mysql2 install]# ln -s /opt/mysql/mysql-5.7.39-linux-glibc2.12-x86_64/ /usr/local/mysql

删除系统自带的mariadb

[root@mysql2 install]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@mysql2 install]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

配置环境变量

[root@mysql2 install]# cat <<EOF>> ~/.bash_profile
export PATH=/usr/local/mysql/bin:$PATH
EOF
[root@mysql2 install]# source ~/.bash_profile

创建多实例配置文件

[root@mysql2 install]# cat <<EOF>> /etc/my_multi.cnf
[mysqld_multi]  
mysqld     = /usr/local/mysql/bin/mysqld_safe  
mysqladmin = /usr/local/mysql/bin/mysqladmin  [mysqld1]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3306/data
socket = /data/mysql/my3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/my3306/logs/error.log
log_bin=/data/mysql/my3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
[mysqld2]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3307/data
socket = /data/mysql/my3307/mysql.sock
server_id = 2
port = 3307
log_error=/data/mysql/my3307/logs/error.log
log_bin=/data/mysql/my3307/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3307/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
EOF

初始化实例

  • mysqld_multi启动的时候会自动初始化数据库

初始化所有实例

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start
[root@mysql2 install]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
mysqld  64896 mysql   23u  IPv6 1344184      0t0  TCP *:mysql (LISTEN)
[root@mysql2 install]# lsof -i:3307
COMMAND   PID  USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
mysqld  62029 mysql   23u  IPv6 1347838      0t0  TCP *:opsession-prxy (LISTEN)

MySQL安装和配置(保姆级别和全网最详细教程)

初始化实例完成后，查询数据库没有启动，–log参数查询原因

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start 2 --log=/tmp/error.log

初始化一个实例

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start 2

重置root密码


```bash
[root@mysql2 install]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3306/mysql.sock
[root@mysql2 install]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3307/mysql.sock

连接

[root@mysql2 install]# mysql -uroot -p123456 --socket=/data/mysql/my3306/mysql.sock
[root@mysql2 install]# mysql -uroot -p123456 --socket=/data/mysql/my3307/mysql.sock

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

停止单个实例

[root@mysql2 install]# mysqladmin -uroot -p123456 shutdown --socket=/data/mysql/my3306/mysql.sock
[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf --password=123456 --user=root stop 2

停止全部实例

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf --password=123456 --user=root stop

查看全部实例状态

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf report

查看单个实例状态

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf report 3307

4.4 使用service 控制实例的启停

修改脚本如下

[root@mysql2 install]# cd /usr/local/mysql/support-files/
[root@mysql2 support-files]# more mysqld_multi.server 
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#basedir=/usr/local/mysql
bindir=/usr/local/mysql/binif test -x $bindir/mysqld_multi
thenmysqld_multi="$bindir/mysqld_multi";
elseecho "Can't execute $bindir/mysqld_multi from dir $basedir";exit;
ficase "$1" in'start' )"$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf start $2;;'stop' )"$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf --password=123456 --user=root stop $2;;'report' )"$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf report $2;;'restart' )"$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf --password=123456 --user=root stop $2"$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf  start $2;;*)echo "Usage: $0 {start|stop|report|restart}" >&2;;
esac

测试如下

[root@mysql2 support-files]# ./mysqld_multi.server report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
[root@mysql2 support-files]# ./mysqld_multi.server start 2
[root@mysql2 support-files]# ./mysqld_multi.server report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
[root@mysql2 support-files]# ./mysqld_multi.server stop
[root@mysql2 support-files]# ./mysqld_multi.server report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running

添加到mysqlds到service

[root@mysql2 support-files]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
[root@mysql2 support-files]# chkconfig /etc/init.d/mysqld_multi on
[root@mysql2 support-files]# chkconfig mysqld_multi --list注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 要列出 systemd 服务，请执行 'systemctl list-unit-files'。查看在具体 target 启用的服务请执行'systemctl list-dependencies [target]'。mysqld_multi   	0:关	1:关	2:开	3:开	4:开	5:开	6:关
[root@mysql2 init.d]# service mysqld_multi start
WARNING: my_print_defaults command not found.
Please make sure you have this command available and
in your path. The command is available from the latest
MySQL distribution.
ABORT: Can't find command 'my_print_defaults'.
This command is available from the latest MySQL
distribution. Please make sure you have the command
in your PATH.

解决方法

[root@mysql2 init.d]# cp /usr/local/mysql/bin/my_print_defaults /usr/bin/
[root@mysql2 init.d]# service mysqld_multi start
[root@mysql2 init.d]# service mysqld_multi report 
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
[root@mysql2 init.d]# service mysqld_multi stop 1
[root@mysql2 init.d]# service mysqld_multi start 1

远程连接参考本文 3.3.3 章节

4.5 卸载mysql多实例

查询数据库状态

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf report

停止数据库

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf --password=123456 --user=root stop

如果配置了使用service控制mysql启动或关闭，删除service服务和脚本

[root@mysql2 ~]# chkconfig --del mysqld
[root@mysql2 ~]# rm -rf /etc/init.d/mysqld

关闭数据库

[root@mysql2 install]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf --password=123456 --user=root stop

如果配置了使用service控制mysql启动或关闭，删除service服务和脚本

[root@mysql2 ~]# chkconfig --del mysqld_multi 
[root@mysql2 ~]# rm -rf /etc/init.d/mysqld_multi

清除所有mysql文件

[root@mysql2 ~]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/spool/mail/mysql
/usr/lib64/mysql
/usr/local/mysql
/opt/mysql
/opt/mysql/mysql-5.7.39-linux-glibc2.12-x86_64/bin/mysql
/opt/mysql/mysql-5.7.39-linux-glibc2.12-x86_64/include/mysql
/data/mysql
/data/mysql/my3306/data/mysql
/data/mysql/my3307/data/mysql
/data/etc/selinux/targeted/active/modules/100/mysql
/date/selinux/targeted/active/modules/100/mysql
/date/etc/selinux/targeted/active/modules/100/mysql

删除查找出来文件，成功卸载mysql。

[root@mysql2 ~]# rm -rf /etc/selinux/targeted/active/modules/100/mysql/ /var/spool/mail/mysql /usr/lib64/mysql/ /usr/local/mysql/ /opt/mysql/ /data/mysql/ /date/selinux/targeted/active/modules/100/mysql
[root@mysql2 ~]# find / -name mysql

在这里插入图片描述

网络标签：

相关问题