1

I'm trying to make Mysql and phpMyAdmin works.

I installed mysql using brew

Following those answers https://stackoverflow.com/questions/6474775/setting-the-mysql-root-user-password-on-os-x

I tried to set a plain text password

UPDATE mysql.user SET authentication_string='root' WHERE User='root';

But now when I try to login into phpmyadmin I get 2 errors (I'm not able to connect even with mysql cli with "Access denied" error):

 mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]


 mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client
  • Have you run flush privileges;? – grg Apr 28 at 16:21
  • actually I miss that :/ how can I fix that since I'm basically locked out now? – alfredopacino Apr 28 at 16:37
  • support.rackspace.com/how-to/… – slm Apr 28 at 16:53
  • yeah I found that and I was trying to follow the steps but after sudo mysqld_safe --skip-grant-tables & I'm still not able to login without password Access denied for user 'root'@'localhost' – alfredopacino Apr 28 at 16:59
  • running out of options.. there is a way I can safely delete user table and restart with a clean installation? – alfredopacino Apr 28 at 17:14
1

I got the problem, basically almost all the solution you can find around are outdated. With mysql 8.0.15 you have to follow this guide https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

I recommend to read the whole page, but basically you have to use

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

NOTE Using the method UPDATE mysql.user SET authentication_string.. will lock you out from your own mysql server (you won't solve even with mysqld_safe --skip-grant-tables). If you end up locked out, I guess the only hope (I didn't test it) is to stop mysqld and relaunch mysqld manually using --init-file="myinitfile" where myinitfile is a plain text file containing the ALTER USER instruction I mentioned before (I'm not sure it is even doable if you have installed mysql using brew, but note I'm new to macOS). Mine was a fresh installation, so I've just deleted the whole /usr/local/var/mysql directory and reinstalled mysql.

  • 1
    Please make sure to include the relevant parts of the linked page into your answer here, specifically the way to change passwords. I can image that this is a common problem, other users will benefit from finding the relevant information right here, without having to hope to another page and search for the answer there. – nohillside Apr 28 at 18:55
  • Even better would be an answer which also explains how to restore access once UPDATE mysql.user SET authentication_string was used. – nohillside Apr 28 at 19:10
  • 1
    edited the answer with more details – alfredopacino Apr 28 at 21:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .