Problems connecting to MySQL locally - try localhost instead of %
I’m setting up mysql locally on my laptop this weekend, and ran into a snag that cost me at least an hour. To mimic a web app I am running locally, I needed to connect to mysql with a specific username and password. But no matter what I did, I always got this error:
MySQL: access denied for user: username (Using password: YES)I got this when connecting via the command line client and via ColdFusion. Finally after some googling I came across this blog post suggesting to change the privilege details to allow connecting from ‘localhost’ instead of ‘%’. The % indicates a wildcard and should allow connections from any host. But for some reason this was not working for me. Changing it to ‘localhost’ fixed the problem and allowed me to connect. I think it may have had something to do with the fact that I checked a box when installing that would only allow connections from the localhost.
I used this SQL to fix the host setting after connecting to the ‘mysql’ database:
update user set Host=’localhost’ where User=’myUserName’