How to Deploy Multiple MySQL Instances on a Single Linux Server

Software Download

# rpm -qa | grep mysql
# ss -antl
# 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/
# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64 mysql

Configure User and Extract Binaries

# 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/
# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
# source /etc/profile.d/mysql.sh
# ln -sv /usr/local/mysql/include/ /usr/include/mysql
# chown -R mysql.mysql /usr/include/mysql
# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
# ldconfig

Create Data Directories

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

Initialize Instances

# mysqld --initialize --user mysql --datadir /opt/data/3306
# echo 'AqldE*a:O8FR' > 3306
# mysqld --initialize --user mysql --datadir /opt/data/3307
# echo 'PAJBo+Gxg19I' > 3307
# mysqld --initialize --user mysql --datadir /opt/data/3308
# echo '=w&lnU)d-7kK' > 3308

Install Perl

# 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 Instances

# mysqld_multi start 3306
# mysqld_multi start 3307
# mysqld_multi start 3308
# ss -antl
# ps -ef | grep 3306 | grep -v grep | awk '{print $2}' | xargs kill -9
# ps -ef | grep 3307 | grep -v grep | awk '{print $2}' | xargs kill -9
# ps -ef | grep 3308 | grep -v grep | awk '{print $2}' | xargs kill -9

Add to systemd Services

# 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, adjusting the port numbers)
# systemctl daemon-reload
# systemctl stop firewalld
# systemctl disable firewalld.service
# setenforce 0
# systemctl start 3306
# systemctl start 3307
# systemctl start 3308
# ss -antl

Initialize Passwords

# cat 3306
AqldE*a:O8FR
# mysql -uroot -p'AqldE*a:O8FR' -S /tmp/mysql3306.sock
mysql> set password=password('3306');
# cat 3307
PAJBo+Gxg19I
# mysql -uroot -p'PAJBo+Gxg19I' -S /tmp/mysql3307.sock
mysql> set password=password('3307');
# cat 3308
=w&lnU)d-7kK
# mysql -uroot -p'=w&lnU)d-7kK' -S /tmp/mysql3308.sock
mysql> set password=password('3308');

Error Resolution

# mysql -uroot -p'AqldE*a:O8FR'
# Error: libncurses.so.5 cannot open shared object file
# dnf provides libncurses.so.5
# dnf -y install ncurses-compat-libs-6.1-9.20180224.el8.i686
# mysql -uroot -p'AqldE*a:O8FR' -S /tmp/mysql3306.sock
# Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock'
# Use the correct socket path with -S /tmp/mysql3306.sock