Jump to content
  • 0

Setting Mariadb-Mysql multiple instances with "mysqld_multi" wrapper.


Question

Hello,

I tried to set several mariadb(mysql) instances using the "mysqld_multi" wrapper for mysqld that is included in the software package for this purpose.

I followed some articles found in the Internet but it seems that something is not accurate enough or my system (Ubuntu 20.04 LTS ) has some peculiarity that prevents these setting to work.

Next there are all the steps I tried in order to run multiple Mariadb instances:

-----

OBJECTIVE: install one Mariadb server on a single machine with multiple instances.
The purposes are:
.- isolate server from data.
.- assign every instance to a diferent project.
.- maintain the data from every project isolated from each other.

Once the server has been installed and it is up and running, we verify that by :

>>> $ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.3.25 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2020-11-15 11:11:07 CET; 19min ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 5751 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 5760 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5767 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-env>
    Process: 5849 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5851 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 5817 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 30 (limit: 19000)
     Memory: 106.6M
     CGroup: /system.slice/mariadb.service
             └─5817 /usr/sbin/mysqld

nov 15 11:11:07 omen systemd[1]: Starting MariaDB 10.3.25 database server...
nov 15 11:11:07 omen mysqld[5817]: 2020-11-15 11:11:07 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0ubuntu0.20.04.1) starting as process 5817 ...
nov 15 11:11:07 omen mysqld[5817]: 2020-11-15 11:11:07 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32194)
nov 15 11:11:07 omen systemd[1]: Started MariaDB 10.3.25 database server.

The default directories comming from the installation process are:

.- db data directory:
    /var/lib/mysql
.- error logs:
    /var/log/mysql
.- pid and socket:
    /var/run/mysqld
    /run/mysqld
.- apparmor configuration:
    /etc/apparmor.d/usr.sbin.mysqld, emtpy fle
.- server configuration:
    /etc/mysql
       conf.d/
       debian.cnf
       debian-start*
       mariadb.cnf
       mariadb.conf.d/
       my.cnf -> /etc/alternatives/my.cnf

When mariadb server starts it follows the sequence:

1.- /etc/mysql/my.cnf -> /etc/alternatives/my.cnf
2.- /etc/alternatives/my.cnf, contains
    my.cnf -> /etc/mysql/mariadb.cnf
3.- /etc/mysql/mariadb.cnf, file only contains
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mariadb.conf.d/
3.1.- /etc/mysql/conf.d, directory is empty
3.2.- /etc/mysql/mariadb.conf.d, directory has this contents:
    50-client.cnf
    50-mysql-clients.cnf
    50-mysqld_safe.cnf
    50-server.cnf


These files are the configuration files of the server that ends in "50-server.cnf".


Some sources that explain how to use the "mysqld_multi" application to run several instances are:
.- https://draghici.net/2018/04/29/run-multiple-mysql-mariadb-instances-on-ubuntu-server/
.- https://journaldunadminlinux.fr/tuto-installation-dune-mysql-multi-instance/
.- https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysql/
.- https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
.- ...some others

Mariadb server includes "mysqld_multi" that is a wrapper for "mysqld" specifically designed to handle multiple instances:
>>>$ which mysqld_multi
/usr/bin/mysqld_multi


Below are the steps I tried but I do not get things working:

1.- Provide "Shutdown_priv" -privilege, to the user "multi_admin" that would be used
to handle the different instances:

>>>$ mysql -uroot -ppassword
MariaDB [(none)]> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multi_pass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit;


2.- Stop the mariadb service in order to safely operate over the db files from now:

>>>$ sudo systemctl stop mariadb


3.- Modify the configuration files in order to set up the new instances and multi_admin user:
3.1.- First backup the default configuration file at the same directory:
>>>$ sudo cp 50-server.cnf backup_50-server.cnf
3.2.- Modify the configuration server "50-server.cnf" as:
(also review the example file provided by issuing ">>>$ mysqld_multi --example > mysqld_multi-example.txt")


[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/multi-instances/mysqld_multi.log
user       = multi_admin  #instances admin user
password   = multi_pass  #password for instances admin user

[mysqld0]
# this configuration is the original for the base server but now it is set as
# "mysqld0" given every multi-instance must be named "mysqldN" being N an integer
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
bind-address            = 127.0.0.1
query_cache_size        = 16M
log_error               = /var/log/mysql/error.log
expire_logs_days        = 10
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

[mysqld1]
# second defined instance
user                    = project1
pid-file                = /run/mysqld1/mysqld.pid  #create "/run/mysql1" for
socket                  = /run/mysqld1/mysqld.sock  #socket and pid files, see point 5
port                    = 3307
datadir                 = /dataDir1  #create "/dataDir1", see point 6
log-error               = /var/log/mysql1/error.log  #create "/var/log/mysql1"
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

### Other settings that may be overlooked
#basedir                 = /usr
#tmpdir                  = /tmp
#lc-messages-dir         = /usr/share/mysql
#skip-external-locking
#bind-address            = 127.0.0.1
#key_buffer              = 16M
#max_allowed_packet      = 16M
#thread_stack            = 192K
#thread_cache_size       = 8
#myisam-recover          = BACKUP
#query_cache_limit       = 1M
#query_cache_size        = 16M
#expire_logs_days        = 10
#max_binlog_size         = 100M

Notes:
.- in case of setting additional instances, replicate [mysql1] group but a different N
it is not mandatory to be [mysqld2], it may contain gaps, i.e. 1, 5, 9...
.- also use different port numbers, i.e. 3309, 3311...


4.- Create the system users per instance:
>>>$ useradd -r project1 #create new users accordingly to the new defined instances

Note: see point "6.MariaDB Server User" from "mysqld_multi-example"
#
#   You can pass the user=... option inside [mysqld#] groups. This
#   can be very handy in some cases, but then you need to run mysqld_multi
#   as UNIX root.


5.- Generate directories and permissions for the defined directories in the configuration file for every instance:
5.1.- directories for pid and socket files for instance "mysqld1":
>>>$ mkdir /run/mysqld1
>>>$ chown project1:mysql /run/mysql1  #user:project1 group:mysql -> are these correct?

Note: I do not now if this directory is needed, but it is fonud as default for the base server
>>>$ mkdir /var/run/mysqld1
>>>$ chown project1:mysql /var/run/mysql1  #user:project1 group:mysql -> are these correct?

5.2.- directories for error logs of the multi_admin and user per instance:
>>>$ mkdir /var/log/mysqld-instances  #user "multi_admin"
>>>$ chown multi_admin:mysql /var/log/mysqld-instances  #user:multi_admin, group:mysql -> are these correct?

>>>$ mkdir /var/log/mysql1
>>>$ chown project1:mysql /var/log/mysql1  #user:project1 group:mysql

Doubt:
a.- Are the users and groups defined per every file correct?


6.- Initialize the db data directory per instance:
>>>$ mysql_install_db --user=project1 --datadir=dataDir1
>>>$ chown -R project1:mysql /dataDir1  #verify user and group and apply chown if necessary

Doubt:
b.- Are the user and group defined correct?


7.- "apparmor" configuration, this information or related, has been found in the following articles
.- https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
.- https://medium.com/@omkarmanjrekar/running-multiple-mysql-instances-on-ubuntu-4af059aad5ae
.- https://linuxhint.com/change_mysql_data_directory_ubuntu/

# vim:syntax=apparmor
# from https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
#include /usr/sbin/mysqld {
#include
#include
#include
#include
#include
 capability dac_override,
 capability sys_resource,
 capability setgid,
 capability setuid,
 network tcp,
 /etc/hosts.allow r,
 /etc/hosts.deny r,
 /etc/mysql/*.pem r,
 /etc/mysql/conf.d/ r,
 /etc/mysql/conf.d/* r,
 /etc/mysql/*.cnf r,
 /usr/lib/mysql/plugin/ r,
 /usr/lib/mysql/plugin/*.so* mr,
 /usr/sbin/mysqld mr,
 /usr/share/mysql/** r,
 /var/log/mysql.log rw,
 /var/log/mysql.err rw,
 /var/lib/mysql/ r,
 /var/lib/mysql/** rwk,
 /var/log/mysql/ r,
 /var/log/mysql/* rw,
 /var/run/mysqld/mysqld.pid rw,
 /var/run/mysqld/mysqld.sock w,
 /run/mysqld/mysqld.pid rw,
 /run/mysqld/mysqld.sock w,
### Pasted this content – ### secondary aka mysql2 ##
 /dataDir1/ r,
 /dataDir1/** rwk,
 /var/log/mysql1/ r,
 /var/log/mysql1/* rw,
 /{,var/}run/mysqld1/mysqld.pid w,
 /{,var/}run/mysqld1/mysqld.sock w,
#################### End of configuration for mysql2 ######################
 /sys/devices/system/cpu/ r,
 # Site-specific additions and overrides. See local/README for details.
 #include }

Notes:

a.- i.e. in my system do not exists " /usr/lib/mysql/plugin/" directory, so, must I eliminate the references to it from the apparmor setting?
b.- this is for me the most "obscure" part of the settings, I had never used "apparmor" and in some articles even some uninstall apparmor in order to have the instances running.
I feel this an extreme solution and not very "professional".

This finalizes the multi instances settings.


8.- Handling the instances with "mysqld_multi":

>>>$ mysqld_multi start  #starts all the instances

but...

>>>$ mysqld_multi report  #reports all the instances status
Reporting MySQL servers
MySQL server from group: mysqld0 is running

Notes:
.- No reference to the "mysqld1" instance.
.- I reviewed the pid and socket files in the "mysql1" directory and they were empty, nevertheless they appear in the "/run/mydsld" and "/var/run/myslqd" directories as defined for the  [mysql0] instance

>>>$ mysqld_multi stop  #stops all the instances

>>>$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld0 is not running

again, no reference to the "mysqld1" instance

I do not have more resources to know what it is not working.

9.- Other point that I do not found in the articles is how to access individual instances as one do with the base server:

>>>$ mysql -uroot -pmv2914kiN
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

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)]> show databases;
+--------------------+
| Database        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ....            |
| ....            |

That's all.

Thank you very much for yur attention.

 

Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0
On 11/18/2020 at 3:23 PM, JosepMVD said:

Hello,

I tried to set several mariadb(mysql) instances using the "mysqld_multi" wrapper for mysqld that is included in the software package for this purpose.

I followed some articles found in the Internet but it seems that something is not accurate enough or my system (Ubuntu 20.04 LTS ) has some peculiarity that prevents these setting to work.

Next there are all the steps I tried in order to run multiple Mariadb instances:

-----

OBJECTIVE: install one Mariadb server on a single machine with multiple instances.
The purposes are:
.- isolate server from data.
.- assign every instance to a diferent project.
.- maintain the data from every project isolated from each other.

Once the server has been installed and it is up and running, we verify that by :

>>> $ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.3.25 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2020-11-15 11:11:07 CET; 19min ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 5751 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 5760 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5767 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-env>
    Process: 5849 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5851 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 5817 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 30 (limit: 19000)
     Memory: 106.6M
     CGroup: /system.slice/mariadb.service
             └─5817 /usr/sbin/mysqld

nov 15 11:11:07 omen systemd[1]: Starting MariaDB 10.3.25 database server...
nov 15 11:11:07 omen mysqld[5817]: 2020-11-15 11:11:07 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0ubuntu0.20.04.1) starting as process 5817 ...
nov 15 11:11:07 omen mysqld[5817]: 2020-11-15 11:11:07 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32194)
nov 15 11:11:07 omen systemd[1]: Started MariaDB 10.3.25 database server.

The default directories comming from the installation process are:

.- db data directory:
    /var/lib/mysql
.- error logs:
    /var/log/mysql
.- pid and socket:
    /var/run/mysqld
    /run/mysqld
.- apparmor configuration:
    /etc/apparmor.d/usr.sbin.mysqld, emtpy fle
.- server configuration:
    /etc/mysql
       conf.d/
       debian.cnf
       debian-start*
       mariadb.cnf
       mariadb.conf.d/
       my.cnf -> /etc/alternatives/my.cnf

When mariadb server starts it follows the sequence:

1.- /etc/mysql/my.cnf -> /etc/alternatives/my.cnf
2.- /etc/alternatives/my.cnf, contains
    my.cnf -> /etc/mysql/mariadb.cnf
3.- /etc/mysql/mariadb.cnf, file only contains
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mariadb.conf.d/
3.1.- /etc/mysql/conf.d, directory is empty
3.2.- /etc/mysql/mariadb.conf.d, directory has this contents:
    50-client.cnf
    50-mysql-clients.cnf
    50-mysqld_safe.cnf
    50-server.cnf


These files are the configuration files of the server that ends in "50-server.cnf".


Some sources that explain how to use the "mysqld_multi" application to run several instances are:
.- https://draghici.net/2018/04/29/run-multiple-mysql-mariadb-instances-on-ubuntu-server/
.- https://journaldunadminlinux.fr/tuto-installation-dune-mysql-multi-instance/
.- https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysql/
.- https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
.- ...some others

Mariadb server includes "mysqld_multi" that is a wrapper for "mysqld" specifically designed to handle multiple instances:
>>>$ which mysqld_multi
/usr/bin/mysqld_multi


Below are the steps I tried but I do not get things working:

1.- Provide "Shutdown_priv" -privilege, to the user "multi_admin" that would be used
to handle the different instances:

>>>$ mysql -uroot -ppassword
MariaDB [(none)]> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multi_pass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit;


2.- Stop the mariadb service in order to safely operate over the db files from now:

>>>$ sudo systemctl stop mariadb


3.- Modify the configuration files in order to set up the new instances and multi_admin user:
3.1.- First backup the default configuration file at the same directory:
>>>$ sudo cp 50-server.cnf backup_50-server.cnf
3.2.- Modify the configuration server "50-server.cnf" as:
(also review the example file provided by issuing ">>>$ mysqld_multi --example > mysqld_multi-example.txt")


[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/multi-instances/mysqld_multi.log
user       = multi_admin  #instances admin user
password   = multi_pass  #password for instances admin user

[mysqld0]
# this configuration is the original for the base server but now it is set as
# "mysqld0" given every multi-instance must be named "mysqldN" being N an integer
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
bind-address            = 127.0.0.1
query_cache_size        = 16M
log_error               = /var/log/mysql/error.log
expire_logs_days        = 10
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

[mysqld1]
# second defined instance
user                    = project1
pid-file                = /run/mysqld1/mysqld.pid  #create "/run/mysql1" for
socket                  = /run/mysqld1/mysqld.sock  #socket and pid files, see point 5
port                    = 3307
datadir                 = /dataDir1  #create "/dataDir1", see point 6
log-error               = /var/log/mysql1/error.log  #create "/var/log/mysql1"
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

### Other settings that may be overlooked
#basedir                 = /usr
#tmpdir                  = /tmp
#lc-messages-dir         = /usr/share/mysql
#skip-external-locking
#bind-address            = 127.0.0.1
#key_buffer              = 16M
#max_allowed_packet      = 16M
#thread_stack            = 192K
#thread_cache_size       = 8
#myisam-recover          = BACKUP
#query_cache_limit       = 1M
#query_cache_size        = 16M
#expire_logs_days        = 10
#max_binlog_size         = 100M

Notes:
.- in case of setting additional instances, replicate [mysql1] group but a different N
it is not mandatory to be [mysqld2], it may contain gaps, i.e. 1, 5, 9...
.- also use different port numbers, i.e. 3309, 3311...


4.- Create the system users per instance:
>>>$ useradd -r project1 #create new users accordingly to the new defined instances

Note: see point "6.MariaDB Server User" from "mysqld_multi-example"
#
#   You can pass the user=... option inside [mysqld#] groups. This
#   can be very handy in some cases, but then you need to run mysqld_multi
#   as UNIX root.


5.- Generate directories and permissions for the defined directories in the configuration file for every instance:
5.1.- directories for pid and socket files for instance "mysqld1":
>>>$ mkdir /run/mysqld1
>>>$ chown project1:mysql /run/mysql1  #user:project1 group:mysql -> are these correct?

Note: I do not now if this directory is needed, but it is fonud as default for the base server
>>>$ mkdir /var/run/mysqld1
>>>$ chown project1:mysql /var/run/mysql1  #user:project1 group:mysql -> are these correct?

5.2.- directories for error logs of the multi_admin and user per instance:
>>>$ mkdir /var/log/mysqld-instances  #user "multi_admin"
>>>$ chown multi_admin:mysql /var/log/mysqld-instances  #user:multi_admin, group:mysql -> are these correct?

>>>$ mkdir /var/log/mysql1
>>>$ chown project1:mysql /var/log/mysql1  #user:project1 group:mysql

Doubt:
a.- Are the users and groups defined per every file correct?


6.- Initialize the db data directory per instance:
>>>$ mysql_install_db --user=project1 --datadir=dataDir1
>>>$ chown -R project1:mysql /dataDir1  #verify user and group and apply chown if necessary

Doubt:
b.- Are the user and group defined correct?


7.- "apparmor" configuration, this information or related, has been found in the following articles
.- https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
.- https://medium.com/@omkarmanjrekar/running-multiple-mysql-instances-on-ubuntu-4af059aad5ae
.- https://linuxhint.com/change_mysql_data_directory_ubuntu/

# vim:syntax=apparmor
# from https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
#include /usr/sbin/mysqld {
#include
#include
#include
#include
#include
 capability dac_override,
 capability sys_resource,
 capability setgid,
 capability setuid,
 network tcp,
 /etc/hosts.allow r,
 /etc/hosts.deny r,
 /etc/mysql/*.pem r,
 /etc/mysql/conf.d/ r,
 /etc/mysql/conf.d/* r,
 /etc/mysql/*.cnf r,
 /usr/lib/mysql/plugin/ r,
 /usr/lib/mysql/plugin/*.so* mr,
 /usr/sbin/mysqld mr,
 /usr/share/mysql/** r,
 /var/log/mysql.log rw,
 /var/log/mysql.err rw,
 /var/lib/mysql/ r,
 /var/lib/mysql/** rwk,
 /var/log/mysql/ r,
 /var/log/mysql/* rw,
 /var/run/mysqld/mysqld.pid rw,
 /var/run/mysqld/mysqld.sock w,
 /run/mysqld/mysqld.pid rw,
 /run/mysqld/mysqld.sock w,
### Pasted this content – ### secondary aka mysql2 ##
 /dataDir1/ r,
 /dataDir1/** rwk,
 /var/log/mysql1/ r,
 /var/log/mysql1/* rw,
 /{,var/}run/mysqld1/mysqld.pid w,
 /{,var/}run/mysqld1/mysqld.sock w,
#################### End of configuration for mysql2 ######################
 /sys/devices/system/cpu/ r,
 # Site-specific additions and overrides. See local/README for details.
 #include }

Notes:

a.- i.e. in my system do not exists " /usr/lib/mysql/plugin/" directory, so, must I eliminate the references to it from the apparmor setting?
b.- this is for me the most "obscure" part of the settings, I had never used "apparmor" and in some articles even some uninstall apparmor in order to have the instances running.
I feel this an extreme solution and not very "professional".

This finalizes the multi instances settings.


8.- Handling the instances with "mysqld_multi":

>>>$ mysqld_multi start  #starts all the instances

but...

>>>$ mysqld_multi report  #reports all the instances status
Reporting MySQL servers
MySQL server from group: mysqld0 is running

Notes:
.- No reference to the "mysqld1" instance.
.- I reviewed the pid and socket files in the "mysql1" directory and they were empty, nevertheless they appear in the "/run/mydsld" and "/var/run/myslqd" directories as defined for the  [mysql0] instance

>>>$ mysqld_multi stop  #stops all the instances

>>>$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld0 is not running

again, no reference to the "mysqld1" instance

I do not have more resources to know what it is not working.

9.- Other point that I do not found in the articles is how to access individual instances as one do with the base server:

>>>$ mysql -uroot -pmv2914kiN
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

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)]> show databases;
+--------------------+
| Database        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ....            |
| ....            |

That's all.

Thank you very much for yur attention.

 

Hi Josep,
I will get back to you once I have checked it out myself and have some information to share.

Thanks.
Shovon

Link to post
Share on other sites
  • 0
On 11/18/2020 at 3:23 PM, JosepMVD said:

Hello,

I tried to set several mariadb(mysql) instances using the "mysqld_multi" wrapper for mysqld that is included in the software package for this purpose.

I followed some articles found in the Internet but it seems that something is not accurate enough or my system (Ubuntu 20.04 LTS ) has some peculiarity that prevents these setting to work.

Next there are all the steps I tried in order to run multiple Mariadb instances:

-----

OBJECTIVE: install one Mariadb server on a single machine with multiple instances.
The purposes are:
.- isolate server from data.
.- assign every instance to a diferent project.
.- maintain the data from every project isolated from each other.

Once the server has been installed and it is up and running, we verify that by :

>>> $ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.3.25 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2020-11-15 11:11:07 CET; 19min ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 5751 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 5760 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5767 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-env>
    Process: 5849 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5851 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 5817 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 30 (limit: 19000)
     Memory: 106.6M
     CGroup: /system.slice/mariadb.service
             └─5817 /usr/sbin/mysqld

nov 15 11:11:07 omen systemd[1]: Starting MariaDB 10.3.25 database server...
nov 15 11:11:07 omen mysqld[5817]: 2020-11-15 11:11:07 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0ubuntu0.20.04.1) starting as process 5817 ...
nov 15 11:11:07 omen mysqld[5817]: 2020-11-15 11:11:07 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32194)
nov 15 11:11:07 omen systemd[1]: Started MariaDB 10.3.25 database server.

The default directories comming from the installation process are:

.- db data directory:
    /var/lib/mysql
.- error logs:
    /var/log/mysql
.- pid and socket:
    /var/run/mysqld
    /run/mysqld
.- apparmor configuration:
    /etc/apparmor.d/usr.sbin.mysqld, emtpy fle
.- server configuration:
    /etc/mysql
       conf.d/
       debian.cnf
       debian-start*
       mariadb.cnf
       mariadb.conf.d/
       my.cnf -> /etc/alternatives/my.cnf

When mariadb server starts it follows the sequence:

1.- /etc/mysql/my.cnf -> /etc/alternatives/my.cnf
2.- /etc/alternatives/my.cnf, contains
    my.cnf -> /etc/mysql/mariadb.cnf
3.- /etc/mysql/mariadb.cnf, file only contains
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mariadb.conf.d/
3.1.- /etc/mysql/conf.d, directory is empty
3.2.- /etc/mysql/mariadb.conf.d, directory has this contents:
    50-client.cnf
    50-mysql-clients.cnf
    50-mysqld_safe.cnf
    50-server.cnf


These files are the configuration files of the server that ends in "50-server.cnf".


Some sources that explain how to use the "mysqld_multi" application to run several instances are:
.- https://draghici.net/2018/04/29/run-multiple-mysql-mariadb-instances-on-ubuntu-server/
.- https://journaldunadminlinux.fr/tuto-installation-dune-mysql-multi-instance/
.- https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysql/
.- https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
.- ...some others

Mariadb server includes "mysqld_multi" that is a wrapper for "mysqld" specifically designed to handle multiple instances:
>>>$ which mysqld_multi
/usr/bin/mysqld_multi


Below are the steps I tried but I do not get things working:

1.- Provide "Shutdown_priv" -privilege, to the user "multi_admin" that would be used
to handle the different instances:

>>>$ mysql -uroot -ppassword
MariaDB [(none)]> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multi_pass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit;


2.- Stop the mariadb service in order to safely operate over the db files from now:

>>>$ sudo systemctl stop mariadb


3.- Modify the configuration files in order to set up the new instances and multi_admin user:
3.1.- First backup the default configuration file at the same directory:
>>>$ sudo cp 50-server.cnf backup_50-server.cnf
3.2.- Modify the configuration server "50-server.cnf" as:
(also review the example file provided by issuing ">>>$ mysqld_multi --example > mysqld_multi-example.txt")


[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/multi-instances/mysqld_multi.log
user       = multi_admin  #instances admin user
password   = multi_pass  #password for instances admin user

[mysqld0]
# this configuration is the original for the base server but now it is set as
# "mysqld0" given every multi-instance must be named "mysqldN" being N an integer
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
bind-address            = 127.0.0.1
query_cache_size        = 16M
log_error               = /var/log/mysql/error.log
expire_logs_days        = 10
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

[mysqld1]
# second defined instance
user                    = project1
pid-file                = /run/mysqld1/mysqld.pid  #create "/run/mysql1" for
socket                  = /run/mysqld1/mysqld.sock  #socket and pid files, see point 5
port                    = 3307
datadir                 = /dataDir1  #create "/dataDir1", see point 6
log-error               = /var/log/mysql1/error.log  #create "/var/log/mysql1"
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

### Other settings that may be overlooked
#basedir                 = /usr
#tmpdir                  = /tmp
#lc-messages-dir         = /usr/share/mysql
#skip-external-locking
#bind-address            = 127.0.0.1
#key_buffer              = 16M
#max_allowed_packet      = 16M
#thread_stack            = 192K
#thread_cache_size       = 8
#myisam-recover          = BACKUP
#query_cache_limit       = 1M
#query_cache_size        = 16M
#expire_logs_days        = 10
#max_binlog_size         = 100M

Notes:
.- in case of setting additional instances, replicate [mysql1] group but a different N
it is not mandatory to be [mysqld2], it may contain gaps, i.e. 1, 5, 9...
.- also use different port numbers, i.e. 3309, 3311...


4.- Create the system users per instance:
>>>$ useradd -r project1 #create new users accordingly to the new defined instances

Note: see point "6.MariaDB Server User" from "mysqld_multi-example"
#
#   You can pass the user=... option inside [mysqld#] groups. This
#   can be very handy in some cases, but then you need to run mysqld_multi
#   as UNIX root.


5.- Generate directories and permissions for the defined directories in the configuration file for every instance:
5.1.- directories for pid and socket files for instance "mysqld1":
>>>$ mkdir /run/mysqld1
>>>$ chown project1:mysql /run/mysql1  #user:project1 group:mysql -> are these correct?

Note: I do not now if this directory is needed, but it is fonud as default for the base server
>>>$ mkdir /var/run/mysqld1
>>>$ chown project1:mysql /var/run/mysql1  #user:project1 group:mysql -> are these correct?

5.2.- directories for error logs of the multi_admin and user per instance:
>>>$ mkdir /var/log/mysqld-instances  #user "multi_admin"
>>>$ chown multi_admin:mysql /var/log/mysqld-instances  #user:multi_admin, group:mysql -> are these correct?

>>>$ mkdir /var/log/mysql1
>>>$ chown project1:mysql /var/log/mysql1  #user:project1 group:mysql

Doubt:
a.- Are the users and groups defined per every file correct?


6.- Initialize the db data directory per instance:
>>>$ mysql_install_db --user=project1 --datadir=dataDir1
>>>$ chown -R project1:mysql /dataDir1  #verify user and group and apply chown if necessary

Doubt:
b.- Are the user and group defined correct?


7.- "apparmor" configuration, this information or related, has been found in the following articles
.- https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
.- https://medium.com/@omkarmanjrekar/running-multiple-mysql-instances-on-ubuntu-4af059aad5ae
.- https://linuxhint.com/change_mysql_data_directory_ubuntu/

# vim:syntax=apparmor
# from https://sharadchhetri.com/how-to-configure-multiple-mysql-instance-in-ubuntu/
#include /usr/sbin/mysqld {
#include
#include
#include
#include
#include
 capability dac_override,
 capability sys_resource,
 capability setgid,
 capability setuid,
 network tcp,
 /etc/hosts.allow r,
 /etc/hosts.deny r,
 /etc/mysql/*.pem r,
 /etc/mysql/conf.d/ r,
 /etc/mysql/conf.d/* r,
 /etc/mysql/*.cnf r,
 /usr/lib/mysql/plugin/ r,
 /usr/lib/mysql/plugin/*.so* mr,
 /usr/sbin/mysqld mr,
 /usr/share/mysql/** r,
 /var/log/mysql.log rw,
 /var/log/mysql.err rw,
 /var/lib/mysql/ r,
 /var/lib/mysql/** rwk,
 /var/log/mysql/ r,
 /var/log/mysql/* rw,
 /var/run/mysqld/mysqld.pid rw,
 /var/run/mysqld/mysqld.sock w,
 /run/mysqld/mysqld.pid rw,
 /run/mysqld/mysqld.sock w,
### Pasted this content – ### secondary aka mysql2 ##
 /dataDir1/ r,
 /dataDir1/** rwk,
 /var/log/mysql1/ r,
 /var/log/mysql1/* rw,
 /{,var/}run/mysqld1/mysqld.pid w,
 /{,var/}run/mysqld1/mysqld.sock w,
#################### End of configuration for mysql2 ######################
 /sys/devices/system/cpu/ r,
 # Site-specific additions and overrides. See local/README for details.
 #include }

Notes:

a.- i.e. in my system do not exists " /usr/lib/mysql/plugin/" directory, so, must I eliminate the references to it from the apparmor setting?
b.- this is for me the most "obscure" part of the settings, I had never used "apparmor" and in some articles even some uninstall apparmor in order to have the instances running.
I feel this an extreme solution and not very "professional".

This finalizes the multi instances settings.


8.- Handling the instances with "mysqld_multi":

>>>$ mysqld_multi start  #starts all the instances

but...

>>>$ mysqld_multi report  #reports all the instances status
Reporting MySQL servers
MySQL server from group: mysqld0 is running

Notes:
.- No reference to the "mysqld1" instance.
.- I reviewed the pid and socket files in the "mysql1" directory and they were empty, nevertheless they appear in the "/run/mydsld" and "/var/run/myslqd" directories as defined for the  [mysql0] instance

>>>$ mysqld_multi stop  #stops all the instances

>>>$ mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld0 is not running

again, no reference to the "mysqld1" instance

I do not have more resources to know what it is not working.

9.- Other point that I do not found in the articles is how to access individual instances as one do with the base server:

>>>$ mysql -uroot -pmv2914kiN
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

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)]> show databases;
+--------------------+
| Database        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ....            |
| ....            |

That's all.

Thank you very much for yur attention.

 

Hi JosepMVD,
I have written an article to help you on this matter. Please check my article Running Multiple MariaDB Instances on Ubuntu 20.04 LTS.

I wish you all the best.

Shovon

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...