Resetting Mysql Password

Posted by : at

Category : mysql

Ads by Google



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 [email protected]'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.


Ads by Google