1208  
查询码:00000145
mysql5.7.22 mysqld_multi多实例安装
作者: 文艺范儿 于 2020年07月30日 发布在分类 / Linux / mysql 下,并于 2020年07月31日 编辑
mysql多实例 mysql mysqld_multi

mysql5,7.22 mysqld_multi多实例安装

1. 背景

  MySQL数据库的集中化运维,可以通过在一台服务器上,部署运行多个MySQL服务进程,通过不同的socket监听不同的服务端口来提供各自的服务。各个实例之间是相互独立的,每个实例的datadir, port, socket, pid都是不同的。

2. 多实例特点

  1.有效利用服务器资源,当单个服务器资源有剩余时,可以充分利用剩余的资源提供更多的服务。
  2.资源互相抢占问题,当某个服务实例服务并发很高时或者开启慢查询时,会消耗更多的内存、CPU、磁盘IO资源,导致服务器上的其他实例提供服务的质量下降。

3. 环境 [ 关闭SeLinux ]

[root@dl_235 tools]# cat /etc/redhat-release 
CentOS Linux release 7.8.2003 (Core)

4. 部署 [ 8个实例 ]

首先安装mysql,此处不做介绍,正常安装即可:

#创建多实例数据目录及日志文件[如果不创建日志文件后面会报错]
mkdir -vp /home/deploy/mysql_data{1..8}/data
touch /home/deploy/mysql_data{1..8}/mysql.log

#修改 MySQL 多实例数据目录所属用户与所属组
chown mysql.mysql -R /home/deploy/mysql_data{1..8}
#配置 MySQL 配置文件 /etc/my.cnf
[mysqld_multi]
mysqld     = /home/deploy/mysql/bin/mysqld_safe
mysqladmin = /home/deploy/mysql/bin/mysqladmin
log        = /tmp/mysql_multi.log
user       = mysql
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysqld1]
event_scheduler=0
datadir    = /home/deploy/mysql_data1/data
socket     = /home/deploy/mysql_data1/mysql.sock
pid-file   = /home/deploy/mysql_data1/mysql.pid
log-error  = /home/deploy/mysql_data1/mysql.log
port       = 3301
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 21

[mysqld2]
event_scheduler=0
datadir    = /home/deploy/mysql_data2/data
socket     = /home/deploy/mysql_data2/mysql.sock
pid-file   = /home/deploy/mysql_data2/mysql.pid
log-error  = /home/deploy/mysql_data2/mysql.log
port       = 3302
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 22

[mysqld3]
event_scheduler=0
datadir    = /home/deploy/mysql_data3/data
socket     = /home/deploy/mysql_data3/mysql.sock
pid-file   = /home/deploy/mysql_data3/mysql.pid
log-error  = /home/deploy/mysql_data3/mysql.log
port       = 3303
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 23

[mysqld4]
event_scheduler=0
datadir    = /home/deploy/mysql_data4/data
socket     = /home/deploy/mysql_data4/mysql.sock
pid-file   = /home/deploy/mysql_data4/mysql.pid
log-error  = /home/deploy/mysql_data4/mysql.log
port       = 3304
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 24

[mysqld5]
event_scheduler=0
datadir    = /home/deploy/mysql_data5/data
socket     = /home/deploy/mysql_data5/mysql.sock
pid-file   = /home/deploy/mysql_data5/mysql.pid
log-error  = /home/deploy/mysql_data5/mysql.log
port       = 3305
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 25

[mysqld6]
event_scheduler=0
datadir    = /home/deploy/mysql_data6/data
socket     = /home/deploy/mysql_data6/mysql.sock
pid-file   = /home/deploy/mysql_data6/mysql.pid
log-error  = /home/deploy/mysql_data6/mysql.log
port       = 3306
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 26

[mysqld7]
event_scheduler=0
datadir    = /home/deploy/mysql_data7/data
socket     = /home/deploy/mysql_data7/mysql.sock
pid-file   = /home/deploy/mysql_data7/mysql.pid
log-error  = /home/deploy/mysql_data7/mysql.log
port       = 3307
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 27

[mysqld8]
event_scheduler=0
datadir    = /home/deploy/mysql_data8/data
socket     = /home/deploy/mysql_data8/mysql.sock
pid-file   = /home/deploy/mysql_data8/mysql.pid
log-error  = /home/deploy/mysql_data8/mysql.log
port       = 3308
user       = mysql
innodb_buffer_pool_size = 32M
skip-name-resolve = 0
server-id  = 28
#初始化各个实例,--initialize-insecure为不设置密码
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data1/data
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data2/data
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data3/data
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data4/data
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data5/data
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data6/data
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data7/data
/home/deploy/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/home/deploy/mysql --datadir=/home/deploy/mysql_data8/data
#复制脚本及修改脚本
#复制多实例脚本到服务管理目录下 [ /etc/init.d/ ]
cp /home/deploy/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

#添加脚本执行权限
chmod +x /etc/init.d/mysqld_multi

#注意修改mysqld_multi脚本目录,这里修改为
basedir=/home/deploy//mysql
bindir=/home/deploy/mysql/bin

#添加进service服务管理
chkconfig --add mysqld_multi

5. 启动测试

#查个多实例状态

[root@dl_235 deploy]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running
MySQL server from group: mysqld5 is not running
MySQL server from group: mysqld6 is not running
MySQL server from group: mysqld7 is not running
MySQL server from group: mysqld8 is not running
#启动多实例并查看状态和端口
[root@dl_235 ~]# /etc/init.d/mysqld_multi start 
[root@dl_235 ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running
MySQL server from group: mysqld6 is running
MySQL server from group: mysqld7 is running
MySQL server from group: mysqld8 is running
[root@dl_235 ~]# ss -lntup|grep 33
tcp    LISTEN     0      128    [::]:3301               [::]:*                   users:(("mysqld",pid=91390,fd=35))
tcp    LISTEN     0      128    [::]:3302               [::]:*                   users:(("mysqld",pid=83518,fd=40))
tcp    LISTEN     0      128    [::]:3303               [::]:*                   users:(("mysqld",pid=91551,fd=20))
tcp    LISTEN     0      128    [::]:3304               [::]:*                   users:(("mysqld",pid=83540,fd=37))
tcp    LISTEN     0      128    [::]:3305               [::]:*                   users:(("mysqld",pid=83565,fd=28))
tcp    LISTEN     0      128    [::]:3306               [::]:*                   users:(("mysqld",pid=83543,fd=31))
tcp    LISTEN     0      128    [::]:3307               [::]:*                   users:(("mysqld",pid=91553,fd=34))
tcp    LISTEN     0      128    [::]:3308               [::]:*                   users:(("mysqld",pid=83581,fd=37))

6. 连接测试

[root@dl_235 ~]# mysql -uroot -p -S /home/deploy/mysql_data1/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> 


 推荐知识

 历史版本

修改日期 修改人 备注
2020-07-31 10:49:22[当前版本] 文艺范儿 格式调整
2020-07-30 21:29:43 文艺范儿 创建版本

  目录
    文艺知识分享平台 -V 4.9.5 -wcp
    京公网安备100012199188号 京ICP备2021030911号