mysql多实例部署

已于 2022-08-12 10:39:16 修改
阅读量182 收藏
点赞数
分类专栏： MySQL数据库 文章标签： mysql linux
于 2022-07-30 21:10:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_52813907/article/details/126077779
版权

mysql多实例部署

多实例就是在一台服务器上同时开启多个不同的数据库服务端口（例如3306、3307），同时运行多个MYSQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。
软件下载

优点:

MySQL多实例，可以通过多个端口向用户提供服务，充分利用一台服务器的闲置资源

缺点：

无论是多少个端口提供服务，但始终使用的是一台服务器的资源，所以当访问量过大时，依然存在高并发的问题

//下载二进制格式的mysql软件包
[root@yxt ~]# cd /usr/src/
[root@yxt src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@yxt src]# ls
debug  kernels  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

//配置用户和组并解压二进制程序至/usr/local下
[root@yxt src]# groupadd -r mysql
[root@yxt src]# useradd -M -s /sbin/nologin -g mysql mysql

//解压软件至/usr/local/
[root@yxt src]# tar -xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@yxt src]# ls /usr/local/
apache    bin    include  libexec                              sbin
apr       etc    lib      mysql-5.7.38-linux-glibc2.12-x86_64  share
apr-util  games  lib64    pcre 
[root@yxt src]# cd /usr/local/
[root@yxt local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'[root@yxt local]# ls
apache    etc      lib64                                pcre
apr       games    libexec                              sbin
apr-util  include  mysql                                share
bin       lib      mysql-5.7.38-linux-glibc2.12-x86_64  src

//修改目录/usr/local/mysql的属主属组
[root@yxt local]# chown -R mysql:mysql /usr/local/mysql
[root@yxt local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 30 18:09 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/

//添加环境变量
[root@yxt local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/myslq.sh
[root@yxt local]# source /etc/profile.d/myslq.sh 
[root@yxt local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/apache/bin/:/root/bin

//头文件
[root@yxt local]# ln -sv /usr/local/mysql/include/ /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include/'

//库文件
[root@yxt local]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib/
[root@yxt local]# ldconfig 

//man文档
[root@yxt local]# vim /etc/man_db.conf 
MANDATORY_MANPATH                       /usr/local/mysql/man

创建各实例数据存放的目录
(这里可能会报错，如果报错，就删除/opt/data/的所有东西，在执行初始化数据库，一次不行，就多试几遍)

[root@yxt ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@yxt ~]# chown -R mysql:mysql /opt/data
[root@yxt ~]# ll /opt/data
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 30 18:20 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 30 18:20 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 30 18:20 3308
[root@yxt ~]# yum -y install tree
[root@yxt ~]# tree /opt/data
/opt/data
?𼰿𺲀?€ 3306
?𼰿𺲀?€ 3307
?𺰿𺲀?€ 3308

初始化各实例

[root@yxt ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-30T11:47:34.228678Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:47:34.700465Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:47:34.745245Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:47:34.802834Z 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: 67074e9a-0ffd-11ed-ab81-000c2922f929.
2022-07-30T11:47:34.804285Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:47:35.395589Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:47:35.395614Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:47:35.396430Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:47:35.469260Z 1 [Note] A temporary password is generated for root@localhost: r4,O4b</p6ed
[root@yxt ~]# echo 'r4,O4b</p6ed' > 3306_pass

[root@yxt ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-30T11:48:18.627377Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:48:18.897686Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:48:18.940571Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:48:18.999510Z 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: 815f2fdc-0ffd-11ed-af2d-000c2922f929.
2022-07-30T11:48:19.001040Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:48:19.328351Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:48:19.328367Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:48:19.328766Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:48:19.453303Z 1 [Note] A temporary password is generated for root@localhost: vZ?d<QMJq1jt
[root@yxt ~]# echo 'vZ?d<QMJq1jt' > 3307_pass

[root@yxt ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-30T11:48:44.493873Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:48:44.795568Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:48:44.887294Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:48:44.945178Z 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: 90d62e1d-0ffd-11ed-af36-000c2922f929.
2022-07-30T11:48:44.946691Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:48:45.147796Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:48:45.147814Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:48:45.148426Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:48:45.170413Z 1 [Note] A temporary password is generated for root@localhost: g;WJ=fE=+1aq
[root@yxt ~]# echo 'g;WJ=fE=+1aq' > 3308_pass

安装perl

[root@yxt data]# yum -y install perl

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

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

使用systemctl服务控制mysql多实例

[root@yxt ~]# cp /usr/lib/systemd/system/sshd.service  /usr/lib/systemd/system/3308.service
[root@yxt ~]# cp /usr/lib/systemd/system/sshd.service  /usr/lib/systemd/system/3307.service
[root@yxt ~]# cp /usr/lib/systemd/system/sshd.service  /usr/lib/systemd/system/3306.service
[root@yxt ~]# vim /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@yxt ~]# vim /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@yxt ~]# vim /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@yxt ~]# systemctl  daemon-reload
[root@yxt ~]# systemctl  stop firewalld
[root@yxt ~]# systemctl  disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@yxt ~]# vim /etc/selinux/config        //修改selinux
SELINUX=disabled
[root@yxt ~]# setenforce 0

//特别注意：先前使用mysqld_multi启动的，只有先杀死3306、3307和3308的MySQL服务进程之后，使用systemctl服务开启或者再停止
[root@yxt ~]# systemctl start 3306
[root@yxt ~]# systemctl start 3307
[root@yxt ~]# systemctl start 3308
[root@yxt ~]# 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@yxt ~]# systemctl stop 3306
[root@yxt ~]# systemctl stop 3307
[root@yxt ~]# systemctl stop 3308
[root@yxt ~]# 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                                 [::]:*   

//记得在开启进行下面的步骤

初始化密码

[root@yxt 3306]# yum -y  install ncurses-compat-libs
[root@yxt ~]# mysql -uroot -p'r4,O4b</p6ed' -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 ('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit;
Bye

[root@yxt ~]# mysql -uroot -p'vZ?d<QMJq1jt' -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 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 ('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit;
Bye

[root@yxt ~]# cat 3308_pass 
g;WJ=fE=+1aq
[root@yxt ~]# mysql -uroot -p'g;WJ=fE=+1aq' -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 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 ('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit;
Bye
风早君
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
linux mariadb 升级,Mariadb数据库更新
weixin_36350479的博客
05-13 1027
线上的有一个c的程序，读了mariadb的数据就core，查了下，我们用了mysql_ping命令，在执行的时候在mariadb的内部core了，用dmesg看了下内核信息，最终定位可能的原因是Mysql的版本问题，所以本次需要将我们用的Mariadb的版本升级到最新，并且测试下是否有问题，页就是一次升级过程。一 查看OS版本和数据库版本#mysqlMariaDB [(none)]> sel...
CentOS7二进制安装mariadb10.2.36
superjundawn的博客
02-21 314
1：准备安装目录，默认的安装目录为/usr/local/mysql，但是本文的安装目录为/data/usr/local/mysql 2：准备mysql数据存放目录 /data/mysql 3：准备二进制文件 https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.2.36/bintar-linux-systemd-x86_64/mariadb-10.2.36-linux-systemd-x86_64.tar.gz 4：准备相关的用户 useradd -
mac 下安装 mysql （蛋疼）
aaxd0402的博客
12-14 300
mac先安装mydql步骤 1、首先从官网上下载 mac版本的mysql；我的是mysql-5.7.20-macos10.12-x86_64.dmg 2、正常安装； 3、在系统偏好设置的最底下。1：是启动，停止服务，2：是是否开机启动。 4、配置文件 位置。/usr/local/etc/my.cnf 用brew install mysql 安装时配置文件也在这里。 5、配置文...
mysql多实例部署shell脚本
10-26
mysql多实例部署shell脚本
mysql5.7多实例部署
09-08
此文档描述了mysql5.7数据库多多实例部署方法，另有多实例多部署脚本
MySQL多实例配置方案
09-09
MySQL多实例就是，在一台机器上开启多个不同的服务端口（如：3306,3307,3308...），运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的端口提供服务。
Mysql数据库多实例安装部署
06-26
Mysql数据库多实例安装部署
快速实现MySQL的部署以及一机多实例部署
09-10
主要为大家详细介绍了快速实现MySQL的部署以及一机多实例部署的相关资料，感兴趣的小伙伴们可以参考一下
CentOS6.5 上部署 MySQL5.7.17 二进制安装以及多实例配置
09-09
主要介绍了CentOS6.5 上部署 MySQL5.7.17 二进制安装以及多实例配置,需要的朋友可以参考下
Mysql数据库单机多实例部署手记
01-19
为了管理方便考虑将mysql数据库进行隔离，即采用单机多实例部署的方式。 　找了会资料发现用的人也不是太多，一般的生产环境为了充分发挥机器性能都是单机单实例运行，再进行一系列的配置调优.. 　多实例的运行...
mysql使用mysqld_multi部署单机多实例的方法教程
09-09
主要给大家介绍了关于mysql使用mysqld_multi部署单机多实例的相关资料，文中通过示例代码将实现的步骤一步步介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习...
mysql备份脚本多库版 适合多实例部署的版本
07-21
mysql备份脚本 可以同时备份一个实例下的多个库 适合多实例部署的版本 各路径请进行相应的修改 别忘了脚本赋权
MySQL 多实例部署 xtrabackup备份与恢复
Yanghu4112的博客
08-30 465
MySQL 多实例部署 1.下载安装包 [root@localhost ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz [root@localhost ~]# ls anaconda-ks.cfg mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz 2.解压至/usr/local [root@localhost
mysql5.7在windows10上的安装方法
danny0798的博客
09-04 244
学习需要,自己更新 # mysql的安装 ## 1.下载 官网:https://dev.mysql.com/downloads/mysql/5.7.html#downloads 版本：windows x64位 下载到本地zip包 下载到本地 ## 2.安装 ### 2.1解压zip包到d盘,配置参数 目录形如：D:\mysql-5.7.35-winx64 在目录下建立1个文件 my.ini 和一个文件夹data ``` [mysqld] ##skip-grant-tables=1 po.
服务器如何启用tlsv1.2协议,Nginx开启TLSv1.3和HTTP/2
weixin_39949894的博客
08-13 1675
安装最新的openssl：https://www.openssl.org/source/，版本：openssl-1.1.1。安装命令：#下载解压tar -zxvf openssl-1.1.1.tar.gzcd openssl-1.1.1#编译安装./config shared zlibmakemake install#版本替换rm -rf /usr/bin/opensslrm -rf /usr/i...
Maven库使用自签名密钥，编译时报错的解决方法
愚蠢的小根儿
12-11 5615
解决Maven报错：hostname in certificate didn't match
mysql多实例停不掉
最新发布
08-27
- *1* [MySQL多实例部署](https://blog.csdn.net/m0_58805648/article/details/125574677)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值