Reset user password in MySQL

Let's see how to reset the MySQL user password.

I am using the below commands to reset the MySQL root user password in the Ubuntu 22 LTS.

1. Stop the MySQL by the following commands

sudo systemctl stop mysql

sudo /etc/init.d/mysql stop

2. Run the mysqld

sudo mysqld_safe --skip-grant-tables &

If you face the error like below,

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists

Then run the below commands, to create and folder and update the permission

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

3. Login into the MySQL

Use the below command to login, without password

mysql -u root

Change the databse to mysql, by usnig the below command

use mysql;

Flush the privileges

FLUSH PRIVILEGES;

Try to alter the password, by using the command

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

If you face the below error

ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded

Then, update the Plugin for authentication

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'

Exit from mysql

exit

4. Stop the MySQL service

sudo systemctl stop mysql

5. Start the service

sudo systemctl start mysql

And, try to login into mysql now with new password you set

mysql -u root -p

Hope it will prompt for password, Enter the password

password: