淡然若云 發表於 2025-11-28 16:15:00

MySQL多实例配置

<h2 id="概述">概述</h2>
<p>MySQL 多实例是指在同一台物理服务器（或虚拟机）上，通过不同的配置文件、端口、数据目录等隔离参数，运行多个独立的 MySQL 服务进程。每个实例拥有自己的数据库、用户、权限、日志和内存资源（可通过配置限制），逻辑上等同于多台独立的 MySQL 服务器。</p>
<p>通过大白话讲就是：</p>
<ul>
<li>一般在一个系统环境中，可以运行多个相同的服务程序信息，并且产生不同的进程和网络端口信息，就可以称为多实例概念；</li>
<li>在数据库服务运行过程中，也可以启动多个数据库服务程序，产生多个数据库服务进程和不同的服务端口，形成多实例；</li>
<li>多个数据库服务实例信息中存储的数据库信息是相互隔离和独立的，并且利用数据库服务多实例可以实现测试与分布式架构需求。</li>
</ul>
<p>企业数据库服务端实例应用架构设计图：<br>
<img src="https://img2024.cnblogs.com/blog/3468887/202511/3468887-20251128145302855-50910180.png" alt="image" loading="lazy"></p>
<h2 id="mysql多实例的核心作用">MySQL多实例的核心作用</h2>
<h3 id="资源利用率最大化核心价值">资源利用率最大化（核心价值）</h3>
<ul>
<li>单台服务器硬件资源（CPU、内存、磁盘）未饱和时，多实例可充分利用空闲资源，避免硬件浪费。</li>
</ul>
<blockquote>
<p>例：一台 32 核 64G 内存的服务器，仅运行 1 个 MySQL 实例（占用 16G 内存），剩余内存可部署 2-3 个实例，服务不同业务。</p>
</blockquote>
<h3 id="隔离性与风险控制">隔离性与风险控制</h3>
<ul>
<li>业务隔离：不同业务（如电商订单、用户会员、数据分析）部署在独立实例，避免某业务的慢查询、高并发影响其他业务。</li>
<li>权限隔离：每个实例有独立的 root 用户和业务账号，防止跨业务数据泄露或误操作。</li>
<li>故障隔离：一个实例崩溃（如内存溢出、表损坏），其他实例不受影响，降低整体服务中断风险。</li>
</ul>
<h3 id="成本优化">成本优化</h3>
<ul>
<li>减少服务器数量，降低硬件采购、机房托管、电力散热等成本，尤其适合中小团队或测试环境。</li>
<li>避免 “为单一低负载业务单独部署服务器” 的资源浪费。</li>
</ul>
<h3 id="灵活适配不同需求">灵活适配不同需求</h3>
<ul>
<li>可针对不同实例配置差异化参数：</li>
<li>核心业务实例：分配更多内存（innodb_buffer_pool_size）、更高 IO 优先级，优化并发性能。</li>
<li>测试 / 报表实例：分配较少资源，允许较低的查询性能，不影响核心业务。</li>
<li>支持不同 MySQL 版本（如 5.7 和 8.0）共存，满足老系统兼容和新功能测试需求。</li>
</ul>
<h2 id="mysql多实例配置实操">MySQL多实例配置实操</h2>
<h3 id="部署规划">部署规划：</h3>
<table>
<thead>
<tr>
<th>实例服务端口</th>
<th>实例存储路径</th>
<th>实例配置文件</th>
<th>套接字文件</th>
</tr>
</thead>
<tbody>
<tr>
<td>端口信息：3307</td>
<td>/data/3307/data</td>
<td>/data/3307/data/my.cnf</td>
<td>/tmp/mysql3307.sock</td>
</tr>
<tr>
<td>端口信息：3308</td>
<td>/data/3308/data</td>
<td>/data/3308/data/my.cnf</td>
<td>/tmp/mysql3308.sock</td>
</tr>
<tr>
<td>端口信息：3309</td>
<td>/data/3309/data</td>
<td>/data/3309/data/my.cnf</td>
<td>/tmp/mysql3309.sock</td>
</tr>
</tbody>
</table>
<h3 id="下载前置依赖">下载前置依赖</h3>
<pre><code># 下载libtinfo5、libnuma1依赖
root@master:~# apt update -y &amp;&amp; apt install -y libtinfo5 libnuma1
</code></pre>
<h3 id="下载mysql服务">下载mysql服务</h3>
<h4 id="方式一进入官网下载并上传到宿主机中适合离线环境">方式一：进入官网下载，并上传到宿主机中，适合离线环境</h4>
<p>官网下载地址： https://downloads.mysql.com/archives/community/<br>
<img src="https://img2024.cnblogs.com/blog/3468887/202511/3468887-20251128160730955-1490962057.png" alt="image" loading="lazy"></p>
<h4 id="方式二直接在宿主机中使用wget进行下载适合宿主机联网环境">方式二：直接在宿主机中使用wget进行下载，适合宿主机联网环境</h4>
<pre><code>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
</code></pre>
<h3 id="解压mysql服务并配置">解压mysql服务并配置</h3>
<pre><code># 服务解压至家目录
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' &gt;&gt; /etc/profile
root@master:~# source /etc/profile

# 检查环境变量
root@master:~# mysql -V
mysqlVer 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

</code></pre>
<h3 id="创建mysql虚拟用户及存储目录">创建mysql虚拟用户及存储目录</h3>
<p>创建虚拟用户</p>
<pre><code># 创建用户
root@master:~# useradd -s /sbin/nologin -M mysql
# 检查用户
root@master:~# id mysql
uid=2001(mysql) gid=2002(mysql) groups=2002(mysql)
</code></pre>
<p>创建存储目录</p>
<pre><code>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 rootroot4096 Nov 28 15:03 mysql3307
drwxr-xr-x 2 rootroot4096 Nov 28 15:03 mysql3308
drwxr-xr-x 2 rootroot4096 Nov 28 15:03 mysql3309
</code></pre>
<h3 id="进行多实例数据目录初始化步骤">进行多实例数据目录初始化步骤</h3>
<pre><code># 如果存在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 /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 InnoDB initialization has started.
2025-11-28T07:06:59.908463Z 1 InnoDB initialization has ended.
2025-11-28T07:07:00.800804Z 0 A deprecated TLS version TLSv1 is enabled for channel mysql_main
2025-11-28T07:07:00.801007Z 0 A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2025-11-28T07:07:00.831043Z 6 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 /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 InnoDB initialization has started.
2025-11-28T07:07:52.250511Z 1 InnoDB initialization has ended.
2025-11-28T07:07:53.016441Z 0 A deprecated TLS version TLSv1 is enabled for channel mysql_main
2025-11-28T07:07:53.016686Z 0 A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2025-11-28T07:07:53.060209Z 6 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 /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 InnoDB initialization has started.
2025-11-28T07:08:29.529660Z 1 InnoDB initialization has ended.
2025-11-28T07:08:30.454927Z 0 A deprecated TLS version TLSv1 is enabled for channel mysql_main
2025-11-28T07:08:30.455127Z 0 A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2025-11-28T07:08:30.593524Z 6 root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
</code></pre>
<h3 id="编写实例配置文件">编写实例配置文件</h3>
<pre><code># 3307实例
root@master:~# cat &gt;/data00/data/mysql3307/my.cnf &lt;&lt;EOF

server_id=3307
port=3307
user=mysql
basedir=/root/mysql
datadir=/data00/data/mysql3307
socket=/tmp/mysql3307.sock
EOF

# 3308实例
root@master:~# cat &gt;/data00/data/mysql3308/my.cnf &lt;&lt;EOF

server_id=3308
port=3308
user=mysql
basedir=/root/mysql
datadir=/data00/data/mysql3308
socket=/tmp/mysql3308.sock
EOF

# 3309实例
root@master:~# cat &gt;/data00/data/mysql3309/my.cnf &lt;&lt;EOF

server_id=3309
port=3309
user=mysql
basedir=/root/mysql
datadir=/data00/data/mysql3309
socket=/tmp/mysql3309.sock
EOF
</code></pre>
<h3 id="编写systemd配置文件">编写systemd配置文件</h3>
<pre><code># 3307 systemd
root@master:~# cat &gt;/usr/lib/systemd/system/mysqld3307.service&lt;&lt;EOF

Description=MySQL Server
Documentation=mysqld.service
After=network.target
After=syslog.target


WantedBy=multi-user.target


User=mysql
Group=mysql
ExecStart=/root/mysql/bin/mysqld --defaults-file=/data00/data/mysql3307/my.cnf
LimitNOFILE = 5000
EOF

# 3308 systemd
root@master:~# cat &gt;/usr/lib/systemd/system/mysqld3308.service&lt;&lt;EOF

Description=MySQL Server
Documentation=mysqld.service
After=network.target
After=syslog.target


WantedBy=multi-user.target


User=mysql
Group=mysql
ExecStart=/root/mysql/bin/mysqld --defaults-file=/data00/data/mysql3308/my.cnf
LimitNOFILE = 5000
EOF

# 3309 systemd
root@master:~# cat &gt;/usr/lib/systemd/system/mysqld3309.service&lt;&lt;EOF

Description=MySQL Server
Documentation=mysqld.service
After=network.target
After=syslog.target


WantedBy=multi-user.target


User=mysql
Group=mysql
ExecStart=/root/mysql/bin/mysqld --defaults-file=/data00/data/mysql3309/my.cnf
LimitNOFILE = 5000
EOF
</code></pre>
<h3 id="启动mysql">启动mysql</h3>
<pre><code># 加载systemd文件
root@master:~# systemctl daemon-reload

# 启动服务
root@master:~# systemctl start mysqld3307
root@master:~# systemctl start mysqld3308
root@master:~# systemctl start mysqld3309
</code></pre>
<h3 id="登录实例并初始化修改密码授权">登录实例并初始化（修改密码、授权）</h3>
<ul>
<li>初始化3307</li>
</ul>
<pre><code>root@master:~# mysql -uroot -S /tmp/mysql3307.sock

# 修改root用户密码为root
mysql&gt; ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)

# 创建一个可远程连接的root用户（可选）
mysql&gt; CREATE USER 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)
# 授权（可选）
mysql&gt; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
# 刷新权限
mysql&gt; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 退出mysql，重新使用用户名、密码连接
root@master:~# mysql -uroot -proot -P3307
mysql: 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&gt;
</code></pre>
<ul>
<li>初始化3308</li>
</ul>
<pre><code># 使用sock连接mysql
root@master:~# mysql -uroot -S /tmp/mysql3308.sock

# 修改root用户密码为root
mysql&gt; ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)

# 创建一个可远程连接的root用户（可选）
mysql&gt; CREATE USER 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)
# 授权（可选）
mysql&gt; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
# 刷新权限
mysql&gt; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 退出mysql，重新使用用户名、密码连接
root@master:~# mysql -uroot -proot -P3308
mysql: 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&gt;
</code></pre>
<ul>
<li>初始化3309</li>
</ul>
<pre><code>root@master:~# mysql -uroot -S /tmp/mysql3309.sock

# 修改root用户密码为root
mysql&gt; ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)

# 创建一个可远程连接的root用户（可选）
mysql&gt; CREATE USER 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)
# 授权（可选）
mysql&gt; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
# 刷新权限
mysql&gt; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 退出mysql，重新使用用户名、密码连接
root@master:~# mysql -uroot -proot -P3309
mysql: 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&gt;
</code></pre>


</div>
<div id="MySignature" role="contentinfo">
    <p>本文来自博客园，作者：huangSir-devops，转载请注明原文链接：https://www.cnblogs.com/huangSir-devops/p/19283168，微信Vac6666666，欢迎交流</p><br><br>
来源：https://www.cnblogs.com/huangSir-devops/p/19283168
頁: [1]
查看完整版本: MySQL多实例配置