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
$root@localhost:# 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:
$root@localhost: 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 :
$root@localhost:# mkdir -p /var/run/mysqld $root@localhost:# 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 :
$root@localhost: 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 root@'localhost' = PASSWORD('newpwd'); mysql> quit
Replace newpwd with the new password, that you want to set !
Now restart mysql :
$root@localhost:# sudo service mysql stop $root@localhost:# sudo service mysql start
now again ` mysql -u root -p’ and use the new password to get
$root@localhost:# mysql -u root -p Enter Password: ( Enter the new password )
Congrats ! If you are IN , then you have resetted your password successfully.