How to reset MySql server root password in RHEL | Centos | Rocky Linux | Ubuntu

Step 1: Stop MySQL Service

To begin, stop the MySQL service:

#systemctl stop mysqld

Step 2: Set MySQL Environment Variable

Set the MySQL environment variable `MYSQLD_OPTS` to bypass the grant tables:

#systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

Step 3: Start MySQL Service

Start the MySQL service again:

#systemctl start mysqld

Step 4: Login to MySQL as Root

Once the service is running, you can log in to MySQL as the root user without a password:

#mysql -u root

Switch to the `mysql` database:

USE mysql;

Step 5: Update the Root Password

Run the following command to update the root user's password:

UPDATE user SET authentication_string=PASSWORD('myRootPassword') WHERE User='root';

Reload the privileges:

FLUSH PRIVILEGES;

Then, exit MySQL:

quit

Step 6: Stop MySQL Service and Unset Environment Variable

Stop the MySQL service again and unset the environment variable:

#systemctl stop mysqld
#systemctl unset-environment MYSQLD_OPTS

Step 7: Start MySQL Service Normally

Start the MySQL service normally:

#systemctl start mysqld

Now, you can log in to MySQL with the new root password:

#mysql -u root -p

This guide helps reset the MySQL root password by temporarily disabling the grant tables, updating the password, and restoring normal operation.