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 (
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:
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.
- 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.sockand the above SQL commands.
Oh and if your want to kill mysql safely use
./mariadb/bin/mysqladmin -S /werther/mysqld.sock -uroot -p shutdown
Ctrl + \ to kill it in your console,
Ctrl + C won't work by design.