wagnerbianchi.com

Multiple MariaDB Instances and systemd units

First of all 😀 if you expect to read something really advanced level, this blog is not for you, just go read another stuff, 😉 I’m saving you some time.

Today I was caught by surprise with a request to help a good friend from Consulting side of the world. As I got very curious to execute this in production considering all the possible barriers I could ever be about to face, I start the project on my local lab to execute the following task:

How to get multiple instances of MariaDB Server running on the same machine and have systemd units for each one of them. Ok, it should be trivial, but, as I’m a hands on guy, I need to put things together to make sense of it and check if it really works. Alright, I have to say that I don’t like the idea to have multiple instances running in one server, as it can be such a big single point of failure as if your hardware has never failed before, it’s gonna fail and everything will just sync altogether. All instances you have, just down and it’s bad, really bad, very bad, you don’t wanna that. Any way, let’s put things together to make sense out of it and show you how I organized stuff.

First of all, you need to download a tar.gz of the MariaDB of the version you want to have running. I got the below:

1
2
3
4
5
6
7
8
9
10
[root@localhost ~]# wget https://downloads.mariadb.org/interstitial/mariadb-5.5.56/bintar-linux-x86_64/mariadb-5.5.56-linux-x86_64.tar.gz/from/http%3A//mirror.ufscar.br/mariadb/
--2017-10-25 13:18:41--  https://downloads.mariadb.org/interstitial/mariadb-5.5.56/bintar-linux-x86_64/mariadb-5.5.56-linux-x86_64.tar.gz/from/http%3A//mirror.ufscar.br/mariadb/
Resolving downloads.mariadb.org (downloads.mariadb.org)... 173.203.201.148
Connecting to downloads.mariadb.org (downloads.mariadb.org)|173.203.201.148|:443... connected.
…
[root@localhost ~]# ls -lh
total 214M
-rw-------. 1 root root 1.5K Jan 27  2016 anaconda-ks.cfg
-rw-r--r--  1 root root 214M Apr 30 12:29 mariadb-5.5.56-linux-x86_64.tar.gz
…

At this point what you need to do is, create the directories to be the database servers BASEDIR e the mysql user:

1
2
3
[root@localhost ~]# mkdir -p /var/lib/mysql/inst01
[root@localhost ~]# mkdir -p /var/lib/mysql/inst02
[root@localhost ~]# adduser mysql -s /sbin/nologin

Gunzip the files:

1
2
3
4
5
6
[root@localhost ~]# tar xvzf mariadb-5.5.56-linux-x86_64.tar.gz
mariadb-5.5.56-linux-x86_64/README
mariadb-5.5.56-linux-x86_64/COPYING
mariadb-5.5.56-linux-x86_64/EXCEPTIONS-CLIENT
mariadb-5.5.56-linux-x86_64/INSTALL-BINARY
…

Copy files to previously created BASEDIR locations:

1
2
[root@localhost ~]# cp -r mariadb-5.5.56-linux-x86_64/* /var/lib/mysql/inst01
[root@localhost ~]# cp -r mariadb-5.5.56-linux-x86_64/* /var/lib/mysql/inst02

Configure the ownership and permissions:

1
2
[root@localhost inst01]# chown -R mysql:mysql /var/lib/mysql/inst01/
[root@localhost inst01]# chown -R mysql:mysql /var/lib/mysql/inst02/

In the defined BASEDIR locations, create a small my.cnf file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@localhost mysql]# pwd
/var/lib/mysql
 
[root@localhost mysql]# cat inst01/my.cnf
[mysqld]
server_id=1
user=mysql
basedir=/var/lib/mysql/inst01/
datadir=/var/lib/mysql/inst01/data
port=3310
socket=/var/lib/mysql/inst01/mysql.socket
innodb-data-home-dir=/var/lib/mysql/inst01
innodb-data-file-path=ibdata1:12M:autoextend
innodb-log-file-size=5M
innodb-log-group-home-dir=/var/lib/mysql/inst01
log-error=/var/lib/mysql/inst01/inst01.err
skip-grant-tables
 
[root@localhost mysql]# cat inst02/my.cnf
[mysqld]
server_id=2
user=mysql
basedir=/var/lib/mysql/inst02/
datadir=/var/lib/mysql/inst02/data
port=3311
socket=/var/lib/mysql/inst02/mysql.socket
innodb-data-home-dir=/var/lib/mysql/inst02
innodb-data-file-path=ibdata1:12M:autoextend
innodb-log-file-size=5M
innodb-log-group-home-dir=/var/lib/mysql/inst02
log-error=/var/lib/mysql/inst01/inst02.err
skip-grant-tables

You can test the server’s start using the below command, but, as I tested that and saw that everything is OK, now it’s time to rock it inside new system units to make it possible to have a clear separation of the both MariaDB Servers running on the same box. I will call the MariaDB running on 3310, mariadb01.service and the one running on port 3311, mariadb02.service and then, I will reload the system units and start the services.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#: commands we need for the units
/var/lib/mysql/inst01/bin/mysqld_safe --defaults-file=/var/lib/mysql/inst01/my.cnf
/var/lib/mysql/inst02/bin/mysqld_safe --defaults-file=/var/lib/mysql/inst02/my.cnf
 
#: create the unit file
vim /etc/systemd/system/mariadb01.service
 
#: add the below to the mariadb01’s unit
[Unit]
Description=mariadb inst01
After=network.target
 
[Service]
Type=simple
User=mysql
ExecStart=/var/lib/mysql/inst01/bin/mysqld_safe --defaults-file=/var/lib/mysql/inst01/my.cnf
Restart=on-abort
 
 
[Install]
WantedBy=multi-user.target

Do the same for the second one, which is the mariadb02 and enable them:

1
2
3
4
[root@localhost mysql]# systemctl enable mariadb01.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb01.service to /etc/systemd/system/mariadb01.service.
[root@localhost mysql]# systemctl enable mariadb02.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb02.service to /etc/systemd/system/mariadb02.service.

Are them really enabled?

1
2
3
4
[root@localhost mysql]# systemctl is-enabled mariadb01.service
enabled
[root@localhost mysql]# systemctl is-enabled mariadb02.service
enabled

Reload them:

[root@localhost mysql]# systemctl daemon-reload

And rock it (start/status):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
#: check if any mysqld processes are running
[root@localhost ~]# ps aux | grep mysqld
root     14487  0.0  0.1 112644   952 pts/1    S+   21:03   0:00 grep --color=auto mysqld
 
#: start the first instance on 3310 and check status
[root@localhost mysql]# systemctl start mariadb01.service
[root@localhost mysql]# systemctl status mariadb01.service
● mariadb01.service - mariadb inst01
   Loaded: loaded (/etc/systemd/system/mariadb01.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2017-10-25 21:04:25 BST; 4s ago
 Main PID: 14493 (mysqld_safe)
   CGroup: /system.slice/mariadb01.service
           ├─14493 /bin/sh /var/lib/mysql/inst01/bin/mysqld_safe --defaults-file=/var/lib/mysql/inst01/my.cnf
           └─14712 /var/lib/mysql/inst01/bin/mysqld --defaults-file=/var/lib/mysql/inst01/my.cnf --basedir=/var/lib/mysql/inst01/ --datadir=/var/lib/mysql/inst01/data --plugin-dir=...
 
Oct 25 21:04:25 localhost.localdomain systemd[1]: Started mariadb inst01.
Oct 25 21:04:25 localhost.localdomain systemd[1]: Starting mariadb inst01...
Oct 25 21:04:25 localhost.localdomain mysqld_safe[14493]: 171025 21:04:25 mysqld_safe Logging to '/var/lib/mysql/inst01/inst01.err'.
Oct 25 21:04:25 localhost.localdomain mysqld_safe[14493]: 171025 21:04:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/inst01/data
 
#: start the first instance on 3311 and check status
[root@localhost mysql]# systemctl start mariadb02.service
[root@localhost mysql]# systemctl status mariadb02.service
● mariadb02.service - mariadb inst02
   Loaded: loaded (/etc/systemd/system/mariadb02.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2017-10-25 21:05:11 BST; 3s ago
 Main PID: 14741 (mysqld_safe)
   CGroup: /system.slice/mariadb02.service
           ├─14741 /bin/sh /var/lib/mysql/inst02/bin/mysqld_safe --defaults-file=/var/lib/mysql/inst02/my.cnf
           └─14960 /var/lib/mysql/inst02/bin/mysqld --defaults-file=/var/lib/mysql/inst02/my.cnf --basedir=/var/lib/mysql/inst02/ --datadir=/var/lib/mysql/inst02/data --plugin-dir=...
 
Oct 25 21:05:11 localhost.localdomain systemd[1]: Started mariadb inst02.
Oct 25 21:05:11 localhost.localdomain systemd[1]: Starting mariadb inst02...
Oct 25 21:05:11 localhost.localdomain mysqld_safe[14741]: 171025 21:05:11 mysqld_safe Logging to '/var/lib/mysql/inst01/inst02.err'.
Oct 25 21:05:11 localhost.localdomain mysqld_safe[14741]: 171025 21:05:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/inst02/data
 
#: checking ps again
[root@localhost ~]# ps aux | grep mysqld
mysql    14493  0.0  0.2 113252  1612 ?        Ss   21:04   0:00 /bin/sh /var/lib/mysql/inst01/bin/mysqld_safe --defaults-file=/var/lib/mysql/inst01/my.cnf
mysql    14712  0.1 12.8 660512 80908 ?        Sl   21:04   0:00 /var/lib/mysql/inst01/bin/mysqld --defaults-file=/var/lib/mysql/inst01/my.cnf --basedir=/var/lib/mysql/inst01/ --datadir=/var/lib/mysql/inst01/data --plugin-dir=/var/lib/mysql/inst01//lib/plugin --log-error=/var/lib/mysql/inst01/inst01.err --pid-file=localhost.localdomain.pid --socket=/var/lib/mysql/inst01/mysql.socket --port=3310
mysql    14741  0.0  0.2 113252  1620 ?        Ss   21:05   0:00 /bin/sh /var/lib/mysql/inst02/bin/mysqld_safe --defaults-file=/var/lib/mysql/inst02/my.cnf
mysql    14960  0.2 12.0 660512 75628 ?        Sl   21:05   0:00 /var/lib/mysql/inst02/bin/mysqld --defaults-file=/var/lib/mysql/inst02/my.cnf --basedir=/var/lib/mysql/inst02/ --datadir=/var/lib/mysql/inst02/data --plugin-dir=/var/lib/mysql/inst02//lib/plugin --log-error=/var/lib/mysql/inst01/inst02.err --pid-file=localhost.localdomain.pid --socket=/var/lib/mysql/inst02/mysql.socket --port=3311
root     14985  0.0  0.1 112644   956 pts/1    S+   21:05   0:00 grep --color=auto mysqld

And now, just to finish it, let’s access the instances:

[root@localhost mysql]# /var/lib/mysql/inst01/bin/mysql --socket=/var/lib/mysql/inst01/mysql.socket --prompt="inst01 [\d]> "
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.56-MariaDB MariaDB Server
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
inst01 [(none)]> \q
Bye
[root@localhost mysql]# /var/lib/mysql/inst01/bin/mysql --socket=/var/lib/mysql/inst02/mysql.socket --prompt="inst02 [\d]> "
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.56-MariaDB MariaDB Server
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
inst02 [(none)]> \q
Bye

A very popular related case is https://ma.ttias.be/increase-open-files-limit-in-mariadb-on-centos-7-with-systemd/ !

So, that’s it.


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

One Response to “Multiple MariaDB Instances and systemd units”

  1. Thanks a lot for this helpful entry 🙂
    as for why one would do such a thing, let’s just say IT people’s own insecurities with databases backups sometimes lead to weird architectures.

Leave a Reply