Follow these steps to reset MySQL root user password:
First stop mysql using below command:
sudo /etc/init.d/mysql stop
Run the commands below to create a new mysqld directory
sudo mkdir /var/run/mysqld/
Set permission to mysql user access to it
sudo chown mysql /var/run/mysqld/
Now run the commands below to start MySQL in safe mode by bypassing the standard authentication process:
sudo mysqld_safe --skip-grant-tables &
You will see like this
root@ip-172-31-4-179:/var/www/html# sudo mysqld_safe --skip-grant-tables & [1] 15675 root@ip-172-31-4-179:/var/www/html# 2019-01-17T12:10:38.077290Z mysqld_safe Logging to syslog. 2019-01-17T12:10:38.080433Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2019-01-17T12:10:38.098434Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
You may need to press the Enter key
Now you can login into database server with the root account without typing a password
sudo mysql -u root
Run below command select ‘mysql’ database. This database holds the settings for root user and other server setting.
use mysql;
Finally, run below command to change the root password
update user set authentication_string=PASSWORD("New_Passwore_Here") where User='root';
Save the change by running the commands below
flush privileges; exit;
Finally, stop MySQL safe_mode and start MySQL default service
sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start
Done!
Now you can login with new password:
sudo mysql -u root -p
and enter new password.
