How to recover mysql password?

Question

What I had already performed are:

sudo /etc/init.d/mysql stop
/usr/sbin/mysqld --skip-grant-tables --skip-networking &

After this I am getting:

[Warning] Using unique option prefix key_buffer instead of
key_buffer_size is deprecated and will be removed in a future release.  
Please use the full name instead.

150929 11:48:31[Note] /usr/sbin/mysqld (mysqld ....)starting as
process 3633 ...

And its not processed further.

What to do?

Do you get the shell prompt after mysqld starts with skip-grant-tables? You should be able to connect with mysql client and set a password. — nobody, Sep 29 '15 at 06:52

score 3 · Accepted Answer · edited Oct 05 '15 at 14:38

3

Next steps could be:

In terminal:
```
mysql
```

In mysql shell:

use mysql;
select user,password,host from user;
update user set password=password("newpassword") where user=root;
select user,password,host from user;
flush tables;
FLUSH PRIVILEGES;
quit

In terminal:

kill -15 `pgrep -f 'skip-grant-tables' 
service mysql start
mysql -u root -p

edited Oct 05 '15 at 14:38

snoop

4,040
9
40
58

answered Sep 29 '15 at 08:05

Ova

466
2
5

after the mysql command : its says permission denied password No – Mrityunjay Kumar Singh Oct 03 '15 at 07:39
If this error is at step 1, then I guess that mysql server runs in normal mode again. Then try following steps: 1) become "root" user of Ubuntu ; 2) run in terminal: mysql --defaults-file=/etc/mysql/debian.cnf ; 3) change password for "root" user. Good explanation how to restore mysql "root" password is for exampel here https://snipt.net/pelle/restore-mysql-root-password-on-debian-6/ – Ova Oct 03 '15 at 17:00
didn't work, this time able to update the password and checkd the table too but not working. – Mrityunjay Kumar Singh Oct 05 '15 at 12:13
very important step after update table is to perform FLUSH PRIVILEGES. if you could login with debian.cnf, then you should perform step as described in comment provided by @Jos http://askubuntu.com/questions/567590/mysql-server-not-recognizing-correct-password. – Ova Oct 05 '15 at 13:14
Updated the password, but getting the same response " Access denied " flush. – Mrityunjay Kumar Singh Oct 07 '15 at 04:30

score 2 · Answer 2 · edited Oct 05 '15 at 15:23

What you did is correct, as explained in the manual (see MySQL 5.5 manual under point B.5.4.1.3). At the point you stopped, your MySQL server is started without permissions control. All you have to do is to follow the next steps in the above link (summarized hereunder):

Connect to your MySQL server via the terminal:
```
shell> mysql
```
Reload the grant tables so that account-management statements work:
```
mysql> FLUSH PRIVILEGES;
```

Change the password (don't forget the ; at the end):

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Exit MySQL client and restart mysql server normally:
```
mysql> exit;
shell> sudo service mysql restart
```

By the way, it is better to use the service start/stop/restart/status to manage the various services like mysqld than to use the /etc/init.d/ version like you did.

In step 3 I get: ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to change passwords — dxvargas, Feb 25 '17 at 10:31

score 1 · Answer 3 · edited Oct 05 '15 at 15:22

Log on to your system as the Unix user that the MySQL server runs as (for example, mysql).
Stop the MySQL server if it is running. You did that with:
```
su /etc/init.d/mysql stop
```
Create a text file containing the following statement on a single line. Replace the password with the password that you want to use.
```
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
```

Save the file. This example names the file /home/me/mysql-init. The file contains the password, so do not save it where it can be read by other users. If you are not logged in as mysql (the user the server runs as), make sure that the file has permissions that permit mysql to read it.

Start the MySQL server with the special --init-file option:

shell> /usr/sbin/mysqld --init-file=/home/me/mysql-init &

The server executes the contents of the file named by the --init-file option at startup, changing the 'root'@'localhost' account password.

After the server has started successfully, delete /home/me/mysql-init.
Kill mysqld and start it with:
```
su /etc/init.d/mysql start
```

How to recover mysql password?

3 Answers3

Linked