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?

Solution 1

Next steps could be:

1. In terminal:

   mysql
   

2. 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
   

3. In terminal:

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

Solution 2

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):

1. Connect to your MySQL server via the terminal:

   shell> mysql
   

2. Reload the grant tables so that account-management statements work:

   mysql> FLUSH PRIVILEGES;
   

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

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

4. 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.

Solution 3

1. Log on to your system as the Unix user that the MySQL server runs as (for example, mysql).

2. Stop the MySQL server if it is running. You did that with:

   su /etc/init.d/mysql stop
   

3. 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.

4. 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.

5. After the server has started successfully, delete /home/me/mysql-init.

6. Kill mysqld and start it with:

   su /etc/init.d/mysql start
   

Comments

[nobody]

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.

[Mrityunjay Kumar Singh]

after the mysql command its saying denied

[Mrityunjay Kumar Singh]

after the mysql command : its says permission denied password No

[Ova]

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 snipt.net/pelle/restore-mysql-root-password-on-debian-6

[Mrityunjay Kumar Singh]

didn't work, this time able to update the password and checkd the table too but not working.

[Ova]

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 askubuntu.com/questions/567590/….

[Mrityunjay Kumar Singh]

Updated the password, but getting the same response " Access denied " flush.

[Daniel]

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