MySQL多实例部署

最新推荐文章于 2024-07-25 17:36:03 发布
最新推荐文章于 2024-07-25 17:36:03 发布
阅读量441 收藏
点赞数
文章标签： mysql 数据库 服务器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/QWQ45597316/article/details/125570844
版权

文章目录

安装mysql

[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql  //创建用户

[root@localhost ~]# cd /usr/src/
[root@localhost src]# tar -xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/  //解压软件包、

[root@localhost src]# cd /usr/local/
[root@localhost local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# chown -R mysql.mysql mysql/  //修改权限

[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh 
[root@localhost local]# source /etc/profile.d/mysql.sh 
[root@localhost local]# which mysql
/usr/local/mysql/bin/mysql  //配置环境变量

[root@localhost mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql  //配置include软连接
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@localhost mysql]# cat /etc/ld.so.conf.d/mysql.conf   //配置lib库
/usr/local/mysql/lib
[root@localhost mysql]# vim /etc/man_db.conf   //添加man文档
MANDATORY_MANPATH                       /usr/local/mysql/man

#创建数据库目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# cd /opt/data/
[root@localhost data]# ll
总用量 0
drwxr-xr-x. 2 mysql mysql 6 7月   2 11:37 3306
drwxr-xr-x. 2 mysql mysql 6 7月   2 11:37 3307
drwxr-xr-x. 2 mysql mysql 6 7月   2 11:37 3308

配置多实例部署

3306初始化

[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3306
2022-07-02T03:39:07.431121Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-02T03:39:07.703358Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-02T03:39:07.831924Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-02T03:39:07.952119Z 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: 8737c75c-f9b8-11ec-a29d-000c29d84f4a.
2022-07-02T03:39:07.952946Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-02T03:39:08.607273Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:39:08.607289Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:39:08.607957Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-02T03:39:08.937337Z 1 [Note] A temporary password is generated for root@localhost: q/m?Crf5iM#o
[root@localhost ~]# echo 'q/m?Crf5iM#o' > 3306  //保存3306的临时密码

初始化3307

[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3307
2022-07-02T03:41:30.616464Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-02T03:41:30.834151Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-02T03:41:30.860689Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-02T03:41:30.918286Z 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: dc6eae26-f9b8-11ec-9d03-000c29d84f4a.
2022-07-02T03:41:30.919190Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-02T03:41:31.278832Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:41:31.278849Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:41:31.280297Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-02T03:41:31.505911Z 1 [Note] A temporary password is generated for root@localhost: iOvQergS.2iC
[root@localhost ~]# echo 'iOvQergS.2iC' > 3307

初始化3308

[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3308
2022-07-02T03:42:11.391605Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-02T03:42:11.644992Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-02T03:42:11.677601Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-02T03:42:11.733139Z 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: f4c28963-f9b8-11ec-b8e5-000c29d84f4a.
2022-07-02T03:42:11.734222Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-02T03:42:12.174186Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:42:12.174202Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-02T03:42:12.174635Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-02T03:42:12.290170Z 1 [Note] A temporary password is generated for root@localhost: rN>I?tXZ:3gN
[root@localhost ~]# echo 'rN>I?tXZ:3gN' > 3308

安装软件包perl

[root@localhost ~]#  yum -y install perl
[root@localhost ~]# yum install ncurses-compat-libs  //连接mysql需要


[root@localhost ~]# file /usr/local/mysql/bin/mysqld_multi   //因为此脚本要用perl来执行
/usr/local/mysql/bin/mysqld_multi: Perl script text executable

配置my.cnf文件

[root@localhost ~]# 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@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308

[root@localhost ~]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port   Process   
LISTEN   0        32         192.168.122.1:53             0.0.0.0:*                
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*                
LISTEN   0        5              127.0.0.1:631            0.0.0.0:*                
LISTEN   0        128              0.0.0.0:111            0.0.0.0:*                
LISTEN   0        128                 [::]:22                [::]:*                
LISTEN   0        5                  [::1]:631               [::]:*                
LISTEN   0        80                     *:3306                 *:*                
LISTEN   0        80                     *:3307                 *:*                
LISTEN   0        80                     *:3308                 *:*                
LISTEN   0        128                 [::]:111               [::]:*

修改密码

#3306修改密码
[root@localhost ~]# mysql -uroot -p'q/m?Crf5iM#o' -S /tmp/mysql3306.sock 

mysql> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.01 sec)

#3307修改密码
[root@localhost ~]# mysql -uroot -p'iOvQergS.2iC' -S /tmp/mysql3307.sock 
mysql> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

#3308修改密码
[root@localhost ~]# mysql -uroot -p'rN>I?tXZ:3gN' -S /tmp/mysql3308.sock 
mysql> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.01 sec)

设置开机自启

[root@localhost ~]# cd /usr/lib/systemd/system
[root@localhost system]# cp sshd.service 3306.service
[root@localhost system]# cat 3306.service 
[Unit]
Description=3306 server daemon
After=network.target sshd-keygen.target
Wants=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

配置软连接

#启动mysql时找不到my_print_defaults
[root@localhost ~]# which my_print_defaults 
/usr/local/mysql/bin/my_print_defaults
[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/

#可以正常启动和关闭
[root@localhost ~]# systemctl daemon-reload 
[root@localhost ~]# systemctl start 3306.service
[root@localhost ~]# systemctl enable --now 3306.service 
Created symlink /etc/systemd/system/multi-user.target.wants/3306.service → /usr/lib/systemd/system/3306.service.
[root@localhost ~]# systemctl status 3306.service 
● 3306.service - 3306 server daemon
   Loaded: loaded (/usr/lib/systemd/system/3306.service; enabled; vendor preset: d>
   Active: active (running) since Sat 2022-07-02 12:26:01 CST; 23s ago
 Main PID: 251609 (mysqld_safe)
    Tasks: 28 (limit: 4596)
   Memory: 155.9M
   CGroup: /system.slice/3306.service
           ├─251609 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3>
           └─251743 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datad>

#关闭
[root@localhost ~]# systemctl stop 3306.service 
[root@localhost ~]# systemctl status 3306.service 
● 3306.service - 3306 server daemon
   Loaded: loaded (/usr/lib/systemd/system/3306.service; enabled; vendor preset: d>
   Active: failed (Result: exit-code) since Sat 2022-07-02 12:27:06 CST; 1s ago
  Process: 257522 ExecStop=/usr/bin/ps -ef | grep 3306 | grep -v grep|awk {print $>
 Main PID: 251609 (code=exited, status=0/SUCCESS)

以同样的方法配置3307和3308的开机自启服务

[root@localhost system]# cp 3306.service  3308.service
[root@localhost system]# cp 3306.service  3307.service

#3307配置
[root@localhost system]# cat 3307.service 
[Unit]
Description=3307 server daemon
After=network.target sshd-keygen.target
Wants=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

#3308配置
[root@localhost system]# cat 3308.service 
[Unit]
Description=3308 server daemon
After=network.target sshd-keygen.target
Wants=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 system]# systemctl enable --now 3307.service 
Created symlink /etc/systemd/system/multi-user.target.wants/3307.service → /usr/lib/systemd/system/3307.service.
[root@localhost system]# systemctl enable --now 3308.service 
Created symlink /etc/systemd/system/multi-user.target.wants/3308.service → /usr/lib/systemd/system/3308.service.

多实例部署可以在同一台主机配置多个mysql让资源不会被浪费 多个mysql库的数据时不同的

[root@localhost ~]# mysql -uroot -p'runtime123!' -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.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> 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)


[root@localhost ~]# mysql -uroot -p'runtime123!' -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 4
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
炸鸡嘎嘎
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 单机部署多实例方案
11-27 523
mysql 单机部署多实例方案
linux部署Mysql多实例
keil_wang的博客
01-26 2400
mysql多实例的概述 多实例就是在一台服务器上开启多个不同的mysql服务端口，运行多个mysql服务进程。这些进程通过不同的socket监听不同的服务端口，以此来提供不同的服务。 多个实例共用一套安装程序，但有各自的配置文件my.cnf，数据文件，进程以及日志文件等。 多实例优缺点 优点：有效的利用服务器资源 当单个服务器资源有剩余时，可以利用多实例来充分利用服务器的资源来提供更多的...
MySQL多实例部署：从概念到实操的全面指南
Srlua的博客
02-20 2359
当公司业务访问量不太大的时候，服务器的资源基本上都是浪费的，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MVSQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源以及搭配好服务，也不会有太大的问题。单实例，也就是Srlua前面带着大家，在一台linux上，某个目录下，安装了一个mysql，且启动了这个mysql，这就表示，这个机器上，有单独的一个mysql个体，一个实例。房，相信对此能有更好地理解，大家蜗居在一起，休息时在自己的卧室，但出来活动肯定是要共用上述公共资源的。
windows下Mysql多实例部署
09-29 797
windows下Mysql多实例部署
mysql怎么部署多实例_mysql多实例部署
weixin_39763033的博客
02-28 491
多实例概述mysql多实例其实就是在同一台服务器上开启不同的端口，运行多个msyql服务进程，通过不同的socket来监听不同的服务端口，提供不同的服务多实例部署多实例部署时通过配置文件的相互隔离来实现的1、先安装MySQL常用的依赖包，并下载所需MySQL对应版本安装包下面准备的是5.7.27二进制的包yum install -y libaio libnuma* perl2、解压、创建mysql...
Window下搭建Mysql多实例部署和主从配置
u013367040的博客
08-31 636
Window下搭建Mysql多实例部署和主从配置
mysql基础4——单机多实例部署
百慕卿君博客
06-30 568
单机多实例
MySQL8.0.20单机多实例部署
rao991207823的博客
04-05 3806
MySQL8.0.20单机多实例部署0.环境需要1.安装步骤 0.环境需要 1.准备Linux环境（系统：CentOS7） 2.准备MySQL安装包（版本：8.0.20） 3.安装方式为：msyql解压安装 1.安装步骤 1.下载解压安装的mysql安装包文件 下载地址：https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.20-el7-x86_64.tar.gz 2.上传解压缩（我这里的上传为：xhell，当然也可使用其他方式） ## 创建mys
MySQL多实例安装部署
SmallSweets的博客
06-18 1205
MySQL多实例概念 多实例就是在一台服务器上同时开启多个不同的数据库服务端口（例如3306、3307），同时运行多个MYSQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。 优点 MySQL多实例，可以通过多个端口向用户提供服务，充分利用一台服务器的闲置资源 缺点 无论是多少个端口提供服务，但始终使用的是一台服务器的资源，所以当访问量过大时，依然存在高并发的问题 安装部署 我这里以创建四个MySQL实例为例，监听的端口分别是3307，3308，3309，331
mysql多实例部署
03-27
mysql5.7在生产环境redhat7.2中，多个实例安装部署详细步骤记录
mysql多实例部署shell脚本
10-26
mysql多实例部署shell脚本
mysql5.7多实例部署
09-08
MySQL 5.7 多实例部署 本文档主要介绍了 MySQL 5.7 数据库的多实例部署方法，包括自动化部署脚本的使用和多实例创建。下面是详细的知识点： 一、MySQL 5.7 多实例部署的优点 * 提高数据库的可用性和可扩展性 * ...
Mysql数据库多实例安装部署
06-26
在实际的生产环境中，为了满足高可用性、高并发性的需求，以及更好地进行资源隔离和管理，常常需要在同一台服务器上部署多个MySQL实例。本文将详细介绍如何在Linux环境下安装和配置多个MySQL实例。 #### 二、准备...
MySQL多实例配置方案
09-09
MySQL多实例就是，在一台机器上开启多个不同的服务端口（如：3306,3307,3308...），运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的端口提供服务。
mysql高阶语句
qq_59169480的博客
07-24 887
高级语法的查询语句。
用 MySQL 批量生成强密码
最新发布
wzy0623的专栏
07-25 1146
用 MySQL 批量生成强密码
《MySQL DBA 修炼之道》第四章 开发进阶
Trishia的博客
07-25 844
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
mysql中where与on区别
m0_63227888的博客
07-24 743
使用ON子句来定义表之间的连接条件。使用WHERE子句来过滤查询结果，或者作为连接后结果的进一步筛选条件。在进行表连接时，应优先使用ON子句来指定连接条件，因为这样可以提高查询效率，避免不必要的全表扫描或笛卡尔积。WHERE子句在连接操作后使用，用于过滤连接后的结果集。
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、付费专栏及课程。

余额充值