Unable to access MySQL after it automatically gene

Question

I have erased and installed OSX 10.11 El Capitan and I have been following through this tutorial to get MySQL up and running on the new OS X. The first step was to download MySQL For Mac OS X 10.9 (x86, 64-bit), DMG Archive (works on 10.11, they recommended in the tutorial). While I were finishing installing the MySQL, I got the message saying that :

2015-10-25T02:10:54.549219Z 1 [Note] A temporary password is generated for root@localhost: R>gFySuiu23U

If you lose this password, please consult the section How to Reset the Root Password in the MySQL reference manual.

That was weird, I have never seen that kind of message. After that, I started MySQL via the Preference Pane and then use /usr/local/mysql/bin/mysql -v command on the terminal for another step. I got an error message saying that :

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

I have also tried to access database through Sequel Pro using root as username and blank password, I got access denied message saying that :

Unable to connect to host 127.0.0.1 because access was denied.

Double-check your username and password and ensure that access from your current location is permitted.

MySQL said: Access denied for user 'root'@'localhost' (using password: NO)

Okay, I also tried this again using root as a username but 'R>gFySuiu23U' as a password (which was generated from MySQL). I got connection failed message saying that :

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Your password has expired. To log in you must change it using a client that supports expired passwords.

How could I solve this problem? I remember that MySQL has never got automatically generated a temporary password like this, hasn't it ?

Answer 1

Try this:

mysql -u root -h 127.0.0.1 -p
Enter password: (enter the random password here)

Ref:https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html

Following this, you may reset your password using ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';

Answer 2

This particular one did the trick for me:

As specified in this link: https://www.variphy.com/kb/mac-os-x-reset-mysql-root-password

Do all the steps except executing

    UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';

Execute

    UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';

And then execute FLUSH PRIVILEGES;

Answer 3

The another way to solve this issue is to use an older version of MySQL instead.

I have uninstalled MySQL version 5.7.9 for Mac OS X 10.9 (x86, 64-bit), DMG Archive and then install the older version, MySQL version 5.6.7 for Mac OS X 10.9 (x86, 64-bit), DMG Archive. This issue is solved. The given autogenerated password before finishing installation of this older version is gone and I can ultimately access the database using root as username and a blank password. Everything is working like a charm!

Answer 4

This may happens when you have installed mysql before. Try the password you set for the last version of mysql. This did work for me.

Answer 5

I'm running macOS Sierra(10.12.3) and I installed mysql-5.7.17-macos10.12-x86_64.dmg.

The answer from @lesley worked for me with the exception that I needed to add ./ to ensure I was calling the mysql binary in my current working directory. Which is where the aforementioned package was installed.

If you cd to /usr/local/mysql/bin and run mysql -u root -p --connect-expired-password, you could receive the following error.

mysql: unknown option '--connect-expired-password'

I did. Because simply running mysql without providing a path, called a previously installed version of the MariaDB client.

So to ensure you are executing the correct binary, you can either

provide the absolute path

/usr/local/mysql/bin/mysql -u root -p --connect-expired-password

or the relative path after changing directories

cd /usr/local/mysql/bin
./mysql -u root -p --connect-expired-password

Both ways should work. Once you are connected to the client, the instruction are the same as above from @lesley.

Enter your temporary password generated by the installer and set your new password.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourNewPassword'); 

Answer 6

I got around this problem by running 'mysql -u root -p --connect-expired-password' Then input the expired auto-gen password from mysql. Finally got in. Selected mysql db with 'use mysql' and then updated user 'root' pw with 'ALTER USER 'root'@'localhost' IDENTIFIED BY 'your new password'