MySQL多实例部署
mysql多实例部署
什么是MySQL多实例？
简单地说，MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306、3307、3308），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。
举例：
假如你买了一间房子，房子相当于mysql多实例，房子里面有很多卧室，每一间卧室相当于一个实例，整间房子就是一个服务器。
什么场景下用多实例
多实例就是一台服务器上装两个数据库、或多个。为什么？什么场景下用到？
假如，你刚成立一个公司规模很小，你买了一台服务器，服务器里装了一个数据库，但是有三个项目都在用这一个服务器，这就导致有一个问题？比如A项目和B项目正常运行，C项目开发了新的代码，要上线，要更新，这时候就需要重启数据库，如果重启数据库，A项目跟B项目都会跟着重启，就会受影响。
这时候就需要用到多实例
一个实例配套一个项目，顾不干涉。
说白了就是公司的资源紧张，你有三个项目，但是呢你只有一个服务器，这一个服务器要为三个项目提供服务，这时候如果你不用多实例来部署，一个数据库里面跑三个项目，你重启一个数据库 ，其余的也会跟着一块重启，这不是我们想要的结果。
有时候需要把数据库停掉，但不是停掉所有数据库，只需要停掉你需要停的那一个。多实例能满足这一需求。
软件下载及配置安装
MySQL官网https://www.mysql.com/
往下滑看到MySQL Community (GPL) Downloads »点击进去
点击进来之后看到Download Archives 点击进去
看到MySQL Community Server 点击
点击Download下载
# 下载二进制格式的mysql软件包
[root@localhost ~]# cd /opt/
[root@localhost opt]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@localhost opt]# ls
mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz配置用户和组并解压二进制程序至/usr/local下
# 创建一个系统用户，没有家目录，不需要登录
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql
[root@localhost ~]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)
# 解压软件至/usr/local/
[root@localhost opt]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql-5.7.37-linux-glibc2.12-x86_64 sbin share src
# 重新命名为MySQL
[root@localhost local]# mv mysql-5.7.37-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
# 修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# ll mysql -d
drwxr-xr-x. 9 mysql mysql 129 Jul 2 13:38 mysql
# 配置环境变量
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# which mysqld
/usr/local/mysql/bin/mysqld
# 配置include
[root@localhost ~]# ln -s /usr/local/mysql/include /usr/include/mysql
# 配置man文档
[root@localhost ~]# vi /etc/man_db.conf
MANDATORY_MANPATH /usr/local/share/man
MANDATORY_MANPATH /usr/local/mysql/man # 添加这一行
# 配置库文件
[root@localhost ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@localhost ~]# ldconfig # 让其生效创建各实例数据存放的目录
# 创建三个实例 (多实例就是两个以上，你创建多少个都可以前提你的服务器能跑得动)
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 2 13:46 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 2 13:46 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 2 13:46 3308
[root@localhost ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308
3 directories, 0 files初始化各实例
# 初始化3306实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
.....省略N
root@localhost: aiC<pMc.8gu4 # 密码
[root@localhost ~]# echo 'aiC<pMc.8gu4' > 3306_pass # 把密码保存到3306这个文件中
# 初始化3307实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
......省略N
root@localhost: a:%lryeaZ5ei
[root@localhost ~]# echo 'a:%lryeaZ5ei' > 3307_pass # 把密码保存到3307这个文件中
# 初始化3308实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
.......省略N
root@localhost: _KjSCefj00QY
[root@localhost ~]# echo '_KjSCefj00QY' > 3308_pass # 把密码保存到3308这个文件中安装perl
[root@localhost ~]# dnf -y install perl配置配置文件/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log
[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log
[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log
# 如果以后更改数据存放的位置，就更改相应的位置启动各实例
[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 :::3307 :::*
LISTEN 0 80 :::3308 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*初始化密码
[root@localhost ~]# ls
3306_pass 3307_pass 3308_pass anaconda-ks.cfg
# 登录的情况下修改密码
[root@localhost ~]# cat 3306_pass
aiC<pMc.8gu4
[root@localhost ~]# mysql -uroot -p'aiC<pMc.8gu4' -S /tmp/mysql3306.sock
.....省略N
mysql> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>quit
# 使用新密码登录
[root@localhost ~]# mysql -uroot -p'runtime123!' -h127.0.0.1 -P3306
......省略N
mysql>
# 不登录的情况下修改密码
[root@localhost ~]# cat 3307_pass
a:%lryeaZ5ei
[root@localhost ~]# mysql -uroot -p'a:%lryeaZ5ei' -S /tmp/mysql3307.sock -e 'set password = password("tkl9639@G");' --connect-expired-password
# 使用新密码登录
[root@localhost ~]# mysql -uroot -p'tkl9639@G' -h127.0.0.1 -P3307
......省略N
mysql>
[root@localhost ~]# cat 3308_pass
_KjSCefj00QY
[root@localhost ~]# mysql -uroot -p'_KjSCefj00QY' -S /tmp/mysql3308.sock -e 'set password = password("runtime123!");' --connect-expired-password
# 使用新密码登录
[root@localhost ~]# mysql -uroot -p'runtime123!' -h127.0.0.1 -P3308
......省略N
mysql>测试
# 在3306上创建tom数据库看3307和3308有没有，没有就说明它们之间是隔离的，如果有的话它们是同一个数据库
# 在3306 操作(不登录的情况下操作)
[root@localhost ~]# mysql -uroot -pruntime123! -S /tmp/mysql3306.sock -e 'create database tom'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pruntime123! -S /tmp/mysql3306.sock -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tom # 创建好了 |
+--------------------+
# 在3307 和3308 查看
## 3307 没有
[root@localhost ~]# mysql -uroot -ptkl9639@G -S /tmp/mysql3307.sock -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
## 3308 也没有
[root@localhost ~]# mysql -uroot -pruntime123! -S /tmp/mysql3308.sock -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
### 说明它们之间是隔离的
# 一个项目用一个实例
配置开机自启
# 3306 的开机自启
# 复制一份，在里面修改
[root@localhost ~]# cp /usr/lib/systemd/system/sshd.service /usr/lib/systemd/system/my3306.service
[root@localhost ~]# cat /usr/lib/systemd/system/my3306.service
[Unit]
Description=3306 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306
ExecStop=ps -ef|grep 3306|grep -v grep|awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@localhost ~]#
# 复制
[root@localhost system]# cp my3306.service my3307.service
[root@localhost system]# cp my3306.service my3308.service
# 3307 修改service文件
[root@localhost system]# cat my3307.service
[Unit]
Description=3307 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3307
ExecStop=ps -ef|grep 3307|grep -v grep|awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
# 3308 修改service文件
[root@localhost system]# cat my3308.service
[Unit]
Description=3308 server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3308
ExecStop=ps -ef|grep 3308|grep -v grep|awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
# 重新加载
[root@localhost ~]# systemctl daemon-reload
# 设置3306开机自启、并立马启动
[root@localhost ~]# systemctl enable --now my3306
[root@localhost ~]# systemctl status my3306
● my3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3306.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2022-07-02 16:26:41 CST; 8s ago
Process: 2337 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (code=exited, status=0/SUCCESS)
......省略
# 设置3307开机自启、并立马启动
[root@localhost ~]# systemctl enable --now my3307
Created symlink /etc/systemd/system/multi-user.target.wants/my3307.service → /usr/lib/systemd/system/my3307.service.
[root@localhost ~]# systemctl status my3307
● my3307.service - 3307 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3307.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2022-07-02 16:28:44 CST; 3s ago
Process: 2527 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3307 (code=exited, status=0/SUCCESS)
....省略
# 设置3308开机自启、并立马启动
[root@localhost ~]# systemctl enable --now my3308
Created symlink /etc/systemd/system/multi-user.target.wants/my3308.service → /usr/lib/systemd/system/my3308.service.
[root@localhost ~]# systemctl status my3308
● my3308.service - 3308 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3308.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2022-07-02 16:30:17 CST; 2s ago
Process: 2716 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3308 (code=exited, status=0/SUCCESS)
....省略
安装登录时报错
# 登录时报错
[root@localhost ~]# mysql -uroot -p'OL;CyL6)h5Do'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
# 需要这个库文件libncurses.so.5
# 查找这个库由哪个包提供
[root@localhost ~]# dnf provides libncurses.so.5
Last metadata expiration check: 0:35:59 ago on Tue 28 Jun 2022 04:33:57 PM CST.
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo : BaseOS
Matched from:
Provide : libncurses.so.5
# 安装 ncurses-compat-libs (再去登录ok)
[root@localhost ~]# dnf -y install ncurses-compat-libs
写service文件让它开机自启报错信息
[root@localhost ~]# systemctl status my3306.service
Unit my3306.service could not be found. # 找不到这个my3306.service
# 解决
关闭防火墙，关闭SElinux
[root@localhost ~]# systemctl disable --now firewalld
# 临时关闭SElinux
[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Permissive
# 永久关闭SElinux
[root@localhost ~]# head -7 /etc/selinux/config | tail -1
SELINUX=disabled
[root@localhost ~]# systemctl status my3306.service
● my3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled; vendor preset: disabled)
Active: inactive (dead)
# 使用service文件启动不了
[root@localhost ~]# systemctl start my3306
Job for my3306.service failed because the control process exited with error code.
See "systemctl status my3306.service" and "journalctl -xe" for details.
#查看问题
[root@localhost ~]# systemctl status my3306.service
● my3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Sat 2022-07-02 15:50:32 CST; 1min 8s ago
Process: 2040 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (code=exited, status=1/FAILURE)
Jul 02 15:50:32 localhost.localdomain mysqld_multi[2040]: Please make sure you have this command available and
Jul 02 15:50:32 localhost.localdomain mysqld_multi[2040]: in your path. The command is available from the latest
Jul 02 15:50:32 localhost.localdomain mysqld_multi[2040]: MySQL distribution.
Jul 02 15:50:32 localhost.localdomain mysqld_multi[2040]: ABORT: Can't find command 'my_print_defaults'.
Jul 02 15:50:32 localhost.localdomain mysqld_multi[2040]: This command is available from the latest MySQL
Jul 02 15:50:32 localhost.localdomain mysqld_multi[2040]: distribution. Please make sure you have the command
Jul 02 15:50:32 localhost.localdomain mysqld_multi[2040]: in your PATH.
# 找不到my_print_defaults
......省略
解决
# 使用软链接让它找到，再试能否启动
[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/
[root@localhost ~]# systemctl start my3306
[root@localhost ~]# systemctl status my3306
● my3306.service - 3306 server daemon
Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled; vendor preset: disabled)
Active: active (running) since Sat 2022-07-02 16:12:34 CST; 7s ago
Process: 2077 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (code=exited, status=0/SUCCESS)
....省略
7551
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
