Multi-Instance set-up with MySQL Enterprise Server 5.7 on RHEL 7 with SystemD

In our current project the customer wants to install and run multiple MySQL Enterprise Server 5.7 Instances on the same machine (yes, I know about virtualization (we run on kvm), containers, Docker, etc.). He wants to use Redhat Enterprise Linux (RHEL) 7 which brings the additional challenge of SystemD. So mysqld_multi is NOT an option any more.

We studied the MySQL documentation about the topic: Configuring Multiple MySQL Instances Using systemd. But to be honest: It was not really clear to me how to do the job...

So we started to work out our own cook-book which I want to share here.

The requirements are as follows:

• Only ONE version of MySQL Enterprise Server binaries at a time is available. If you want to have more complicated set-ups (multi version) consider our MyEnv.
• Because Segregation of Duties is an issue for this customer from the financial industries we are not allowed to use the operating system root user or have sudo privileges.
• We have to work with the operating system user mysql as non privileged user.

Preparation work for the operating system administrator

This is the only work which has to be done under a privileged account (root):

shell> sudo yum install libaio
shell> sudo groupadd mysql
shell> sudo useradd -r -g mysql -s /bin/bash mysql
shell> sudo cp mysqld@.service /etc/systemd/system/

Installation of MySQL Enterprise Server binaries as non privileged user

To perform this task we need the generic MySQL Binary Tar Balls which you can get from the Oracle Software Delivery Cloud:

shell> mkdir /home/mysql/product
shell> cd /home/mysql/product
shell> tar xf /download/mysql-<version>.tar.gz
shell> ln -s mysql-<version> mysql-5.7.x
shell> ln -s mysql-5.7.x mysql
shell> echo 'export PATH=$PATH:/home/mysql/product/mysql/bin' >> ~/.bashrc
shell> . ~/.bashrc

Creating, Starting and Stopping several MySQL Enterprise Server Instances

shell> export INSTANCE_NAME=TMYSQL01   # and TMYSQL02 and TMYSQL03
shell> mkdir -p /mysql/${INSTANCE_NAME}/etc /mysql/${INSTANCE_NAME}/log /mysql/${INSTANCE_NAME}/data /mysql/${INSTANCE_NAME}/binlog
shell> cat <<_EOF >/mysql/${INSTANCE_NAME}/etc/my.cnf
#
# /mysql/${INSTANCE_NAME}/etc/my.cnf
#
[mysqld]
datadir   = /mysql/${INSTANCE_NAME}/data
pid_file  = /var/run/mysqld/mysqld_${INSTANCE_NAME}.pid
log_error = /mysql/${INSTANCE_NAME}/log/error_${INSTANCE_NAME}.log
port      = 3306   # and 3307 and 3308
socket    = /var/run/mysqld/mysqld_${INSTANCE_NAME}.sock
_EOF
shell> cd /home/mysql/product/mysql
shell> bin/mysqld --defaults-file=/mysql/${INSTANCE_NAME}/etc/my.cnf --initialize --user=mysql --basedir=/home/mysql/product/mysql
shell> bin/mysqld --defaults-file=/mysql/${INSTANCE_NAME}/etc/my.cnf --daemonize >/dev/null 2>&1 &
shell> mysqladmin --user=root --socket=/var/run/mysqld/mysqld_${INSTANCE_NAME}.sock --password shutdown

So far so good. We can do everything with the database without root privileges. One thing is missing: The MySQL Database Instances should be started automatically at system reboot. For this we need a SystemD unit file:

#
# /etc/systemd/system/mysqld@.service
#

[Unit]

Description=Multi-Instance MySQL Enterprise Server
After=network.target syslog.target


[Install]

WantedBy=multi-user.target
 

[Service]

User=mysql
Group=mysql
Type=forking
PIDFile=/var/run/mysqld/mysqld_%i.pid
TimeoutStartSec=3
TimeoutStopSec=3
# true is needed for the ExecStartPre
PermissionsStartOnly=true
ExecStartPre=/bin/mkdir -p /var/run/mysqld
ExecStartPre=/bin/chown mysql: /var/run/mysqld
ExecStart=/home/mysql/product/mysql/bin/mysqld --defaults-file=/mysql/%i/etc/my.cnf --daemonize
LimitNOFILE=8192
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

This file must be copied as root to:

shell> cp mysqld@.service /etc/systemd/system/

Now you can check if SystemD behaves correctly as follows:

shell> sudo systemctl daemon-reload
shell> sudo systemctl enable mysqld@TMYSQL01   # also TMYSQL02 and TMYSQL03
shell> sudo systemctl start mysqld@TMYSQL01
shell> sudo systemctl status 'mysqld@TMYSQL*'
shell> sudo systemctl start mysqld@TMYSQL01

How to go even further

If you need a more convenient or a more flexible solution you can go with our MySQL Enterprise Environment MyEnv.