Multiple MySQL instances on the same server

Everything Linux, A.I, IT News, DataOps, Open Source and more delivered right to you.
Subscribe
"The best Linux newsletter on the web"

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.

Follow the screen instructions and continue

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:

if we continue this way we wouldn't end with multiple MySQL instances on the same server
and it’s perfectly reasonable

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:

uncoment the deb-src lines
# 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
Multiple MySQL instances on the same server
Installing building dependencies

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
Creating the system database

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
MariaDB running

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
Multiple MySQL instances on the same server
MariaDB is up&running

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
Multiple MySQL instances on the same server
MySQL is up&running. I don’t need parameters in the client

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

# netstat -puta
we have multiple MySQL instances on the same server
tcp port 3306 is well known for mysql/mariadb service. In 3307 is mariadb listening for connections.

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»
Everything Linux, A.I, IT News, DataOps, Open Source and more delivered right to you.
Subscribe
"The best Linux newsletter on the web"
Gonzalo Rivero
Gonzalo Rivero
I am Gonzalo, I live in Salta, a city located in the NW of Argentina. I play the guitar and a little harmonica. I also like to bike.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest articles

Join us on Facebook