mysql多个实例部署方法及两种启动方式

咖啡调调。

已于 2022-10-24 12:57:23 修改

阅读量492

点赞数

分类专栏： MySQL 文章标签： mysql linux centos 1024程序员节

于 2022-07-29 16:57:24 首次发布

本文链接：https://blog.csdn.net/qq_65998623/article/details/126059699

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL多实例

文章目录

MySQL多实例
@[toc]
1. 软件下载
2. 配置用户并解压
3. 创建目录
4. 初始化
5. 编写配置文件
6. 启动实例
7. 初始化密码

1. 软件下载

使用wget命令进行下载
最新下载链接：https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

//此处已经提前下载好了
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug  kernels
[root@localhost src]# ls
debug  kernels  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]#

2. 配置用户并解压

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

//配置用户
[root@localhost src]# useradd -r -M -s /sbin/nologin mysql
 
//解压软件包
[root@localhost src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@localhost src]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.38-linux-glibc2.12-x86_64  share

//做软链接并且修改属主属组
[root@localhost src]# cd /usr/local/
[root@localhost local]# ln -s mysql-5.7.38-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ll
total 0
······
lrwxrwxrwx. 1 root root  35 Jul 29 14:41 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
······
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
total 0
······
lrwxrwxrwx. 1 mysql mysql  35 Jul 29 14:41 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Jul 29 14:35 mysql-5.7.38-linux-glibc2.12-x86_64
······
[root@localhost local]# 

//配置环境变量以及头文件
[root@localhost local]# cd mysql
[root@localhost mysql]# ls
LICENSE  README  bin  docs  include  lib  man  share  support-files
[root@localhost mysql]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
[root@localhost mysql]# source  /etc/profile.d/mysql.sh 
[root@localhost mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@localhost mysql]# chown -R mysql.mysql /usr/include/mysql
[root@localhost mysql]# ll -d /usr/include/mysql
lrwxrwxrwx. 1 mysql mysql 25 Jul 29 14:49 /usr/include/mysql -> /usr/local/mysql/include/
[root@localhost mysql]# 
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost mysql]# ldconfig 
[root@localhost mysql]# vim /etc/man_db.conf 
······
23 MANDATORY_MANPATH                       /usr/local/mysql/man

3. 创建目录

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll -d /opt/data/
drwxr-xr-x. 5 mysql mysql 42 Jul 29 15:01 /opt/data/
[root@localhost ~]#

4. 初始化

初始化各实例

//初始化3306实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
2022-07-29T07:37:44.660713Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:37:44.811333Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:37:44.836720Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:37:44.893189Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 55eedcd9-0f11-11ed-b732-000c29eef386.
2022-07-29T07:37:44.894013Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:37:45.179213Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:37:45.179228Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:37:45.179709Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:37:45.303870Z 1 [Note] A temporary password is generated for root@localhost: dffLm,F4eVfq
[root@localhost ~]# echo 'dffLm,F4eVfq' > 3306_pass

//初始化3307实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2022-07-29T07:39:36.277636Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:39:36.499922Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:39:36.538721Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:39:36.592811Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9882e06a-0f11-11ed-8607-000c29eef386.
2022-07-29T07:39:36.593540Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:39:36.741378Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:39:36.741391Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:39:36.741741Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:39:36.831493Z 1 [Note] A temporary password is generated for root@localhost: wq1wbqcw*ayK
[root@localhost ~]# echo 'wq1wbqcw*ayK' > 3307_pass

//初始化3308实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2022-07-29T07:40:13.572516Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:40:13.790332Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:40:13.818876Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:40:13.875676Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: aebbca4d-0f11-11ed-8dbb-000c29eef386.
2022-07-29T07:40:13.876300Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:40:14.088693Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:40:14.088706Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:40:14.089049Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:40:14.180830Z 1 [Note] A temporary password is generated for root@localhost: #<6?AKGgAWoh
[root@localhost ~]# echo '#<6?AKGgAWoh' > 3308_pass

[root@localhost ~]# ls
3306_pass  3307_pass  3308_pass  anaconda-ks.cfg

5. 编写配置文件

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

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe	//定义mysqld程序命令存放路径
mysqladmin = /usr/local/mysql/bin/mysqladmin	//定义mysqladmin程序命令存放路径

[mysqld3306]
datadir = /opt/data/3306	//定义数据目录存放路径
port = 3306	//定义端口
socket = /tmp/mysql3306.sock	//定义套接字
pid-file = /opt/data/3306/mysql_3306.pid	//定义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 ~]#

6. 启动实例

启动会弹出报错信息，需要安装perl

[root@localhost ~]# mysqld_multi start 3306
-bash: /usr/local/mysql/bin/mysqld_multi: /usr/bin/perl: bad interpreter: No such file or directory
[root@localhost ~]# dnf -y install perl

再次进行启动

[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# ss -anlt
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        80                     *:3306                *:*              
LISTEN   0        80                     *:3307                *:*              
LISTEN   0        80                     *:3308                *:*              
LISTEN   0        128                 [::]:22               [::]:*

这种启动方式启动可以正常查看到端口，但是在进行stop停止的时候，可以看到端口依然是存在的，所以并不推荐这种启动方式，可以写一个systemctl配置文件，以这种方式来实现启动，停止等一系列操作。

systemctl启动

[root@localhost ~]# cp /usr/lib/systemd/system//sshd.service /usr/lib/systemd/system/3306.service
[root@localhost ~]# vim /usr/lib/systemd/system/3306.service 
[root@localhost ~]# cat /usr/lib/systemd/system/3306.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 ~]# cp /usr/lib/systemd/system/3306.service /usr/lib/systemd/system/3307.service
[root@localhost ~]# cp /usr/lib/systemd/system/3306.service /usr/lib/systemd/system/3308.service
[root@localhost ~]# vim /usr/lib/systemd/system/3307.service
[root@localhost ~]# cat /usr/lib/systemd/system/3307.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
[root@localhost ~]# vim /usr/lib/systemd/system/3308.service
[root@localhost ~]# cat /usr/lib/systemd/system/3308.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
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# vim /etc/selinux/config
SELINUX=disabled
[root@localhost ~]# setenforce 0
[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin//my_print_defaults
[root@localhost ~]# systemctl start 3306
[root@localhost ~]# systemctl start 3307
[root@localhost ~]# systemctl start 3308
[root@localhost ~]# ss -anlt
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        80                     *:3306                *:*              
LISTEN   0        80                     *:3307                *:*              
LISTEN   0        80                     *:3308                *:*              
LISTEN   0        128                 [::]:22               [::]:*              
[root@localhost ~]# systemctl stop 3306
[root@localhost ~]# systemctl stop 3307
[root@localhost ~]# systemctl stop 3308
[root@localhost ~]# ss -anlt
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               [::]:*

7. 初始化密码

登录各实例修改临时密码

[root@localhost ~]# ls
3306_pass  3307_pass  3308_pass  anaconda-ks.cfg
[root@localhost ~]# cat 3306_pass 
dffLm,F4eVfq
[root@localhost ~]# mysql -uroot -p'dffLm,F4eVfq' -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 2
Server version: 5.7.38

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> set password = password('123!com');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# 
[root@localhost ~]# cat 3307_pass
wq1wbqcw*ayK
[root@localhost ~]# mysql -uroot -p'wq1wbqcw*ayK' -S /tmp/mysql3307.sock -e 'set password = password("wangqing123!");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# cat 3308_pass
#<6?AKGgAWoh
[root@localhost ~]# mysql -uroot -p'#<6?AKGgAWoh' -S /tmp/mysql3308.sock -e 'set password = password("wangqing123!");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#

报错解决办法

//如果登录弹出以下报错信息
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
//就需要安装一个包
[root@localhost ~]# dnf provides libncurses.so.5
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:23:43 ago on Fri Jul 29 15:36:58 2022.
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : BaseOS
Matched from:
Provide    : libncurses.so.5

[root@localhost ~]# dnf -y install ncurses-compat-libs*
······
                                   

Complete!