MySQL – How to Reset MySQL Root Password in MySQL 8.0.36 on Red Hat 8

bashlinuxMySQLredhat-enterprise-linux

For years I have been using mysqld_safe (like this) with --skip-grant-tables so I can safely reset my MySQL root password:

sudo mysqld_safe  --skip-grant-tables &

But recently, while attempting to install MySQL 8.0.36 on Red Hat 8 I get:

mysqld_safe: command not found

What the heck? If the mysqld_safe command is not found, how am I supposed to set/change the root password?

Best Answer

There are definitely a lot of answers on the Internet pertaining to how to change the MySQL root user password. And the main takeaway I got from them in 2024 is this:

The `mysqld_safe` (binary/shell script) is definitley no longer a part of the MySQL 8 package (8.0.36) in Red Hat 8 and possible other Linux distros.

It might be gone (as a binary/shell script) from the MySQL install on systemd managed systems since at least MySQL 5.7.6 according to this other answer on Stack Overflow:

“As of MySQL 5.7.6, for MySQL installation using an RPM distribution, server startup and shutdown is managed by systemd on several Linux platforms. On these platforms, mysqld_safe is no longer installed because it is unnecessary. For more information, see Section 2.5.10, “Managing MySQL Server with systemd”.”

Regardless of the reason, if you get this message when attempting to run mysqld_safe:

mysqld_safe: command not found

And your system runs systemd (aka: systemctl) then this is definitely the modern answer that will solve your problems in 4 easy steps! Here we go!

Stop mysqld and restart it with the proper MYSQLD_OPTS:

sudo service mysqld stop;
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking";
sudo service mysqld start;

Once MySQL has started, login to MySQL as root with no password:
```
mysql -u root;
```
Now, in MySQL run these commands; note that [password] should be changed to whatever MySQL password you want it yo be set to:
```
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '[password]';
FLUSH PRIVILEGES;
exit;
```
Note, I believe the first FLUSH PRIVILEGES; is connected starting up MySQL with the MYSQLD_OPTS set to --skip-grant-tables. Regardless, running FLUSH PRIVILEGES; doesn’t pose any risk/danger.

Now, roll back the MYSQLD_OPTS and restart MySQL as follows:

sudo service mysqld stop;
sudo systemctl unset-environment MYSQLD_OPTS;
sudo service mysqld start;

Now with all that done, try to login to MySQL as root with a password like this:

mysql -u root -p

Hit Enter/Return, then enter your newly set root password and… et voilà! You should be logged into MySQL as root!

Note: I chose to set the password with caching_sha2_password authentication plugin, but if you want to be more modern, you can use mysql_native_password as follows:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[password]';

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 – Solutions

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

The mysqld_safe (binary/shell script) is definitley no longer a part of the MySQL 8 package (8.0.36) in Red Hat 8 and possible other Linux distros.

Related Solutions

Word – WAMP – Reset Root password on thesql

Can’t Reset MySQL (MariaDB) Root Password – Solutions

Related Question

The `mysqld_safe` (binary/shell script) is definitley no longer a part of the MySQL 8 package (8.0.36) in Red Hat 8 and possible other Linux distros.