1 单机部署多实例概述
MySQL多实例就是在一台机器上开启多个不同的服务端口(如:3306,3307),运行多个MySQL服务进程,通过不同的socket监听不同的服务端口来提供各自的服务,每个实例有自己的配置文件,数据文件,进程及日志文件。
为什么要做多实例:
- 资源隔离,减少相互影响
- 分担连接数(否则连接数上升性能会下降)
- 更充分的利用资源(不同的业务错高峰混跑),从而节约服务器资源
优点:
- 有效的利用服务器资源
- 当单个服务器资源有剩余时,可以利用多实例来充分利用服务器的资源来提供更多的服务
缺点:
会出现资源互相抢占的现象。当某个实例的并发量很高,或者有慢查询时,会消耗服务器更多的资源,这时就会影响其他实例提供的服务,访问质量下降。
部署mysql多实例的两种方式
- 使用多个配置文件启动不同的进程来实现多实例,这种方式的优势逻辑简单,配置简单,缺点是管理起来不太方便。 这种方式就是多次部署多套单实例,只是目录结构和端口不一样。这个和单实例操作一样,这里不再单独演示。
- 通过官方自带的mysqld_multi使用单独的配置文件来实现多实例,这种方式定制每个实例的配置不太方面,优点是管理起来很方便,集中管理; 我们这里演示的是这种方法。
2 部署示例
2.1 安装MySQL 软件
MySQL 软件的安装参考之前的博客:
Redhat 7.7 平台 MySQL 5.7.33 安装手册(Tar包)
https://www.cndba.cn/dave/article/4509
2.2 创建多实例
单机环境部署多实例需要注意以下问题:
- 配置文件安装路径不能相同
- 数据库目录不能相同
- 启动脚本不能同名
- 端口不能相同
- socket文件的生成路径不能相同
2.2.1 创建目录结构
[root@dm8 data]# mkdir -p /data/mysql/{3306,3307,3308}/data
[root@dm8 mysql]# mkdir -p /data/mysql/{3306,3307,3308}/log
[root@dm8 mysql]# touch /data/mysql/3306/log/error.log
[root@dm8 mysql]# touch /data/mysql/3307/log/error.log
[root@dm8 mysql]# touch /data/mysql/3308/log/error.log
[root@dm8 mysql]# chown -R mysql:mysql /data/mysql
2.2.2 修改配置文件
在/etc/my.cnf 文件中添加如下内容:
[root@dm8 3306]# cat /etc/my.cnf
[client]
default-character-set=utf8mb4
[mysqld]
user=mysql
basedir=/usr/local/mysql
lower_case_table_names=1
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/data/mysql/mysqld_multi.log
# 3306 数据库实例
[mysqld3306]
port=3306
server_id=1
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/3306/data
socket=/tmp/mysql_3306.sock
log-error=/data/mysql/3306/log/error.log
pid-file=/data/mysql/3306/mysql3306.pid
## skip-grant-tables #用于跳过密码登录
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
explicit_defaults_for_timestamp=true
# 3307 数据库实例
[mysqld3307]
port=3307
server_id=2
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/3307/data
socket=/tmp/mysql_3307.sock
log-error=/data/mysql/3307/log/error.log
pid-file=/data/mysql/3307/mysql3307.pid
## 默认最大连接数设置
# max_connections=300
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
explicit_defaults_for_timestamp=true
# 3308 数据库实例
[mysqld3308]
port=3308
server_id=3
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/3308/data
socket=/tmp/mysql_3308.sock
log-error=/data/mysql/3308/log/error.log
pid-file=/data/mysql/3308/mysql3308.pid
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
explicit_defaults_for_timestamp=true
[root@dm8 3306]#
2.2.3 初始化实例
[dave@www.cndba.cn ~]$ mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/3306/data/ --initialize
2022-05-28T02:40:22.905532Z 1 [Note] A temporary password is generated for root@localhost: AKL24)n+%qfC
[dave@www.cndba.cn ~]$ mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/3307/data/ --initialize
2022-05-28T02:40:33.266853Z 1 [Note] A temporary password is generated for root@localhost: RQbAkX9p*/rH
[dave@www.cndba.cn ~]$ mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/3308/data/ --initialize
2022-05-28T02:40:43.185864Z 1 [Note] A temporary password is generated for root@localhost: +tYq1prSkQqv
[dave@www.cndba.cn ~]$
2.2.4 启动实例
启动mysql
[dave@www.cndba.cn ~]$ mysqld_multi --defaults-file=/etc/my.cnf start 3306
[dave@www.cndba.cn ~]$ mysqld_multi --defaults-file=/etc/my.cnf start 3307
[dave@www.cndba.cn ~]$ mysqld_multi --defaults-file=/etc/my.cnf start 3308
查看mysql服务
[dave@www.cndba.cn ~]$ mysqld_multi --defaults-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[dave@www.cndba.cn ~]$
2.2.5 修改root用户密码并配置远程登录
Root 用户的密码在刚才初始化的时候有提示。
设置本地访问密码
[dave@www.cndba.cn ~]$ mysql -uroot -p -S /tmp/mysql_3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.7.33
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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
授权root用户可以远程登陆
GRANT ALL ON *.* TO 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
flush privileges;
mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2.3 多实例启停管理
直接使用mysqld_multi进行多实例管理:
[dave@www.cndba.cn ~]$ mysqld_multi --help
mysqld_multi version 2.16 by Jani Tolonen
……
Usage: mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
……
启动/停止全部实例:
/usr/local/mysql/bin/mysqld_multi start
/usr/local/mysql/bin/mysqld_multi stop
/usr/local/mysql/bin/mysqld_multi report
启动/停止单个实例:
/usr/local/mysql/bin/mysqld_multi start 3306
/usr/local/mysql/bin/mysqld_multi stop 3306
/usr/local/mysql/bin/mysqld_multi report 3306
2.4 mysqld_multi stop 不生效的解决方法
实际测试stop 并没有停掉:
[dave@www.cndba.cn ~]$ mysqld_multi stop 3306
[dave@www.cndba.cn ~]$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[dave@www.cndba.cn ~]$
[dave@www.cndba.cn ~]$ mysqld_multi --defaults-file=/etc/my.cnf stop 3306
[dave@www.cndba.cn ~]$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[dave@www.cndba.cn ~]$
在数据库级别停则没有问题:
[dave@www.cndba.cn ~]$ mysql -uroot -proot -S /tmp/mysql_3306.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 14
Server version: 5.7.33 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> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql>
[dave@www.cndba.cn ~]$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[dave@www.cndba.cn ~]$
查看了mysqld_multi stop后的实例日志,提示有认证信息:
2022-05-28T03:10:32.873545Z 13 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2022-05-28T03:13:43.752827Z 14 [Note] Access denied for user 'root'@'localhost' (using password: NO)
也就是说mysqld_multi stop 命令必须凭借有效的 user & password 配置去管理相应的实例。
因此可以创建一个独立的用户来关闭数据库,或者使用我们之前的root用户:
grant shutdown on . to ‘username’@’localhost’ identified by ‘password’
[dave@www.cndba.cn log]$ cat /etc/my.cnf
[client]
default-character-set=utf8mb4
[mysqld]
user=mysql
basedir=/usr/local/mysql
lower_case_table_names=1
[mysqld_multi]
user=root
pass=root
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/data/mysql/mysqld_multi.log
注意这里用的是pass=root。 如果写成password 一样是无效的,重新测试可以正常管理:
[dave@www.cndba.cn ~]$ mysqld_multi stop 3307
[dave@www.cndba.cn ~]$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is running
[dave@www.cndba.cn ~]$
[dave@www.cndba.cn ~]$ mysqld_multi stop
[dave@www.cndba.cn ~]$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
[dave@www.cndba.cn ~]$
[dave@www.cndba.cn ~]$ mysqld_multi start
[dave@www.cndba.cn ~]$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[dave@www.cndba.cn ~]$
[root@dm8 3306]# netstat -lntup |grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 19009/mysqld
tcp6 0 0 :::3307 :::* LISTEN 19092/mysqld
tcp6 0 0 :::3308 :::* LISTEN 19170/mysqld
[root@dm8 3306]#
版权声明:本文为博主原创文章,未经博主允许不得转载。