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

Installing MySQL manually by downloading packages for the first time generates a default password for root. Copy and save that. If not done somehow on successive re-installations it does not show that password.

Thus you cannot login to root. Do the following :

1. Find mysql related entries from system sudo find / -name mysql
2. Remove all mysql related entries by doing rm -rf <mysql_entries_above>
3. Download latest mysql-server and intall it.
4. You will be promted with a default password which you need to copy.
5. Run mysql_secure_installation and paste that password when asked for root.
6. Subsequently follow the steps and change admin password when prompted for.

Answer 2

I installed view brew, and I had the same error message until I noticed this caveat:

We've installed your MySQL database without a root password. To secure it run: mysql_secure_installation

To connect run: mysql -uroot

To have launchd start mysql now and restart at login: brew services start mysql

Or, if you don't want/need a background service you can just run: mysql.server start

Answer 3

Doing these steps under OSX 10.11 El Capitan and MySQL 5.7.X, should do the trick.

Considering that you already have MySQL installed then..

Open a terminal window and type:

1. sudo /usr/local/mysql/support-files/mysql.server stop
2. sudo mysqld_safe --skip-grant-tables

Since the command fired in the step 2 will be under on going state, you need to open another terminal window and then type:

1. mysql -u root -p
2. UPDATE mysql.user SET password_expired='N', authentication_string=PASSWORD('') WHERE User='root';
3. quit;
4. sudo /usr/local/mysql/support-files/mysql.server restart

Important: in the step 2 you must replace for your password.

Hope it will wok for you.

Answer 4

MySQL password expired

Resetting the password will solve the problem temporarily, however, from MySQL 5.7.4 to 5.7.10 (I think to encourage better security) the default value for the default_password_lifetime variable is 360 (about a year). For those versions, if you make no changes to this variable (or to individual user accounts) all user passwords expire after 360 days.

Typically, from a script you might get the message: "Your password has expired. To log in you must change it using a client that supports expired passwords."

So, to prevent automatic password expiry, log in as root (mysql -u root -p), then, for clients that automatically connect to the server (e.g. scripts.) change the password expiration settings for those clients:

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER;

or you can disable automatic password expiration for all users:

SET GLOBAL default_password_lifetime = 0;

Links:

MySQL: Password Expiration and Sandbox Mode
MySQL: Password Expiration Policy
Password expiration policy in MySQL Server 5.7

Answer 5

Answer 7 worked for me: El capitan, MySQL installed from dmg and autogenerated password, but made sure to cd to /usr/local/bin/mysql before entering ./mysql -root -p Obvious, but I didn't the first time.

Now to find where all my databases and tables are and how to link them in.

Answer 6

For Mysql 5.7 I use

shell $ > sudo grep 'temporary password' /var/log/mysqld.log