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
Top comments (0)