mysql多实例部署

多实例部署

MySQL多实例部署主要是为了在同一台服务器上运行多个MySQL服务实例,以便提供更高的系统资源利用率和灵活性。其主要作用如下:

资源利用率提高:多实例可以共享同一台服务器的硬件资源,如CPU、内存和磁盘。

系统灵活性:多实例可以在同一台服务器上运行不同的应用或服务,提高系统的灵活性和可用性。

隔离性:每个实例都可以配置不同的数据、配置文件和服务参数,有助于隔离不同应用或服务对资源的需求。

管理简化:通过脚本和管理工具,可以方便地管理多个MySQL实例

多实例部署实验

1、下载依赖包

[root@node1 ~]# yum -y install libncurses*   libaio  tar  perl
 

2、解压压缩包

[root@node1 ~]# tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@node1 ~]# cd /usr/local/
[root@node1 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.37-linux-glibc2.12-x86_64  sbin  share  src

3、创建软连接

[root@node1 local]# ln -sv  mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/'
[root@node1 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 Jul 11 13:57 lib64
drwxr-xr-x. 2 root root   6 May 16  2022 libexec
lrwxrwxrwx  1 root root  36 Aug  8 18:46 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x  9 root root 129 Aug  8 18:44 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 16  2022 sbin
drwxr-xr-x. 5 root root  49 Jul 11 13:57 share
drwxr-xr-x. 2 root root   6 May 16  2022 src

4、创建用户并给MySQL目录给予所有者和所属组的权限

[root@node1 local]# useradd -r -M -s /sbin/nologin  mysql
[root@node1 local]# id mysql
uid=997(mysql) gid=994(mysql) groups=994(mysql)
[root@node1 local]# chown -R mysql.mysql  /usr/local/mysql
[root@node1 local]# mkdir /opt/data -p
[root@node1 local]# chown -R mysql.mysql  /opt/data/
[root@node1 local]# ls -l /usr/local/mysql
lrwxrwxrwx 1 mysql mysql 36 Aug  8 18:46 /usr/local/mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/

5、创建环境变量

[root@node1 ~]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@node1 ~]# . /etc/profile.d/mysql.sh 
[root@node1 ~]# echo $PATH
/usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin

6、创建目录并给目录给予所有者和所属组的权限(mysql用户)

[root@node1 ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@node1 ~]# chown -R mysql.mysql /opt/data/
[root@node1 ~]# ls -l /opt/data/
total 0
drwxr-xr-x 2 mysql mysql 6 Aug  8 18:58 3306
drwxr-xr-x 2 mysql mysql 6 Aug  8 18:58 3307
drwxr-xr-x 2 mysql mysql 6 Aug  8 18:58 3308

7、初始化数据库

[root@node1 ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2024-08-08T11:25:21.832194Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-08T11:25:22.356948Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-08T11:25:22.380772Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-08T11:25:22.433895Z 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: e68ef1d2-5578-11ef-8034-000c290143a8.
2024-08-08T11:25:22.434905Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-08T11:25:22.651551Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-08T11:25:22.651794Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-08T11:25:22.652177Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-08T11:25:22.776392Z 1 [Note] A temporary password is generated for root@localhost: ?p9IoF,Dcu/M  -----> 密码

[root@node1 ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2024-08-08T11:26:30.796225Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-08T11:26:30.942995Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-08T11:26:30.964128Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-08T11:26:31.017881Z 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: 0f7007a0-5579-11ef-8813-000c290143a8.
2024-08-08T11:26:31.018972Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-08T11:26:31.447490Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-08T11:26:31.447702Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-08T11:26:31.448013Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-08T11:26:31.561267Z 1 [Note] A temporary password is generated for root@localhost: NPhetW()g6Sh

[root@node1 ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2024-08-08T11:27:07.023920Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-08T11:27:07.165021Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-08T11:27:07.184649Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-08T11:27:07.237925Z 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: 2506c537-5579-11ef-8fbb-000c290143a8.
2024-08-08T11:27:07.238448Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-08T11:27:07.466232Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-08T11:27:07.466723Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-08T11:27:07.467377Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-08T11:27:07.512952Z 1 [Note] A temporary password is generated for root@localhost: x?.y*sd:s4oP

8、修改配置文件

[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# 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

9、启动MySQL

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

10、初始化密码

[root@node1 ~]# mysql -uroot -p'?p9IoF,Dcu/M' -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 3
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> 
mysql> set password = password('redhat');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

[root@node1 ~]# mysql -uroot -p'NPhetW()g6Sh' -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> 
mysql> set password = password('redhat');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 


[root@node1 ~]# mysql -uroot -p'x?.y*sd:s4oP' -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('redhat');
Query OK, 0 rows affected, 1 warning (0.00 sec)


  • 18
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值