Skip to content

Random stuff

Wrangling mariadb/mysql to run in a custom setup

So you want to run mysql/mariadb on linux in an unprivileged user, with a custom path (instead of /tmp, /var/run..) and maybe a custom port? (And no docker!)

Then welcome to a world of pain.

First let's create a new user, we'll call him werther. Then we download the tar.gz from mariadb.org and unpack it. Now what we expect and hope to do is something like the following layout:

/werther/
  - mariadb # binary 'n stuff
    - bin
    - scripts
    - ..
  - data # actual database things

We'll create a nice my.cnf from the internet, because mysql/mariadb doesn't actually give you an example or generator for the default config. This one here is the final one and already spoils some of the things that I'll talk about now.

If you don't want all the pain then scroll down for the solution. Other people might find this entry through all the errors in here and hopefully use that as a solution.

werther@box# cat my.cnf
[client]
port    = 3307
socket  = /werther/mysqld.sock

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket  = /werther/mysqld.sock
nice    = 0
syslog

[mariadb]
#disable_unix_socket

[mysqld]
# Basic Settings
user      = werther
pid-file  = /werther/mysqld.pid
socket    = /werther/mysqld.sock
port      = 3307
basedir   = /werther/mariadb
datadir   = /werther/data
tmpdir    = /werther/tmp

#skip-grant-tables
#skip-networking

lc-messages-dir         = /werther
skip-external-locking

# Engine
default-storage-engine  = InnoDB

# Listening IP
bind-address            = 0.0.0.0

# Safety
max-connect-errors      = 1000000
max_allowed_packet      = 64M
skip-name-resolve
sysdate-is-now          = 1
innodb                  = FORCE
innodb-strict-mode      = 1

wait_timeout            = 60
interactive_timeout     = 60

# Buffers
sort_buffer_size        = 4M
read_buffer_size        = 2M
join_buffer_size        = 8M
read_rnd_buffer_size    = 16M

First let's try to just start this thing:

werther@box# ./mariadb/bin/mysqld --defaults-file=my.cnf
[ERROR] Could not open mysql.plugin table: "Table 'mysql.plugin' doesn't exist". Some plugins may be not loaded
[ERROR] mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 13 "Permission denied")
[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.db' doesn't exist

That didn't work so well, well maybe we can try out the mariadb-install-db thingy.

werther@box# ./mariadb/bin/scripts/mariadb-install-db

FATAL ERROR: Could not find mariadbd

The following directories were searched:

    /usr/bin
    /usr/libexec
    /usr/sbin
    /usr/bin

If you compiled from source, you need to either run 'make install' to
copy the software into the correct location ready for operation.
If you don't want to do a full install, you can use the --srcdir
option to only install the mysql database and privilege tables.

If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.

Ok ok, so you want something to work with.

werther@box:~/mariadb# ./scripts/mariadb-install-db --basedir=.
chown: changing ownership of '/var/lib/mysql': Operation not permitted

No don't chown, just run as the user you are. It's not like you need root to start on the default port of 3306.

werther@box:~/mariadb# ./scripts/mariadb-install-db --basedir=. --user=werther --data=/werther
chown: changing ownership of './lib/plugin/auth_pam_tool_dir/auth_pam_tool': Operation not permitted
Couldn't set an owner to './lib/plugin/auth_pam_tool_dir/auth_pam_tool'.
It must be root, the PAM authentication plugin doesn't work otherwise..

Installing MariaDB/MySQL system tables in '/werther' ...
2021-11-23 21:30:53 0 [Note] Using unique option prefix 'key_buffer' is error-prone and can break in the future. Please use the full name 'key_buffer_size' instead.
2021-11-23 21:30:53 0 [Warning] Ignoring user change to 'werther' because the user was set to 'mysql' earlier on the command line

./bin/mariadbd: Can't create file '/var/log/mysql/error.log' (errno: 13 "Permission denied")
2021-11-23 21:30:53 0 [ERROR] mariadbd: Can't open shared library '/werther/mariadb-10.6.5-linux-systemd-x86_64/lib/plugin/auth_socket.so' (errno: 0, cannot open shared object file: No such file or directory)
2021-11-23 21:30:53 0 [ERROR] Couldn't load plugins from 'auth_socket.so'.
2021-11-23 21:30:54 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2021-11-23 21:30:54 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

Yeah ok, so it does start, but it will try to do all sorts of stuff like searching for a auth_socket.so lib that actually does not exist inside the plugin folder.

Let's add more parameters, more is always better. Maybe we can skip the socket stuff and use a better data dir, while avoiding any /var/log stuff.

werther@box:~/mariadb# ./scripts/mariadb-install-db --basedir=. --user=werther --datadir=/werther/data --defaults-file=/werther/my.cnf --auth-root-authentication-method=normal --skip-name-resolve
all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is werther@localhost, it has no password either, but
you need to be the system 'werther' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

Ok nice, there is root@localhost and a werther@localhost user? Let's connect.

werther@box# mysql --protocol=tcp werther@localhost:3307
ERROR 1045 (28000): Access denied for user 'werther'@'localhost' (using password: NO)
werther@box# mysql -p 3307 --protocol=tcp werther@localhost
ERROR 1045 (28000): Access denied for user 'werther'@'localhost' (using password: NO)

Same with a password (-p).

Of course this doesn't work because reasons. Let's google a bit and retry, uncomment disable_unix_socket as tcp doesn't seem to work without a password (we don't have) and see.

werther@box:~/mariadb# ./mariadb/bin/mysqld --defaults-file=/werther/my.cnf --basedir=. --user=werther --datadir=/werther/data
[..]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following command:

'./bin/mysql_secure_installation'
[..]
FATAL ERROR: Could not find /usr/local/mysql/bin/my_print_defaults
[..]

Yeah ok it makes sense and I'd really just like to add a new user I can connect with and be done.

Maybe it needs our config stuff ?

werther@box# mysql_secure_installation --defaults-file=/werther/my.cnf --basedir=. --user=werther --datadir=/werther/data
ERROR 1049 (42000): Unknown database '/werther/data'

Not all the config stuff, --datadir=/werther/data is apparently too much.

werther@box# ./mysql_secure_installation --basedir=/werther
print: ./bin/my_print_defaults

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
Info: Using unique option prefix 'data' is error-prone and can break in the future. Please use the full name 'database' instead.
Info: Using unique option prefix 'data' is error-prone and can break in the future. Please use the full name 'database' instead.
ERROR 1045 (28000): Access denied for user 'werther'@'localhost' (using password: YES)
Enter current password for root (enter for none): 
Aborting!

Somewhere here I fucked up killing the mariadb process with screen and had to re-run mariadb-install-db after a nice rm -rf /werther/data/*:

2021-11-23 22:40:55 0 [Note] InnoDB: Dump from the start of the mini-transaction (LSN=43068) to 100 bytes after the record:
 len 101; hex ffffffff000034000631000039002d32ffffffff0000b700ffffffff000034002d31000039002e32ffffffff0000b700ffffffff000034002e31000039002f32ffffffff0000b700ffffffff000034002f31000039003032ffffffff0000b700ffffffff00; asc       4  1  9 -2              4 -1  9 .2              4 .1  9 /2              4 /1  9 02             ;
2021-11-23 22:40:55 0 [Warning] InnoDB: Log scan aborted at LSN 43008
2021-11-23 22:40:55 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2021-11-23 22:40:55 0 [Note] InnoDB: Starting shutdown...
2021-11-23 22:40:55 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-11-23 22:40:55 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-11-23 22:40:55 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-11-23 22:40:55 0 [ERROR] Failed to initialize plugins.
2021-11-23 22:40:55 0 [ERROR] Aborting

Anyway apparently the secure_installation thingy isn't happy, because it also can't authenticate. Something something PAM not chowning as it'd like to do.

Google a bit and try https://stackoverflow.com/a/57766252/3332686 We'll make it a little bit easier by uncommenting these lines in my.cnf instead of using the cli options:

#skip-grant-tables
#skip-networking

Also if you try to use the --skip-grant-tables --skip-networking flags, beware that your other params (defaults-file) have to come first. Otherwise mysqld will suddenly tell you

[ERROR] ./bin/mysqld: unknown variable 'defaults-file=/werther/my.cnf'

And we obviously can't live without the my.cnf flags, or it'll try to use its own paths again.

So we got it running in non-networking and non-grant mode. Let's try to change the permissions. (Note the socket usage, as networking is off and logging in via tcp wasn't working in the first place.)

werther@box# mysql -uroot --socket=/werther/mysqld.sock
>> CREATE USER 'admin'@'%' IDENTIFIED BY 'ASDF'
    -> ;
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

Yeah that won't work, maybe make the account stuff less obvious ?

>> UPDATE mysql.user SET authentication_string=PASSWORD('ASDF'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
ERROR 1348 (HY000): Column 'authentication_string' is not updatable

Nevermind, flush first and then use the new password command ?

>> use mysql;
>> FLUSH PRIVILEGES;
>> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('ASDF');
>> CREATE USER 'admin'@'%' IDENTIFIED BY 'ASDF';
>> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;;
>> FLUSH PRIVILEGES;

Yep, that's the solution. There you've got local root login and a global admin account. This was the short version of me trying to setup mariadb without the typical apt install.

Summary

  • Create your my.cnf as above.
  • install the server via ./scripts/mariadb-install-db --basedir=. --user=werther --datadir=/werther/data --defaults-file=/werther/my.cnf --auth-root-authentication-method=normal --skip-name-resolve
  • Then edit my.cnf to contain the skip-grant-tables and skip-networking options. ./bin/mysqld --defaults-file=/werther/my.cnf --basedir=. --user=werther --datadir=/werther/data. (Run this with a screen, it'll block your terminal.)
  • Then run mysql -uroot --socket=/werther/mysqld.sock and the above SQL commands.

Oh and if your want to kill mysql safely use

./mariadb/bin/mysqladmin -S /werther/mysqld.sock -uroot -p shutdown

Otherwise Ctrl + \ to kill it in your console, Ctrl + C won't work by design.