Mysql数据库的多实例部署

mysql多实例部署

先进行软件下载

上传二进制格式的mysql软件包
[root@cjy ~]# ls
anaconda-ks.cfg  mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz

配置用户和组并解压二进制程序至/usr/local下

创建用户和组
[root@cjy ~]# useradd -r -s /sbin/nologin -M mysql解压软件至/usr/local/
[root@cjy ~]# tar xf mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
[root@cjy ~]# cd /usr/local/
[root@cjy local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-8.0.35-linux-glibc2.28-x86_64  share
[root@cjy local]# mv mysql-8.0.35-linux-glibc2.28-x86_64 mysql
[root@cjy local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src修改目录/usr/local/mysql的属主属组
[root@cjy local]# chown -R mysql.mysql mysql
[root@cjy local]# ll
total 0
drwxr-xr-x. 2 root  root    6 May 16  2022 bin
drwxr-xr-x. 2 root  root    6 May 16  2022 etc
drwxr-xr-x. 2 root  root    6 May 16  2022 games
drwxr-xr-x. 2 root  root    6 May 16  2022 include
drwxr-xr-x. 2 root  root    6 May 16  2022 lib
drwxr-xr-x. 3 root  root   17 Dec  4 15:58 lib64
drwxr-xr-x. 2 root  root    6 May 16  2022 libexec
drwxr-xr-x. 9 mysql mysql 129 Dec 10 16:07 mysql
drwxr-xr-x. 2 root  root    6 May 16  2022 sbin
drwxr-xr-x. 5 root  root   49 Dec  4 15:58 share
drwxr-xr-x. 2 root  root    6 May 16  2022 src配置环境变量
[root@cjy ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@cjy ~]# source /etc/profile.d/mysql.sh
[root@cjy ~]# which mysql
/usr/local/mysql/bin/mysql

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

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}[root@localhost ~]# chown -R mysql.mysql /opt/data/[root@localhost ~]# ll /opt/data/
总用量 0
drwxr-xr-x 2 mysql mysql 6 5月   9 21:24 3306
drwxr-xr-x 2 mysql mysql 6 5月   9 21:24 3307
drwxr-xr-x 2 mysql mysql 6 5月   9 21:24 3308[root@localhost ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 33083 directories, 0 files

初始化各实例

初始化3306实例
[root@cjy ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
2023-12-10T08:15:59.163216Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 5030
2023-12-10T08:15:59.170851Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-10T08:15:59.416073Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-10T08:16:01.622124Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rsQufqdY3G:8
[root@cjy ~]# echo 'rsQufqdY3G:8' > 3306初始化3307实例
[root@cjy ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2023-12-10T08:16:42.014859Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 5072
2023-12-10T08:16:42.022012Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-10T08:16:42.217146Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-10T08:16:43.678076Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: p!)hzaard9pG
[root@cjy ~]# echo 'p!)hzaard9pG' > 3307初始化3308实例
[root@cjy ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2023-12-10T08:17:15.877075Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 5114
2023-12-10T08:17:15.883292Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-10T08:17:16.080413Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-10T08:17:17.657816Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: w::tf_4x;46J
[root@cjy ~]# echo 'w::tf_4x;46J' > 3308[root@cjy ~]# ls
3306  3307  3308  anaconda-ks.cfg  mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz

安装perl

[root@cjy ~]# yum -y install perl

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

[root@cjy ~]# vim /etc/my.cnf
[root@cjy ~]# cat /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@cjy ~]# mysqld_multi start 3306
[root@cjy ~]# ss -antl
State   Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process  
LISTEN  0        128              0.0.0.0:22             0.0.0.0:*              
LISTEN  0        128                 [::]:22                [::]:*              
LISTEN  0        70                     *:33060                *:*              
LISTEN  0        151                    *:3306                 *:*              
[root@cjy ~]# mysqld_multi start 3307
[root@cjy ~]# ss -antl
State   Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process  
LISTEN  0        128              0.0.0.0:22             0.0.0.0:*              
LISTEN  0        128                 [::]:22                [::]:*              
LISTEN  0        70                     *:33060                *:*              
LISTEN  0        151                    *:3307                 *:*              
LISTEN  0        151                    *:3306                 *:*              
[root@cjy ~]# mysqld_multi start 3308
[root@cjy ~]# ss -antl
State   Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process  
LISTEN  0        128              0.0.0.0:22             0.0.0.0:*              
LISTEN  0        128                 [::]:22                [::]:*              
LISTEN  0        70                     *:33060                *:*              
LISTEN  0        151                    *:3308                 *:*              
LISTEN  0        151                    *:3307                 *:*              
LISTEN  0        151                    *:3306                 *:*              

初始化密码

[root@cjy ~]# ls
3306  3307  3308  anaconda-ks.cfg  mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz
[root@cjy ~]# cat 3306
rsQufqdY3G:8
[root@cjy ~]# mysql -uroot -p'rsQufqdY3G:8' -P3306 -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 9
Server version: 8.0.35Copyright (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> alter user root@'localhost' identified with mysql_native_password by 'Passsw0rd@_';
Query OK, 0 rows affected (0.00 sec)mysql> quit
Bye[root@cjy ~]# mysql -uroot -pPassw0rd@_ -P3306 -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 11
Server version: 8.0.35 MySQL Community Server - GPLCopyright (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> quit
Bye[root@cjy ~]# cat 3307
p!)hzaard9pG
[root@cjy ~]# mysql -uroot -p'p!)hzaard9pG' -P3307 -S /tmp/mysql3307.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 8
Server version: 8.0.35Copyright (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> alter user root@'localhost' identified with mysql_native_password by 'Passw0rd@_';
Query OK, 0 rows affected (0.00 sec)mysql> quit
Bye
[root@cjy ~]# mysql -uroot -pPassw0rd@_ -P3307 -S /tmp/mysql3307.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 8
Server version: 8.0.35Copyright (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>quit
Bye[root@cjy ~]# cat 3308
w::tf_4x;46J
[root@cjy ~]# mysql -uroot -p'w::tf_4x;46J' -P3308 -S /tmp/mysql3308.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 8
Server version: 8.0.35Copyright (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> alter user root@'localhost' identified with mysql_native_password by 'Passw0rd@_';
Query OK, 0 rows affected (0.00 sec)mysql> quit
Bye
[root@cjy ~]# mysql -uroot -pPassw0rd@_ -P3308 -S /tmp/mysql3308.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 9
Server version: 8.0.35 MySQL Community Server - GPLCopyright (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> quit
Bye

停止端口号

[root@cjy ~]# ss -antl
State     Recv-Q    Send-Q       Local Address:Port        Peer Address:Port    Process    
LISTEN    0         128                0.0.0.0:22               0.0.0.0:*                  
LISTEN    0         128                   [::]:22                  [::]:*                  
LISTEN    0         70                       *:33060                  *:*                  
LISTEN    0         151                      *:3308                   *:*                  
LISTEN    0         151                      *:3307                   *:*                  
LISTEN    0         151                      *:3306                   *:*                  
[root@cjy ~]# kill -9 $(ps -ef|grep -v 'grep' | grep 3306 | awk '{print $2}')
[root@cjy ~]# kill -9 $(ps -ef|grep -v 'grep' | grep 3307 | awk '{print $2}')
[root@cjy ~]# kill -9 $(ps -ef|grep -v 'grep' | grep 3308 | awk '{print $2}')
[root@cjy ~]# ss -antl
State     Recv-Q    Send-Q       Local Address:Port        Peer Address:Port    Process    
LISTEN    0         128                0.0.0.0:22               0.0.0.0:*                  
LISTEN    0         128                   [::]:22                  [::]:*                  

[root@cjy ~]# cd /usr/lib/systemd/system
[root@cjy system]# cp sshd.service mysql3306.service
[root@cjy system]# vim mysql3306.service 
[root@cjy system]# cat mysql3306.service 
[Unit]
Description=mysql 3306 server daemon
After=network.target[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start3306
ExecStop=kill -9 $(ps -ef|grep -v 'grep' | grep 3306 | awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID[Install]
WantedBy=multi-user.target
[root@cjy system]# cp mysql3306.service mysql3307.service
[root@cjy system]# cp mysql3306.service mysql3308.service
[root@cjy system]# vim mysql3307.service 
[root@cjy system]# cat mysql3307.service 
[Unit]
Description=mysql 3307 server daemon
After=network.target[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start3307
ExecStop=kill -9 $(ps -ef|grep -v 'grep' | grep 3307 | awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID[Install]
WantedBy=multi-user.target[root@cjy system]# vim mysql3308.service 
[root@cjy system]# cat mysql3308.service 
[Unit]
Description=mysql 3308 server daemon
After=network.target[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start3308
ExecStop=kill -9 $(ps -ef|grep -v 'grep' | grep 3308 | awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID[Install]
WantedBy=multi-user.target

[root@cjy ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/
[root@cjy ~]# which my_print_defaults
/usr/local/mysql/bin/my_print_defaults
[root@cjy ~]# systemctl daemon-reload
[root@cjy ~]# systemctl start mysql3306
[root@cjy ~]# systemctl start mysql3307
[root@cjy ~]# systemctl start mysql3308
再设置开机自启
[root@cjy ~]# systemctl enable mysql3306
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3306.service → /usr/lib/systemd/system/mysql3306.service.
[root@cjy ~]# systemctl enable mysql3307
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3307.service → /usr/lib/systemd/system/mysql3307.service.
[root@cjy ~]# systemctl enable mysql3308
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3308.service → /usr/lib/systemd/system/mysql3308.service.
[root@cjy ~]# systemctl status mysql3306