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/data3.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'
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
5
0- 0
已为社区贡献6条内容
所有评论(0)