How to reset MySQL root user password and privileges

Question

I assigned a password to the root MySQL user in XAMPP some times ago. I recently tried to undo that and give it no password but it's not working.

I tried setting the password to an empty string using mysqld --skip-grant-tables and then using the mysql command line directly. It looks like it works, the root user no longer has a password, however if I login in PhpMyAdmin I cannot see any of my databases, only an "information_schema" and a "test" database. If I look at the User tab, it seems to have all the privileges though:

enter image description here

Any suggestion on how to fix this issue?

Note: I checked with the command line, all my databases and tables are still there, I just cannot see them in PHPMyAdmin.

does your phpadmin use a local address to login?

notbad.jpeg
– notbad.jpeg

2012-07-06 03:48:06 +00:00
Commented Jul 6, 2012 at 3:48 — notbad.jpeg
– notbad.jpeg, Commented Jul 6, 2012 at 3:48

F21 · Accepted Answer · 2012-07-06 03:52:50Z

1

Try removing the anonymous user who is allowed to connect from any host. My feeling is that that row is taking precidence over the row for root when MySQL validates the login.

If you have removed that Anonymous user and the issue still occurs, try setting a password to root and the host to localhost then connect again.

answered Jul 6, 2012 at 3:52

F21

33.6k27 gold badges105 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

laurent Over a year ago

Yes that was it - deleting the anonymous user and changing the host to "localhost" fixed the issue. Thanks a lot, I've been struggling with this for days.

anthony Over a year ago

How does the precedence work in this case? The first row that satisfies the login requirements?

Collectives™ on Stack Overflow

How to reset MySQL root user password and privileges

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related