Mysql – Recover lost MySQL password

MySQLpassword-recovery

I hate to ask a stupid question like forgetting the password, but this one is really hard to get back. Tried all the methods I can find online, and none is working. Even deleting the database requires my password, which means if I don't get my password back somehow, or reset, I am basically done with using it for my life.

I am using Mac OS 10.10.2 Here is what i have tried: Tried to stop it first, using a script from the oracle site:

kill `cat /mysql-data-directory/host_name.pid`

…getting no such directory error. I terminated MySQL manually from system preferences, and did the following:

Everyone is saying to use: 

mysqld_safe --skip-grant-tables

…but it is not working here. No mysqld_safe command even exists when I punched it into cmd. Then I digged into the directory files, and used:

sudo ./usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

After i did, it still asked for my password! Then I tried to remove sudo: 

./usr/local/mysql/bin mysqld_safe --skip-grant-tables

it becomes:

fan$ ./mysqld_safe --skip-grant-tables
./mysqld_safe: line 522: /usr/local/mysql/data/mysqld_safe.pid: Permission denied
awk: i/o error occurred while closing /dev/stdout
 input record number 8, file 
 source line number 1
160204 00:21:13 mysqld_safe Logging to '/usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err'.
touch: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
chmod: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
160204 00:21:13 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
./mysqld_safe: line 130: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
rm: /tmp/mysql.sock: Permission denied
rm: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.pid: Permission denied
rm: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.pid.shutdown: Permission denied

Please help to save my life with this mess! I really really don't understand why Oracle wants to give me a so sophisticated password in the beginning instead of asking me to set one! I jotted down the password it gave me, which was a combination (&*,<>AaFz0).

I have updated my termination procedures, and it is not working at all. I have used http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html to try to solve the problem. I am getting the same thing,the big chunk of nonsense above.

Here is what I got:

/usr/local/mysql/data/mysqld_safe.pid: Permission denied
awk: i/o error occurred while closing /dev/stdout
 input record number 1, file 
 source line number 1
160204 01:11:50 mysqld_safe Logging to '/usr/local/mysql/data/Fans-MacBook-Pro.local.err'.
touch: /usr/local/mysql/data/Fans-MacBook-Pro.local.err: Permission denied

Best Answer

Try the below:

  1. Add below 2 lines in my.cnf

    skip-grant-tables
skip-networking

  2. Restart mysql service

    service mysqld restart

    Note: if you don't have a service, you can do from /etc/init.d/

  3. Connect mysql:

    mysql

  4. Update root password:

    UPDATE mysql.user 
SET PASSWORD=PASSWORD('MyNewPass') 
WHERE USER='root'; 

flush privileges"; 
exit;

  5. Remove newly added lines from my.cnf and restart the mysql service.

Permission errors mean you don't have admin permission where you are trying to do the task, so login with admin login first.

Related Question