How to recover mysql root password in MacOS

Question

I start my MAMP and try to connect to my MySQL server via localhost or 127.0.0.1, I tried all the possible password that I think it is correct, but for some reasons it still didn't like it, so I think I forgot it somehow. I need help.

---

Try #1

Enter all the possible passwords

MySQL -u root -h localhost -p

Enter password: <---I've tried almost 10 possible passwords

I got

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

---

Try #2

Enter possible passwords

MySQL -u root -h localhost

I got

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

---

Try #3

MySQL.server stop

I got

ERROR! MySQL server PID file could not be found!

I'm trying to run this

mysqld_safe --skip-grant-tables &

I got

---

Try #4

mysqld_safe --skip-grant-tables &

I got

[1] 72194
🌈  ~  2017-10-04T12:13:42.6NZ mysqld_safe Logging to '/usr/local/var/mysql/BH-MacBook-Pro-15-512GB.local.err'.
2017-10-04T12:13:42.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2017-10-04T12:13:44.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/BH-MacBook-Pro-15-512GB.local.pid ended

Open a new tab

MySQL -u root

I still get

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

---

Try #5

Close my MAMP application, and run this

mysqld_safe --skip-grant-tables &

I got

[1] 72194
🌈  ~  2017-10-04T12:13:42.6NZ mysqld_safe Logging to '/usr/local/var/mysql/BH-MacBook-Pro-15-512GB.local.err'.
2017-10-04T12:13:42.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2017-10-04T12:13:44.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/BH-MacBook-Pro-15-512GB.local.pid ended

Open a new tab

MySQL -u root

I still get

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

---

I don't what else to try anymore.

I can't really set the root password because I can't even get in.

---

This is the process of my MySQL

ps aux | grep MySQL

bheng            95850   0.2  1.0  2869440 175404   ??  S     8:48AM   0:00.28 /usr/local/opt/mysql/bin/mysqld --basedir=/usr/local/opt/mysql --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/opt/mysql/lib/plugin --bind-address=127.0.0.1 --log-error=/usr/local/var/mysql/bh-mbp-15-512gb.ad.benu.net.err --pid-file=/usr/local/var/mysql/bh-mbp-15-512gb.ad.benu.net.pid
_mysql             124   0.0  0.0  2864848   4040   ??  Ss   22Sep17   0:32.26 /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --keyring-file-data=/usr/local/mysql/keyring/keyring --early-plugin-load=keyring_file=keyring_file.so
bheng            95863   0.0  0.0  2423384    396 s009  R+    8:48AM   0:00.00 grep mysql
bheng            95749   0.0  0.0  2448756   2496   ??  S     8:48AM   0:00.03 /bin/sh /usr/local/opt/mysql/bin/mysqld_safe --bind-address=127.0.0.1 --datadir=/usr/local/var/mysql
bheng            66379   0.0  0.1  2696936   9304   ??  S     8:06AM   0:00.51 /Applications/MAMP/Library/bin/mysqld --basedir=/Applications/MAMP/Library --datadir=/Applications/MAMP/db/mysql --plugin-dir=/Applications/MAMP/Library/lib/plugin --log-error=/Applications/MAMP/logs/mysql_error_log.err --pid-file=/Applications/MAMP/tmp/mysql/mysql.pid --socket=/Applications/MAMP/tmpmysql/mysql.sock --port=8889
bheng            66263   0.0  0.0  2446708   2100   ??  S     8:06AM   0:00.02 /bin/sh /Applications/MAMP/Library/bin/mysqld_safe --port=8889 --socket=/Applications/MAMP/tmp/mysql/mysql.sock --pid-file=/Applications/MAMP/tmp/mysql/mysql.pid --log-error=/Applications/MAMP/logs/mysql_error_log

🌈 ~

Answer 1

Please follow the steps in same sequence.

Make sure, your MySQL server is down. Execute below command :

mysql.server stop

Then, start MySQL server in safe mode

mysql.server start --skip-grant-tables --skip-networking

Then, connect to MySQL using root user.

mysql -u root

Change the database from none to mysql

MariaDB [(none)]> use mysql;
Database changed

Update both authentication_string and password column with same password as shown below (if you get column not found error then remove one )

MariaDB [mysql]> update user set authentication_string=PASSWORD('password'), password=PASSWORD('password') where user='root';

MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> quit;

Now, stop everything including MySQL server and MAMP application as well.

And, search for config.inc.php file inside your MAMP folder (you could use command + space and search this file).

You may not have privilege to change this file. After updating the privilege of the file you need to look for $cfg['Servers'][$i]['password'] and update the password value as shown below.

$cfg['Servers'][$i]['password'] = 'password';

Save this file and restart everything in normal mode. And try connecting root user with new password as shown below :

mysql -uroot -ppassword

Answer 2

This is how you can do it in macOS Sierra, probably you install it via pkg and in the system preferences have an icon for MySQL, something like this:

If is up and running open a terminal and run this command:

pgrep -fl mysql

That will help you to find the path of mysqld and the current command used to start the server, the output could be something like this:

6283 /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid

Next step is to stop MySQL, you can do this by clicking on the System Preferences menu and click on the Stop MySQL Server button, Once is stoped within a terminal you will need to start again the server but with some extra options, something like this:

sudo /usr/local/mysql/bin/mysqld \
  --skip-grant-tables \
  --skip-networking \
  --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid

It is indeed the same command from the output of pgrep -fl mysql but in this case, you just need to add:

--skip-grant-tables --skip-networking

After doing that you will notice that the menu from the System Preferences is Green again and MySQL should be up and running, now you can try to login and change password:

mysql -uroot
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('secret');
mysql> FLUSH PRIVILEGES;
mysql> exit

Stop and start again MySQL from the System Preference panel and it should be working now with the new password.

Answer 3

Follow those instructions and you should be fine:

https://svendeswan.wordpress.com/2016/01/26/mysql-reset-root-password-without-knowing-it-mac-mamp/

which is based on this one:

https://www.tech-otaku.com/local-server/resetting-mamp-mysql-root-user-password/

Answer 4

You have to reset the MySQL root password as follow :

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

mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('new_password');
mysql> FLUSH PRIVILEGES;
mysql> exit;

service mysql restart

And then, you will be able to connect using : mysql -u root -p Your password