概述
MySQL 多实例是指在同一台物理服务器（或虚拟机）上，通过不同的配置文件、端口、数据目录等隔离参数，运行多个独立的 MySQL 服务进程。每个实例拥有自己的数据库、用户、权限、日志和内存资源（可通过配置限制），逻辑上等同于多台独立的 MySQL 服务器。
通过大白话讲就是：
- 一般在一个系统环境中，可以运行多个相同的服务程序信息，并且产生不同的进程和网络端口信息，就可以称为多实例概念；
- 在数据库服务运行过程中，也可以启动多个数据库服务程序，产生多个数据库服务进程和不同的服务端口，形成多实例；
- 多个数据库服务实例信息中存储的数据库信息是相互隔离和独立的，并且利用数据库服务多实例可以实现测试与分布式架构需求。
企业数据库服务端实例应用架构设计图：
MySQL多实例的核心作用
资源利用率最大化（核心价值）
- 单台服务器硬件资源（CPU、内存、磁盘）未饱和时，多实例可充分利用空闲资源，避免硬件浪费。
例：一台 32 核 64G 内存的服务器，仅运行 1 个 MySQL 实例（占用 16G 内存），剩余内存可部署 2-3 个实例，服务不同业务。
隔离性与风险控制
- 业务隔离：不同业务（如电商订单、用户会员、数据分析）部署在独立实例，避免某业务的慢查询、高并发影响其他业务。
- 权限隔离：每个实例有独立的 root 用户和业务账号，防止跨业务数据泄露或误操作。
- 故障隔离：一个实例崩溃（如内存溢出、表损坏），其他实例不受影响，降低整体服务中断风险。
成本优化
- 减少服务器数量，降低硬件采购、机房托管、电力散热等成本，尤其适合中小团队或测试环境。
- 避免 “为单一低负载业务单独部署服务器” 的资源浪费。
灵活适配不同需求
- 可针对不同实例配置差异化参数：
- 核心业务实例：分配更多内存（innodb_buffer_pool_size）、更高 IO 优先级，优化并发性能。
- 测试 / 报表实例：分配较少资源，允许较低的查询性能，不影响核心业务。
- 支持不同 MySQL 版本（如 5.7 和 8.0）共存，满足老系统兼容和新功能测试需求。
MySQL多实例配置实操
部署规划：
| 实例服务端口 | 实例存储路径 | 实例配置文件 | 套接字文件 |
|---|---|---|---|
| 端口信息：3307 | /data/3307/data | /data/3307/data/my.cnf | /tmp/mysql3307.sock |
| 端口信息：3308 | /data/3308/data | /data/3308/data/my.cnf | /tmp/mysql3308.sock |
| 端口信息：3309 | /data/3309/data | /data/3309/data/my.cnf | /tmp/mysql3309.sock |
下载前置依赖
# 下载libtinfo5、libnuma1依赖 root@master:~# apt update -y && apt install -y libtinfo5 libnuma1 下载mysql服务
方式一：进入官网下载，并上传到宿主机中，适合离线环境
官网下载地址： https://downloads.mysql.com/archives/community/
方式二：直接在宿主机中使用wget进行下载，适合宿主机联网环境
root@master:/data00/software/mysql_8.0.26# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz 解压mysql服务并配置
# 服务解压至家目录 root@master:/data00/software/mysql_8.0.26# tar -xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz # 创建软链接 root@master:/data00/software/mysql_8.0.26# ln -s /data00/software/mysql_8.0.26/mysql-8.0.26-linux-glibc2.12-x86_64 /root/mysql # 配置环境变量 root@master:~# echo 'export PATH=/root/mysql/bin:$PATH' >> /etc/profile root@master:~# source /etc/profile # 检查环境变量 root@master:~# mysql -V mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL) 创建mysql虚拟用户及存储目录
创建虚拟用户
# 创建用户 root@master:~# useradd -s /sbin/nologin -M mysql # 检查用户 root@master:~# id mysql uid=2001(mysql) gid=2002(mysql) groups=2002(mysql) 创建存储目录
root@master:~# mkdir -p /data00/data/mysql330{7..9} root@master:~# ll /data00/data/ total 16 drwxr-xr-x 6 mysql mysql 4096 Nov 28 14:27 mysql drwxr-xr-x 2 root root 4096 Nov 28 15:03 mysql3307 drwxr-xr-x 2 root root 4096 Nov 28 15:03 mysql3308 drwxr-xr-x 2 root root 4096 Nov 28 15:03 mysql3309 进行多实例数据目录初始化步骤
# 如果存在my.cnf，挪走 root@master:~# ll /etc/my.cnf -rw-r--r-- 1 root root 394 Nov 28 14:25 /etc/my.cnf root@master:~# mv /etc/my.cnf /tmp # 初始化3307实例 root@master:~# mysqld --initialize-insecure --user=mysql --datadir=/data00/data/mysql3307 --basedir=/root/mysql 2025-11-28T07:06:59.371413Z 0 [System] [MY-013169] [Server] /data00/software/mysql_8.0.26/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 712455 2025-11-28T07:06:59.378238Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2025-11-28T07:06:59.908463Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2025-11-28T07:07:00.800804Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2025-11-28T07:07:00.801007Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2025-11-28T07:07:00.831043Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. # 初始化3308实例 root@master:~# mysqld --initialize-insecure --user=mysql --datadir=/data00/data/mysql3308 --basedir=/root/mysql 2025-11-28T07:07:51.734893Z 0 [System] [MY-013169] [Server] /data00/software/mysql_8.0.26/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 712572 2025-11-28T07:07:51.742666Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2025-11-28T07:07:52.250511Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2025-11-28T07:07:53.016441Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2025-11-28T07:07:53.016686Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2025-11-28T07:07:53.060209Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. # 初始化3309实例 root@master:~# mysqld --initialize-insecure --user=mysql --datadir=/data00/data/mysql3309 --basedir=/root/mysql 2025-11-28T07:08:28.983028Z 0 [System] [MY-013169] [Server] /data00/software/mysql_8.0.26/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 712843 2025-11-28T07:08:28.989875Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2025-11-28T07:08:29.529660Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2025-11-28T07:08:30.454927Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2025-11-28T07:08:30.455127Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2025-11-28T07:08:30.593524Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 编写实例配置文件
# 3307实例 root@master:~# cat >/data00/data/mysql3307/my.cnf <<EOF [mysqld] server_id=3307 port=3307 user=mysql basedir=/root/mysql datadir=/data00/data/mysql3307 socket=/tmp/mysql3307.sock EOF # 3308实例 root@master:~# cat >/data00/data/mysql3308/my.cnf <<EOF [mysqld] server_id=3308 port=3308 user=mysql basedir=/root/mysql datadir=/data00/data/mysql3308 socket=/tmp/mysql3308.sock EOF # 3309实例 root@master:~# cat >/data00/data/mysql3309/my.cnf <<EOF [mysqld] server_id=3309 port=3309 user=mysql basedir=/root/mysql datadir=/data00/data/mysql3309 socket=/tmp/mysql3309.sock EOF 编写systemd配置文件
# 3307 systemd root@master:~# cat >/usr/lib/systemd/system/mysqld3307.service<<EOF [Unit] Description=MySQL Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/root/mysql/bin/mysqld --defaults-file=/data00/data/mysql3307/my.cnf LimitNOFILE = 5000 EOF # 3308 systemd root@master:~# cat >/usr/lib/systemd/system/mysqld3308.service<<EOF [Unit] Description=MySQL Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/root/mysql/bin/mysqld --defaults-file=/data00/data/mysql3308/my.cnf LimitNOFILE = 5000 EOF # 3309 systemd root@master:~# cat >/usr/lib/systemd/system/mysqld3309.service<<EOF [Unit] Description=MySQL Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/root/mysql/bin/mysqld --defaults-file=/data00/data/mysql3309/my.cnf LimitNOFILE = 5000 EOF 启动mysql
# 加载systemd文件 root@master:~# systemctl daemon-reload # 启动服务 root@master:~# systemctl start mysqld3307 root@master:~# systemctl start mysqld3308 root@master:~# systemctl start mysqld3309 登录实例并初始化（修改密码、授权）
- 初始化3307
root@master:~# mysql -uroot -S /tmp/mysql3307.sock # 修改root用户密码为root mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; Query OK, 0 rows affected (0.01 sec) # 创建一个可远程连接的root用户（可选） mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root'; Query OK, 0 rows affected (0.01 sec) # 授权（可选） mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) # 刷新权限 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) # 退出mysql，重新使用用户名、密码连接 root@master:~# mysql -uroot -proot -P3307 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 13 Server version: 8.0.26 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> - 初始化3308
# 使用sock连接mysql root@master:~# mysql -uroot -S /tmp/mysql3308.sock # 修改root用户密码为root mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; Query OK, 0 rows affected (0.01 sec) # 创建一个可远程连接的root用户（可选） mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root'; Query OK, 0 rows affected (0.01 sec) # 授权（可选） mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) # 刷新权限 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) # 退出mysql，重新使用用户名、密码连接 root@master:~# mysql -uroot -proot -P3308 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 13 Server version: 8.0.26 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> - 初始化3309
root@master:~# mysql -uroot -S /tmp/mysql3309.sock # 修改root用户密码为root mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; Query OK, 0 rows affected (0.01 sec) # 创建一个可远程连接的root用户（可选） mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root'; Query OK, 0 rows affected (0.01 sec) # 授权（可选） mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) # 刷新权限 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) # 退出mysql，重新使用用户名、密码连接 root@master:~# mysql -uroot -proot -P3309 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 13 Server version: 8.0.26 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> 
