| 實例 | 端口 | 數據目錄 | Socket | Server ID | 內存分配 |
|---|---|---|---|---|---|
| Instance 1 | 3306 | /data/3306/data | /data/3306/mysql.sock | 1 | 2GB |
| Instance 2 | 3307 | /data/3307/data | /data/3307/mysql.sock | 2 | 2GB |
# 安裝依賴
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
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
創建 /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 大小即可。
# 創建 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-idopen_files_limit 導致「Too many open files」tmpdir 造成臨時表衝突mysqld_multi 官方工具管理(舊版本適用)