Posted by : at

Category : mysql

Resetting Mysql Root Password

I have this wierd error, or may be i forgot my root mysql password. But this trick helped me change my password. Damn ! The current mysql version that I am using is mysql 5.7.26

So When I was trying to log in to mysql, I was getting this Error

[email protected]:# mysql -u root -p

Enter password: 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

And this was so frustating, as it was working fine few days ago, with the password that i entered. And without this there was

no way to create or access my current databases.

Changing Root Password

So then I tried to start mysql in safe mode , but this also gave me error:

[email protected]: sudo mysqld_safe --skip-grant-tables 

2019-09-24T15:42:35.549918Z mysqld_safe Logging to '/var/log/mysql/error.log'.  

2019-09-24T15:42:35.553312Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

This means there was no mysqld file ? Need to figure out a way to create that :

[email protected]:# mkdir -p /var/run/mysqld

[email protected]:# chown mysql:mysql /var/run/mysqld

Or If it shows error like this :

ERROR:Cannot connect to local mysqld.sock (2)

Its probably because of mysql server not running : Type this:

mysqladmin -h 127.0.0.1 --protocol=tcp -u root -p shutdown 

Now we have created the files and also given the permissions, lets try to run our safe command again :

[email protected]: sudo mysqld_safe --skip-grant-tables &

2019-09-24T15:45:28.205742Z mysqld_safe Logging to syslog.          

2019-09-24T15:45:28.209783Z mysqld_safe Logging to '/var/log/mysql/error.log'.               

2019-09-24T15:45:28.232818Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql                                                                                         

mysql -u root                                                                                

Welcome to the MySQL monitor.  

Commands end with ; or \g.                                   

 

Your MySQL connection id is 2                                                                

Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)                                                                                                                                          

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.                                                                                                              

Oracle is a registered trademark of Oracle Corporation and/or its                            

affiliates. Other names may be trademarks of their respective                                

owners.                                                                                                                                                                                   

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                                            

mysql> use mysql                                                                             

Reading table information for completion of table and column names                           

You can turn off this feature to get a quicker startup with -A                                                                                                                            

Database changed                                                                             

mysql> describe user;                                         

Now I got the access to mysql. Time to change the password of the ‘root’ user:

mysql> FLUSH PRIVILEGES;

mysql> SET PASSWORD FOR [email protected]'localhost' = PASSWORD('newpwd');

mysql> quit

Replace newpwd with the new password, that you want to set !

Now restart mysql :

[email protected]:# sudo service mysql stop

[email protected]:# sudo service mysql start

now again ` mysql -u root -p’ and use the new password to get

[email protected]:# mysql -u root -p

Enter Password: ( Enter the new password )

Congrats ! If you are IN , then you have resetted your password successfully.