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

mysql
linux
发布日期:   2024-04-20
更新日期:   2024-04-20
文章字数:   1.4k
阅读时长:   6 分

实战：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

最后

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

mysql
赏
  目录