MySQL多实例部署

最新推荐文章于 2024-10-20 17:13:34 发布

碳烤小卤蛋

最新推荐文章于 2024-10-20 17:13:34 发布

阅读量49

点赞数

文章标签： mysql adb android

本文链接：https://blog.csdn.net/qq_64989514/article/details/133555287

版权

MySQL多实例部署

应用场景：在一台服务器中跑多个数据库

安装mysql

1.安装依赖包

[root@node01 ~]# yum -y install ncurses-devel openssl-devel openssl make mariadb-devel libtool perl libncurses*

2.创建用户和组

[root@node01 ~]# useradd -r -M -s /sbin/nologin mysql
uid=993(mysql) gid=990(mysql) groups=990(mysql)

3.解压源码包到/usr/local/

[root@node01 src]# ls
debug    mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz
kernels
[root@node01 src]# tar xf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@node01 src]# ls /usr/local/
bin      lib                                  sbin
etc      lib64                                share
games    libexec                              src
include  mysql-5.7.39-linux-glibc2.12-x86_64
[root@node01 src]# 
#创建软连接
[root@node01 src]# cd /usr/local/
[root@node01 local]# ln -s mysql-5.7.39-linux-glibc2.12-x86_64/ mysql
//修改目录/usr/local/mysql的属主属组
[root@node01 local]# chown -R mysql.mysql /usr/local/mysql
[root@node01 local]# ll /usr/local/mysql
lrwxrwxrwx 1 mysql mysql 36 Sep 13 13:20 /usr/local/mysql -> mysql-5.7.39-linux-glibc2.12-x86_64/

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

//建立数据存放目录
[root@node01 ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@node01 ~]# chown -R mysql.mysql /opt/data/
[root@node01 ~]# ll /opt/data/
total 0
drwxr-xr-x 2 mysql mysql 6 Sep 13 13:26 3306
drwxr-xr-x 2 mysql mysql 6 Sep 13 13:26 3307
drwxr-xr-x 2 mysql mysql 6 Sep 13 13:26 3308

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

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

在我们使用mysql_multi去启动服务是，先去看一下该命令是基于什么使用的

在这里插入图片描述

启动各实例，并记下密码

//初始化3306实例
[root@node01 ~]# mysqld_multi start 3306


Installing new database in /opt/data/3306
2023-09-13T05:36:02.904148Z 1 [Note] A temporary password is generated for root@localhost: NQlq&kJi-2D#
[root@node01 ~]# echo 'NQlq&kJi-2D#' > /opt/data/3306/pass

//初始化3307实例
[root@node01 ~]# mysqld_multi start 3307


Installing new database in /opt/data/3307

2023-09-13T05:38:05.514252Z 1 [Note] A temporary password is generated for root@localhost: nsMj7wrS.)9G
[root@node01 ~]# echo 'nsMj7wrS.)9G' > /opt/data/3307/pass

//初始化3308实例
[root@node01 ~]# mysqld_multi start 3308


Installing new database in /opt/data/3308

2023-09-13T05:38:54.228344Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-13T05:38:54.253162Z 1 [Note] A temporary password is generated for root@localhost: h#qpvlQ%y5o;
[root@node01 ~]# echo 'h#qpvlQ%y5o;' > /opt/data/3308/pass

启动各实例

[root@node01 ~]# mysqld_multi start 3306
[root@node01 ~]# mysqld_multi start 3307
[root@node01 ~]# mysqld_multi start 3308
[root@node01 ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0      128          0.0.0.0:22        0.0.0.0:*   
LISTEN 0      128             [::]:22           [::]:*   
LISTEN 0      80                 *:3306            *:*   
LISTEN 0      80                 *:3307            *:*   
LISTEN 0      80                 *:3308            *:*

初始化密码

3306：
[root@node01 ~]# cd /opt/data/
[root@node01 data]# ls
3306  3307  3308
[root@node01 data]# cat 3306/pass 
NQlq&kJi-2D#
[root@node01 data]# mysql -uroot -p'NQlq&kJi-2D#' -S /tmp/mysql3306.sock #登录到3306设置密码
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.39

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

mysql> exit
Bye

3307：
[root@node01 data]# cat 3307/pass 
nsMj7wrS.)9G
[root@node01 data]# mysql -uroot -p'nsMj7wrS.)9G' -S /tmp/mysql3307.sock 
mysql> set password = password('1234563307');
Query OK, 0 rows affected, 1 warning (0.00 sec)

3308：
[root@node01 data]# cat 3308/pass 
h#qpvlQ%y5o;
[root@node01 data]# mysql -uroot -p'h#qpvlQ%y5o;' -S /tmp/mysql3308.sock 
mysql> set password = password('1234563308');
Query OK, 0 rows affected, 1 warning (0.01 sec)

检验

登录3306
[root@node01 data]# mysql -uroot -p1234563306 -h127.0.0.1 -P3306
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.39 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> exit
Bye

3307：
[root@node01 data]# mysql -uroot -p1234563307 -h127.0.0.1 -P3307
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.39 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> exit
Bye

3308：
[root@node01 data]# mysql -uroot -p1234563308 -h127.0.0.1 -P3308
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.39 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> exit
Bye