Unable to access MySQL with new user: Permission denied error

Other Questions MySQL
1

I’m having trouble logging into MySQL with a newly created user on my cloud server. I set up the root account fine but can’t get the new user working.

Here’s what I did:

  1. Logged in as root
  2. Created a new user named ‘sam’ for all hosts
  3. Granted ‘sam’ full privileges
CREATE USER 'sam'@'%' IDENTIFIED BY 'secretpass123';
GRANT ALL PRIVILEGES ON *.* TO 'sam'@'%' WITH GRANT OPTION;

When I try to log in as ‘sam’, I receive this error:

ERROR 1045 (28000): Access denied for user 'sam'@'localhost' (using password: YES)

I’ve verified that the password is correct. What might be the issue and how can I resolve this login problem?

2

I’ve encountered this issue before, and it can be frustrating. One thing to check is whether you’ve flushed the privileges after granting them. Sometimes MySQL doesn’t immediately recognize new users or privilege changes without a flush.

Try running this command as root:

FLUSH PRIVILEGES;

Another possibility is that the user is being created for all hosts (‘%’) but you’re trying to connect from localhost. You might need to create a specific user for localhost:

CREATE USER 'sam'@'localhost' IDENTIFIED BY 'secretpass123';
GRANT ALL PRIVILEGES ON *.* TO 'sam'@'localhost' WITH GRANT OPTION;

If neither of these work, double-check your MySQL configuration files for any restrictive settings. Sometimes, especially on cloud servers, there might be additional security measures in place that are blocking connections.

Lastly, ensure you’re using the correct host when connecting. If you’re on the same machine, use ‘localhost’ instead of an IP address or hostname.

3

Have you checked the MySQL error log? It often provides more detailed information about access issues. You can usually find it at /var/log/mysql/error.log or a similar path depending on your system.

Another thing to consider is SELinux if you’re running on a system that uses it. It can sometimes interfere with MySQL connections. You might want to temporarily disable it to see if that’s the cause.

Also, make sure your MySQL server is actually listening on all interfaces. Check the bind-address in your my.cnf file. If it’s set to 127.0.0.1, it will only accept local connections.

Lastly, if you’re connecting remotely, ensure your firewall isn’t blocking the MySQL port (usually 3306). You might need to add a rule to allow incoming connections on that port.