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.
outubro 26th, 2017 at 6:46
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.