MySQL Recover and reset the default root password for MySQL 5.7+ reset root password when " /var/run/mysqld' for UNIX socket file don't exists"
Example
if I forget the password then I'll get error.
$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I tried to solve the issue by first knowing the status:
$ systemctl status mysql.service
mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en Active: active (running) since Thu 2017-06-08 14:31:33 IST; 38s ago
Then I used the code mysqld_safe --skip-grant-tables & but I get the error:
mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
$ systemctl stop mysql.service
$ ps -eaf|grep mysql
$ mysqld_safe --skip-grant-tables &
I solved:
$ mkdir -p /var/run/mysqld
$ chown mysql:mysql /var/run/mysqld
Now I use the same code mysqld_safe --skip-grant-tables & and get
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
If I use $ mysql -u root I'll get :
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2017, 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>
Now time to change password:
mysql> use mysql
mysql> describe user;
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> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('newpwd');
or If you have a mysql root account that can connect from everywhere, you should also do:
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
Alternate Method:
USE mysql
UPDATE user SET Password = PASSWORD('newpwd')
WHERE Host = 'localhost' AND User = 'root';
And if you have a root account that can access from everywhere:
USE mysql
UPDATE user SET Password = PASSWORD('newpwd')
WHERE Host = '%' AND User = 'root';`enter code here
now need to quit from mysql and stop/start
FLUSH PRIVILEGES;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start
now again ` mysql -u root -p' and use the new password to get
mysql>
