Mysql多实例部署
-
多实例部署优点:
- 有效利用服务器资源: 单个服务器资源过盛时充分利用剩余的资源提供更多的服务
- 节约服务器资源
- 在公司启动初期没有很多用户,方便以后的架构
-
准备工作:
- mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz(二进制安装包)
-
解压安装包和配置用户和组
//解压压缩包至/usr/local
[root@localhost src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/loacl/
//创建用户
[root@localhost src]# useradd -r -M -s /sbin/nologin mysql
[root@localhost src]# grep mysql /etc/group
mysql:x:991:
//创建软链接
[root@localhost src]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost local]# ll
lrwxrwxrwx. 1 root root 47 Aug 29 13:43 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Aug 29 13:37 mysql-5.7.34-linux-glibc2.12-x86_64
//更改属主属组
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
lrwxrwxrwx. 1 mysql mysql 47 Aug 29 13:43 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Aug 29 13:37 mysql-5.7.34-linux-glibc2.12-x86_64
- 配置环境变量
[root@localhost ~]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost ~]# source /etc/profile.d/mysql.sh
- 创建多实例存放的目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308
3 directories, 0 files
//更改属主属组
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Aug 29 13:53 3306
drwxr-xr-x. 2 mysql mysql 6 Aug 29 13:53 3307
drwxr-xr-x. 2 mysql mysql 6 Aug 29 13:53 3308
- 初始化各实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
[root@localhost ~]# echo '2PRpmFaXiu)K' > 3306_passwd
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
[root@localhost ~]# echo 'a(u5>0c&mpbD' > 3307_passwd
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
[root@localhost ~]# echo 'wAbmE_FVi2e<' > 3308_passwd
- 安装perl和libncurses.so.5
//查看libncurses.so.5是由哪个包提供
[root@localhost ~]# yum whatprovides libncurses.so.5
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
[root@localhost ~]# yum -y install ncurses-compat-libs
//安装perl
[root@localhost ~]# yum -y install perl
- 配置配置文件/etc/my.cnf
[root@localhost ~]# 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/3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log
[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log
[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/3308.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 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 [::]:*
//无法使用mysqld_multi无法停止各实例,需要使用pkill mysql 或者kill -9 3306(或者3307、3308)
- 初始化密码
//初始化3306密码
[root@localhost ~]# mysql -uroot -p2PRpmFaXiu)K -h127.0.0.1 -P3306
mysql> set password = password('test123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
//初始化3307密码
[root@localhost ~]# mysql -uroot -p'a(u5>0c&mpbD' -h127.0.0.1 -P3307
mysql> set password = password('test123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
//初始化3308密码
[root@localhost ~]# mysql -uroot -p'wAbmE_FVi2e<' -h127.0.0.1 -P3308
mysql> set password = password('test123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 设置开机自启
//mysqld_multi服务脚本复制到/etc/init.d/下
[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
//在/etc/init.d/mysqld_multi文件中增加环境变量设置export PATH=/usr/local/mysql/bin:$PATH
[root@localhost ~]# vim /etc/init.d/mysqld_multi
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
export PATH=/usr/local/mysql/bin:$PATH
//系统重新加载服务
[root@localhost ~]# systemctl daemon-reload
//开启服务
[root@localhost ~]# service mysqld_multi start 3306
[root@localhost ~]# service mysqld_multi start 3307
[root@localhost ~]# service mysqld_multi start 3308
[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 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:22 [::]:*
//设置开机自启
[root@localhost ~]# chkconfig --add mysqld_multi
[root@localhost ~]# reboot
[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 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:22 [::]:*
- 以上就是mysql多实例的部署,简单总结一下步骤:
- 1.解压安装包和配置用户和组
- 2.配置环境变量
- 3.创建多实例存放的目录
- 4.初始化各实例
- 5.安装perl和libncurses.so.5
- 6.配置配置文件/etc/my.cnf
- 7.启动各实例
- 8.初始化各实例密码
- 9.设置开机自启