I think one can end up in this position with older versions of mysql already installed. I had the same problem and none of the above solutions worked for me. I fixed it thus:
Used brew's remove
& cleanup
commands, unloaded the launchctl
script, then deleted the mysql directory in /usr/local/var
, deleted my existing /etc/my.cnf
(leave that one up to you, should it apply) and launchctl plist
Updated the string for the plist. Note also your alternate security script directory will be based on which version of MySQL you are installing.
Step-by-step:
brew remove mysql
brew cleanup
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /usr/local/var/mysql
I then started from scratch:
- installed mysql with
brew install mysql
ran the commands brew suggested: (see note: below)
unset TMPDIR
mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
Start mysql with mysql.server start
command, to be able to log on it
Used the alternate security script:
/usr/local/Cellar/mysql/5.5.10/bin/mysql_secure_installation
Followed the launchctl
section from the brew package script output such as,
#start
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
#stop
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
Note: the --force
bit on brew cleanup
will also cleanup outdated kegs, think it's a new-ish homebrew feature.
Note the second: a commenter says step 2 is not required. I don't want to test it, so YMMV!
You probably have an anonymous user ''@'localhost'
or ''@'127.0.0.1'
.
As per the manual:
When multiple matches are possible, the server must determine which of
them to use. It resolves this issue as follows: (...)
- When a client attempts to connect, the server looks through the rows [of table mysql.user] in sorted order.
- The server uses the first row that matches the client host name and user name.
(...)
The server uses sorting rules that order rows with the most-specific Host values first.
Literal host names [such as 'localhost'] and IP addresses are the most specific.
Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%'
when connecting from localhost
.
'bill'@'localhost'
does match 'bill'@'%'
, but would match (e.g.) ''@'localhost'
beforehands.
The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).
Below edits are mostly irrelevant to the main question. These are only meant to answer some questions raised in other comments within this thread.
Edit 1
Authenticating as 'bill'@'%'
through a socket.
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock
Welcome to the MySQL monitor (...)
mysql> SELECT user, host FROM mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| bill | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| bill@localhost | bill@% |
+----------------+----------------+
1 row in set (0.02 sec)
mysql> SHOW VARIABLES LIKE 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | ON |
+-----------------+-------+
1 row in set (0.00 sec)
Edit 2
Exact same setup, except I re-activated networking, and I now create an anonymous user ''@'localhost'
.
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql
Welcome to the MySQL monitor (...)
mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';
Query OK, 0 rows affected (0.00 sec)
mysql> Bye
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
--socket=/tmp/mysql-5.5.sock
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
-h127.0.0.1 --protocol=TCP
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
-hlocalhost --protocol=TCP
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
Edit 3
Same situation as in edit 2, now providing the anonymous user's password.
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost
Welcome to the MySQL monitor (...)
mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| bill@localhost | @localhost |
+----------------+----------------+
1 row in set (0.01 sec)
Conclusion 1, from edit 1: One can authenticate as 'bill'@'%'
through a socket.
Conclusion 2, from edit 2: Whether one connects through TCP or through a socket has no impact on the authentication process (except one cannot connect as anyone else but 'something'@'localhost'
through a socket, obviously).
Conclusion 3, from edit 3: Although I specified -ubill
, I have been granted access as an anonymous user. This is because of the "sorting rules" advised above. Notice that in most default installations, a no-password, anonymous user exists (and should be secured/removed).
Best Answer
The MySQL server must be running to change the password, open and start MAMP now. keep it running until the end of this.
With the MySQL server running, open the terminal (located in '/Applications/Utilities') enter or copy/paste the following:
You'll be prompted for a password. Enter the current password for the root user. If you changed it in phpMyAdmin, use that password.
Mosty password is blank so just press Enter and Enter Your New Password.
Now You Have to make changes in some file:
You need to replace all occurrences of the old root password with the new one you just created. To keep it easy here is a list of the files, you can open them in your favorite text editor and search for 'root', changing '-proot' to '-p NewPassword', replacing 'NewPassword' with your own.
/Applications/MAMP/bin/upgradeMysql.sh
Stop and restart MAMP
I hope that works/make sense because this works for me.