签到成功

知道了

CNDBA社区CNDBA社区

MySQL 单机 部署 多实例 操作手册

2022-05-28 11:32 1956 0 原创 MySQL
作者: dave

1 单机部署多实例概述


MySQL多实例就是在一台机器上开启多个不同的服务端口(如:3306,3307),运行多个MySQL服务进程,通过不同的socket监听不同的服务端口来提供各自的服务,每个实例有自己的配置文件,数据文件,进程及日志文件。http://www.cndba.cn/cndba/dave/article/108028http://www.cndba.cn/cndba/dave/article/108028

为什么要做多实例:

http://www.cndba.cn/cndba/dave/article/108028
http://www.cndba.cn/cndba/dave/article/108028

  1. 资源隔离,减少相互影响
  2. 分担连接数(否则连接数上升性能会下降)
  3. 更充分的利用资源(不同的业务错高峰混跑),从而节约服务器资源

优点:

  1. 有效的利用服务器资源
  2. 当单个服务器资源有剩余时,可以利用多实例来充分利用服务器的资源来提供更多的服务

缺点:
会出现资源互相抢占的现象。当某个实例的并发量很高,或者有慢查询时,会消耗服务器更多的资源,这时就会影响其他实例提供的服务,访问质量下降。

部署mysql多实例的两种方式

  1. 使用多个配置文件启动不同的进程来实现多实例,这种方式的优势逻辑简单,配置简单,缺点是管理起来不太方便。 这种方式就是多次部署多套单实例,只是目录结构和端口不一样。这个和单实例操作一样,这里不再单独演示。
  2. 通过官方自带的mysqld_multi使用单独的配置文件来实现多实例,这种方式定制每个实例的配置不太方面,优点是管理起来很方便,集中管理; 我们这里演示的是这种方法。

2 部署示例


2.1 安装MySQL 软件

MySQL 软件的安装参考之前的博客:

Redhat 7.7 平台 MySQL 5.7.33 安装手册(Tar包)
https://www.cndba.cn/dave/article/4509

2.2 创建多实例

单机环境部署多实例需要注意以下问题:

  1. 配置文件安装路径不能相同
  2. 数据库目录不能相同
  3. 启动脚本不能同名
  4. 端口不能相同
  5. 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服务

http://www.cndba.cn/cndba/dave/article/108028

[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 并没有停掉:http://www.cndba.cn/cndba/dave/article/108028

[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 ~]$

在数据库级别停则没有问题:

http://www.cndba.cn/cndba/dave/article/108028

[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 配置去管理相应的实例。 http://www.cndba.cn/cndba/dave/article/108028http://www.cndba.cn/cndba/dave/article/108028

因此可以创建一个独立的用户来关闭数据库,或者使用我们之前的root用户:

grant shutdown on . to ‘username’@’localhost’ identified by ‘password’

http://www.cndba.cn/cndba/dave/article/108028

[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]#

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2283
    原创
  • 3
    翻译
  • 579
    转载
  • 196
    评论
  • 访问:8172873次
  • 积分:4428
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ