Setup Multiple MariaDB (or MySQL) Instances

If you encounter a situation where you need to set up multiple database server instances on a single host it is possible to do this flexibly using systemd and the mysql_multi.

I am using MariaDB instead of MySQL as I am concerned with the direction and stewardship of MySQL under Oracle. But, if you wish to stay with MySQL the guide below will work just the same.¹

Install MariaDB

To install MariaDB on Fedora 18 just run yum install mariadb-server.

Running one instance is easy, all you have to do is start and enable the server with systemctl start mysqld & systemctl enable mysqld.² But to set up two or more instances we will have to configure this ourselves. One way of doing this is to create a systemd service.

Create systemd Service

To set up two (or more) instances we are going to create a systemd service template. This allows you to create multiple instances from only one configuration file. A service template has an @ character before the ’.service’ suffix.

Create the file mysqld@.service in the /etc/systemd/system/ directory.³ The contents of this file should look like this:⁴

[Unit]
Description=MySQL Multi Server for instance %i
After=syslog.target
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking
ExecStart=/usr/bin/mysqld_multi start %i
ExecStop=/usr/bin/mysqld_multi stop %i
Restart=always
PrivateTmp=true

[Install]
WantedBy=multi-user.target

We configure the user and group that the service runs as. Also, the commands needed to start and stop the service - we are using the mysql_multi script here, the %i is a variable that is generated from the service Instance name, e.g. if you call systemctl start mysqld@8, then %i will equal 8. Other settings to note are that we set the Restart property to always so systemd will try and restart the instance using mysqld_multi if it stops for any reason. We also set PrivateTmp for security reasons.

Be aware that unit files are Case-sensitive, so ‘Forking’ will not work for example.

MariaDB Configuration

As mysql_multi will be called with the name of your instance it will need to be configured in your /etc/my.cnf along with each instance as so:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe --basedir=/usr

[mysqld1]
port=63132
datadir=/var/lib/mysql/1/
socket=/var/lib/mysql/1/mysql.sock
pid-file=/var/run/mysqld/mysqld1.pid

log-error=/var/log/mysqld1.log

# Disabling symbolic-links is recommended to prevent assorted security
# risks
symbolic-links=0

[mysqld2]
port=63133
datadir=/var/lib/mysql/2/
socket=/var/lib/mysql/2/mysql.sock
pid-file=/var/run/mysqld/mysqld2.pid

log-error=/var/log/mysqld2.log

# Disabling symbolic-links is recommended to prevent assorted security
# risks
symbolic-links=0

$\dots$

mysqld_safe is a script that starts the MySQL daemon. It ensures that the server is restarted if an error occurs. It also logs information to the specified error log file. This is in addition to the systemd service restart mentioned above. It is called here with the basedir option due to selinux.⁵

The port range 63132-63133 is set open by default on fedora 18. So these can be used for setting up multiple instances without needing to add a port with semanage, if you prefer using, say 3307 you will need to allow the mysqld process to listen on this port using semanage port -a mysqld_port_t 3307.

Note: Tools like semanage are not part of a minimal Fedora installation. To get them install the policycoreutils-python package.

Setup and Configure Instances

1. Create directories and log files.

   Create data directories for each instance:

   # mkdir /var/lib/mysql/{1,2}

   By creating the datadir for each instance under the default /var/lib/mysql/ we inherit the selinux context and won’t have issues. If you wish to use a different datadir you can copy the context using:

   # chcon --reference=/var/lib/mysql/ /target/directory

   Create a log file for each instance and give it the correct permissions and security context:

    # touch /var/log/mysqld{1,2}.log
    # chmod o-r mysqld{1,2}.log
    # chcon --reference=/var/log/mysqld.log /var/log/mysqld{1,2}.log

2. Initialize mysql databases:

    # mysql_install_db --user=mysql --datadir=/var/lib/mysql/1/
    # mysql_install_db --user=mysql --datadir=/var/lib/mysql/2/

3. Start mysqld instances:

    # systemctl start mysqld@1
    # systemctl start mysqld@2

4. Check the logs to make sure everything is running okay:

    # systemctl status mysqld@1
    # systemctl status mysqld@2
    # cat /var/log/mysqld.log
    # cat /var/log/mysqld1.log
    # cat /var/log/mysqld2.log

5. Secure Installation

   mysql_secure_installation is a script that helps to set up a server for production use. It does the following actions:

   • Set password for root accounts.
   • Remove root access from external hosts.
   • Removes the anonymus user accounts.
   • Removes the test database and priviledges.

   As we have selected non-standard sockets we are unable to use the mysql_secure_installation script as it stands as it can’t find the socket our instances are listening on. A little trick you can use is to temporarily create a link to the mysql.sock file of the instance you wish to securely install i.e.
   ln /var/lib/mysql/1/mysql.sock /var/lib/mysql/mysql.sock and then run the secure installation script. A better solution would be to patch this script to accept an option that specifies the location of the socket but this way works fine for now.

6. Enable services at startup.

    # systemctl enable mysqld@1
    # systemctl enable mysqld@2

And you’re done.