mysqld_multi 實戰：單機多實例部署、主從鏈路構建與運維自動化

核心優勢

• 資源隔離：獨立 datadir、socket、port、log、pid、配置參數
• 版本共享：單一二進制，降低升級維護成本
• 靈活編排：開發/測試/生產混部、多租戶隔離、藍綠發布

單機多實例配置範例

# /etc/my.cnf
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = mysql
password   = MultiAdminPass!

[mysqld1]  # 生產主庫
port       = 3306
socket     = /tmp/mysql.sock1
datadir    = /data/mysql1
pid-file   = /data/mysql1/mysqld.pid
log-error  = /data/mysql1/error.log
slow_query_log_file = /data/mysql1/slow.log
server-id  = 1
log-bin    = /data/mysql1/binlog
binlog-format = ROW
gtid-mode  = ON
enforce-gtid-consistency = ON
innodb_buffer_pool_size = 2G

[mysqld2]  # 生產從庫
port       = 3307
socket     = /tmp/mysql.sock2
datadir    = /data/mysql2
pid-file   = /data/mysql2/mysqld.pid
log-error  = /data/mysql2/error.log
server-id  = 2
relay-log  = /data/mysql2/relay
read-only  = ON
super-read-only = ON

[mysqld3]  # 測試實例
port       = 3308
socket     = /tmp/mysql.sock3
datadir    = /data/mysql3
server-id  = 3

[mysqld4]  # 開發實例
port       = 3309
socket     = /tmp/mysql.sock4
datadir    = /data/mysql4
server-id  = 4

實例生命周期管理

# 初始化數據目錄 (每實例執行一次)
mysqld --initialize-insecure --user=mysql --datadir=/data/mysqlN

# 啟動
mysqld_multi --defaults-file=/etc/my.cnf start 1-2      # 啟動 1,2
mysqld_multi --defaults-file=/etc/my.cnf start 3,4      # 啟動 3,4
mysqld_multi --defaults-file=/etc/my.cnf start 1-4      # 全啟動

# 狀態
mysqld_multi --defaults-file=/etc/my.cnf report 1-4

# 停止
mysqld_multi --defaults-file=/etc/my.cnf stop 3         # 停單個
mysqld_multi --defaults-file=/etc/my.cnf stop 1-4       # 全停止

客戶端連接指定實例

mysql -u root -p -P 3306 -S /tmp/mysql.sock1  # 連實例1
mysql -u root -p -P 3307 -S /tmp/mysql.sock2  # 連實例2
# 或使用 --protocol=TCP --port=3306 (TCP 連接無需 socket)

構建實例級主從鏈路

前置條件

• 主實例 (3306) 已開啟 binlog、GTID、創建 replication 用戶
• 從實例 (3307) 配置 server-id=2、read-only=ON
• 數據基準同步：主實例 mysqldump --single-transaction --master-data=2 --all-databases > base.sql → 從實例導入

從實例配置 (mysqld2 區塊追加)

[mysqld2]
# ... 現有配置 ...
master-host     = 127.0.0.1
master-port     = 3306
master-user     = repl
master-password = ReplPass!
master-connect-retry = 30
master-use-gtid = current_pos
log-slave-updates = ON
report-host     = slave1
report-port     = 3307

驗證

mysql -u root -p -P 3307 -e "SHOW SLAVE STATUS\G"
-- Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0

自動化運維腳本

#!/bin/bash
# /usr/local/sbin/mysql_multi_manager.sh
CONF=/etc/my.cnf
CMD=$1
shift
GRP=${1:-"1-4"}

case $CMD in
  start|stop|restart|report)
    mysqld_multi --defaults-file=$CONF $CMD $GRP
    ;;
  backup)
    for port in 3306 3307 3308 3309; do
      mysqldump --single-transaction --routines --triggers -u backup -pBackupPass! -P $port --all-databases | gzip > /backup/mysql_${port}_$(date +%F).sql.gz
    done
    ;;
  health)
    for port in 3306 3307 3308 3309; do
      mysqladmin -u monitor -pMonitorPass! -P $port ping && echo "Instance $port OK" || echo "Instance $port DOWN"
    done
    ;;
  *)
    echo "Usage: $0 {start|stop|restart|report|backup|health} [GNR]"
    ;;
esac

監控告警接入

• Prometheus mysqld_exporter 多目標採集：--mysql.address=127.0.0.1:3306 等
• 關鍵指標：mysql_up、mysql_global_status_threads_connected、mysql_global_status_slow_queries、mysql_slave_status_seconds_behind_master
• Grafana 儀表板按實例區分顯示

避坑指南

1. datadir 權限：每實例 chown -R mysql:mysql /data/mysqlN，SELinux 需 semanage fcontext -a -t mysqld_db_t "/data/mysqlN(/.*)?"
2. 端口衝突：netstat -tlnp | grep -E '330[6-9]' 預檢
3. 內存總和：所有實例 innodb_buffer_pool_size 之和 ≤ 物理內存 70%
4. 文件描述符：ulimit -n 65535、open_files_limit=65535 每實例配置
5. 備份一致性：並發備份時注意 IO 爭用，建議錯峰或使用 mydumper 並行導出