mysql多实例部署

于 2022-07-03 15:31:31 发布
阅读量288 收藏
点赞数
文章标签： mysql 服务器 linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_59088930/article/details/125584207
版权

mysql多实例部署

在这里插入图片描述

选择版本 系统 内核

//配置用户和组并解压二进制程序至/usr/local下
[root@localhost ~]# ls
anaconda-ks.cfg  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql
[root@localhost ~]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# mv mysql-5.7.37-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root  root    6 6月  22 2021 bin
drwxr-xr-x. 2 root  root    6 6月  22 2021 etc
drwxr-xr-x. 2 root  root    6 6月  22 2021 games
drwxr-xr-x. 2 root  root    6 6月  22 2021 include
drwxr-xr-x. 2 root  root    6 6月  22 2021 lib
drwxr-xr-x. 3 root  root   17 4月  14 01:35 lib64
drwxr-xr-x. 2 root  root    6 6月  22 2021 libexec
drwxr-xr-x. 9 mysql mysql 129 7月   3 13:57 mysql
drwxr-xr-x. 2 root  root    6 6月  22 2021 sbin
drwxr-xr-x. 5 root  root   49 4月  14 01:35 share
drwxr-xr-x. 2 root  root    6 6月  22 2021 src
//配置环境变量
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysqld.sh
[root@localhost local]# source /etc/profile.d/mysqld.sh   // 重启
[root@localhost local]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost local]# vim /etc/man_db.conf 
[root@localhost local]# vim /etc/ld.so.conf.d/mysqld.conf
[root@localhost local]# ldconfig

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

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# ls /opt/data/
3306  3307  3308

初始化各实例

[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
2022-07-03T06:09:48.279549Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T06:09:48.468951Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T06:09:48.500856Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T06:09:48.561772Z 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: be41157e-fa96-11ec-9398-000c29752da2.
2022-07-03T06:09:48.562545Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T06:09:48.795169Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T06:09:48.795201Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T06:09:48.795512Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T06:09:48.865897Z 1 [Note] A temporary password is generated for root@localhost: y:Uw&h*Fe5?#
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2022-07-03T06:10:54.000610Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T06:10:54.194319Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T06:10:54.230010Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T06:10:54.242065Z 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: e5671a61-fa96-11ec-bf65-000c29752da2.
2022-07-03T06:10:54.242793Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T06:10:54.979885Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T06:10:54.979920Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T06:10:54.980402Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T06:10:55.269471Z 1 [Note] A temporary password is generated for root@localhost: 7TbyNnKNbN/=
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2022-07-03T06:11:22.852684Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-03T06:11:23.029570Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-03T06:11:23.063439Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-03T06:11:23.125999Z 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: f69e70df-fa96-11ec-aeac-000c29752da2.
2022-07-03T06:11:23.127182Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-03T06:11:23.505562Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T06:11:23.505589Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-03T06:11:23.505896Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-03T06:11:23.569834Z 1 [Note] A temporary password is generated for root@localhost: yUi>h3aM)fti

安装perl

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

配置配置文件/etc/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 ~]#

启动各实例

[root@localhost ~]# mysqld_multi start 3306
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# mysqld_multi start 3307
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# mysqld_multi start 3308
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# 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                       *:3307                  *:*                
LISTEN   0        80                       *:3308                  *:*                
LISTEN   0        128                   [::]:22                 [::]:*                
LISTEN   0        80                       *:3306                  *:*

初始化密码

[root@localhost ~]# mysql -uroot -p'y:Uw&h*Fe5?#' -S /tmp/mysql3306.sock 
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost ~]# yum provides libncurses.so.5  
上次元数据过期检查：0:05:19 前，执行于 2022年07月03日 星期日 14时36分36秒。
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
仓库        ：base
匹配来源：
提供    : libncurses.so.5

[root@localhost ~]# yum -y install ncurses-compat-libs // 安装ncurses-compat-libs
[root@localhost ~]# mysql -uroot -p'y:Uw&h*Fe5?#' -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

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');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'7TbyNnKNbN/=' -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.37

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');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'yUi>h3aM)fti' -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.37

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');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

设置开机自启

[root@localhost ~]# cp /usr/lib/systemd/system/sshd.service /usr/lib/systemd/system/my3306.service

[root@localhost ~]# cat /usr/lib/systemd/system/my3306.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 ~]# systemctl daemon-reload  //重启服务
[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/  做软连接
[root@localhost ~]# systemctl status my3306  
● my3306.service - 3306 server daemon  // 查看状态
   Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled; vendor preset: d>
   Active: active (running) since Sun 2022-07-03 15:18:54 CST; 45s ago
  Process: 226473 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (code=exited>
 Main PID: 226491 (mysqld_safe)
    Tasks: 28 (limit: 11175)
   Memory: 197.3M
   CGroup: /system.slice/my3306.service
           ├─226491 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3306>
           └─226630 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=>

7月 03 15:18:54 localhost.localdomain systemd[1]: Starting 3306 server daemon...
7月 03 15:18:54 localhost.localdomain mysqld_multi[226473]: Wide character in print a>
7月 03 15:18:54 localhost.localdomain systemd[1]: Started 3306 server daemon.

[root@localhost ~]# systemctl enable my3306.service //设置开机自启
Created symlink /etc/systemd/system/multi-user.target.wants/my3306.service → /usr/lib/
systemd/system/my3306.service.
[root@localhost ~]# systemctl status my3306
● my3306.service - 3306 server daemon
   Loaded: loaded (/usr/lib/systemd/system/my3306.service; enabled; vendor preset: di>
   Active: active (running) since Sun 2022-07-03 15:18:54 CST; 1min 36s ago
 Main PID: 226491 (mysqld_safe)
    Tasks: 28 (limit: 11175)
   Memory: 197.3M
   CGroup: /system.slice/my3306.service
           ├─226491 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3306>
           └─226630 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=>

7月 03 15:18:54 localhost.localdomain systemd[1]: Starting 3306 server daemon...
7月 03 15:18:54 localhost.localdomain mysqld_multi[226473]: Wide character in print a>
7月 03 15:18:54 localhost.localdomain systemd[1]: Started 3306 server daemon.
// 重启测试
[root@localhost ~]# reboot 

连接断开
连接成功
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Sun Jul  3 13:55:22 2022 from 192.168.183.1
[root@localhost ~]# 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        128                   [::]:22                 [::]:*                
[root@localhost ~]# systemctl status my3306
● my3306.service - 3306 server daemon
   Loaded: loaded (/usr/lib/systemd/system/my3306.service; enabled; vendor preset: di>
   Active: active (running) since Sun 2022-07-03 15:22:58 CST; 23s ago
  Process: 1044 ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 (code=exited, >
 Main PID: 1064 (mysqld_safe)
    Tasks: 28 (limit: 11175)
   Memory: 221.8M
   CGroup: /system.slice/my3306.service
           ├─1064 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3306 ->
           └─1333 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/o>

7月 03 15:22:58 localhost.localdomain systemd[1]: Starting 3306 server daemon...
7月 03 15:22:58 localhost.localdomain mysqld_multi[1044]: Wide character in print at >
7月 03 15:22:58 localhost.localdomain systemd[1]: Started 3306 server daemon.
田小杨
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
部署MySQL双实例
02-23 589
什么是MySQL双实例或者多实例？ 就是在一台机器上面开启多个不同的端口，运行多个MySQL服务进程。这些MySQL多实例公用一套安装程序，使用不同的(也可以是相同的)配置文件，启动程序，数据文件。在提供服务时候，多实例MySQL在逻辑上看来是各自独立的，多个实例的自身是根据配置文件对应的设定值，来取得相关硬件资源的多少。 应用场景 伪分布式架构的原因，而项目启动初期又不一定有
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多实例.docx
05-12
在一台服务器上部署多个MySQL实例能够提高资源利用率，并且方便进行隔离管理和维护。以下将详细介绍如何在同一台虚拟机上配置MySQL数据库多实例。 ##### 1. 建立目录结构 为了清晰地区分各个实例，首先需要创建...
快速实现MySQL的部署以及一机多实例部署
09-10
MySQL的部署是数据库管理的基础，尤其是一机多实例部署，能有效地利用...通过自动化脚本，我们可以简化这些过程，实现快速且高效地在一台服务器上部署多个MySQL实例。这对于需要隔离不同应用或测试环境的场景非常有用。
CentOS6.5 上部署 MySQL5.7.17 二进制安装以及多实例配置
09-09
主要介绍了CentOS6.5 上部署 MySQL5.7.17 二进制安装以及多实例配置,需要的朋友可以参考下
Mysql数据库单机多实例部署手记
01-19
为了管理方便考虑将mysql数据库进行隔离，即采用单机多实例部署的方式。 　找了会资料发现用的人也不是太多，一般的生产环境为了充分发挥机器性能都是单机单实例运行，再进行一系列的配置调优.. 　多实例的运行...
mysql使用mysqld_multi部署单机多实例的方法教程
09-09
在本教程中，我们将探讨如何使用`mysqld_multi`工具在单台机器上部署多个MySQL实例。`mysqld_multi`是MySQL提供的一种管理工具，它允许在同一个服务器上运行多个`mysqld`进程，每个进程都有独立的数据目录、端口和...
MySQL多实例部署
mushuangpanny的博客
07-02 921
资金比较紧张的公司；并发访问不是特别大的业务ABC三个项目，在一个数据库运行三个项目，C项目要更新数据库，需要重启数据库，AB项目会受到影响
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、付费专栏及课程。

余额充值