How to install mysql/mariadb?

bavramor · August 11, 2019, 11:53pm

I installed mariadb via configuration.nix. For the installation I made the following entries in my configurastions.nix.

  services.mysql.enable = true;
  services.mysql.package = pkgs.mariadb;
  services.longview.mysqlPasswordFile = "/run/keys/mysql.password";

After I upgraded my system I get this error:

warning: The option definition services.mysql.rootPassword' in /etc/nixos/configuration.nix’ no longer has any effect; please remove it. Use socket authentication or set the password outside of the nix store.

Before I always set my password in the configuration.nix with:

services.mysql.rootPassword

I also does a simple “systemctl start mysql”. However I get the following error message under “systemctl status mysql”:

● mysql.service - MySQL Server
   Loaded: loaded (/nix/store/2aliiwch37wiis98q6nbplcwyqk987l5-unit-mysql.service/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: signal) since Mon 2019-08-12 00:45:56 CEST; 15min ago
  Process: 4784 ExecStartPre=/nix/store/bg6p3jcw23xmag07nzakkqh5hmy0h5yn-unit-script-mysql-pre-start (code=exited, status=0/SUCCESS)
  Process: 4785 ExecStart=/nix/store/r8s4kw7wskyn73ksb5nm9a5ykmyxk5ca-mariadb-10.3.15/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --datadir=/var/lib/mysql --basedir=/nix/store/r8s4kw7wskyn73ksb5nm9a5ykmyxk5ca-mariadb-10.3.15 $_WS>
  Process: 4820 ExecStartPost=/nix/store/hvfnmysk0hrgfp8pzr19r9sv0np88d1g-mysql-setup (code=exited, status=0/SUCCESS)
 Main PID: 4785 (code=killed, signal=ABRT)
   Status: "Taking your SQL requests now…"

With a mysql or mysql -u root -p I get the following error message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

Would be grateful for any help

aanderse · August 12, 2019, 12:37am

What did you upgrade from and to?

bavramor · August 12, 2019, 12:49am

From NixOS 18.09 to NixOS 19.03.

aanderse · August 12, 2019, 1:25am

Can you double check that? The services.mysql.rootPassword option is not marked as deprecated in 19.03 but it is in unstable. Care to share the output of nix-channel --list run as root?

Also from the looks of your mariadb version you appear to be on unstable with a channel that was updated 11 or more days ago.

More details from journald would be useful too.

bavramor · August 12, 2019, 10:59am

First of all, thank you for your help.

You were right. I had a bug in the channel list. But I corrected it now and did a “nixos-rebuild switch” afterwards.

My nix-channel –list now:

nixos https://nixos.org/channels/nixos-19.03
nixpkgs https://nixos.org/channels/nixpkgs-unstable

He doesn’t complain about the line anymore:

services.mysql.rootPassword = "/etc/mysql/mysql.password";

But I still can’t start mysql:

systemctl start mysql
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

systemctl status mysql.service

mysql.service - MySQL Server
Loaded: loaded (/nix/store/f9j4s0cckhvl3kp5xmm5z6c5z5i14z2q-unit-mysql.service/mysql.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Mon 2019-08-12 12:48:22 CEST; 2min 26s ago
Process: 5800 ExecStart=/nix/store/b5b9983d7nj93xbv5xs906axiyxqipgq-mariadb-10.2.17/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --datadir=/var/lib/mysql --basedir=/nix/store/b5b9983d7nj93xbv5xs906axiy>
Process: 5797 ExecStartPre=/nix/store/mrfi1vv9q59s1ai0239xl7wnn3lmx37i-unit-script-mysql-pre-start (code=exited, status=0/SUCCESS)
Main PID: 5800 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
Aug 12 12:48:21 torinshalle mysqld[5800]: 2019-08-12 12:48:21 140251327633472 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
Aug 12 12:48:22 torinshalle mysqld[5800]: 2019-08-12 12:48:22 140251327633472 [Note] InnoDB: Starting shutdown...
Aug 12 12:48:22 torinshalle mysqld[5800]: 2019-08-12 12:48:22 140251327633472 [ERROR] Plugin 'InnoDB' init function returned error.
Aug 12 12:48:22 torinshalle mysqld[5800]: 2019-08-12 12:48:22 140251327633472 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Aug 12 12:48:22 torinshalle mysqld[5800]: 2019-08-12 12:48:22 140251327633472 [Note] Plugin 'FEEDBACK' is disabled.
Aug 12 12:48:22 torinshalle mysqld[5800]: 2019-08-12 12:48:22 140251327633472 [ERROR] Unknown/unsupported storage engine: InnoDB
Aug 12 12:48:22 torinshalle mysqld[5800]: 2019-08-12 12:48:22 140251327633472 [ERROR] Aborting
Aug 12 12:48:22 torinshalle systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Aug 12 12:48:22 torinshalle systemd[1]: mysql.service: Failed with result 'exit-code'.
Aug 12 12:48:22 torinshalle systemd[1]: Failed to start MySQL Server

.

journalctl -xe

Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Compressed tables use zlib 1.2.11
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Using Linux native AIO
Aug 2 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Number of pools: 1
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Using generic crc32 instructions
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Completed initialization of buffer pool
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140404762371840 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setprio>
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Highest supported file format is Barracuda.
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [ERROR] InnoDB: Downgrade after a crash is not supported. The redo log was created with MariaDB 10.3.15.
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] InnoDB: Starting shutdown...
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [ERROR] Plugin 'InnoDB' init function returned error.
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [Note] Plugin 'FEEDBACK' is disabled.
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [ERROR] Unknown/unsupported storage engine: InnoDB
Aug 12 12:55:21 torinshalle mysqld[14969]: 2019-08-12 12:55:21 140411550525504 [ERROR] Aborting
Aug 12 12:55:21 torinshalle systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Aug 12 12:55:21 torinshalle systemd[1]: mysql.service: Failed with result 'exit-code'.
Aug 12 12:55:21 torinshalle systemd[1]: Failed to start MySQL Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- Unit mysql.service has failed.

-- The result is RESULT.

aanderse · August 12, 2019, 3:48pm

I could be wrong here but it sounds like you may have unintentionally upgraded mysql, it crashed, then you downgraded mysql and now you’re unable to bring it back up.

I think you need to modify services.mysql.package to be the version of mysql you accidentally upgraded to (which is 10.3.15, or higher might be fine too), start the database up and hope you have no errors, then potentially downgrade (if you wish to do so, if that is supported).

So to be explicit, if I were in your situation I would:

take a snapshot of my filesystem housing /var/lib/mysql
modify /etc/nixos/configuration such that: services.mysql.package = (import <nixpkgs>{}).mariadb;
run systemctl start mysql.service
cross my fingers and hope it boots
optionally switch back to stable mysql, if supported and desired, by changing back /etc/nixos/configuration.nix such that: services.mysql.package = pkgs.mariadb;

I can’t stress enough that backup part. Please let me know how that works out for you.

bavramor · August 13, 2019, 9:06pm

you might be right about that. But wouldn’t it be easier to remove all traces from mysql/mariadb? I have everything as a backup. And then do a clean installation of Mariadb?

I would first remove the entry from configuration.nix and then make a nixos-rebuild switch. Then I would manually remove everything that looks like mysql/mariadb?

aanderse · August 14, 2019, 12:53am

If you delete all stateful data (rm -rf /var/lib/mysql) and restart the service (systemctl start mysql.service) then you will have a “clean installation”. If this works for you it is certainly more straight forward

bavramor · August 19, 2019, 7:49pm

Hey, it’s working Thank you very much. cya

aanderse · August 19, 2019, 8:27pm

No problem. Glad to hear.

DataEraserC · October 18, 2023, 3:30pm

I use this to make up my development env (mysqld)

github.com

Program-Learning/nix-shell-dev_env/blob/361ca3343735b08b13dfe8fb139d6b5f5ded629c/mariadb.nix

# https://jeancharles.quillet.org/posts/2022-01-30-Local-mariadb-server-with-nix-shell.html
let pkgs = import <nixpkgs> {};
in pkgs.mkShell {
  buildInputs = [ pkgs.mariadb ];
  shellHook = ''
    MYSQL_BASEDIR=${pkgs.mariadb}
    MYSQL_HOME=$PWD/mysql
    MYSQL_DATADIR=$MYSQL_HOME/data
    export MYSQL_UNIX_PORT=$MYSQL_HOME/mysql.sock
    MYSQL_PID_FILE=$MYSQL_HOME/mysql.pid
    alias mysql='mysql -u root'

    if [ ! -d "$MYSQL_HOME" ]; then
      # Make sure to use normal authentication method otherwise we can only
      # connect with unix account. But users do not actually exists in nix.
      mysql_install_db --auth-root-authentication-method=normal \
        --datadir=$MYSQL_DATADIR --basedir=$MYSQL_BASEDIR \
        --pid-file=$MYSQL_PID_FILE
    fi

This file has been truncated. show original