1.mysql5.7.44多实例【rocky9.4】

1.1.基础环境

安装依赖
yum -y install libaio numactl-libs ncurses-compat-libs

1.2.准备用户

groupadd -r mysql
useradd -r -g mysql -s /sbin/nologin mysql

1.3.准备安装包

此方法为二进制安装，需要提前下载二进制包

直接下载安装包，点击download

右击复制链接地址
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

1.4.软件环境

mkdir -p /data/softs
cd /data/softs

# 上传下载的安装包到/data/softs目录下
# 也可以使用wget
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

# 解压移动
tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql

1.5.环境变量设定

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh

1.6.相关目录结构设置

# 创建目录
mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}

[root@Rocky9-15 ~]# tree /mysql
/mysql
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
│   └── share
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
│   └── share
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket
    └── share

24 directories, 0 files

# 为目录赋予用户权限
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /mysql/

1.7.生成三个实例的初始数据

# 空密码初始化
for i in 6 7 8
do
    /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/mysql/330$i/data
done

1.8.创建主配置文件

for i in 6 7 8
do
cat > /mysql/330$i/etc/my.cnf <<-eof
[mysqld]
user=mysql
port=330$i
socket = /mysql/330$i/socket/mysql.sock
basedir = /mysql/330$i
datadir = /mysql/330$i/data
skip_name_resolve = 1
pid-file = /mysql/330$i/pid/mysqld.pid
log-error = /mysql/330$i/log/error.log
lc-messages-dir = /usr/local/mysql/share/english
lc-messages = en_US

[client]
socket = /mysql/330$i/socket/mysql.sock
eof
done

1.9.定制服务管理文件

下面两个服务脚本都可以使用

• 服务脚本一

for i in 6 7 8
do
cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf
ExecReload=/bin/kill -s HUP \$MAINPID
ExecStop=/bin/kill -s TERM \$MAINPID
LimitNOFILE = 10000
EOF
done

• 服务脚本二

for i in 6 7 8
do
cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf
ExecStop=/usr/local/mysql/bin/mysqladmin -uroot -p'Mysql.123456' -S /mysql/330$i/socket/mysql.sock
LimitNOFILE = 10000
EOF
done

1.10.更改文件属性

# 为目录赋予用户权限
chown -R mysql:mysql /mysql/

1.11.启动服务

# 重载配置
systemctl daemon-reload

for i in 6 7 8
do
systemctl enable --now mysqld330$i
done

1.12.登录数据库

[root@Rocky9-15 ~]# mysql -S /mysql/3306/socket/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> select version();
+-----------+
| version() |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@Rocky9-15 ~]# mysql -S /mysql/3307/socket/mysql.sock -e "select version();"
+-----------+
| version() |
+-----------+
| 5.7.44    |
+-----------+
[root@Rocky9-15 ~]# mysql -S /mysql/3308/socket/mysql.sock -e "select version();"
+-----------+
| version() |
+-----------+
| 5.7.44    |
+-----------+

1.13.设置登录密码

# 更改密码
for i in 6 7 8
do
mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456'
done

# 查看版本信息
for i in 6 7 8
do
mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();"
done

# 查看端口号
for i in 6 7 8
do
mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;"
done

2.mysql8.4.0多实例【rocky9.4】

2.1.基础环境

# 安装依赖
yum -y install libaio numactl-libs ncurses-compat-libs

2.2.准备用户

groupadd -r mysql
useradd -r -g mysql -s /sbin/nologin mysql

2.3.准备安装包

mkdir -p /data/softs

cd /data/softs

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

2.4.软件环境

cd /data/softs

tar xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

mv mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql

2.5.环境变量

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh

2.6.相关目录结构设置

mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}

[root@Rocky9-15 ~]# tree /mysql/
/mysql/
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   ├── share
│   └── socket
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   ├── share
│   └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    ├── share
    └── socket

24 directories, 0 files

# 为目录赋予用户权限
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /mysql/

2.7.生成三个实例的初始数据

# 空密码初始化
for i in 6 7 8
do
    /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/mysql/330$i/data
done

2.8.创建主配置文件

for i in 6 7 8
do
cat > /mysql/330$i/etc/my.cnf <<-eof
[mysql]
port = 330$i
socket = /mysql/330$i/socket/mysql.sock

[mysqld]
port = 330$i
mysqlx_port = 330$i0
mysqlx_socket = /mysql/330$i/socket/mysqlx.sock
basedir = /mysql/330$i
datadir = /mysql/330$i/data
socket = /mysql/330$i/socket/mysql.sock
pid-file = /mysql/330$i/pid/mysqld.pid
log-error = /mysql/330$i/log/error.log

eof
done

2.9.定制服务管理文件

for i in 6 7 8
do
cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf
ExecReload=/bin/kill -s HUP \$MAINPID
ExecStop=/bin/kill -s TERM \$MAINPID
LimitNOFILE = 10000
EOF
done

2.10.更改文件属性

# 为目录赋予用户权限
chown -R mysql:mysql /mysql/

2.11.启动服务

# 重载配置
systemctl daemon-reload

for i in 6 7 8
do
systemctl enable --now mysqld330$i
done

2.12.登录数据库

[root@Rocky9-15 ~]# mysql -S /mysql/3306/socket/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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> select version();
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@Rocky9-15 ~]# mysql -S /mysql/3307/socket/mysql.sock -e "select version();"
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
[root@Rocky9-15 ~]# mysql -S /mysql/3308/socket/mysql.sock -e "select version();"
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+

2.13.设置登录密码

# 设置密码
for i in 6 7 8
do
mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456'
done

# 查看版本信息
for i in 6 7 8
do
mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();"
done

# 查看端口号
for i in 6 7 8
do
mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;"
done

3.mysql5.6.50多实例【rocky9.4】

3.1.基础环境

yum install -y perl-Data-Dumper autoconf libaio  perl-Sys-Hostname ncurses-compat-libs

3.2.准备用户

groupadd -r mysql
useradd -r -g mysql -s /sbin/nologin mysql

3.3.准备安装包

mkdir -p /data/softs

cd /data/softs

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz

3.4.软件环境

cd /data/softs

tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql

3.5.环境变量

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh

3.6.相关目录结构设置

mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}

[root@Rocky9-18 softs]# tree /mysql/
/mysql/
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   ├── share
│   └── socket
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   ├── share
│   └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    ├── share
    └── socket

24 directories, 0 files

# 为目录赋予用户权限
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /mysql/

3.7.生成三个实例的初始数据

# 空密码初始化
for i in 6 7 8
do
    /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mysql/330$i/data
done

3.8.创建主配置文件

for i in 6 7 8
do
cat > /mysql/330$i/etc/my.cnf <<-eof
[mysqld]
port=330$i
user=mysql
socket = /mysql/330$i/socket/mysql.sock
basedir = /usr/local/mysql
datadir = /mysql/330$i/data
innodb_file_per_table=on
skip_name_resolve = on
symbolic-links=0

[client]
socket = /mysql/330$i/socket/mysql.sock

[mysqld_safe]
pid-file = /mysql/330$i/pid/mysqld.pid
log-error = /mysql/330$i/log/error.log
eof
done

3.9.定制服务管理文件

for i in 6 7 8
do
cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf
ExecReload=/bin/kill -s HUP \$MAINPID
ExecStop=/bin/kill -s TERM \$MAINPID
LimitNOFILE = 10000
EOF
done

3.10.更改文件属性

# 为目录赋予用户权限
chown -R mysql:mysql /mysql/

3.11.启动服务

# 重载配置
systemctl daemon-reload

for i in 6 7 8
do
systemctl enable --now mysqld330$i
done

3.12.登录数据库

[root@Rocky9-18 ~]# mysql -S /mysql/3306/socket/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.50 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> select version();
+-----------+
| version() |
+-----------+
| 5.6.50    |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

3.13.设置登录密码

# 设置密码
for i in 6 7 8
do
mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456'
done

# 查看版本信息
for i in 6 7 8
do
mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();"
done

# 查看端口号
for i in 6 7 8
do
mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;"
done

4.不同版本(5.6、5.7、8.0)的数据库服务多实例配置实现

4.1.多实例部署环境规划

4.2.基础环境

# 安装依赖
yum -y install libaio numactl-libs ncurses-compat-libs perl-Data-Dumper autoconf libaio  perl-Sys-Hostname ncurses-compat-libs

4.3.准备用户

groupadd -r mysql
useradd -r -g mysql -s /sbin/nologin mysql

4.4.准备安装包

mkdir -p /data/softs

cd /data/softs

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

4.5.软件环境

如果不想浪费下载时间，可以上传下载好的安装包

cd /data/softs

tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql3306

tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql3307

tar xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz
mv mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql3308

4.6.相关目录结构设置

mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}

# 为目录赋予用户权限
chown -R mysql:mysql /usr/local/mysql*
chown -R mysql:mysql /mysql/

4.7.生成三个实例的初始数据

由于每个实例初始化都要找环境变量，为了避免冲突
1.配置环境变量
2.初始化mysql脚本
3.初始化环境变量

下面最好一个步骤一个步骤执行

echo $PATH

echo 'PATH=/usr/local/mysql3306/bin:$PATH' > /etc/profile.d/mysql3306.sh
source /etc/profile.d/mysql3306.sh

/usr/local/mysql3306/scripts/mysql_install_db --basedir=/usr/local/mysql3306 --datadir=/mysql/3306/data
PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin"



echo 'PATH=/usr/local/mysql3307/bin:$PATH' > /etc/profile.d/mysql3307.sh
source /etc/profile.d/mysql3307.sh

/usr/local/mysql3307/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql3307 --datadir=/mysql/3307/data
PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin"



echo 'PATH=/usr/local/mysql3308/bin:$PATH' > /etc/profile.d/mysql3308.sh
source /etc/profile.d/mysql3308.sh
/usr/local/mysql3308/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql3308 --datadir=/mysql/3308/data
PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin"

4.8.环境变量--可省略

注意：环境变量仅对当前窗口有效，如果更换窗口，需要重新执行环境变量

echo 'PATH=/usr/local/mysql3306/bin:$PATH' > /etc/profile.d/mysql3306.sh
source /etc/profile.d/mysql3306.sh

echo 'PATH=/usr/local/mysql3307/bin:$PATH' > /etc/profile.d/mysql3307.sh
source /etc/profile.d/mysql3307.sh

echo 'PATH=/usr/local/mysql3308/bin:$PATH' > /etc/profile.d/mysql3308.sh
source /etc/profile.d/mysql3308.sh

4.9.创建主配置文件

cat > /mysql/3306/etc/my.cnf <<-eof
[mysqld]
port=3306
user=mysql
socket = /mysql/3306/socket/mysql.sock
basedir = /usr/local/mysql3306
datadir = /mysql/3306/data
innodb_file_per_table=on
skip_name_resolve = on
symbolic-links=0

[client]
socket = /mysql/3306/socket/mysql.sock

[mysqld_safe]
pid-file = /mysql/3306/pid/mysqld.pid
log-error = /mysql/3306/log/error.log
eof

cat > /mysql/3307/etc/my.cnf <<-eof
[mysqld]
user=mysql
port=3307
socket = /mysql/3307/socket/mysql.sock
basedir = /mysql/3307
datadir = /mysql/3307/data
skip_name_resolve = 1
pid-file = /mysql/3307/pid/mysqld.pid
log-error = /mysql/3307/log/error.log
lc-messages-dir = /usr/local/mysql3307/share/english
lc-messages = en_US

[client]
socket = /mysql/3307/socket/mysql.sock
eof

cat > /mysql/3308/etc/my.cnf <<-eof
[mysql]
port = 3308
socket = /mysql/3308/socket/mysql.sock

[mysqld]
port = 3308
mysqlx_port = 33080
mysqlx_socket = /mysql/3308/socket/mysqlx.sock
basedir = /mysql/3308
datadir = /mysql/3308/data
socket = /mysql/3308/socket/mysql.sock
pid-file = /mysql/3308/pid/mysqld.pid
log-error = /mysql/3308/log/error.log
eof

4.10.定制服务管理文件

for i in 6 7 8
do
cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF
[Unit]
Description=MySQL Community Server
Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql330$i/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf
ExecReload=/bin/kill -s HUP \$MAINPID
ExecStop=/bin/kill -s TERM \$MAINPID
LimitNOFILE = 10000
EOF
done

4.11.更改文件属性

# 为目录赋予用户权限
chown -R mysql:mysql /mysql/

4.12.启动服务

# 重载配置
systemctl daemon-reload

for i in 6 7 8
do
systemctl enable --now mysqld330$i
done

4.13.测试是否存活

for i in 6 7 8
do
    /usr/local/mysql330$i/bin/mysqladmin -S /mysql/330$i/socket/mysql.sock ping
done

4.13.登录数据库

注意：由于是多个版本的mysql，设置环境变量会发生冲突

所以，登录mysql使用绝对路径登录

/usr/local/mysql3306/bin/mysql -S /mysql/3306/socket/mysql.sock

/usr/local/mysql3307/bin/mysql -S /mysql/3307/socket/mysql.sock

/usr/local/mysql3307/bin/mysql -S /mysql/3307/socket/mysql.sock

4.14.设置登录密码

# 设置密码
for i in 6 7 8
do
    /usr/local/mysql330$i/bin/mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456'
done

# 查看版本信息
for i in 6 7 8
do
/usr/local/mysql330$i/bin/mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();"
done

# 查看端口号
for i in 6 7 8
do
/usr/local/mysql330$i/bin/mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;"
done

endl