MySQL教程FG177-MySQL多实例部署与管理

内容简介：MySQL多实例是在同一台服务器上运行多个MySQL服务实例的技术，可提高服务器资源利用率。本文风哥教程参考MySQL官方文档MySQL Server Administration部分，详细介绍MySQL多实例的部署、配置和管理方法，帮助DBA高效管理MySQL多实例环境。学习交流加群风哥QQ113257174

Part01-基础概念与理论知识

1.1 MySQL多实例概述

MySQL多实例是指在同一台物理服务器上运行多个独立的MySQL服务实例，每个实例具有：

独立的配置文件
独立的数据目录
独立的端口号
独立的进程空间
独立的内存资源

1.2 MySQL多实例优势

MySQL多实例的主要优势包括：

资源利用率高：充分利用服务器资源，提高硬件投资回报率
隔离性好：实例之间相互独立，一个实例故障不影响其他实例
管理方便：集中管理多个数据库服务，减少运维成本
成本降低：减少服务器数量，降低硬件和维护成本
灵活性强：可以为不同业务配置不同的参数和资源

Part02-生产环境规划与建议

2.1 多实例部署规划

在生产环境中，多实例的部署需要考虑以下因素：

服务器硬件：CPU核心数、内存大小、磁盘空间等
业务需求：每个实例的性能要求、数据量大小等
资源隔离：使用cgroups等技术实现资源隔离
高可用性：考虑复制、备份等容灾策略

2.2 资源分配规划

多实例的资源分配需要根据业务需求和服务器能力来确定：

内存分配：每个实例分配独立的buffer pool、log buffer等
CPU分配：使用CPU亲和性或cgroups限制每个实例的CPU使用
磁盘分配：不同实例的数据目录最好放在不同的磁盘上
网络分配：为不同实例配置不同的端口号

2.3 端口与路径规划

多实例的端口和路径规划需要统一管理：

端口规划：建议使用连续的端口号，如3306、3307、3308等
路径规划：统一的数据目录结构，如/mysql/data/3306、/mysql/data/3307等
配置文件：统一的配置文件命名，如/etc/my.cnf.d/my3306.cnf、/etc/my.cnf.d/my3307.cnf等

风哥提示：多实例部署时，资源分配需要预留一定的冗余，避免单个实例资源耗尽影响其他实例。

Part03-生产环境项目实施方案

3.1 多实例部署准备

多实例部署前需要进行准备工作。

# 检查系统环境
# cat /etc/redhat-release
Red Hat Enterprise Linux release 9.3 (Plow)

# 检查MySQL是否已安装
# rpm -qa | grep mysql
mysql-community-server-8.4.0-1.el9.x86_64
mysql-community-client-8.4.0-1.el9.x86_64
mysql-community-common-8.4.0-1.el9.x86_64
mysql-community-libs-8.4.0-1.el9.x86_64

# 创建多实例目录结构
# mkdir -p /mysql/data/3306
# mkdir -p /mysql/data/3307
# mkdir -p /mysql/logs/3306
# mkdir -p /mysql/logs/3307
# mkdir -p /mysql/tmp/3306
# mkdir -p /mysql/tmp/3307

# 设置目录权限
# chown -R mysql:mysql /mysql
# chmod -R 755 /mysql

3.2 多实例安装配置

配置多实例的配置文件和初始化数据库。

# 创建3306实例配置文件
# vi /etc/my.cnf.d/my3306.cnf
[mysqld]
# 实例唯一标识
server-id = 1
# 端口号
port = 3306
# 数据目录
datadir = /mysql/data/3306
# 套接字文件
socket = /mysql/tmp/3306/mysql.sock
# 错误日志
log_error = /mysql/logs/3306/error.log
# 二进制日志
log_bin = /mysql/logs/3306/binlog
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /mysql/logs/3306/slow.log
# 缓冲池大小
innodb_buffer_pool_size = 1G
# 日志文件大小
innodb_log_file_size = 256M
# 临时表目录
tmpdir = /mysql/tmp/3306

[mysql]
socket = /mysql/tmp/3306/mysql.sock

[mysqladmin]
socket = /mysql/tmp/3306/mysql.sock

[mysqldump]
socket = /mysql/tmp/3306/mysql.sock

# 创建3307实例配置文件
# vi /etc/my.cnf.d/my3307.cnf
[mysqld]
# 实例唯一标识
server-id = 2
# 端口号
port = 3307
# 数据目录
datadir = /mysql/data/3307
# 套接字文件
socket = /mysql/tmp/3307/mysql.sock
# 错误日志
log_error = /mysql/logs/3307/error.log
# 二进制日志
log_bin = /mysql/logs/3307/binlog
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /mysql/logs/3307/slow.log
# 缓冲池大小
innodb_buffer_pool_size = 1G
# 日志文件大小
innodb_log_file_size = 256M
# 临时表目录
tmpdir = /mysql/tmp/3307

[mysql]
socket = /mysql/tmp/3307/mysql.sock

[mysqladmin]
socket = /mysql/tmp/3307/mysql.sock

[mysqldump]
socket = /mysql/tmp/3307/mysql.sock

# 初始化3306实例
# mysqld –defaults-file=/etc/my.cnf.d/my3306.cnf –initialize –user=mysql
2026-04-02T14:00:00.123456Z 0 [Warning] [MY-011068] [Server] The syntax ‘expire-logs-days’ is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2026-04-02T14:00:00.123456Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.4.0) initializing of server in progress as process 12345
2026-04-02T14:00:00.123456Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2026-04-02T14:00:00.123456Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2026-04-02T14:00:00.123456Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ABCDEFGHIJKLMNOP

# 初始化3307实例
# mysqld –defaults-file=/etc/my.cnf.d/my3307.cnf –initialize –user=mysql
2026-04-02T14:01:00.123456Z 0 [Warning] [MY-011068] [Server] The syntax ‘expire-logs-days’ is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2026-04-02T14:01:00.123456Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.4.0) initializing of server in progress as process 12346
2026-04-02T14:01:00.123456Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2026-04-02T14:01:00.123456Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2026-04-02T14:01:00.123456Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: QRSTUVWXYZABCDEF

3.3 多实例服务管理

创建多实例的系统服务，便于管理。

# 创建3306实例的systemd服务文件
# vi /usr/lib/systemd/system/mysqld_3306.service
[Unit]
Description=MySQL Server 3306
After=network.target remote-fs.target nss-lookup.target

[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld –defaults-file=/etc/my.cnf.d/my3306.cnf
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

# 创建3307实例的systemd服务文件
# vi /usr/lib/systemd/system/mysqld_3307.service
[Unit]
Description=MySQL Server 3307
After=network.target remote-fs.target nss-lookup.target

[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld –defaults-file=/etc/my.cnf.d/my3307.cnf
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

# 重载systemd配置
# systemctl daemon-reload

# 启动3306实例
# systemctl start mysqld_3306

# 启动3307实例
# systemctl start mysqld_3307

# 查看实例状态
# systemctl status mysqld_3306
● mysqld_3306.service – MySQL Server 3306
Loaded: loaded (/usr/lib/systemd/system/mysqld_3306.service; disabled; vendor preset: disabled)
Active: active (running) since Tue 2026-04-02 14:02:00 CST; 1min ago
Main PID: 12347 (mysqld)
Status: “Server is operational”
Tasks: 38 (limit: 23456)
Memory: 1.2G
CGroup: /system.slice/mysqld_3306.service
└─12347 /usr/sbin/mysqld –defaults-file=/etc/my.cnf.d/my3306.cnf

# systemctl status mysqld_3307
● mysqld_3307.service – MySQL Server 3307
Loaded: loaded (/usr/lib/systemd/system/mysqld_3307.service; disabled; vendor preset: disabled)
Active: active (running) since Tue 2026-04-02 14:02:05 CST; 1min ago
Main PID: 12348 (mysqld)
Status: “Server is operational”
Tasks: 38 (limit: 23456)
Memory: 1.2G
CGroup: /system.slice/mysqld_3307.service
└─12348 /usr/sbin/mysqld –defaults-file=/etc/my.cnf.d/my3307.cnf

Part04-生产案例与实战讲解

4.1 多实例日常操作

多实例的日常操作包括连接、启动、停止、重启等。

# 连接3306实例
# mysql -u root -p -S /mysql/tmp/3306/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.0 MySQL Community Server – GPL

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> SELECT @@port;
+——–+
| @@port |
+——–+
| 3306 |
+——–+
1 row in set (0.00 sec)

# 连接3307实例
# mysql -u root -p -S /mysql/tmp/3307/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.0 MySQL Community Server – GPL

mysql> SELECT @@port;
+——–+
| @@port |
+——–+
| 3307 |
+——–+
1 row in set (0.00 sec)

# 修改3306实例root密码
# mysqladmin -u root -p -S /mysql/tmp/3306/mysql.sock password ‘new_password’
Enter password:

# 修改3307实例root密码
# mysqladmin -u root -p -S /mysql/tmp/3307/mysql.sock password ‘new_password’
Enter password:

# 重启3306实例
# systemctl restart mysqld_3306

# 停止3307实例
# systemctl stop mysqld_3307

# 设置开机自启
# systemctl enable mysqld_3306
# systemctl enable mysqld_3307

4.2 多实例监控管理

监控多实例的运行状态和性能。

# 查看实例进程
# ps aux | grep mysqld
mysql 12347 0.5 5.0 1234567 204800 ? Sl 14:02 0:01 /usr/sbin/mysqld –defaults-file=/etc/my.cnf.d/my3306.cnf
mysql 12348 0.4 5.0 1234567 204800 ? Sl 14:02 0:01 /usr/sbin/mysqld –defaults-file=/etc/my.cnf.d/my3307.cnf

# 查看实例端口
# netstat -tlnp | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 12347/mysqld
tcp6 0 0 :::3307 :::* LISTEN 12348/mysqld

# 监控实例资源使用
# top -p 12347,12348
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12347 mysql 20 0 1234567 204800 36800 S 0.3 5.0 0:01.23 mysqld
12348 mysql 20 0 1234567 204800 36800 S 0.2 5.0 0:01.15 mysqld

# 编写多实例监控脚本
#!/bin/bash
# mysql_multi_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

INSTANCES=(3306 3307)

for port in “${INSTANCES[@]}”; do
echo “=== MySQL Instance $port Status ===”

# 检查服务状态
systemctl status mysqld_$port | grep -E “Active:|Main PID:|Status:”

# 检查连接数
mysql -u root -p”password” -S /mysql/tmp/$port/mysql.sock -e “SHOW STATUS LIKE ‘Threads_connected’;”

# 检查QPS
mysql -u root -p”password” -S /mysql/tmp/$port/mysql.sock -e “SHOW GLOBAL STATUS LIKE ‘Queries’;”

# 检查缓冲池使用情况
mysql -u root -p”password” -S /mysql/tmp/$port/mysql.sock -e “SHOW ENGINE INNODB STATUS\G” | grep -E “Buffer pool size|Free buffers|Database pages”

echo “”
done

4.3 多实例故障排查

排查多实例常见故障。

案例：实例启动失败
问题描述：3307实例启动失败
解决方法：查看错误日志，分析故障原因

# 查看实例状态
# systemctl status mysqld_3307
● mysqld_3307.service – MySQL Server 3307
Loaded: loaded (/usr/lib/systemd/system/mysqld_3307.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Tue 2026-04-02 14:15:00 CST; 1min ago
Process: 12349 ExecStart=/usr/sbin/mysqld –defaults-file=/etc/my.cnf.d/my3307.cnf (code=exited, status=1/FAILURE)
Main PID: 12349 (code=exited, status=1/FAILURE)
Status: “Server startup in progress”

# 查看错误日志
# tail -n 50 /mysql/logs/3307/error.log
2026-04-02T14:15:00.123456Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.0) starting as process 12349
2026-04-02T14:15:00.123456Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2026-04-02T14:15:00.123456Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2026-04-02T14:15:00.123456Z 0 [ERROR] [MY-010119] [Server] Aborting

# 检查数据目录权限
# ls -la /mysql/data/3307/
drwxr-xr-x 2 root root 4096 Apr 2 14:15 .
drwxr-xr-x 4 mysql mysql 4096 Apr 2 14:00 ..

# 修复权限
# chown -R mysql:mysql /mysql/data/3307/

# 重新启动实例
# systemctl start mysqld_3307

# 验证启动成功
# systemctl status mysqld_3307
● mysqld_3307.service – MySQL Server 3307
Loaded: loaded (/usr/lib/systemd/system/mysqld_3307.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2026-04-02 14:16:00 CST; 1min ago
Main PID: 12350 (mysqld)
Status: “Server is operational”

Part05-风哥经验总结与分享

5.1 多实例管理最佳实践

统一目录结构：使用统一的目录结构，便于管理和维护
合理分配资源：根据业务需求分配内存、CPU等资源
使用系统服务：创建systemd服务，便于启动、停止和开机自启
定期备份：每个实例都需要独立的备份策略
监控告警：建立多实例的监控和告警机制
安全管理：每个实例都需要独立的安全配置
版本一致：所有实例使用相同的MySQL版本，便于维护

5.2 多实例部署注意事项

风哥提示：多实例部署虽然可以提高资源利用率，但也增加了管理复杂度，需要注意以下事项。

资源隔离：使用cgroups等技术实现资源隔离，避免实例之间相互影响
端口冲突：确保每个实例使用不同的端口号
配置文件：每个实例的配置文件要独立，避免相互影响
性能监控：加强性能监控，及时发现资源瓶颈
故障隔离：一个实例故障不应影响其他实例的正常运行
备份恢复：每个实例都需要独立的备份和恢复策略
升级维护：升级维护时需要逐个实例进行，避免同时影响所有业务

生产环境建议：多实例部署适合资源充足的服务器，对于关键业务，建议使用独立的服务器部署，提高安全性和稳定性。更多视频教程www.fgedu.net.cn

from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html