Multiple instance configuration of MariaDB fails to start

This document (7023920) is provided subject to the disclaimer at the end of this document.

Environment

SUSE Linux Enterprise Server 15 (SLES 15)
SUSE Linux Enterprise Server 12 Service Pack 4 (SLES 12 SP4)

Situation

NOTE: There are two ways to run multiple instances of a MariaDB on the same server. You can use the mariadb@.service unit file to start independent instances, or you can use the mysqld_multi sections of the /etc/my.cnf file. This document applies to the former case only.

When attempting to configure multiple MariaDB instances using the mariadb@.service unit file, the instances fail to start. The same applies when a non-default MariaDB configuration file is used. A MariaDB instance configuration file was placed in /etc/my.cnf.d/mynode1.cnf.

$ sudo systemctl start mariadb@node1.service
Job for mariadb@node1.service failed because the control process exited with error code. See "systemctl status mariadb@node1.service" and "journalctl -xe" for details.

$ sudo journalctl -xe
$ sudo systemctl status mariadb@node1.service
...
Jun 06 15:08:35 sles12sp4 mysql-systemd-helper[2363]: Could not open required defaults file: /etc/mynode1.cnf
Jun 06 15:08:35 sles12sp4 mysql-systemd-helper[2363]: Fatal error in defaults handling. Program aborted

After moving /etc/my.cnf.d/mynode1.cnf to /etc/mynode1.cnf, the error log shows:

Jun 04 14:30:44 sles12sp4 mysql-systemd-helper[1179]: 2019-06-04 14:30:44 140137018382464 [Warning] Can't create test file /var/lib/mysql/node1/databases/sles12sp4.lower-test
Jun 04 14:30:44 sles12sp4 mysql-systemd-helper[1179]: 2019-06-04 14:30:44 140137018382464 [ERROR] Aborting
Jun 04 14:30:44 sles12sp4 systemd[1]: mariadb@node1.service: Main process exited, code=exited, status=1/FAILURE
Jun 04 14:30:44 sles12sp4 systemd[1]: Failed to start MySQL server - node1 instance.



$ cat /etc/mynode1.cnf
[client]
[mysqld]
port       = 3310
datadir    = /var/lib/mysql/node1/databases
pid-file   = /var/lib/mysql/node1/databases/mysqld.pid
socket     = /run/mysql/node1mysql.sock
bind-address    = 127.0.0.1
log-error       = /var/log/mysql/node1/mysqld.log
secure_file_priv = /var/lib/mysql-files
innodb_file_format=Barracuda
innodb_file_per_table=ON
server-id    = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
!includedir /etc/my.cnf.d

$ sudo ls -l /var/lib | grep mysql
drwxr-x--- 1 mysql   mysql     0 Jun  4 00:55 mysql-files
drwx------ 1 mysql   root    242 Jun  6 15:08 mysql-node1

If the default single instance of MariaDB was started first, the /var/lib/mysql directory was created with the resulting errors.

$ sudo ls -l /var/lib | grep mysql
drwxr-xr-x 1 mysql   root    346 Jun  6 15:32 mysql
drwxr-x--- 1 mysql   mysql     0 Jun  4 00:55 mysql-files
drwx------ 1 mysql   root    242 Jun  6 15:08 mysql-node1

[Warning] Can't create test file /var/lib/mysql/node1/databases/sles12sp4.lower-test
[ERROR] Aborting

If you first create /var/lib/mysql/node1/databases directory and attempt to start the instance, you get system log errors.

[ERROR] mysqld: Can't create/write to file '/var/lib/mysql/node1/databases/aria_log_control' (Errcode: 13 "Permission denied")
[ERROR] mysqld: Got error 'Can't create file' when trying to use aria control file '/var/lib/mysql/node1/databases/aria_log_control'
[ERROR] InnoDB: Operating system error number 13 in a file operation.
[ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
[ERROR] InnoDB: Cannot open datafile './ibdata1'
[ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_da
[ERROR] InnoDB: Database creation was aborted with error Cannot open a file. You may need to delete the ibdata1 file before trying to start up again.
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

Resolution

Engineering is aware of the issue and a PTF is available from support.

Configuration

MariaDB on SUSE only looks for instance configuration files in /etc, not /etc/my.cnf.d, so make sure your configuration files are saved in /etc. The resolution is based on the following configuration file examples.

Values included in /etc/mynode1.cnf

[mysqld]
port       = 3310
datadir    = /var/lib/mysql/node1/databases
pid-file   = /var/lib/mysql/node1/databases/mysqld.pid
socket     = /run/mysql/node1mysql.sock
log-error       = /var/log/mysql/node1/mysqld.log
secure_file_priv = /var/lib/mysql-files

Values included in /etc/mynode2.cnf

[mysqld]
port       = 3315
datadir    = /var/lib/mysql/node2/databases
pid-file   = /var/lib/mysql/node2/databases/mysqld.pid
socket     = /run/mysql/node2mysql.sock
log-error       = /var/log/mysql/node2/mysqld.log
secure_file_priv = /var/lib/mysql-files

Steps to Resolve

1. Install the updates when/if available in the update channel or apply the PTF received from support.

2. Stop all MariaDB instances:

$ sudo systemctl stop mariadb@node1.service
$ sudo systemctl stop mariadb@node2.service

3. Remove old datadirs:

$ sudo rm -rf /var/lib/mysql-node1 /var/lib/mysql/node1 /var/lib/mysql-node2 /var/lib/mysql/node2

4. Create the directories for the instances with the correct permissions:

$ sudo mkdir -p /var/lib/mysql/node1
$ sudo mkdir -p /var/lib/mysql/node2
$ sudo chown mysql:root /var/lib/mysql/node1
$ sudo chown mysql:root /var/lib/mysql/node2

5. Start the MariaDB instances:

$ sudo systemctl start mariadb@node1.service
$ sudo systemctl start mariadb@node2.service

6. Check the running services

$ sudo ss -nlp | grep mysql
u_str  LISTEN 0 80 /run/mysql/node2mysql.sock 150930  * 0       users:(("mysqld",pid=7356,fd=20))
u_str  LISTEN 0 80 /run/mysql/node1mysql.sock 150771  * 0       users:(("mysqld",pid=7211,fd=20))
tcp    LISTEN 0 80 127.0.0.1:3310                     0.0.0.0:* users:(("mysqld",pid=7211,fd=19))
tcp    LISTEN 0 80 127.0.0.1:3315                     0.0.0.0:* users:(("mysqld",pid=7356,fd=19))
$ sudo systemctl status mariadb@node1.service
$ sudo systemctl status mariadb@node2.service

Cause

SUSE only uses the /etc directory for MariaDB instance configuration files. The mysql-systemd-helper is not processing the instance configuration files correctly. A PTF is available.

Additional Information

In general, the directories in the path to datadir should have 755 root:root while the datadir itself is created as 700 mysql:root. For example,

datadir = /var/lib/mysql/node1/databases

then

/var/lib/mysql/node1 directories will have 755 root:root, and
/var/lib/mysql/node1/databases directory will have 700 mysql:root

Disclaimer

This Support Knowledgebase provides a valuable tool for SUSE customers and parties interested in our products and solutions to acquire information, ideas and learn from one another. Materials are provided for informational, personal or non-commercial use within your organization and are presented "AS IS" WITHOUT WARRANTY OF ANY KIND.