Mysql多实例安装(mysql5.6、5.7、8.0 三版本共存)

前言：

MySQL多实例是什么？

多实例是指在一台服务器上同时开启多个不同的数据库服务端口（例如3306、3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。

这些MySQL实例共用一个MySQL数据库安装程序，使用不同的my.cnf配置文件启动。在提供服务时，多实例MySQL在逻辑上各自独立，根据配置文件的设定获取服务器相应数量的资源。

为什么需要多实例？

• 最大有效利用服务器资源，节省成本
• 适用于资金有限但需要数据库独立服务和主从复制等技术的场景

局限性：

当某个数据库实例并发很高且SQL查询耗时时，会消耗大量系统资源（CPU、磁盘IO等），导致同一服务器的其它数据库实例响应变慢，无法像虚拟机一样完全隔离。

官方提供的是mysqld_multi管理方式（多个实例共用一个配置文件my.cnf），本文采用一个实例对应一个my.cnf配置文件的管理思路。

注意：确保实施环境是CentOS7新系统，未安装过MySQL数据库，否则易出现冲突导致失败！

准备三个版本的Mysql安装包

环境清理

# 查看是否自带的mysql
rpm -qa | grep -i mysql
rpm -qa | grep -i mariadb
mariadb-libs-5.5.68-1.el7.x86_64mariadb-5.5.68-1.el7.x86_64

# 删除mariadb
yum remove mariadb
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
rm -rf /etc/my.cnf

创建运行MySQL的系统用户

useradd -r -s /sbin/nologin mysql

创建数据目录、日志目录并授权

# 创建数据目录
[root@localhost src]# mkdir -p /data/mysql56/data
[root@localhost src]# mkdir -p /data/mysql57/data
[root@localhost src]# mkdir -p /data/mysql80/data

# 创建日志目录
[root@localhost src]# mkdir -p /data/mysql56/log
[root@localhost src]# mkdir -p /data/mysql57/log
[root@localhost src]# mkdir -p /data/mysql80/log

[root@localhost src]# chown -R mysql.mysql /data/
[root@localhost src]# ll /data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Sep 22 21:52 mysql56
drwxr-xr-x. 2 mysql mysql 6 Sep 22 21:52 mysql57
drwxr-xr-x. 2 mysql mysql 6 Sep 22 21:52 mysql80

MySQL 5.6 安装

# 解压MySQL5.6
[root@localhost local]#tar zxvf src/mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz

# 重命名安装目录
[root@localhost local]# mv mysql-5.6.30-linux-glibc2.5-x86_64/ mysql5.6

# 进入安装目录
[root@localhost local]# cd mysql5.6/

# 更改安装目录权限
[root@localhost mysql5.6]# chown -R mysql.mysql /usr/local/mysql5.6/

# 初始化数据库
[root@localhost mysql5.6]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql56/data --basedir=/usr/local/mysql5.6/

初始化报错处理

如果出现报错：FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db: Data::Dumper，请安装：

yum -y install autoconf

出现两个OK代表初始化成功

# 查看mysql5.6数据目录
[root@localhost mysql5.6]# ll /data/mysql56/data
total 110600
-rw-rw----. 1 mysql mysql 12582912 Sep 22 22:05 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Sep 22 22:05 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Sep 22 22:05 ib_logfile1
drwx------. 2 mysql mysql     4096 Sep 22 22:05 mysql
drwx------. 2 mysql mysql     4096 Sep 22 22:05 performance_schema
drwx------. 2 mysql mysql        6 Sep 22 22:05 test

编写配置文件my.cnf

[root@localhost mysql5.6]# vim my.cnf
[client]
port=3306
socket=/tmp/mysql56.sock

[mysqld]
socket=/tmp/mysql56.sock
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql5.6/
datadir=/data/mysql56/data
tmpdir=/tmp
log-error=/data/mysql56/log/mysqld56.log
pid-file=/data/mysql56/mysqld56.pid

修改MySQL 5.6启动脚本

vim /usr/local/mysql5.6/support-files/mysql.server

# 拷贝启动脚本
[root@localhost mysql5.6]# cp -a ./support-files/mysql.server /etc/init.d/mysql56

# 启动mysql
[root@localhost mysql5.6]# /etc/init.d/mysql56 start

修改管理员密码

# 因为配置文件已经指定sock路径在/tmp/mysql56.sock
[root@localhost mysql5.6]# ./bin/mysqladmin -S /tmp/mysql56.sock -uroot password '123456'
Warning: Using a password on the command line interface can be insecure.

[root@localhost mysql5.6]# ./bin/mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)  # 没有指定sock就会报错

# 指定sock访问
[root@localhost mysql5.6]# ./bin/mysql -S /tmp/mysql56.sock -uroot -p123456
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 2
Server version: 5.6.30 MySQL Community Server (GPL)
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.
mysql>

MySQL 5.7 安装

# 解压mysql5.7
[root@localhost local]# tar zxvf src/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

# 重命名安装目录并进入该目录
[root@localhost local]# mv mysql-5.7.39-linux-glibc2.12-x86_64/ mysql5.7
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql5.7/
[root@localhost local]# cd mysql5.7/
[root@localhost mysql5.7]

在安装目录下创建配置文件my.cnf

vim my.cnf
[client]
port=3307
socket=/tmp/mysql57.sock

[mysqld]
socket=/tmp/mysql57.sock
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3307
basedir=/usr/local/mysql5.7/
datadir=/data/mysql57/data
tmpdir=/tmp
log-error=/data/mysql57/log/mysqld57.log
pid-file=/data/mysql57/mysqld57.pid

MySQL 5.7初始化

# mysql5.7初始化
[root@localhost mysql5.7]#/usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql5.7/ --datadir=/data/mysql57/data

修改MySQL 5.7启动脚本

vim /usr/local/mysql5.7/support-files/mysql.server

拷贝启动脚本

[root@localhost mysql5.7]# cp -a ./support-files/mysql.server /etc/init.d/mysql57

启动MySQL 5.7

查看临时密码

[root@localhost mysql5.7]# cat /data/mysql57/log/mysqld57.log  | grep password
2022-09-23T05:25:47.783251Z 1 [Note] A temporary password is generated for root@localhost: nd>phWNwB7YS
2022-09-23T05:35:41.498538Z 0 [Note] Shutting down plugin 'sha256_password'
2022-09-23T05:35:41.498540Z 0 [Note] Shutting down plugin 'mysql_native_password'

重置密码

[root@localhost mysql5.7]# ./bin/mysql -S /tmp/mysql57.sock -uroot -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.39
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> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

MySQL 8.0 安装

# 解压mysql8.0
[root@localhost local]# tar xvf src/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz

# 重命名安装目录并进入该目录
[root@localhost local]# mv mysql-8.0.30-linux-glibc2.12-x86_64/ mysql8
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql8/
[root@localhost local]# cd mysql8

新建配置文件my.cnf

vim my.cnf
[client]
port=3308
socket=/tmp/mysql8.sock

[mysqld]
socket=/tmp/mysql8.sock
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
skip-name-resolve
user=mysql
port=3308
basedir=/usr/local/mysql8/
datadir=/data/mysql80/data
tmpdir=/tmp
log-error=/data/mysql80/log/mysqld8.log
pid-file=/data/mysql80/mysqld8.pid

初始化数据库

[root@localhost mysql8]# /usr/local/mysql8/bin/mysqld --defaults-file=/usr/local/mysql8/my.cnf --initialize --datadir=/data/mysql80/data --basedir=/usr/local/mysql8

修改MySQL 8.0启动脚本

vim /usr/local/mysql8/support-files/mysql.server

# 拷贝启动脚本
[root@localhost mysql8]# cp ./support-files/mysql.server /etc/init.d/mysql8

启动MySQL 8.0

[root@localhost mysql8]# /etc/init.d/mysql8 start
Starting MySQL... SUCCESS!

查看临时密码

[root@localhost mysql8]# cat /data/mysql80/log/mysqld8.log | grep password
2022-09-23T06:23:14.874662Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: j:t+<fs:?1Gw

修改密码

[root@localhost mysql8]# mysql -S /tmp/mysql8.sock -uroot -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.30
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> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.03 sec)

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

三个实例已成功开启

MySQL实例管理优化

1. 环境变量配置（使用默认的MySQL 5.7变量）

[root@localhost mysql8]# vim /etc/profile
[root@localhost mysql8]# source /etc/profile

2. 通过sock访问各个实例

[root@localhost]# mysql -S /tmp/mysql56.sock -uroot -p

[root@localhost]# mysql -S /tmp/mysql57.sock -uroot -p

[root@localhost]# mysql -S /tmp/mysql8.sock -uroot -p

3. 使用alias别名简化操作

# 设置alias别名
[root@localhost ~]# alias mysql5.6='mysql -S /tmp/mysql56.sock'
[root@localhost ~]# alias mysql5.7='mysql -S /tmp/mysql57.sock'
[root@localhost ~]# alias mysql8='mysql -S /tmp/mysql8.sock'

# 使alias永久生效
[root@localhost mysql8]# vim /etc/bashrc
[root@localhost mysql8]# source /etc/bashrc

最终效果