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 guide. 

MySQL is a relational database management system based on SQL – Structured Query Language. The application is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications. The most common use for mySQL however, is for the purpose of a web database.

Here at LinuxAPT, as part of our Server Management Services, we regularly help our Customers to perform MySQL related queries.

In this context, we shall look into how to change the password for any other existing MySQL user account.

How to reset MySQL root password on Red Hat Enterprise Linux (RHEL) ?

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.

To 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.

1. Stop MySQL

2. Skip MySQL grant tables

3. Start MySQL in a minimal environment

4. Login to MySQL

5. Alter the MySQL root user

6. Stop MySQL

7. Unset Option to skip MySQL grant tables

8. Start MySQL in a normal environment

Now let's into the above areas briefly.

1. 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

2. 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"

3. 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

4. Login to MySQL

Now, you may login to MySQL as follows:

$ sudo mysql -u root

Once you see the mysql> prompt, you know that you have successfully bypassed the password requirement.

5. Alter the MySQL root user

Having successfully logged in to MySQL, you may now alter the root user and update the password.

i. First, reload the grant tables with the query below:

mysql> FLUSH PRIVILEGES;

ii. 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!'; 

iii. Reload the grant tables and quit MySQL with the next two queries:

mysql> FLUSH PRIVILEGES;
mysql> QUIT;

6. Stop MySQL

Run the command below to stop MySQL:

$ sudo systemctl stop mysqld

7. 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

8. 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;

How to 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 'linuxapt'@'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.

[Need urgent assistance in fixing MySQL related queries? We can help you. ]