实战:Centos7配置mysql多实例(mysql-5.7.42)-2024.4.20(测试成功)


实战:Centos7配置mysql多实例(mysql-5.7.42)-2024.4.20(测试成功)

image-20240420162318911

目录

image-20240420164118965

实验环境

centos7 1810
mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz

实验软件

链接:https://pan.baidu.com/s/1zFL_PF2J_cEJBCUUPbf3Hg?pwd=0elw
提取码:0elw
2024.4.20-实战:Centos7配置mysql多实例-2024.4.20(测试成功)

image-20240420163002029

下载mysql安装包

进入官网下载专用的mysql安装包,专用的才支持多实例

https://downloads.mysql.com/archives/community/

image-20240420160232244

确保无旧的mysql环境

配置mysql安装环境,首先确保你已经卸载全部之前安装过的mysql文件

# 卸载方法
yum list installed | grep mysql
yum remove -y mysql-server

上传安装包并解压

  • 上传安装包到你的虚拟机或者服务器:
[root@vm-template ~]#ll -h
total 647M
-rw-rw-rw- 1 root root 647M Apr 20 15:12 mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
  • 解压然后移动到本地要安装的文件夹
tar -xf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.42-linux-glibc2.12-x86_64 /usr/local/mysql

编写my.cnf配置文件

#先备份
cp /etc/my.cnf{,.bak}

vim /etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root

[mysqld1]
datadir = /mysqlmul/mysqld1
port = 3306
log-error = /mysqlmul/mysqld1/mysqld1.err
pid-file = /mysqlmul/mysqld1/mysqld1.pid
socket = /mysqlmul/mysqld1/mysqld1.sock

[mysqld2]
datadir = /mysqlmul/mysqld2
port = 3307
log-error = /mysqlmul/mysqld2/mysqld2.err
pid-file = /mysqlmul/mysqld2/mysqld2.pid
socket = /mysqlmul/mysqld2/mysqld2.sock

创建工作目录

mkdir -p /mysqlmul/mysqld1
mkdir -p /mysqlmul/mysqld2

将mysql添加到环境变量

vim /etc/profile

export PATH=/usr/local/mysql/bin:$PATH

##生效
source /etc/profile

新建MySQL用户

useradd -M -s /sbin/nologin mysql

启动第一台

mysqld_multi start 1

image-20240420161212272

接下来 拿着这个生成的密码登录mysql:

mysql -uroot -p'gFIA/htj*7ho' -S /mysqlmul/mysqld1/mysqld1.sock

在修改当前的用户密码 就可以:

alter user 'root'@'localhost' identified by 'xyy520';
flush privileges;

image-20240420161406856

启动第二台

mysqld_multi start 2

image-20240420161440774

同样,修改密码即可:

mysql -uroot -p'J%oRIn&8sdpX' -S /mysqlmul/mysqld2/mysqld2.sock


alter user 'root'@'localhost' identified by 'xyy520';
flush privileges;

image-20240420161406856

验证

接下来验证 可以看到3306,3307 端口都在listen 实验成功:

image-20240420161719808

再测试

  • 关闭mysql实例
[root@vm-template ~]#mysqld_multi stop 1
[root@vm-template ~]#mysqld_multi stop 2
[root@vm-template ~]#mysqld_multi stop 3

image-20240420162425284

[!NOTE]

这个方法无法停止mysql实例哦……,只能使用mysqladmin来停止实例了……😒

  • 业务开机自启脚本😒

  • 且这些实例都是以root身份运行的……

image-20240420161719808

以上3个问题后续再看吧,本次到此结束。😂

扩展

在原基础上添加一个或多个实例

 1,首先编辑/etc/my.cnf文件,添加新的实列配置就可以,配置如下
 vim /etc/my.cnf
 
 [mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root

[mysqld1]   
datadir = /mysqlmul/mysqld1
port = 3306 
log-error = /mysqlmul/mysqld1/mysqld1.err
pid-file = /mysqlmul/mysqld1/mysqld1.pid
socket = /mysqlmul/mysqld1/mysqld1.sock
              
[mysqld2] 
datadir = /mysqlmul/mysqld2
port = 3307 
log-error = /mysqlmul/mysqld2/mysqld2.err
pid-file = /mysqlmul/mysqld2/mysqld2.pid
socket = /mysqlmul/mysqld2/mysqld2.sock

[mysqld3] 
datadir = /mysqlmul/mysqld3
port = 3308 
log-error = /mysqlmul/mysqld3/mysqld3.err
pid-file = /mysqlmul/mysqld3/mysqld3.pid
socket = /mysqlmul/mysqld3/mysqld3.sock

##创建目录
mkdir -p /mysqlmul/mysqld3

2,再重新按照以下命令启动就可以 密码配置同上面的配置就可以
mysqld_multi start  3

mysql -uroot -p'tz?jL>ul3x%%' -S /mysqlmul/mysqld3/mysqld3.sock


alter user 'root'@'localhost' identified by 'xyy520';
flush privileges;

验证 可以看见由两台实例变为三台实例:

image-20240420162056943

FAQ

参考文档

感谢这位博主的详细文档哦:😘

https://blog.csdn.net/m0_56796630/article/details/132433854

image-20240420160137662

其它

以下方法,自己测试一直有问题,这里仅仅做记录:

  • 实战案例:CentOS 7 实现MySQL的多实例

image-20240420163439785

yum install mariadb-server
Systemctl start mariadb

mkdir  -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid} 
chown  -R mysql.mysql /mysql

mysql_install_db  --datadir=/mysql/3306/data --user=mysql
mysql_install_db  --datadir=/mysql/3307/data --user=mysql
mysql_install_db  --datadir=/mysql/3308/data --user=mysql

cp /etc/my.cnf /mysql/3306/etc/
vim /mysql/3306/etc/my.cnf
[mysqld]
#加此行,如果port是3306可省略此行
port=3306  
datadir=/mysql/3306/data/
socket=/mysql/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid
#重复上面步骤设置3307,3308


vim /mysql/3306/bin/mysqld 
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="magedu"
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
     printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaultsfile=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
    else
     printf "MySQL is running...\n"
      exit
    fi
}
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock}
shutdown
   fi
}
function_restart_mysql()
{
   printf "Restarting MySQL...\n"
   function_stop_mysql
    sleep 2
   function_start_mysql
}
case $1 in
start)
   function_start_mysql
;;
stop)
   function_stop_mysql
;;
restart)
   function_restart_mysql
;;
*)
   printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
#重复上述过程,分别建立3307,3308的启动脚本     
     
     
     
/mysql/3306/bin/mysqld  start
/mysql/3307/bin/mysqld  start
/mysql/3308/bin/mysqld  start


/mysql/3306/bin/mysqld  start
mysql -uroot -S /mysql/3306/socket/mysql.sock
mariadb>show variables like ‘port’  #确认连接的端口

mysqladmin -uroot -S /mysql/3306/socket/mysql.sock password 'magedu'  #加上新口令
#或者登录mysql,执行下面也可以
Mariadb>update mysql.user set password=password(“centos”) where user=’root’;
Mariadb>flush privileges;
#重复步骤,分别修改别外两个实例3307,3308对应root口令


mysql -uroot -S /mysql/3306/socket/mysql.sock –p #提示输入口令才能登录

关于我

我的博客主旨:

  • 排版美观,语言精炼;
  • 文档即手册,步骤明细,拒绝埋坑,提供源码;
  • 本人实战文档都是亲测成功的,各位小伙伴在实际操作过程中如有什么疑问,可随时联系本人帮您解决问题,让我们一起进步!

🍀 微信二维码

x2675263825 (舍得), qq:2675263825。

image-20230107215114763

🍀 微信公众号

《云原生架构师实战》

image-20230107215126971

🍀 个人博客站点

https://onedayxyy.cn/

🍀 语雀

https://www.yuque.com/xyy-onlyone

🍀 csdn

https://blog.csdn.net/weixin_39246554?spm=1010.2135.3001.5421

image-20230107215149885

🍀 知乎

https://www.zhihu.com/people/foryouone

image-20230107215203185

最后

好了,关于本次就到这里了,感谢大家阅读,最后祝大家生活快乐,每天都过的有意义哦,我们下期见!


  目录