Reset MySQL Root Password (Version 8 and above)

Today, I faced a problem while trying to reset the root password for a MySQL database. In order to help others who may be in a similar situation, I am sharing a tutorial that outlines the steps that worked for me.

While there are many resources available on this topic, I hope that this post will be a helpful addition.

To follow these steps, you will need access to the server using SSH.

Stop the MySQL process using the following command:

sudo service mysql stop

Start the MySQL service with the --skip-grant-tables option, which allows you to connect to the MySQL server without a password:

sudo mysqld_safe --skip-grant-tables &

If you encounter an error saying "mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists," try creating the directory and setting the correct ownership and permissions with these commands:

sudo mkdir /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
sudo chmod 755 /var/run/mysqld

Connect to the MySQL server as the root user:

mysql -u root

Run the following MySQL commands to reset the root password:

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

Connect to the MySQL server again as the root user:

mysql -u root

Set a new password for the root user using the ALTER USER command. Be sure to use a strong, unique password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '{mypassword}';

Replace *{mypassword}** with your desired password.*

Stop the running mysqld process:

sudo killall -KILL mysql mysqld_safe mysqld

Start the MySQL process normally and log in with your new password:

service mysql start
mysql -u root -p