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 formerly 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, uncommenting 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 --data=/werther/data
ERROR 1049 (42000): Unknown database '/werther/data'

Not all the config stuff, --data=/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.

Positive experiences with linux on X1 Carbon Gen1

I recently had the chance of introducing linux to the laptop of a friend. Despite some discussions I've had recently on lobsters about the ugly sides of linux on the desktop, it turned out pretty well.

To preface this: this specific laptop had a bad case of broken windows updates with issues giving you "no admin" errors, except if you run each command in an admin console. And it was RMA'ed 4 times because of display flickering on anything other than 100% display brightness, which would send someone with epilepsy straight to the hospital.

Setup

If you want to know how we got it working, here is the description: We installed KUbuntu 21.04 on a Lenovo X1 Carbon 1st Gen (2015) and fixed some stuff:

"Back-"light

What won't work out of the box is display brightness controls and if you dig further you'll find that it's not possible to do so via the kernel internal systems, no matter what kind of startup params you throw at GRUB_CMDLINE_LINUX_DEFAULT inside /etc/default/grub. (We tried acpi_backlight=native, video.brightness_switch_enabled=0 and acpi_backlight=vendor.) The values in /sys/class/backlight/intel_backlight/brightness are simply ignored by the hardware.

We also looked into the arch wiki entries for the X1 Carbon, but they won't even mention any problems with the backlight. Another problem is that many of these docs are now based around old X11 tools and will break with wayland or when wayland compatibility doesn't work anymore (the way it currently does).

What does work though is setting the brightness in xrandr like xrandr --output eDP1 --brightness 0.3. As it turns out, there is no backlight for OLEDs, so what you do is mirror the values inside /sys/class/backlight/intel_backlight/brightness to xrandr. We just went with lawleagle/oled-linux, adding it to the autostart. (There are a ton of different scripts that do the same, you can even get a rust version if you like.) With that setup you'll have no minimum brightness value though, so setting a brightness of 0 is actually black, in contrast to what you can do in windows or non-OLED displays.

Results

The whole laptop is now operating on much lower temperatures, never starting to vent, even after playing a lot of videos. You can use the wacom based touch display out of the box. The pen detection also works and you can use the touch input even when you disabled the display, allowing you to draw on it while looking at an external display. So it seems like that despite my fears this is now the new OS for my friend and their first linux on a desktop (with 0 IT background).

The display flickering from windows is gone, so it may have always been a driver issue. Some KDE animations flicker (only specific windows, never the full screen), so we simply turned them off.

What we didn't get to work was the fingerprint sensor, apparently support for that is currently blocked by SDDM and thus the KDE desktop. Oh and grub is very laggy on a 2k screen, not sure if there is a fix for that.

Of course windows later greeted us with a nice "BitLocker recovery" screen, as I wasn't aware of that and we simply made one partition of ~300GB free space for linux, instead of directly wiping windows from the disk.