MySQL多实例

MySQL多实例

实例：实例并不是指数据库，一个实例对应的是一个数据库，多实例实际上是在一台服务器上部署多个数据库，这些数据库共享使用同一台机器的硬件资源

部署MySQL多实例

多实例安装与二进制安装步骤大致相同，前面相同的步骤此处省略，完整安装步骤请参考(https://blog.csdn.net/m0_63013378/article/details/129166406?spm=1001.2014.3001.5501)

#前面步骤省略，创建数据目录时需要创建多个，有几个实例创建几个，属主属组全改为MySQL用户
[root@test1 mysql]# mkdir -p /opt/data/{3306,3307,3308}
[root@test1 mysql]# ls /opt/data/
3306  3307  3308
[root@test1 mysql]# chown -R mysql.mysql /opt/data/
[root@test1 mysql]# ll /opt/data/
total 0
drwxr-xr-x 2 mysql mysql 6 Feb 27 11:53 3306
drwxr-xr-x 2 mysql mysql 6 Feb 27 11:53 3307
drwxr-xr-x 2 mysql mysql 6 Feb 27 11:53 3308
[root@test1 mysql]# 

初始化3306实例

[root@test1 mysql]# mysqld --initialize --user mysql --datadir /opt/data/3306

......

localhost: UA#M)NWsy04i
[root@test1 mysql]# echo 'UA#M)NWsy04i' > 3306password

初始化3307实例

[root@test1 ~]# mysqld --initialize --user mysql --datadir /opt/data/3307

.....

root@localhost: fc?nZiw>w8%w
[root@test1 ~]# echo 'fc?nZiw>w8%w' > 3307password

初始化3308实例

[root@test1 ~]# mysqld --initialize --user mysql --datadir /opt/data/3308

.......

root@localhost: .)d>f24loOfR
[root@test1 ~]# echo '.)d>f24loOfR' > 3308password

安装依赖perl和libncurses.so.5

[root@test1 ~]# yum -y install perl
[root@test1 ~]# export LC_ALL=C  //设置标准语言环境
[root@test1 ~]# yum -y install libncurses.so.5

手动生成MySQL配置文件

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

启动多实例

启动方法1（一般不使用）：

#只能启动，不能停止
[root@test1 ~]# mysqld_multi stop 3306
[root@test1 ~]# mysqld_multi stop 3307
[root@test1 ~]# mysqld_multi stop 3308
#只能通过杀死进程的方式停止
[root@test1 ~]# ps -ef | grep 3306 | grep -v grep | awk '{print$2}' |xargs kill -9

启动方法2：

#通过systemctl管理
[root@test1 ~]# cp /usr/lib/systemd/system/sshd.service .
[root@test1 ~]# vim sshd.service 
[root@test1 ~]# mv sshd.service mysqld3306.service
[root@test1 ~]# cat mysqld3306.service 
[Unit]
Description=mysqld3306 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@test1 ~]# cp mysqld3306.service mysqld3307.service 
[root@test1 ~]# cp mysqld3306.service mysqld3308.service 
[root@test1 ~]# vim mysqld3307.service #把3306改为3307
[root@test1 ~]# vim mysqld3308.service #把3306改为3308
[root@test1 ~]# mv mysqld3306.service mysqld3307.service mysqld3308.service /usr/lib/systemd/system/
[root@test1 ~]# systemctl daemon-reload 
[root@test1 ~]# systemctl start mysqld3306.service
#这里直接启动会报错，查看提示后显示警告：WARNING: my_print_defaults command not found.
#意思是找不到my_print_defaults命令，解决方法是做软链接，链到/usr/bin下
[root@test1 ~]# which my_print_defaults
/usr/local/mysql/bin/my_print_defaults
[root@test1 ~]# ln -sv /usr/local/mysql/bin/my_print_defaults /usr/bin/my_print_defaults
'/usr/bin/my_print_defaults' -> '/usr/local/mysql/bin/my_print_defaults'
[root@test1 ~]# 
[root@test1 ~]# 
[root@test1 ~]# systemctl start mysqld3306.service 
[root@test1 ~]# ss -anlt
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@test1 ~]# systemctl stop mysqld3306.service 
[root@test1 ~]# ss -anlt
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                         [::]:*                          
[root@test1 ~]# 
#可以正常关闭和启动

修改实例的密码

#由于是多实例，登录时需要指定套接字的路径
[root@test1 ~]# cat 3307password 
fc?nZiw>w8%w
[root@test1 ~]# mysql -uroot -p'fc?nZiw>w8%w' -S /tmp/mysql3307.sock -e "set password = password('sakura');" --connect-expired-password
[root@test1 ~]# mysql -uroot -psakura -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 5
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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>