Running Multiple Instances of MariaDB on a Single Server & replicating different instances to different replicas...

Sometimes, we may have a need to run multiple instances of MariaDB or Mysql on a single server. (P.S: Personally, I wouldn't advise deploying this to a production (unless in cases where server has capable enough infrastructure build to handle the concurrent workloads; preferably development or UAT). This is however not a new thing to the world of database administration ( Oracle, for instance, has a similar implementation - The concept of "Instance Caging", with the option to connect to each instance using a service name, via TNS listeners).

In this article, We'll take a walk-through on how we can have multiple instances of MariaDB/MySQL running on the same server, and also configure replication such that each instance will replicate separately to a different replica.

For the purpose of this article, we'll be using an Ubuntu 20.04 LTS (Jammy Jellyfish) and MariaDB ES 10.6.8-4

In trying to achieve this, it is best to have these in mind:

• Create a separate datadir for the various instances (starting multiple instances with the same data directory does not give you extra performance in a threaded system, so why risk it??).
• Assign different TCP/IP ports for the various instances
• specify different Unix sockets for the various instances' connections.
• Use different process ID files (specified by the --pid-file option or pid-file in the config file) if using mysqld_safe to start mysqld.

having these 3 would simplify management, and of course, is advised best practise.

One way we can achieve running multiple mariadb instances on a server could be by:

• having different configuration files for each instance.
• specifying the different parameters - port, datadir, sock, and other needed configurations.
• starting / stopping the various instances using the mysqld with the specific options. e.g

starting with: mysqld --defaults-file=/etc/mysql/mdb1.cnf --pid-file=/run/mysqld/mdb1.pid --socket=/run/mysqld/mdb1.sock --port=3308 (*if you run into an issue, starting this with root user (not recommended), you might have fix the apparmour bug and need to change from "enforce" to "complain")

shutting down with: mysqladmin --defaults-file=/etc/mysql/mysql/mdb1.cnf shutdown

connecting via: mysql --defaults-file=/etc/mysql/mdb1.cnf -u example_user -p

Of course these can be converted to helper bash scripts or bash aliases to aid starting and shutting down, as well as connecting.

Using this approach, we're faced with a number of redundancies - managing different configuration files, etc.

Is there a better (and more manageable) way to achieve this? Absolutely - say hello to "mariadb-multi" (or "mysqld_multi").

In simplest terms, mysqld_multi is a wrapper that is designed to manage several mysqld processes running on the same host.?Keep in mind, in order for multiple mysqld processes to work on the same host, these processes must meet the requirements mentioned earlier here.

mysqld_multi has some capabilities that makes it a preferred choice in managing multiple instances - it can start or stop servers, report their current status, as a group or individually. In addition to this, it is pretty quite easy and straightforward to setup and use.

Basically, for usage, mysqld_muti have this syntax:

mysqld_multi [options] {start | stop | report } [GNR1, ...GNRn]          

where GNR - refers to the group option number, specified in [...] in the config file . As can be seen from the syntax, mysqld_multi does have some options, that we can use to tweak its behaviour. for this article, we'll use the ones I consider quite needful: defaults-file (used to specify the configuration for the mysqld_multi process) and log (to specify the logfile for the mysql_multi process).

With mysqld_multi, we do not have to create the different configuration files, rather we can use one configuration file (how easy that makes our life??!!).

Okay, enough talk, lets get down to business - How do we actually implement this?. We'll highlight it the following bullets:

• Install the needed version of MariaDB or MySQL on the host server. I already have this installed in my VM (ubuntu 20.04)

• You might want to check that we have the utility present in our installation (just for curiosity purposes)

Now we have that installed, we can proceed.

• stop the currently running mariadb/mysqld process. (either using mysqladmin ... shutdown or stopping the service from systemd)
• create the needed data directories for the instance (and if needed, the directory for the socks and pid file), assigning the right user & group ownership. In our case, we'll be installing 2 instances, so:

root@ansible-host1:/var/lib/mysql# mkdir -p -v /var/lib/mysql{1..2}
mkdir: created directory '/var/lib/mysql1'
mkdir: created directory '/var/lib/mysql2'
root@ansible-host1:/var/lib/mysql# chown mysql:mysql -v /var/lib/mysql{1..2}
changed ownership of '/var/lib/mysql1' from root:root to mysql:mysql
changed ownership of '/var/lib/mysql2' from root:root to mysql:mysql
root@ansible-host1:/usr/local# mkdir -p -v /usr/local/mysql/mdb{1..2}
mkdir: created directory '/usr/local/mysql'
mkdir: created directory '/usr/local/mysql/mdb1'
mkdir: created directory '/usr/local/mysql/mdb2'
root@ansible-host1:chown -v -R mysql:mysql /usr/local/mysql/mdb{1..2}
ownership of '/usr/local/mysql/mdb1' retained as mysql:mysql
ownership of '/usr/local/mysql/mdb2' retained as mysql:mysql
root@ansible-host1:/usr/local# mkdir -p -v /var/log/mysql{1..2}
mkdir: created directory '/var/log/mysql1'
mkdir: created directory '/var/log/mysql2'
root@ansible-host1:chown -v -R mysql:mysql /var/log/mysql{1..2}
ownership of '/var/log/mysql1' retained as mysql:mysql
ownership of '/var/log/mysql2' retained as mysql:mysql        

• ensure the ports specified (to be used) are open for TCP/IP traffic.
• create a separate configuration file as below, and specify the needed parameters. in our case, we'll keep configurations minimal (notice how we've somehow enforced resource management - e.g by assigning different sizes for innodb_buffer_pool for both instances. other performance parameters can also be tweaked).

# Configuration Settings for mysqld_multi , as well as separate instances.?
#
[mysqld_multi]
mysqld? ? ?= /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

[mysqld1]
socket? ? ?= /tmp/mysql.sock1
port? ? ? ?= 3307
pid-file? ?= /usr/local/mysql/mdb1/ansible-host1.pid1
datadir? ? = /var/lib/mysql1
innodb_buffer_pool_size = 2G
log-error? = /var/log/mysql1/error.log
log_bin = /var/lib/mysql1/mdb1-mysql-bin.log
server-id? ? ? ? ? ? ? = 1
expire_logs_days? ? ? ? = 10
sync_binlog? ? ? ? ? ? ?= 1
innodb_flush_log_at_trx_commit? ? ? ? ? = 1
innodb_flush_method? ? ? ? ? ? ? ? ? ? ?= O_DIRECT? ? ??
binlog_format? ? ? ? ? ? ? ? ? ? ? ? ? ?= mixed
gtid_strict_mode? ? ? ? ? ? ? ? ? ? ? ? = 1
table_cache? ? ? ? ? ? ? ? ?= 64


[mysqld2]
socket? ? ?= /tmp/mysql.sock2
port? ? ? ?= 3308
pid-file? ?= /usr/local/mysql/mdb2/ansible-host1.pid2
datadir? ? = /var/lib/mysql2
innodb_buffer_pool_size = 1G
log-error? = /var/log/mysql2/error.log
log-bin = /var/lib/mysql2/mdb2-mysql-bin.log
server-id? ? ? ? ? ? ? = 2
expire_logs_days? ? ? ? =?5
sync_binlog		= 1
innodb_flush_log_at_trx_commit		= 1
innodb_flush_method			= O_DIRECT	
binlog_format				= mixed
gtid_strict_mode			= 1
table_cache? ? ? ? ? ? ? ? ?= 64
        

• Run the below command to start the multiple instances from the mysqld_multi prompt (here, we'll specify the --defaults-file option to tell mysqld_multi where to read configurations from, and also instruct mysql_multi to write it's log to a log file specified using --logs)

root@ansible-host1:/etc/mysql# mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf --log=/etc/mysql/multi_ins.log start        

If this is the first time the instance are being started, mysqld_multi is going to prepare/initialise the specified locations of the datadir (creates system tables and databases). output would be similar to below:

Installing new database in /var/lib/mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql1' ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo
See the MariaDB Knowledgebase at https://mariadb.com/kb
You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql1'
As a MariaDB Corporation subscription customer please contact us
via https://support.mariadb.com/ to report problems.
You also can get consultative guidance on questions specific to your deployment,
such as how to tune for performance, high availability, security audits, and code review.
You also find detailed documentation about how to use MariaDB Enterprise Server at https://mariadb.com/docs/.
The latest information about MariaDB Server is available at https://mariadb.com/kb/en/library/release-notes/.
Installing new database in /var/lib/mysql2
Installing MariaDB/MySQL system tables in '/var/lib/mysql2' ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo
See the MariaDB Knowledgebase at https://mariadb.com/kb
You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql2'


As a MariaDB Corporation subscription customer please contact us
via https://support.mariadb.com/ to report problems.
You also can get consultative guidance on questions specific to your deployment,
such as how to tune for performance, high availability, security audits, and code review.


You also find detailed documentation about how to use MariaDB Enterprise Server at https://mariadb.com/docs/.
The latest information about MariaDB Server is available at https://mariadb.com/kb/en/library/release-notes/.1        

• check the status of the instances using the report command:

root@ansible-host1:/etc/mysql# mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf report
Wide character in print at /usr/bin/mysqld_multi line 711.
Reporting MariaDB servers
MariaDB server from group: mysqld1 is running
MariaDB server from group: mysqld2 is running        

N/B: If you use a separate config file, different from the default config file of the mysqld instance (e.g /etc/my.cnf), you must also specify the --defaults-file=... when running mysqld_multi report, else it would complain of not having any server(s) to report. see snippet below for example error:

root@ansible-host1:/etc/mysql# mysqld_multi report
Wide character in print at /usr/bin/mysqld_multi line 711.
Reporting MariaDB servers
No groups to be reported (check your GNRs)        

• Next, we'll connect to each of the instances using the created socket, and check the value of the "expire_logs_days" parameter, which we've set to different values on the config.

root@ansible-host1:/etc/mysql# mysql -A -S /tmp/mysql.sock1
Welcome to the MariaDB monitor.? Commands end with ; or \g.
Server version: 10.6.9-5-MariaDB-enterprise-log MariaDB Enterprise Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|? ? ? ? ? 10.000000 |
+--------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> select @@port;
+--------+
| @@port |
+--------+
|? ?3307 |
+--------+
1 row in set (0.000 sec)
MariaDB [(none)]> \q
Bye
root@ansible-host1:/etc/mysql# mysql -A -S /tmp/mysql.sock2
Welcome to the MariaDB monitor.? Commands end with ; or \g.
Server version: 10.6.9-5-MariaDB-enterprise-log MariaDB Enterprise Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|? ? ? ? ? ?5.000000 |
+--------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> select @@port;
+--------+
| @@port |
+--------+
|? ?3308 |
+--------+
MariaDB [(none)]> \q
Bye        

• to test the GNR functionality, we'll stop and start an instance using it's GNR:

root@ansible-host1:/etc/mysql# mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf --log=/etc/mysql/multi_ins.log stop 2
Wide character in print at /usr/bin/mysqld_multi line 711.
root@ansible-host1:/etc/mysql# mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf --log=/etc/mysql/multi_ins.log report
Wide character in print at /usr/bin/mysqld_multi line 711.
Reporting MariaDB servers
MariaDB server from group: mysqld1 is running
MariaDB server from group: mysqld2 is not running
root@ansible-host1:/etc/mysql# mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf --log=/etc/mysql/multi_ins.log start 2
Wide character in print at /usr/bin/mysqld_multi line 711.
root@ansible-host1:/etc/mysql# mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf --log=/etc/mysql/multi_ins.log report
Wide character in print at /usr/bin/mysqld_multi line 711.
Reporting MariaDB servers
MariaDB server from group: mysqld1 is running
MariaDB server from group: mysqld2 is running        

• Create a test database in instance 1, and verify that this only exists on instance 1:

root@ansible-host1:/etc/mysql# mysql -A -S /tmp/mysql.sock1
...
MariaDB [(none)]> create database MIS1;
Query OK, 1 row affected (0.003 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database? ? ? ? ? ?|
+--------------------+
| MIS1? ? ? ? ? ? ? ?|
| information_schema |
| mysql? ? ? ? ? ? ? |
| performance_schema |
| sys? ? ? ? ? ? ? ? |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [(none)]> exit
Bye
root@ansible-host1:/etc/mysql# mysql -A -S /tmp/mysql.sock2
...
MariaDB [(none)]> show databases;
+--------------------+
| Database? ? ? ? ? ?|
+--------------------+
| information_schema |
| mysql? ? ? ? ? ? ? |
| performance_schema |
| sys? ? ? ? ? ? ? ? |
+--------------------+
4 rows in set (0.001 sec)        

And that's it! we have a working deployment of two (2) separate MariaDB/MySQL databases on the same host/server.!!

While implementing this, some important things to keep in mind for the choice of host server:

• Total usable Memory RAM of the server
• CPU configuration of the server.
• disk I/O of the server.
• and of course, Storage.

Keeping in mind that there is a possibility of "resource contention". (You can tweak this a little from the resources allocated to the instances in the config file).

You can also take a dump of a specific instance, by specifying the socket flag (-S) in the mariabackup or mysqldump command. a backup/dump of instance have been taken, specifying the socket flag for instance 1 (/tmp/mysql.sock1), and of --master-data, to aid replication setup.

Next, we'll proceed to setup replication. our replica will only replicate from instance 1. Bear in mind that Replication cannot use Unix socket files. hence, we will connect to the source server using TCP/IP, and specifying the option "MASTER_PORT" in our "CHANGE MASTER..." command. As usual, prepare the replica server, to replicate from the master (ensuring all prerequisites have been met), now we'll run the change master ... command, and check the replication status.

# Run change master command on replica:
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='ansible-host1', MASTER_LOG_FILE='mdb1-mysql-bin.000005', MASTER_LOG_POS=462, MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_PORT=3307;
Query OK, 0 rows affected (0.018 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec);

# Check replica status: 
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************? ? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event? ? ? ? ? ? ? ? ? ?
Master_Host: ***.***.**.**? ? ? ? ? ? ? ? ? ?
Master_User: repl_user? ? ? ? ? ? ? ? ? ?
Master_Port: 3307? ? ? ? ? ? ? ?
Master_Log_File: mdb1-mysql-bin.000005? ? ? ? ? ?
Read_Master_Log_Pos: 780? ? ? ? ? ? ? 
Slave_IO_Running: Yes? ? ? ? ? ? ?
Slave_SQL_Running: Yes? ? ? ? ? ?
Exec_Master_Log_Pos: 780? ? ? ? ?
Seconds_Behind_Master: 0? ? ? ? ? ? ? ? ?
Last_IO_Errno: 0? ? ? ? ? ? ? ? ?
Last_IO_Error:?? ? ? ? ? ? ? ? 
Last_SQL_Errno: 0? ? ? ? ? ? ? ? 
Last_SQL_Error:?? ? ? ? ? ? ? 
Master_Server_Id: 1? ? ? ? ? ? ? ? ? ? 
Using_Gtid: No? ? ? ? ? ? ? ? ?
Parallel_Mode: optimistic? ? ? ?
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates? ? Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 1? ? 
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)        

(You can even choose to setup the replication to use gtid. should be an easy process, as you should have the replica info on the dump). Give it a try!

lastly, we'll create a test database and table on instance1, and confirm that only this instance is being replicated:

# On master node, instance 1:
root@ansible-host1:~# mysql -A -S /tmp/mysql.sock1
...
MariaDB [(none)]> create database TEST_DB;
Query OK, 1 row affected (0.004 sec)
MariaDB [(none)]> use TEST_DB;
Database changed
MariaDB [TEST_DB]> create table test_multi(id int auto_increment primary key, name varchar(10));
Query OK, 0 rows affected (0.031 sec)
MariaDB [TEST_DB]> show tables;
+-------------------+
| Tables_in_TEST_DB |
+-------------------+
| test_multi? ? ? ? |
+-------------------+
1 row in set (0.001 sec)
MariaDB [TEST_DB]> show schemas;
+--------------------+
| Database? ? ? ? ? ?|
+--------------------+
| MIS1? ? ? ? ? ? ? ?|
| TEST_DB? ? ? ? ? ? |
| information_schema |
| mysql? ? ? ? ? ? ? |
| performance_schema |
| sys? ? ? ? ? ? ? ? |
+--------------------+
6 rows in set (0.001 sec)


# On master node, instance 2:
MariaDB [(none)]> show databases;
+--------------------+
| Database? ? ? ? ? ?|
+--------------------+
| information_schema |
| mysql? ? ? ? ? ? ? |
| performance_schema |
| sys? ? ? ? ? ? ? ? |
+--------------------+
4 rows in set (0.001 sec)

# On replica node:
MariaDB [(none)]> show databases;
+--------------------+
| Database? ? ? ? ? ?|
+--------------------+
| MIS1? ? ? ? ? ? ? ?|
| TEST_DB? ? ? ? ? ? |
| information_schema |
| mysql? ? ? ? ? ? ? |
| performance_schema |
| sys? ? ? ? ? ? ? ? |
+--------------------+
6 rows in set (0.001 sec)
MariaDB [(none)]> show tables in TEST_DB;
+-------------------+
| Tables_in_TEST_DB |
+-------------------+
| test_multi? ? ? ? |
+-------------------+
1 row in set (0.000 sec)        

P.S: You can instead convert the commands for connecting to each instance, starting and stopping each instance, etc. to bash helper scripts (probably make life easier).

A question might come to mind: what if we want to run different instances of different versions on the same server?... well, this will be addressed in my next article.

Happy Reading!!