Why does thesql_secure_installation fail to permit me (as MySQL root user) to enter no password

fedoraMySQL

I'm a new Fedora user…. after using Ubuntu for more than a decade, their Eric6 implementation is, per Eric6 Support, a failure. Fedora is supposed to have it right. So, I'm testing Fedora.

I'm running Fedora 36 and have installed MySQL per the yum instructions (https://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html). When I get to the part of running mysql_secure_installation (step 6), the first thing it asks is for a root password… which I have not set yet.

If I press enter, it stops and complains access denied: using password no. If I try to use my standard password (which, again, I have not set yet), it aborts with access denied: using password yes.

So, I go through the process of stopping the server, executing mysql without networking, so I can sneak behind and set a MySQL root password using the ALter USER statement.

Having apparent success in doing this, I continue to start MySQL server again (service mysqld start) and receive this:

Redirecting to /bin/systemctl start mysqld.service Job for
mysqld.service failed because the control process exited with error
code.

See "systemctl status mysqld.service" and "journalctl -xeu
mysqld.service" for details.

Following these directions, I get (from systemctl status mysqld.service)

× mysqld.service – MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Mon 2022-08-29 01:26:45 CDT; 3min 3s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 4521 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Process: 4558 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE) Main PID: 4558 (code=exited,
status=1/FAILURE)
Status: "Server shutdown complete"
Error: 13 (Permission denied)
CPU: 3.379s

Aug 29 01:26:41 kamz-workstation systemd[1]: Starting mysqld.service –

MySQL Server… Aug 29 01:26:45 kamz-workstation systemd[1]:
mysqld.service: Main process exited, code=exited, status=1/FAILURE Aug
29 01:26:45 kamz-workstation systemd[1]: mysqld.service: Failed with
result 'exit-code'. Aug 29 01:26:45 kamz-workstation systemd[1]:
Failed to start mysqld.service – MySQL Server. Aug 29 01:26:45
kamz-workstation systemd[1]: mysqld.service: Consumed 3.379s CPU time.

and (from journalctl -xeu mysqld.service), I receive:

Aug 29 01:08:35 kamz-workstation systemd[1]: Starting mysqld.service –
MySQL Server… ░░ Subject: A start job for unit mysqld.service has
begun execution ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has begun execution. ░░ ░░ The job
identifier is 313. Aug 29 01:08:44 kamz-workstation systemd[1]:
mysqld.service: Main process exited, code=exited, status=1/FAILURE ░░
Subject: Unit process exited ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ An
ExecStart= process belonging to unit mysqld.service has exited. ░░ ░░
The process' exit code is 'exited' and its exit status is 1. Aug 29
01:08:44 kamz-workstation systemd[1]: mysqld.service: Failed with
result 'exit-code'. ░░ Subject: Unit failed ░░ Defined-By: systemd ░░
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ ░░ The unit mysqld.service has entered the 'failed' state with
result 'exit-code'. Aug 29 01:08:44 kamz-workstation systemd[1]:
Failed to start mysqld.service – MySQL Server. ░░ Subject: A start job
for unit mysqld.service has failed ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has finished with a failure. ░░ ░░
The job identifier is 313 and the job result is failed. Aug 29
01:08:44 kamz-workstation systemd[1]: mysqld.service: Consumed 4.192s
CPU time. ░░ Subject: Resources consumed by unit runtime ░░
Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░
The unit mysqld.service completed and consumed the indicated
resources. Aug 29 01:12:13 kamz-workstation systemd[1]: Starting
mysqld.service – MySQL Server… ░░ Subject: A start job for unit
mysqld.service has begun execution ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has begun execution. ░░ ░░ The job
identifier is 3201. Aug 29 01:12:16 kamz-workstation systemd[1]:
mysqld.service: Main process exited, code=exited, status=1/FAILURE ░░
Subject: Unit process exited ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ An
ExecStart= process belonging to unit mysqld.service has exited. ░░ ░░
The process' exit code is 'exited' and its exit status is 1. Aug 29
01:12:16 kamz-workstation systemd[1]: mysqld.service: Failed with
result 'exit-code'. ░░ Subject: Unit failed ░░ Defined-By: systemd ░░
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ ░░ The unit mysqld.service has entered the 'failed' state with
result 'exit-code'. Aug 29 01:12:16 kamz-workstation systemd[1]:
Failed to start mysqld.service – MySQL Server. ░░ Subject: A start job
for unit mysqld.service has failed ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has finished with a failure. ░░ ░░
The job identifier is 3201 and the job result is failed. Aug 29
01:12:16 kamz-workstation systemd[1]: mysqld.service: Consumed 3.339s
CPU time. ░░ Subject: Resources consumed by unit runtime ░░
Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░
The unit mysqld.service completed and consumed the indicated
resources. Aug 29 01:20:38 kamz-workstation systemd[1]: Starting
mysqld.service – MySQL Server… ░░ Subject: A start job for unit
mysqld.service has begun execution ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has begun execution. ░░ ░░ The job
identifier is 3420. Aug 29 01:20:42 kamz-workstation systemd[1]:
mysqld.service: Main process exited, code=exited, status=1/FAILURE ░░
Subject: Unit process exited ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ An
ExecStart= process belonging to unit mysqld.service has exited. ░░ ░░
The process' exit code is 'exited' and its exit status is 1. Aug 29
01:20:42 kamz-workstation systemd[1]: mysqld.service: Failed with
result 'exit-code'. ░░ Subject: Unit failed ░░ Defined-By: systemd ░░
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ ░░ The unit mysqld.service has entered the 'failed' state with
result 'exit-code'. Aug 29 01:20:42 kamz-workstation systemd[1]:
Failed to start mysqld.service – MySQL Server. ░░ Subject: A start job
for unit mysqld.service has failed ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has finished with a failure. ░░ ░░
The job identifier is 3420 and the job result is failed. Aug 29
01:20:42 kamz-workstation systemd[1]: mysqld.service: Consumed 3.977s
CPU time. ░░ Subject: Resources consumed by unit runtime ░░
Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░
The unit mysqld.service completed and consumed the indicated
resources. Aug 29 01:26:41 kamz-workstation systemd[1]: Starting
mysqld.service – MySQL Server… ░░ Subject: A start job for unit
mysqld.service has begun execution ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has begun execution. ░░ ░░ The job
identifier is 3644. Aug 29 01:26:45 kamz-workstation systemd[1]:
mysqld.service: Main process exited, code=exited, status=1/FAILURE ░░
Subject: Unit process exited ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ An
ExecStart= process belonging to unit mysqld.service has exited. ░░ ░░
The process' exit code is 'exited' and its exit status is 1. Aug 29
01:26:45 kamz-workstation systemd[1]: mysqld.service: Failed with
result 'exit-code'. ░░ Subject: Unit failed ░░ Defined-By: systemd ░░
Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░ ░░ The unit mysqld.service has entered the 'failed' state with
result 'exit-code'. Aug 29 01:26:45 kamz-workstation systemd[1]:
Failed to start mysqld.service – MySQL Server. ░░ Subject: A start job
for unit mysqld.service has failed ░░ Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░ A
start job for unit mysqld.service has finished with a failure. ░░ ░░
The job identifier is 3644 and the job result is failed. Aug 29
01:26:45 kamz-workstation systemd[1]: mysqld.service: Consumed 3.379s
CPU time. ░░ Subject: Resources consumed by unit runtime ░░
Defined-By: systemd ░░ Support:
https://lists.freedesktop.org/mailman/listinfo/systemd-devel ░░ ░░
The unit mysqld.service completed and consumed the indicated
resources.

I can read the reports over and over and nothing jumps out at me. Why does mysql_secure_installation fail me? So, I've gone from not setting a root password but mysql_secure_installation aborting because I enter no password… to setting (in behind-the-scenes manner) a root password, when I cannot get MySQL server running again.

Best Answer

While installing you have to get inside as root to fix everything first. Then you can add a user and use it with that user account.

Related Solutions

Word – WAMP – Reset Root password on thesql

It seems that windows is not reading the HOSTS file properly, as a result only 127.0.0.1 is accepted NOT localhost.

To work around this:

Go to your wamp\apps\phpmyadmin.versionnumber\

Open config.inc.php

Change the localhost to 127.0.0.1

Save and restart all services

Can’t reset MySQL (MariaDB) root password

I have found a solution that is as strange as the problem itself.

~~Reboot MySQL/MariaDB using --skip-grant-tables (search for tutorials on the web).~~ (not necessary at all, read my edits at the end of the post)

Look at the plugin field into the mysql.user table:

MariaDB [mysql]> SELECT user, plugin FROM user;
+------+-------------+
| user | plugin      |
+------+-------------+
| root | unix_socket |
| root | unix_socket |
| root | unix_socket |
| root | unix_socket |
+------+-------------+

I had to reset the plugin field of each entry to a blank string.

UPDATE user SET plugin="";   // without WHERE clause

Also, make sure that a password is defined, because sometimes it seems to be erased (select on user, password fields). If not, update it with:

UPDATE user SET password=PASSWORD("my_password") WHERE user="root";

Privileges parameters need to be saved explicitly:

FLUSH PRIVILEGES;

Then, restart MySQL in normal mode and you should be able to connect to the root account.

This will not necessarily disable the connection via Unix socket. After my MySQL va repaired, in PMA, I can see that the connection is established through an Unix socket.

EDIT, some months later: I'm now used to have this problem come back frequently, I think at each update of MariaDB (or something like that). So I've got a better comprehension of the probem ; there's an UNIX_SOCKET plugin that can let you log in a MariaDB account without having to create a password, because it uses the shell's credentials to trust you, without having to enter any password. In fact, this plugin is an authentication plugin and not a method of communication with the SQL server. So you can safely disable it if you don't use unix socket as a logging-in method. The only thing I can't explain is why the UNIX_SOCKET plugin is regularly set on each account of the database, without any action on my side.

This has the nice side effect that, when it happens, you can login to the SQL server without having to restart MariaDB with --skip-grant-tables: just log-in to the system's root account, then just connect with mysql -u root without password, then reset the plugin field in the way it is explained above.

EDIT 2: Confirmed, it happens on each MariaDB upgrade on Ubuntu.

Best Answer

Related Solutions

Word – WAMP – Reset Root password on thesql

Can’t reset MySQL (MariaDB) root password

Related Question