If you have forgotten your MySQL root password on Red Hat Enterprise Linux (RHEL) 8, and you would like to reset it, then you have found the right tutorial. In this guide, you will also learn how to change the password for any other existing MySQL user account.
Prerequisite
In order to successfully reset a forgotten MySQL root password, you need to have a Linux user account with sudo privileges on the server running MySQL.
Reset Forgotten MySQL Root Password
You would need to go through the following steps to successfully reset your MySQL root password if you have forgotten it.
- Stop MySQL
- Skip MySQL grant tables
- Start MySQL in a minimal environment
- Login to MySQL
- Alter the MySQL root user
- Stop MySQL
- Unset Option to skip MySQL grant tables
- Start MySQL in a normal environment
Stop MySQL
The first thing you should do is stop the MySQL server. On RHEL 8, run the following command to stop the MySQL server.
$ sudo systemctl stop mysqld
Skip MySQL grant tables
The next step is to put the MySQL server in an environment which bypasses the grant tables that store information about user privileges. Run the command below.
$ sudo systemctl set-environment MYSQLD_OPTS=”--skip-grant-tables”
Start MySQL in a minimal environment
After successfully setting MySQL to skip the grant tables, you may start the service with:
$ sudo systemctl start mysqld
Login to MySQL
Now, you may login to MySQL as follows.
$ sudo mysql -u root
Once you see the mysql> prompt as shown in figure 1 below, you know that you have successfully bypassed the password requirement.
Alter the MySQL root user
Having successfully logged in to MySQL, you may now alter the root user and update the password.
First, reload the grant tables with the query below.
mysql> FLUSH PRIVILEGES;
Next, run the following query to alter the MySQL root user and specify a new password. Replace NewRootPassw0rd! with your desired password.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootPassw0rd!';
Reload the grant tables and quit MySQL with the next two queries.
mysql> FLUSH PRIVILEGES;
mysql> QUIT;
Stop MySQL
Run the command below to stop MySQL.
$ sudo systemctl stop mysqld
Unset Option to Skip MySQL grant tables
The next command will unset the option to skip the MySQL grant tables.
$ sudo systemctl unset-environment MYSQLD_OPTS
Start MySQL in a normal environment
Run the command below to start MySQL again in normal mode.
$ sudo systemctl start mysqld
You should now be able to login successfully to MySQL using your new root password as follows.
$ sudo mysql -u root -p
Quit MySQL with:
mysql> QUIT;
Change MySQL Root Password
If you know your MySQL root password and you would like to change it, then simply log in to MySQL as follows.
$ sudo mysql -u root -p
Once logged in, run the query below to change the password for your MySQL root user. Replace ChangeRootPassw0rd! with the new password that you would like to use.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ChangeRootPassw0rd!';
Note: To change the password for any other MySQL user, just replace root in the query above with the other user. For example:
mysql> ALTER USER 'shola'@'localhost' IDENTIFIED BY 'ChangeSholaPassw0rd!';
After that, reload the grant tables with:
mysql> FLUSH PRIVILEGES;
You may now quit MySQL and login again with your new password.
Conclusion
In this guide, we have provided clear instructions to help you successfully reset or change your MySQL root password on Red Hat Enterprise Linux 8. As always, feedback regarding this tutorial is highly welcome.