The officially official Devuan Forum!

tlathm

Member

Registered: 2017-11-25

Posts: 103  

MariaDB server debian.cnf file with Beowulf

I've been installing LAMP stuff on a beowulf server (upgraded from ascii) and just ran into something that took me forever to untangle that didn't seem right:

I installed default-mysql-server which installed the mariadb client and server 10.3. After getting it installed I set the root@localhost password. After doing that it refused to stop or start correctly. What I finally discovered was that I needed to expressly add the root password to the password entries in /etc/mysql/debian.cnf. I just checked, and under Devuan jessie with mysql installed instead of mariadb, the /etc/mysql/debian.cnf file ended up automatically configured with a user named debian-sys-maint and a generated password...which was why it worked fine in that case.

Does anyone have any idea how that's supposed to work? That certainly didn't seem right. Thanks in advance!

EDIT: Related question: The root@localhost user was originally created with a "plugin" entry of "mysql_native_password". Based on severl things I read I had ended up changing that to an empty string, though the above stuff was the issue and it had nothing to do with that. The user appears to work either way. Does anyone know how that should actually be set?

Tom

Dutch_Master

Member

Registered: 2018-05-31

Posts: 286  

Re: MariaDB server debian.cnf file with Beowulf

When I installed db-servers, the root-pw question came up as part of the installer. Apparently it didn't with yours. Perhaps a purge and re-install? I assume it's not a production machine (yet).

tlathm

Member

Registered: 2017-11-25

Posts: 103  

Re: MariaDB server debian.cnf file with Beowulf

I installed default-mysql-server which installed these:

default-mysql-server galera-3 gawk libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mysql-perl
libdbi-perl libfcgi-perl libhtml-template-perl libmpfr6 libsigsegv2 libsnappy1v5 libterm-readkey-perl
mariadb-client-10.3 mariadb-client-core-10.3 mariadb-server-10.3 mariadb-server-core-10.3 socat

When you refer to installing "db-servers" are you referring to, in this case for example, mariadb-server-10.3? I definitely didn't get any prompts or errors of any kind.

When you get the root password prompt, did you end up with a debian-sys-maint user and password in /etc/mysql/debian.cnf? Mine ended up like this:

cat /etc/mysql/debian.cnf 
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = root
password = 
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = 
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Adding the root password there gets everything working, though I'm unclear as to what was supposed to have happened. I'm also unclear as to whether that's any less secure than the setup with the debian-sys-maint user. As far as a purge and a re-install, there's certainly no reason to think it's going to behave any differently then it did yesterday.

Tom

tlathm

Member

Registered: 2017-11-25

Posts: 103  

Re: MariaDB server debian.cnf file with Beowulf

Ahhh! Got it...well sort of. This is apparently different with MariaDB. I just found this in the file /usr/share/doc/mariadb-server-10.3/README.Debian.gz:

* ROOT USER AUTHENTICATION VIA UNIX SOCKET
==========================================
On new installs no root password is set and no debian-sys-maint user is
created anymore. Instead the MariaDB root account is set to be authenticated
using the unix socket, e.g. any mysqld invocation by root or via sudo will
let the user see the mysqld prompt.

You may never ever delete the mysql user "root". Although it has no password
is set, the unix_auth plugin ensure that it can only be run locally as the root
user.

The credentials in /etc/mysql/debian.cnf specify the user which is used by the
init scripts to stop the server and perform logrotation. This used to be the
debian-sys-maint user which is no longer used as root can run directly.

If you have start/stop problems make sure that the /etc/mysql/debian.cnf file
specifies the root user and no password.

In our case however we actually need to have a password on the root user. Given that, I think what I have is correct...though that's a little confusing.

Tom

tlathm

Member

Registered: 2017-11-25

Posts: 103  

Re: MariaDB server debian.cnf file with Beowulf

Wow. It seems like Debian has made this whole handling of root in mariadb 10.3 astonishingly cryptic. I was just reading this, which mentions the program mysql_secure_installation, which I knew nothing about:

https://www.digitalocean.com/community/ … -debian-10

Several things concern/confuse me about this:

They seem to push you towards having the root account use the unix_socket plugin authentication with no password. My initital installation ended up with the root user set for mysql_native_password and no password.

They also aren't clear at all as to what to do in cases where you actually want a password on the root account as apposed to relying on the unix_socket login. What I did...adding the root password to debian.cnf...is the only way I could find. While the debian.cnf file says "# Automatically generated for Debian scripts. DO NOT TOUCH!", I'm actually not sure that really applies with mariadb 10.3 since they've abandoned the use of the debian-sys-maint user and all that.

They also seem to imply that the changes I made to either the root user or the debian.cnf (adding the root password) file might get lost in a future update(?).

I'm not sure it could get more confusing.

EDIT: As a test I tried a fresh install of mariadb server, and then used that mysql_secure_installation to set the root password. That however set the root user's plugin to unix_socket and still allowed it to login without a password locally. It also left that debian.cnf as-is with the root user and no password. So for cases where you actually want the root user to require a password, the only option is to change (or set to '') that plugin for the root user, and that requires putting the root password in debian.cnf or the service cannot stop or start.

Tom

Last edited by tlathm (2020-01-04 16:10:08)