First of all \ud83d\ude00 if you expect to read something really advanced level, this blog is not for you, just go read another stuff, \ud83d\ude09 I’m saving you some time.<\/p>\n
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:<\/p>\n
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\u2019m 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\u2019t 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\u2019s gonna fail and everything will just sync altogether. All instances you have, just down and it\u2019s bad, really bad, very bad, you don\u2019t wanna that. Any way, let\u2019s put things together to make sense out of it and show you how I organized stuff.<\/p>\n
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:<\/p>\n
[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\/\r\n--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\/\r\nResolving downloads.mariadb.org (downloads.mariadb.org)... 173.203.201.148\r\nConnecting to downloads.mariadb.org (downloads.mariadb.org)|173.203.201.148|:443... connected.\r\n\u2026\r\n[root@localhost ~]# ls -lh\r\ntotal 214M\r\n-rw-------. 1 root root 1.5K Jan 27 2016 anaconda-ks.cfg\r\n-rw-r--r-- 1 root root 214M Apr 30 12:29 mariadb-5.5.56-linux-x86_64.tar.gz\r\n\u2026<\/pre>\nAt this point what you need to do is, create the directories to be the database servers BASEDIR e the mysql user:<\/p>\n
[root@localhost ~]# mkdir -p \/var\/lib\/mysql\/inst01\r\n[root@localhost ~]# mkdir -p \/var\/lib\/mysql\/inst02\r\n[root@localhost ~]# adduser mysql -s \/sbin\/nologin<\/pre>\nGunzip the files:<\/p>\n
[root@localhost ~]# tar xvzf mariadb-5.5.56-linux-x86_64.tar.gz\r\nmariadb-5.5.56-linux-x86_64\/README\r\nmariadb-5.5.56-linux-x86_64\/COPYING\r\nmariadb-5.5.56-linux-x86_64\/EXCEPTIONS-CLIENT\r\nmariadb-5.5.56-linux-x86_64\/INSTALL-BINARY\r\n\u2026<\/pre>\nCopy files to previously created BASEDIR locations:<\/p>\n
[root@localhost ~]# cp -r mariadb-5.5.56-linux-x86_64\/* \/var\/lib\/mysql\/inst01\r\n[root@localhost ~]# cp -r mariadb-5.5.56-linux-x86_64\/* \/var\/lib\/mysql\/inst02<\/pre>\nConfigure the ownership and permissions:<\/p>\n
[root@localhost inst01]# chown -R mysql:mysql \/var\/lib\/mysql\/inst01\/\r\n[root@localhost inst01]# chown -R mysql:mysql \/var\/lib\/mysql\/inst02\/<\/pre>\nIn the defined BASEDIR locations, create a small my.cnf file:<\/p>\n
[root@localhost mysql]# pwd\r\n\/var\/lib\/mysql\r\n\r\n[root@localhost mysql]# cat inst01\/my.cnf\r\n[mysqld]\r\nserver_id=1\r\nuser=mysql\r\nbasedir=\/var\/lib\/mysql\/inst01\/\r\ndatadir=\/var\/lib\/mysql\/inst01\/data\r\nport=3310\r\nsocket=\/var\/lib\/mysql\/inst01\/mysql.socket\r\ninnodb-data-home-dir=\/var\/lib\/mysql\/inst01\r\ninnodb-data-file-path=ibdata1:12M:autoextend\r\ninnodb-log-file-size=5M\r\ninnodb-log-group-home-dir=\/var\/lib\/mysql\/inst01\r\nlog-error=\/var\/lib\/mysql\/inst01\/inst01.err\r\nskip-grant-tables\r\n\r\n[root@localhost mysql]# cat inst02\/my.cnf\r\n[mysqld]\r\nserver_id=2\r\nuser=mysql\r\nbasedir=\/var\/lib\/mysql\/inst02\/\r\ndatadir=\/var\/lib\/mysql\/inst02\/data\r\nport=3311\r\nsocket=\/var\/lib\/mysql\/inst02\/mysql.socket\r\ninnodb-data-home-dir=\/var\/lib\/mysql\/inst02\r\ninnodb-data-file-path=ibdata1:12M:autoextend\r\ninnodb-log-file-size=5M\r\ninnodb-log-group-home-dir=\/var\/lib\/mysql\/inst02\r\nlog-error=\/var\/lib\/mysql\/inst01\/inst02.err\r\nskip-grant-tables<\/pre>\nYou can test the server\u2019s start using the below command, but, as I tested that and saw that everything is OK, now it\u2019s 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.<\/p>\n
#: commands we need for the units\r\n\/var\/lib\/mysql\/inst01\/bin\/mysqld_safe --defaults-file=\/var\/lib\/mysql\/inst01\/my.cnf\r\n\/var\/lib\/mysql\/inst02\/bin\/mysqld_safe --defaults-file=\/var\/lib\/mysql\/inst02\/my.cnf\r\n\r\n#: create the unit file\r\nvim \/etc\/systemd\/system\/mariadb01.service\r\n\r\n#: add the below to the mariadb01\u2019s unit\r\n[Unit]\r\nDescription=mariadb inst01\r\nAfter=network.target\r\n\r\n[Service]\r\nType=simple\r\nUser=mysql\r\nExecStart=\/var\/lib\/mysql\/inst01\/bin\/mysqld_safe --defaults-file=\/var\/lib\/mysql\/inst01\/my.cnf\r\nRestart=on-abort\r\n\r\n\r\n[Install]\r\nWantedBy=multi-user.target<\/pre>\nDo the same for the second one, which is the mariadb02 and enable them:<\/p>\n
[root@localhost mysql]# systemctl enable mariadb01.service\r\nCreated symlink from \/etc\/systemd\/system\/multi-user.target.wants\/mariadb01.service to \/etc\/systemd\/system\/mariadb01.service.\r\n[root@localhost mysql]# systemctl enable mariadb02.service\r\nCreated symlink from \/etc\/systemd\/system\/multi-user.target.wants\/mariadb02.service to \/etc\/systemd\/system\/mariadb02.service.<\/pre>\nAre them really enabled?<\/p>\n
[root@localhost mysql]# systemctl is-enabled mariadb01.service\r\nenabled\r\n[root@localhost mysql]# systemctl is-enabled mariadb02.service\r\nenabled<\/pre>\nReload them:<\/p>\n
[root@localhost mysql]# systemctl daemon-reload<\/pre>\nAnd rock it (start\/status):<\/p>\n
#: check if any mysqld processes are running\r\n[root@localhost ~]# ps aux | grep mysqld\r\nroot 14487 0.0 0.1 112644 952 pts\/1 S+ 21:03 0:00 grep --color=auto mysqld\r\n\r\n#: start the first instance on 3310 and check status\r\n[root@localhost mysql]# systemctl start mariadb01.service\r\n[root@localhost mysql]# systemctl status mariadb01.service\r\n\u25cf mariadb01.service - mariadb inst01\r\n Loaded: loaded (\/etc\/systemd\/system\/mariadb01.service; enabled; vendor preset: disabled)\r\n Active: active (running) since Wed 2017-10-25 21:04:25 BST; 4s ago\r\n Main PID: 14493 (mysqld_safe)\r\n CGroup: \/system.slice\/mariadb01.service\r\n \u251c\u250014493 \/bin\/sh \/var\/lib\/mysql\/inst01\/bin\/mysqld_safe --defaults-file=\/var\/lib\/mysql\/inst01\/my.cnf\r\n \u2514\u250014712 \/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=...\r\n\r\nOct 25 21:04:25 localhost.localdomain systemd[1]: Started mariadb inst01.\r\nOct 25 21:04:25 localhost.localdomain systemd[1]: Starting mariadb inst01...\r\nOct 25 21:04:25 localhost.localdomain mysqld_safe[14493]: 171025 21:04:25 mysqld_safe Logging to '\/var\/lib\/mysql\/inst01\/inst01.err'.\r\nOct 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\r\n\r\n#: start the first instance on 3311 and check status\r\n[root@localhost mysql]# systemctl start mariadb02.service\r\n[root@localhost mysql]# systemctl status mariadb02.service\r\n\u25cf mariadb02.service - mariadb inst02\r\n Loaded: loaded (\/etc\/systemd\/system\/mariadb02.service; enabled; vendor preset: disabled)\r\n Active: active (running) since Wed 2017-10-25 21:05:11 BST; 3s ago\r\n Main PID: 14741 (mysqld_safe)\r\n CGroup: \/system.slice\/mariadb02.service\r\n \u251c\u250014741 \/bin\/sh \/var\/lib\/mysql\/inst02\/bin\/mysqld_safe --defaults-file=\/var\/lib\/mysql\/inst02\/my.cnf\r\n \u2514\u250014960 \/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=...\r\n\r\nOct 25 21:05:11 localhost.localdomain systemd[1]: Started mariadb inst02.\r\nOct 25 21:05:11 localhost.localdomain systemd[1]: Starting mariadb inst02...\r\nOct 25 21:05:11 localhost.localdomain mysqld_safe[14741]: 171025 21:05:11 mysqld_safe Logging to '\/var\/lib\/mysql\/inst01\/inst02.err'.\r\nOct 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\r\n\r\n#: checking ps again\r\n[root@localhost ~]# ps aux | grep mysqld\r\nmysql 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\r\nmysql 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\r\nmysql 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\r\nmysql 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\r\nroot 14985 0.0 0.1 112644 956 pts\/1 S+ 21:05 0:00 grep --color=auto mysqld<\/pre>\nAnd now, just to finish it, let\u2019s access the instances:<\/p>\n
[root@localhost mysql]# \/var\/lib\/mysql\/inst01\/bin\/mysql --socket=\/var\/lib\/mysql\/inst01\/mysql.socket --prompt=\"inst01 [\\d]> \"\r\nWelcome to the MariaDB monitor. Commands end with ; or \\g.\r\nYour MariaDB connection id is 3\r\nServer version: 5.5.56-MariaDB MariaDB Server\r\n\r\nCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\ninst01 [(none)]> \\q\r\nBye\r\n[root@localhost mysql]# \/var\/lib\/mysql\/inst01\/bin\/mysql --socket=\/var\/lib\/mysql\/inst02\/mysql.socket --prompt=\"inst02 [\\d]> \"\r\nWelcome to the MariaDB monitor. Commands end with ; or \\g.\r\nYour MariaDB connection id is 1\r\nServer version: 5.5.56-MariaDB MariaDB Server\r\n\r\nCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\ninst02 [(none)]> \\q\r\nBye<\/pre>\nA very popular related case is https:\/\/ma.ttias.be\/increase-open-files-limit-in-mariadb-on-centos-7-with-systemd\/ !<\/p>\n
So, that\u2019s it.<\/p>\n","protected":false},"excerpt":{"rendered":"
First of all \ud83d\ude00 if you expect to read something really advanced level, this blog is not for you, just go read another stuff, \ud83d\ude09 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[42],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1335"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1335"}],"version-history":[{"count":9,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1335\/revisions"}],"predecessor-version":[{"id":1378,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1335\/revisions\/1378"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1335"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}