Step‑by‑Step Guide to Deploy Multiple MySQL Instances on Linux

Software download

# rpm -qa | grep mysql
# ss -antl
# cd /usr/src
# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
# tar -xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

Configure user and extract binaries to /usr/local

# useradd -r -M -s /sbin/nologin mysql
# id mysql
# tar -xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# cd /usr/local/
# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64 mysql
# chown -R mysql.mysql mysql*

Create data directories for each instance

# mkdir -p /opt/data/{3306,3307,3308}
# chown -R mysql.mysql /opt/data/
# tree /opt/data/

Initialize each instance

# mysqld --initialize --user mysql --datadir /opt/data/3306
# mysqld --initialize --user mysql --datadir /opt/data/3307
# mysqld --initialize --initialize --user mysql --datadir /opt/data/3308

Install perl (required dependency)

# dnf -y install perl

Configure /etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error = /var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error = /var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error = /var/log/3308.log

Start each instance

# mysqld_multi start 3306
# mysqld_multi start 3307
# mysqld_multi start 3308
# ss -antl   # verify ports 3306‑3308 are listening

Add instances to systemd

# cd /usr/lib/systemd/system
# cp sshd.service 3306.service
# cp sshd.service 3307.service
# cp sshd.service 3308.service
# vim 3306.service
[Unit]
Description=3306 server daemon
After=network.target sshd-keygen.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306
ExecStop=ps -ef | grep 3306 | grep -v grep | awk '{print $2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target
# repeat for 3307.service and 3308.service (adjust port numbers)
# systemctl daemon-reload
# systemctl start 3306.service 3307.service 3308.service

Initialize root passwords

# echo 'AqldE*a:O8FR' > 3306   # temporary password generated for instance 3306
# mysql -uroot -p'AqldE*a:O8FR' -S /tmp/mysql3306.sock
mysql> SET PASSWORD = PASSWORD('3306');
mysql> quit
# repeat for 3307 and 3308 using their respective temporary passwords

Error troubleshooting

# mysql error: libncurses.so.5 missing
# dnf provides libncurses.so.5
# dnf -y install ncurses-compat-libs-6.1-9.20180224.el8

# mysql error: cannot connect to local MySQL server through socket '/tmp/mysql.sock'
# use the correct socket, e.g. -S /tmp/mysql3306.sock