How to proper setting root mysql password

Question

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

Answer 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.