mysql多实例部署

软件下载

[root@node1 ~]# cd /usr/src/
[root@node1 src]# ls
debug  kernels
[root@node1 src]# groupadd -r mysql
[root@node1 src]# useradd -M -s /sbin/nologin -g mysql mysql
[root@node1 src]# ls
debug  kernels  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz


[root@node1 src]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/


[root@node1 ~]# cd /usr/local/
[root@node1 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.37-linux-glibc2.12-x86_64  sbin  share  src
[root@node1 local]# mv mysql-5.7.37-linux-glibc2.12-x86_64 mysql
[root@node1 local]# ll
total 0
drwxr-xr-x. 2 root root   6 Jun 22  2021 bin
drwxr-xr-x. 2 root root   6 Jun 22  2021 etc
drwxr-xr-x. 2 root root   6 Jun 22  2021 games
drwxr-xr-x. 2 root root   6 Jun 22  2021 include
drwxr-xr-x. 2 root root   6 Jun 22  2021 lib
drwxr-xr-x. 3 root root  17 Jul  3 15:23 lib64
drwxr-xr-x. 2 root root   6 Jun 22  2021 libexec
drwxr-xr-x. 9 root root 129 Jul  3 15:55 mysql
drwxr-xr-x. 2 root root   6 Jun 22  2021 sbin
drwxr-xr-x. 5 root root  49 Jul  3 15:23 share
drwxr-xr-x. 2 root root   6 Jun 22  2021 src

修改目录属主 配置环境变量

[root@node1 local]# chown mysql.mysql mysql
[root@node1 local]# ll
total 0
drwxr-xr-x. 2 root  root    6 Jun 22  2021 bin
drwxr-xr-x. 2 root  root    6 Jun 22  2021 etc
drwxr-xr-x. 2 root  root    6 Jun 22  2021 games
drwxr-xr-x. 2 root  root    6 Jun 22  2021 include
drwxr-xr-x. 2 root  root    6 Jun 22  2021 lib
drwxr-xr-x. 3 root  root   17 Jul  3 15:23 lib64
drwxr-xr-x. 2 root  root    6 Jun 22  2021 libexec
drwxr-xr-x. 9 mysql mysql 129 Jul  3 15:55 mysql
drwxr-xr-x. 2 root  root    6 Jun 22  2021 sbin
drwxr-xr-x. 5 root  root   49 Jul  3 15:23 share
drwxr-xr-x. 2 root  root    6 Jun 22  2021 src


[root@node1 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

[root@node1 ~]# . /etc/profile.d/mysql.sh
[root@node1 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

创建各实例数据存放的目录

[root@node1 ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@node1 ~]# chown -R mysql.mysql /opt/data/
[root@node1 ~]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul  3 16:00 3306
drwxr-xr-x. 2 mysql mysql 6 Jul  3 16:00 3307
drwxr-xr-x. 2 mysql mysql 6 Jul  3 16:00 3308

初始化各实例

[root@node1 ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
[root@node1 ~]# echo 'k6%jwiqb6r4L' > 3306

[root@node1 ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
[root@node1 ~]# echo 'Own!WeuNe0L%' > 3307

[root@node1 ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
[root@node1 ~]# echo 'pEmmxrzWA7#)' > 3308

配置配置文件/etc/my.cnf

[root@node1 ~]# 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

安装perl

yum -y install perl

启动各实例

[root@node1 ~]#  mysqld_multi start 3307
[root@node1 ~]#  mysqld_multi start 3308
[root@node1 ~]#  mysqld_multi start 3306
[root@node1 ~]# ss -ant
State      Recv-Q     Send-Q           Local Address:Port           Peer Address:Port      Process     
LISTEN     0          128                    0.0.0.0:22                  0.0.0.0:*                     
ESTAB      0          0                192.168.80.22:22             192.168.80.1:56391                 
ESTAB      0          52               192.168.80.22:22             192.168.80.1:55780                 
LISTEN     0          80                           *:3306                      *:*                     
LISTEN     0          80                           *:3307                      *:*                     
LISTEN     0          80                           *:3308                      *:*                     
LISTEN     0          128                       [::]:22                     [::]:*  

初始化密码

[root@node1 ~]# mysql -uroot -p'k6%jwiqb6r4L' -S /tmp/mysql3306.sock
mysql> set password = password

设置好密码以此类推

用新的密码链接试试

[root@node1 ~]# mysql -uroot -p' '  -S /tmp/mysql3306.sock
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 3
Server version: 5.7.37 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@node1 ~]# mysql -uroot -p'  '  -h192.168.80.22 -P3306

同时连接2个不同实例的数据库进行操作

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
//在3306上面创建数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
//3307是没有的

设置开机自启

[root@node1 ~]# cp /usr/lib/systemd/system/sshd.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


# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
//关掉

[root@node1 ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/
//做一个软连接

[root@node1 ~]# systemctl status my3306
● my3306.service - 3306 server daemon
   Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-07-03 16:59:08 CST; 19s ago
  Process: 6559 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (code=exited, status=0/SUCCESS)
 Main PID: 6564 (mysqld_safe)
    Tasks: 28 (limit: 11175)
   Memory: 179.8M
   CGroup: /system.slice/my3306.service
           ├─6564 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3306 --port=3306 --sock>
           └─6703 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data/3306 --pl>

Jul 03 16:59:07 node1 systemd[1]: Starting 3306 server daemon...
Jul 03 16:59:08 node1 systemd[1]: Started 3306 server daemon.


[root@node1 ~]# systemctl enable my3306
Created symlink /etc/systemd/system/multi-user.target.wants/my3306.service → /usr/lib/systemd/system/my3306.service.