文章最后更新时间:2025年06月12日
前言:
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
最终效果
文章版权声明:除非注明,否则均为柳三千运维录原创文章,转载或复制请以超链接形式并注明出处。