萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 多實例部署完整指南:單機運行多個獨立實例的最佳實踐

多實例架構優勢

  • 資源隔離:CPU、內存、連接數、緩存池獨立分配
  • 版本共存:同機器運行不同 MySQL 版本
  • 故障隔離:單實例崩潰不影響其他業務
  • 成本優化:充分利用高配服務器資源

部署環境規劃

實例端口數據目錄SocketServer ID內存分配
Instance 13306/data/3306/data/data/3306/mysql.sock12GB
Instance 23307/data/3307/data/data/3307/mysql.sock22GB

標準化部署流程

1. 系統準備

# 安裝依賴
yum install -y gcc gcc-c++ cmake ncurses-devel libaio-devel

# 創建專用用戶
useradd -s /sbin/nologin -M mysql

# 下載編譯安裝(以 5.7.37 為例)
cd /opt/src
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.37.tar.gz
tar xf mysql-5.7.37.tar.gz
cd mysql-5.7.37

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.37 \
  -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \
  -DENABLED_LOCAL_INFILE=1 -DWITH_SSL=system

make -j$(nproc) && make install

# 創建軟連接便於管理
ln -s /usr/local/mysql-5.7.37 /usr/local/mysql

2. 初始化多實例數據目錄

mkdir -p /data/{3306,3307}/{data,logs,tmp}
chown -R mysql:mysql /data

# 實例 3306 初始化
/usr/local/mysql/bin/mysqld --initialize-insecure \
  --basedir=/usr/local/mysql \
  --datadir=/data/3306/data \
  --user=mysql

# 實例 3307 初始化
/usr/local/mysql/bin/mysqld --initialize-insecure \
  --basedir=/usr/local/mysql \
  --datadir=/data/3307/data \
  --user=mysql

3. 配置文件模板化管理

創建 /etc/mysql/3306.cnf 和 /etc/mysql/3307.cnf:

# /etc/mysql/3306.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
tmpdir = /data/3306/tmp
pid-file = /data/3306/mysql.pid

server-id = 1
log-bin = /data/3306/logs/mysql-bin
relay-log = /data/3306/logs/relay-bin
log-error = /data/3306/logs/error.log
slow_query_log = 1
slow_query_log_file = /data/3306/logs/slow.log
long_query_time = 1

# 內存配置(根據實例分配調整)
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
max_connections = 500
open_files_limit = 65535

# 安全與性能
skip-name-resolve
lower_case_table_names = 1
explicit_defaults_for_timestamp = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld_safe]
pid-file = /data/3306/mysql.pid

3307.cnf 只需修改端口、路徑、server-id、buffer_pool 大小即可。

4. 啟動腳本與系統服務管理

# 創建 systemd 服務文件 /etc/systemd/system/mysqld@.service
[Unit]
Description=MySQL Server %i
After=network.target

[Service]
Type=forking
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/%i.cnf
ExecStop=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/mysql/%i.cnf shutdown
PIDFile=/data/%i/mysql.pid
TimeoutSec=300
Restart=on-failure
RestartSec=5
LimitNOFILE=65535

[Install]
WantedBy=multi-user.target

啟用並啟動:

systemctl daemon-reload
systemctl enable mysqld@3306 mysqld@3307
systemctl start mysqld@3306 mysqld@3307

# 驗證
systemctl status mysqld@3306
netstat -tunlp | grep 330
mysql -S /data/3306/mysql.sock -e "SELECT @@port, @@server_id;"

運維管理技巧

統一管理腳本

#!/bin/bash
# /usr/local/bin/mysql-multi.sh
ACTION=$1
PORT=$2
CNF="/etc/mysql/${PORT}.cnf"
SOCK="/data/${PORT}/mysql.sock"

case $ACTION in
  start) systemctl start mysqld@${PORT} ;;
  stop) systemctl stop mysqld@${PORT} ;;
  restart) systemctl restart mysqld@${PORT} ;;
  status) systemctl status mysqld@${PORT} ;;
  login) /usr/local/mysql/bin/mysql -S ${SOCK} ;;
  backup) 
    /usr/local/mysql/bin/mysqldump -S ${SOCK} --all-databases --single-transaction --master-data=2 | gzip > /backup/${PORT}_$(date +%F).sql.gz
    ;;
  *) echo "Usage: $0 {start|stop|restart|status|login|backup} {3306|3307}" ;;
esac

資源隔離驗證

# 檢查各實例緩存池
mysql -S /data/3306/mysql.sock -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -S /data/3307/mysql.sock -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# 壓測隔離性
sysbench --mysql-socket=/data/3306/mysql.sock --threads=100 --time=60 oltp_read_write run

避坑指南

  • ❌ 共用 basedir 但忘記修改 datadir、socket、port、server-id
  • ❌ 未調整 open_files_limit 導致「Too many open files」
  • ❌ 多實例共用 tmpdir 造成臨時表衝突
  • ✅ 使用 mysqld_multi 官方工具管理(舊版本適用)
  • ✅ 容器化部署(Docker/K8s)是現代更優選擇
copyright © 萬盛學電腦網 all rights reserved