MySQL 多实例-平芜编程栈

拿 MySQL 数据库来说明，就是在一台服务器上运行多个 MySQL 服务端进程，每个进程监听一个端口（3306，3307，3308），维护一套属于其自己的配置和数据。

注意点：端口不能一样，配置是唯一的，数据是唯一的

可以用不同的 MySQL 版本实现多实例，也可以用相同的 MySQL 版本实现多实例。

以下演示使用的是相同版本的

1.Rocky9安装Mariadb软件

（1）安装软件

yum install -y mariadb-server mariadb-pam

注意：默认安装mariadb-server的时候，不会安装 mariadb-pam软件，如果不安装该软件的时候，后面会发生如下命令执行提示。所以，在Rocky系统上，原则上，最好安装配套的 mariadb-pam 软件

chown: 无法访问 '/usr/lib64/mariadb/plugin/auth_pam_tool_dir/auth_pam_tool': 没有那个文件或目录 Couldn't set an owner to '/usr/lib64/mariadb/plugin/auth_pam_tool_dir/auth_pam_tool'. It must be root, the PAM authentication plugin doesn't work otherwise.. chown: 无法访问 '/usr/lib64/mariadb/plugin/auth_pam_tool_dir': 没有那个文件或目录 Cannot change ownership of the '/usr/lib64/mariadb/plugin/auth_pam_tool_dir' directory to the 'mysql' user. Check that you have the necessary permissions and try again.

（2）环境检测

包安装会自动创建一个，没有的话就自己创

[root@rocky30 ~] id mysql 用户id=27(mysql) 组id=27(mysql) 组=27(mysql)

默认的配置文件地址：/etc/my.cnf.d/mariadb-server.cnf

注意：这个时候，不要启动服务，不然的话，对应的数据目录下，就会生成大量的数据了。

2.Rocky初始化Mariadb多实例数据库

创建相关目录

[root@rocky30 ~] mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}

为目录赋予用户权限

chown -R mysql:mysql /mysql/

生成三个实例的初始数据

mysql_install_db --user=mysql --datadir=/mysql/3306/data mysql_install_db --user=mysql --datadir=/mysql/3307/data mysql_install_db --user=mysql --datadir=/mysql/3308/data

3.3306实例数据库配置

（1）定制配置

创建配置文件

cat > /mysql/3306/etc/my.cnf <<-eof [mysqld] port=3306 datadir=/mysql/3306/data socket=/mysql/3306/socket/mysql.sock log-error=/mysql/3306/log/mysql.log pid-file=/mysql/3306/pid/mysql.pid eof

定制服务脚本

vim /mysql/3306/bin/mysqld #!/bin/bash PORT=3306 USER="root" PWD="Magedu" CMD_PATH="/usr/bin" BASE_DIR="/mysql" SOCKET="${BASE_DIR}/${PORT}/socket/mysql.sock" LOG_FILE="${BASE_DIR}/${PORT}/log/service.log" # 日志记录函数 log() { local message="$1" local timestamp=$(date +"%Y-%m-%d %H:%M:%S") echo "$timestamp - $message" >> "$LOG_FILE" } mysql_start() { if [ ! -e "$SOCKET" ]; then log "Starting MySQL..." echo "Starting MySQL..." ${CMD_PATH}/mysqld_safe --defaults-file=${BASE_DIR}/${PORT}/etc/my.cnf &>/dev/null & local pid=$! sleep 2 if ps -p $pid > /dev/null; then log "MySQL started successfully." else log "Failed to start MySQL." echo "Failed to start MySQL." fi else log "MySQL is running..." echo "MySQL is running..." exit fi } mysql_stop() { if [ ! -e "$SOCKET" ]; then log "MySQL is stopped..." echo "MySQL is stopped..." exit else log "Stopping MySQL..." echo "Stopping MySQL..." ${CMD_PATH}/mysqladmin -u ${USER} -p${PWD} -S ${SOCKET} shutdown local result=$? if [ $result -eq 0 ]; then log "MySQL stopped successfully." else log "Failed to stop MySQL." echo "Failed to stop MySQL." fi fi } mysql_restart() { log "Restarting MySQL..." echo "Restarting MySQL..." mysql_stop sleep 2 mysql_start } usage_msg() { echo "Usage: ${BASE_DIR}/${PORT}/bin/mysqld {start|stop|restart}" } # 信号处理函数 trap 'mysql_stop; exit 1' SIGTERM SIGINT case $1 in start) mysql_start;; stop) mysql_stop;; restart) mysql_restart;; *) usage_msg;; esac

为目录赋予用户权限

chmod +x /mysql/3306/bin/mysqld

启动服务

/mysql/3306/bin/mysqld start 检测效果 [root@rocky30 ~] netstat -tnulp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 34739/mariadbd

如果启动失败关闭 selinux 临时关闭：setenforce 0

（2）改造为服务脚本

[root@rocky30 ~] /mysql/3306/bin/mysqld stop Stopping MySQL... [root@rocky30 ~] vim /etc/systemd/system/mysql3306.service [Unit] Description=MySQL 3306 Server After=network.target [Service] User=mysql Group=mysql ExecStart=/usr/bin/mysqld_safe --defaults-file=/mysql/3306/etc/my.cnf ExecStop=/usr/bin/mysqladmin -u root -pMagedu -S /mysql/3306/socket/mysql.sock shutdown Restart=always RestartSec=5 [Install] WantedBy=multi-user.target

启动服务

[root@rocky30 ~] systemctl daemon-reload [root@rocky30 ~] systemctl start mysql3306 [root@rocky30 ~] systemctl status mysql3306 ● mysql3306.service - MySQL 3306 Server Loaded: loaded (/etc/systemd/system/mysql3306.service; disabled; preset: disabled) Active: activating (auto-restart) (Result: exit-code) since Mon 2025-12-01 18:05:13 CST; 2s ago Process: 34913 ExecStart=/usr/bin/mysqld_safe --defaults-file=/mysql/3306/etc/my.cnf (code=exited, status=0/SUCCESS)

进入数据库

[root@rocky30 ~] mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) #发生报错，mysql客户端命令在连接的时候，没有指定选项参数，使用的是全局的默认配置 [root@rocky30 ~] mysql -S /mysql/3306/socket/mysql.sock #多实例的情况下需要指定路径

4.其他两个实例数据库配置

（1）转移配置文件

cat > /mysql/3307/etc/my.cnf <<-eof [mysqld] port=3307 datadir=/mysql/3307/data socket=/mysql/3307/socket/mysql.sock log-error=/mysql/3307/log/mysql.log pid-file=/mysql/3307/pid/mysql.pid eof cat > /mysql/3308/etc/my.cnf <<-eof [mysqld] port=3308 datadir=/mysql/3308/data socket=/mysql/3308/socket/mysql.sock log-error=/mysql/3308/log/mysql.log pid-file=/mysql/3308/pid/mysql.pid eof

（2）定制启动文件

转移数据库启动文件

for i in 7 8 do cp -a /mysql/3306/bin/mysqld /mysql/330$i/bin/mysqld sed -i "s#3306#330$i#g" /mysql/330$i/bin/mysqld done

定制服务文件

for i in 7 8 do cp -a /etc/systemd/system/mysql3306.service /etc/systemd/system/mysql330$i.service sed -i "s#3306#330$i#g" /etc/systemd/system/mysql330$i.service done

（3）测试

启动数据库 [root@rocky30 ~] /mysql/3307/bin/mysqld start Starting MySQL... [root@rocky30 ~] /mysql/3308/bin/mysqld start Starting MySQL... [root@rocky30 ~] netstat -tnulp | grep 330 tcp6 0 0 :::3308 :::* LISTEN 57735/mariadbd tcp6 0 0 :::3306 :::* LISTEN 57357/mariadbd tcp6 0 0 :::3307 :::* LISTEN 57613/mariadbd

5.多实例场景下，如何连接不同的数据库

方法一：（1）默认连接3306

mysql-clients.cnf 和 client.cnf 都是管理连接的配置文件

为客户端连接配置文件定制 专属的连接属性 [root@rocky9 ~] vim /etc/my.cnf.d/mysql-clients.cnf [mysql] socket=/mysql/3306/socket/mysql.sock 或者 编辑另外一个文件 [root@rocky9 ~] vim /etc/my.cnf.d/client.cnf [client] socket=/mysql/3306/socket/mysql.sock

（2）测试

[root@rocky30 ~] mysql Welcome to the MariaDB monitor. Commands end with ; or \g. …… MariaDB [(none)]> \s -------------- mysql Ver 15.1 Distrib 10.5.29-MariaDB, for Linux (x86_64) using EditLine wrapper …… UNIX socket: /mysql/3306/socket/mysql.sock

方法二：命令别名

alias myslq3306='myslq -S /path/to/3306/socket/mysql.sock'

MySQL 多实例