Sometimes when trying to log in MySQL as root user using mysql -uroot -p, the user account after login(select current_user()) is actually “@localhost”, without permission to do anything in the database. The user may only see very few databases. Why is that and how to solve the problem?

I encountered this problem once when running MySQL server on my Mac laptop. MySQL version is 5.7. My root account didn’t have a password. But the logged in user was correct when I used DBeaver or other DB clients to connect to MySQL server as root. I checked the users using the DB client and it appeared that there were multiple accounts in the server, one of which was the anonymous one(“@localhost”). There was one user “root@127.0.0.1” which was what DBeaver was using. However, that user was actually NOT “root@localhost”. And since the default host of mysql -uroot was “localhost”, the matched user was “@locahost”, not the expected “root@localhost”. And there are usually very few privileges for the “@localhost” user, it is understandable why the user may only see very few databases after logged in.

So the solution is simple. Either specify the host explicitly using mysql -h 127.0.0.1 -uroot -p, or create a new user “root@localhost” and grant all privileges to it.