Multiple MySQL instances on the same server

Some weeks ago I’ve wrote an article about how to run different PHP versions on the same server. Mel like it and suggested me to do the same for MySQL and/or MariaDB. This is how to run multiple MySQL instances on the same server.

Why?

^{«And the reasons? There are no reasons. Who needs reasons?»} _{Mark Renton, from Trainspotting}

For now I will say: because we can. With the know-how we can find a reason later.

Installing

I’ve installed first mysql and then mariadb; again without a reason to install in that order. You can do it the other way (first maria, then mysql) or even different versions of the same database.

What I have to do is install one of them from the packages using apt-get and the other compiling the source code. Later you will see why. If you choose another order or compile both from the sources you should check the official documentation first.

Binary packages

I’m working with Ubuntu 20.10 and going to install MySQL and MariaDB. The first step is really easy, run the following commands to install mysql from the packages available in the repository:

# sudo -i
# apt-get install mysql-server

The sudo -i command is equivalent to su -, remember to exit when over.

Stop for now the mysql service: systemctl stop mysql

Compile from source

If you try to install mariadb with apt-get when mysql is already installed as I did, the system will try to uninstall mysql:

The system will try to delete the previous because both, mariadb and mysql, are for the same task so they conflict. And that’s why we need to install it from source.

Dependencies

You can get generic and detailed instructions to build mariadb from here and then here. On my ubuntu I’ve:
First enable source repositories:

# nano /etc/apt/sources.list
# apt-get update

Second install a number of tools, build dependencies, needed to compile MariaDB; you can install it easy with:

# apt build-dep mariadb-server

Compile and install

Dependencies installed, now it’s time to unpack the tarball:

# tar zxvf mariadb-10.6.5.tar.gz
# cd mariadb-10.6.5

And build with cmake. On the first cmake I’m setting some paths to make sure everything is going to /usr/local/mariadb so it won’t conflict with MySQL.

# cmake -DCMAKE_INSTALL_PREFIX:PATH=/usr/local/mariadb -DGRN_DEFAULT_DOCUMENT_ROOT:PATH=/usr/local/mariadb/share/groonga/html/admin -DGRN_LOG_PATH:FILEPATH=/usr/local/mariadb/var/log/groonga/groonga.log -DMYSQL_DATADIR:PATH=/usr/local/mariadb/data

Now we can make the build and install it.

# cmake --build .
# cmake --install .

Create mysql database and start the service

First we need to create a minimal config file, with the following content:

# nano /usr/local/mariadb/etc/my.cnf
[mysqld]
user		= mysql
pid-file	= /usr/local/mariadb/var/run/mysqld/mysqld.pid
socket	= /usr/local/mariadb/var/run/mysqld/mysqld.sock
port		= 3307
datadir	= /usr/local/mariadb/data
bind-address		= 127.0.0.1

Pay attention that I choose another TCP port for MariaDB so it won’t conflict with MySQL. Now we can create the system database:

# cd /usr/local/mariadb
# ./scripts/mysql_install_db --defaults-file=/usr/local/mariadb/etc/my.cnf --user=mysql

Now with the system database created, we can start the mariadb daemon for the first time:

# ./bin/mysqld_safe --defaults-file='/usr/local/mariadb/etc/my.cnf' --datadir='/usr/local/mariadb/data' --user=mysql

Warning!: make sure that the command line parameter --defaults-file is the first one! Otherwise the mariadb daemon will use /etc/mysql/my.cnf from mysql. I’ve spent two days trying to figurate what’s going on.

Testing

First I’m gonna try to connect to MariaDB, as I’m using a non-standard tcp port and other socket I need to tell that to the mysql client:

# mysql -S /usr/local/mariadb/var/run/mysqld/mysqld.sock -P3307

Now I can change to the other tab to test mysql. I’ve stopped first so I need to start the service before I try to connect:

# systemctl start mysql
# mysql

A last checking could be that every daemon is listening on their respective tcp port:

# netstat -puta

We can confirm that we have multiple mysql instances on the same server. Even one of that instances isn’t mysql.

Homework

If you are still reading here there are some remaining tasks:

1. Create a systemd script for MariaDB to start on system startup
2. Find the compile argument for define the default my.cnf file location
3. «Choose life»