mariadb - configuration using non standard options.

English Programming/Scripting
1

Hello.
Up to now, I can install a mariadb server using non standard options.
The initial database is left with unmodified options (options set when installing mariadb : zypper in mariadb)

The new databases are started with systemctl start mariadb@%i ( using ExecStart=/usr/bin/mysqld_multi start %i )

The databases application are installed this way

APP_1 is installed in /bdd/bd_1/app_1/APP_1
Socket file and Pid files are in : /bdd/bd_1/app_1/sock
Logs are in : /var/log/mysql/APP_1/

APP_2 is installed in /bdd/bd_2/app_2/APP_2
Socket file and Pid files are in : /bdd/bd_2/app_sock
Logs are in : /var/log/mysql/APP_1/

And so on.

Taking APP_1 as example :
Operating system files permissions are :
/bdd/bd_1/app_1 is owned by mysql:mysql
/bdd/bd_1/app_1/sock is owned by mysql:mysql
/bdd/bd_1/app_1/APP_1 is owned by ‘app_1:app_1’ ( app_1 is a system user in group app_1 without login )
/var/log/mysql/APP_1/*.log are owned by mysql:mysql

The database system files are created this way :

mysql_install_db --defaults-file="/etc/my.cnf.d/cfg_1.cnf" --user=app_1  --datadir=/bdd/bd_1/app_1/APP_1

The problem is that the database does not start because access forbiden :

2021-04-01 20:02:02 0 [ERROR] mysqld: File '/bdd/bd_1/app_1/APP_1/aria_log_control' not found (Errcode: 13 "Permission denied")
2021-04-01 20:02:02 0 [ERROR] mysqld: Got error 'Can't open file' when trying to use aria control file '/bdd/bd_1/app_1/APP_1/aria_log_control'
2021-04-01 20:02:02 0 [ERROR] Plugin 'Aria' init function returned error.
2021-04-01 20:02:02 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2021-04-01 20:02:02 0 [Note] InnoDB: Using Linux native AIO
2021-04-01 20:02:02 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-04-01 20:02:02 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-04-01 20:02:02 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-04-01 20:02:02 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-04-01 20:02:02 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-04-01 20:02:02 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2021-04-01 20:02:02 0 [ERROR] Failed to initialize plugins.
2021-04-01 20:02:02 0 [ERROR] Aborting

–

Starting MariaDB servers

210401 20:02:02 mysqld_safe Logging to '/var/log/mysql/APP_1/APP_1_error.log'.
210401 20:02:02 mysqld_safe Starting mysqld daemon with databases from /bdd/bd_1/app_1/APP_1
mysqld_multi log file version 2.20; run: Thu Apr  1 20:02:02 2021

Stopping MariaDB servers

I would like all the apps system files not owned by mysql and have an admistrative user not mysql.

Any help is welcome.

2

See documentation
https://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html

The first thing to note is that the specified User should be a previously created System account.
Not having personally run this before, I’m guessing that this User isn’t an account with elevated permissions for a reason, so things like starting mysqld should not be done by this user, you should start the instance using root or on boot so that the database instance is already available to your User for access.
You can do a bit of research on this, but it’s what makes sense to me.

TSU

3

All this is obvious but does not answer the question. Also I am in the database installation step ( using mysql_install_db, mysql_secure_installation, … ) and not in the boot boot step.
The install script is run using sudo, therefore it is the case for starting the instancewith

sudo systemctl start mariadb@1

.
The documentation is useless because the cases of databases indexed by mariadb@ are practically not processed. For example from what I could understand from my reading, “mysql_secure_installation” seems not to be able to work with such configurations.

In short, apart from a simple database installed in the/var/lib/mysql directory with a mysql owner, it’s a mess.

Any help is welcome.

4

First,
I would suggest that what you’ve described is actually simply MySQL/MariaDB application management, and has nothing to do with scripting, with your agreement an Administrator should move this thread to the Applications forum.

Now,
Believe the answer to your question can be found in the MySQL documentation.
There are two approaches to running multiple MySQL database instances… A separate binary for each instance or multiple instances from one binary (which you are doing) or some combiantion of the two.

The following are the pages relevant to what you have been asking about. Although they describe installing, configuring and managing using commands using mysql Admin commands.
From what I can see, your error is almost self-explanatory… by not configuring each error thrown as a different value, when you start the second instance it’s conflicting with the first which is the default so already owns those values.

The “from 30,000 foot level” description
https://dev.mysql.com/doc/refman/8.0/en/multiple-servers.html
The description for setting up each instance. Although it describes setting up manually instead of using the mysql_db_install script, it identifies each parameter that needs to be set up with a different value. This is what you need to configure correctly to fix your errors.
https://dev.mysql.com/doc/refman/8.0/en/multiple-data-directories.html
Once each parameter has been set up properly, starting your instances.
https://dev.mysql.com/doc/refman/8.0/en/multiple-unix-servers.html
A better way to start your databases
https://dev.mysql.com/doc/refman/8.0/en/mysqld-multi.html
Although you haven’t gotten to the point of a client app using one of your mysql instances, the following is what you need at that point. So, for reference
https://dev.mysql.com/doc/refman/8.0/en/multiple-server-clients.html

HTH,
TSU

5

Sounds like a typical case of apparmor restriction. Did you try stopping apparmor?

6

Using all possible combinations of parameters I managed to fully configure a multi-process server.

Note : I am using a script which is started with sudo.
So every command were executed for user ‘root:root’
The script change owner and permissions as necessary.

The following is a summary of the tasks to do. The commands are supposed to be entered in a terminal emulator. As I have done my test from a script, some commands relative mysql programs (mysql, mysql_install_db, mariadb-secure-installation) may not work correctly. In that case put the commands in a script like me, delete every things, restart the process with the script.

1st step

Stop mysql

sudo systemctl stop mariadb
sudo systemctl -l --no-pager  status  mariadb

2st step

backup initial /etc/my.cnf
As root make your own /etc/my.cnf for this test.
As an example the initial single mysql database is moved to a new folder : /bdd
Logs are sent to /var/log/mysql/mysql

#
#/etc/my.cnf
#
[client]
port       = 3306
socket     = /run/mysql/mysql.sock


[mysqld]
bind-address     = 127.0.0.1
secure_file_priv = /var/lib/mysql-files
server-id         = 1111
datadir                 = /bdd/mysql/mysql
port                  = 3307
socket                = /bdd/mysql/sock/mysqld.sock
pid-file              = /bdd/mysql/sock/mysqld.pid
log-error             = /var/log/mysql/mysql/mysqld_error.log
general_log           = 1
general_log_file      = /var/log/mysql/mysql/mysqld.log


[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysql/mysqld_multi/mysqld_multi.log

!includedir /etc/my.cnf.d

3rd step

Create prerequisite files and folders.

sudo mkdir  /bdd/mysql/{mysql,sock}
sudo chmod  755 /bdd/mysql/{mysql,sock}
sudo chown  mysql:mysql  /bdd/mysql/{mysql,sock}

sudo mkdir  /var/log/mysql/{mysql,mysqld_multi}
sudo chmod  755 /var/log/mysql/{mysql,mysqld_multi}
sudo touch  /var/log/mysql/mysql/mysqld.log
sudo touch  /var/log/mysql/mysql/mysqld_error.log
sudo touch  /var/log/mysql/mysqld_multi/mysqld_multi.log
sudo chmod  644  /var/log/mysql/mysql/*/mysqld*.log
sudo chown  mysql:mysql  /var/log/mysql/mysql/*/mysqld*.log

4th step

Create a systemd file because the helper given by opensuse does not work in this context
Systemd folder : /etc/systemd/system
Systemd file name : mariadb@.service
Systemd file contents :

[Unit]
#
#/etc/systemd/system/mariadb@.service
#
Description=MySQL Multi Server for instance %i
After=syslog.target
After=network.target
StartLimitBurst=5
StartLimitIntervalSec=33

[Service]
User=mysql
Group=mysql
Type=forking
PrivateTmp=true
ExecStart=/usr/bin/mysqld_multi start %i
ExecStop=/usr/bin/mysqld_multi stop %i
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

5th step

Create a configuration file for the database instance 2 and 3

example for instance 2

Config folder : /etc/my.cnf.d
Config file name : cfg_2.cnf
Config file contents :

#
#/etc/my.cnf.d/cfg_2.cnf
#
[mysqld2]
port=63132
datadir=/var/lib/mysql/2/
socket=/var/lib/mysql/2/mysql.sock
pid-file=/var/lib/mysql/2/mysqld2.pid
log-error=/var/log/mysql/mariadb2.log
symbolic-links=0

6th step

Create prerequisite files and folders

sudo mkdir  /var/lib/mysql/{2,3}
sudo chmod  755 /var/lib/mysql/{2,3}
sudo chown  mysql:mysql  /var/lib/mysql/{2,3}

sudo touch  /var/log/mysql/mariadb{2,3}.log
sudo chmod  644  /var/log/mysql/mariadb{2,3}.log
sudo chown  mysql:mysql  /var/log/mysql/mariadb{2,3}.log

7th step

Install initial database

sudo mysql_install_db  --user=mysql  datadir= /bdd/mysql/mysql/

Install database instance 2

sudo mysql_install_db  --user=mysql  --datadir=/var/lib/mysql/2/

Install database instance 3

sudo mysql_install_db  --user=mysql  --datadir=/var/lib/mysql/3/

8th step

Start databases

Start single database

systemctl start mariadb.service
systemctl -l --no-pager status mariadb.service

Start database instance 2

systemctl start mariadb@2.service
systemctl -l --no-pager status mariadb@2.service

Start database instance 3

systemctl start mariadb@3.service
systemctl -l --no-pager status mariadb@3.service

9th step

Verify databases

single database

mysql --socket=/bdd/mysql/sock/mysqld.sock  -BNe "show global variables" | grep -w 'datadir\|general_log\|general_log_file\|log_error\|log_output\|pid_file\|plugin_dir\|port\|secure_auth\|secure_file_priv\|secure_timestamp\|server_id\|socket'

database instance 2

mysql --socket=/var/lib/mysql/2/mysql.sock  -BNe "show global variables" | grep -w 'datadir\|general_log\|general_log_file\|log_error\|log_output\|pid_file\|plugin_dir\|port\|secure_auth\|secure_file_priv\|secure_timestamp\|server_id\|socket'

database instance 3

mysql --socket=/var/lib/mysql/3/mysql.sock  -BNe "show global variables" | grep -w 'datadir\|general_log\|general_log_file\|log_error\|log_output\|pid_file\|plugin_dir\|port\|secure_auth\|secure_file_priv\|secure_timestamp\|server_id\|socket'

10th step

Secure database install (only instance database)

database instance 2

mariadb-secure-installation  --socket=/var/lib/mysql/2/mysql.sock

database instance 3

mariadb-secure-installation  --socket=/var/lib/mysql/3/mysql.sock

Phew! It’s all over now.