Fixing MariaDB self-lockout for root user

Help! (Home for newbies)
1
SYSTEM INFORMATION
OS type and version Rocky Linux 9.4
Virtualmin version 7.20.1
MySQL version 10.5.22-MariaDB

I was trying to get SSL working for maria db and changed the options to require X509 for SSL login for my root user. After enabling this I can no longer access the module in webmin to change it back and it errors with access denied for user and I cannot access mariadb.

image
image1576×242 34.4 KB

× mariadb.service - MariaDB 10.5 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: disabled)
     Active: failed (Result: exit-code) since Wed 2024-07-17 19:24:29 BST; 1 day 21h ago
   Duration: 7min 11.302s
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 154763 ExecStartPre=/usr/libexec/mariadb-check-socket (code=exited, status=0/SUCCESS)
    Process: 154791 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
    Process: 154838 ExecStart=/usr/libexec/mariadbd --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER (code=exited, status=1/FAILURE)
   Main PID: 154838 (code=exited, status=1/FAILURE)
     Status: "MariaDB server is down"
        CPU: 232ms

Jul 17 19:23:58 mail.imhostuk.com systemd[1]: Starting MariaDB 10.5 database server...
Jul 17 19:23:59 mail.imhostuk.com mariadb-prepare-db-dir[154791]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Jul 17 19:23:59 mail.imhostuk.com mariadb-prepare-db-dir[154791]: If this is not the case, make sure the /var/lib/mysql is empty before running mariadb-prepare-db-dir.
Jul 17 19:24:29 mail.imhostuk.com systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jul 17 19:24:29 mail.imhostuk.com systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jul 17 19:24:29 mail.imhostuk.com systemd[1]: Failed to start MariaDB 10.5 database server.
~


Jul 17 19:17:22 mail.imhostuk.com systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://wiki.rockylinux.org/rocky/support
░░ 
░░ An ExecStart= process belonging to unit mariadb.service has exited.
░░ 
░░ The process' exit code is 'exited' and its exit status is 1.
Jul 17 19:17:22 mail.imhostuk.com systemd[1]: mariadb.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://wiki.rockylinux.org/rocky/support
░░ 
░░ The unit mariadb.service has entered the 'failed' state with result 'exit-code'.
Jul 17 19:17:22 mail.imhostuk.com systemd[1]: Failed to start MariaDB 10.5 database server.
░░ Subject: A start job for unit mariadb.service has failed
░░ Defined-By: systemd
░░ Support: https://wiki.rockylinux.org/rocky/support
░░ 
░░ A start job for unit mariadb.service has finished with a failure.
░░ 
░░ The job identifier is 175673 and the job result is failed.
Jul 17 19:23:58 mail.imhostuk.com systemd[1]: Starting MariaDB 10.5 database server...
░░ Subject: A start job for unit mariadb.service has begun execution
░░ Defined-By: systemd
░░ Support: https://wiki.rockylinux.org/rocky/support
░░ 
░░ A start job for unit mariadb.service has begun execution.
░░ 
░░ The job identifier is 175930.
Jul 17 19:23:59 mail.imhostuk.com mariadb-prepare-db-dir[154791]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Jul 17 19:23:59 mail.imhostuk.com mariadb-prepare-db-dir[154791]: If this is not the case, make sure the /var/lib/mysql is empty before running mariadb-prepare-db-dir.
Jul 17 19:24:29 mail.imhostuk.com systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://wiki.rockylinux.org/rocky/support
░░ 
░░ An ExecStart= process belonging to unit mariadb.service has exited.
░░ 
░░ The process' exit code is 'exited' and its exit status is 1.
Jul 17 19:24:29 mail.imhostuk.com systemd[1]: mariadb.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://wiki.rockylinux.org/rocky/support
░░ 
░░ The unit mariadb.service has entered the 'failed' state with result 'exit-code'.
Jul 17 19:24:29 mail.imhostuk.com systemd[1]: Failed to start MariaDB 10.5 database server.
░░ Subject: A start job for unit mariadb.service has failed
░░ Defined-By: systemd
░░ Support: https://wiki.rockylinux.org/rocky/support
░░ 
░░ A start job for unit mariadb.service has finished with a failure.
░░ 
░░ The job identifier is 175930 and the job result is failed.

If you need anything else please let me know.

Thanks for looking.

2

You need to repair the damage done to the mariadb config files, if you have execkeeper installed you could perhaps rollback the changes with that, or if you made a backup of the config files restore them back else it will be a case of going through the config files to find the error

3

Can I start fresh with mariadb? I have nothing important in the db’s that are there already.

4

That’s a crazy first instinct.

You aren’t getting “access denied”. Mariadb simply isn’t starting, which probably means an invalid config file, since you were modifying the config file. You can probably just removed the lines you added and start it again.

So, check /var/log/mariadb.log for any additional clues, and if it’s not apparent there why it’s failing, start it in safe mode: mariadbd-safe - MariaDB Knowledge Base

You need to see why it isn’t starting. Starting it interactively in safe mode will probably tell you. Presumably it’s bogus configuration.

1 Like
5

and – carefully review your general setup – could it be possible that you don’t even need SSL for your database connection?

that is, is the database and the parts using the database (apache/php?) on the same server?

6

I fixed it. The error in the log I posted in my original post was due to mariadb process already running and mariadb could not grant a lock to the file. Killing the process for mariadb and restarting the service fixed the original error I posted here.

In webmin I had edited the root mysql user to require X509 trying to get ssl working as it was reporting in phpmyadin that ssl was not running for mysql and this caused the initial lock out which gave me access denied for user errors. To remove the requirement of X509 for the root user to login to mysql I took the below steps.

I ran as root:

mysqld_safe --skip-grant-tables --skip-networking &

then in another terminal I connected to mysql by running:

mysql -u root

Then I removed requirement of X509 for the root user:

ALTER USER 'root'@'localhost' REQUIRE NONE;

Then flushed priveleges:

flush privileges;

rebooted the server and everything is back up and running.

Thanks for the pointers and safemode suggestion @Joe it pushed me in the right direction to figure this out. I’ve been at this every day for about 2 days now, such a relief now it is fixed.

Thank you.

2 Likes
7

I would also now check you have valid backups of your sites and server.

1 Like
8

Yeah I am working on that as it would have saved so much hassle caused here. Will the webmin filesystem backup module be sufficient or would you recommend something different?

9

I have not done my backup section but this is what I think is the case

Webmin backup = only the /etc folder which hold all of the config files
Virtualmin backup = virtual server backups and the virtualmin config if selected

you definitely need both. I would preferred a unified backup here so I have the option of doing a “full” backup from virtualmin.

1 Like
10

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.