Fixing MySQL 1045 Error: Access Denied

MySQL 1045 error Access DeniedDuring our work in support, we see this again and again: “I try to connect to MySQL and am getting a 1045 error”, and most times it comes accompanied with “…but I am sure my user and password are OK”.  So we decided it was worth showing other reasons this error may occur.

MySQL 1045 error Access Denied triggers in the following cases:

1) Connecting to wrong host:

If not specifying the host to connect (with -h flag), MySQL client will try to connect to the localhost instance while you may be trying to connect to another host/port instance.

Fix: Double check if you are trying to connect to localhost, or be sure to specify host and port if it’s not localhost:

 

2) User does not exist:

Fix: Double check if the user exists:

If the user does not exist, create a new user:

 

3) User exists but client host does not have permission to connect:

Fix: You can check to see which host user/host MySQL allows connections with the following query:

If you need to check from which IP the client is connecting, you can use the following Linux commands for server IP:

or for public IP:

You can then create a user with correct Host (client IP), or with ‘%’ (wildcard) to match any possible IP:

 

4) Password is wrong, or the user forgot his password:

Fix: Check and/or reset password:

You cannot read user passwords in plain text from MySQL as the password hash is used for authentication, but you can compare hash strings with “PASSWORD” function:

We can see that PASSWORD(‘forgotten’) hash does not match the authentication_string column, which means password string=’forgotten’ is not the correct password to log in. Also, in case the user has multiple hosts (with different password), he may be trying to connect using the password for the wrong host.

In case you need to override the password you can execute the following query:

 

5) Special characters in the password being converted by Bash:

Fix: Prevent bash from interpreting special characters by wrapping password in single quotes:

 

6) SSL is required but the client is not using it:

Fix: Adding –ssl-mode flag (–ssl flag is deprecated but can be used too)

You can read more in-depth on how to configure SSL in MySQL in the blog post about “Setting up MySQL SSL and Secure Connections” and “SSL in 5.6 and 5.7“.

7) PAM backend not working:

Fix: Double check user/password is correct for the user to authenticate with the PAM currently being used.

In my example, I am using Linux shadow files for authentication. In order to check if the user exists:

To reset password:

Finally, if you are genuinely locked out and need to circumvent the authentication mechanisms in order to regain access to the database, here are a few simple steps to do so:

  1. Stop the instance
  2. Edit my.cnf and add skip-grant-tables under [mysqld] (this will allow access to MySQL without prompting for a password). On MySQL 8.0, skip-networking is automatically enabled (only allows access to MySQL from localhost), but for previous MySQL versions it’s suggested to also add –skip-networking under [mysqld]
  3. Start the instance
  4. Access with root user (mysql -uroot -hlocalhost); 
  5. Issue the necessary GRANT/CREATE USER/SET PASSWORD to correct the issue (likely setting a known root password will be the right thing: SET PASSWORD FOR ‘root’@’localhost’ = ‘S0vrySekr3t’). Using grant-skip-tables won’t read grants into memory and GRANT/CREATE/SET PASSWORD statements won’t work straight away. First, you need to execute “FLUSH PRIVILEGES;” before executing any GRANT/CREATE/SET PASSWORD statement, or you can modify mysql.users table with a query which modifies the password for User and Host like “UPDATE mysql.user SET authentication_string=PASSWORD(‘newpwd’) WHERE User=’root’ and Host=’localhost’;”
  6. Stop the instance
  7. Edit my.cnf and remove skip-grant-tables and skip-networking
  8. Start MySQL again
  9. You should be able to login with root from the localhost and do any other necessary corrective operations with root user.

Learn more about Percona Server for MySQL

Share this post

Comments (10)

  • Jonatas Cruz Reply

    Pretty useful post.
    Just an observation, in case 2 the “FLUSH PRIVILEGES” is not needed.

    July 5, 2019 at 4:04 pm
    • Carlos Tutte Reply

      Hi Jonatas! Thanks for your comment

      July 8, 2019 at 4:18 pm
  • Javier T Zon Reply

    Staring with “skip-grant” won’t allow to run GRANT.
    Keep that in mind!

    July 6, 2019 at 2:15 pm
    • marcos.albe Reply

      Hah, good catch! I guess we seldom take this route, and obviously we only use direct updates to mysql tables; Will fix that line. Thanks, Javi!

      July 6, 2019 at 2:17 pm
  • Luca Ferrari Reply

    Just one side note: I believe showing example run as root (I mean, the operating system user) is not good and could led people to think OS’ root and mysql one are the same.

    July 10, 2019 at 7:52 am
    • Carlos Tutte Reply

      Hi Luca! Thanks for your comment.
      Sadly, mysql super user is called root just like OS super user. DBA’s should be aware of this, but some unexperienced users may be confused beliving is the root user.
      I think showing example with root user is the right thing, as it’s the super user who can issue CREATE/GRANTS by default, and using a different user can further complicate things for inexperienced people (using “other user” in examples won’t exist in their database, and needs extra privileges to be able to execute most of the examples).

      July 10, 2019 at 11:13 am
      • Luca Ferrari Reply

        Sorry, in a rush I’ve expressed badly my concept. I’m not saying that using mysql ‘root’ is bad, rather that it is bad to show examples run as operating system ‘root’ account. Using at least a ‘sudo’ clearly states where it is required to use operating system root or not, i.e., when operating system privileges are required.
        As far as I can tell, there is not a place in your examples where I have to execute commands like ‘root@percona’ instead of an unprivileged operating system user.
        That’s my point.
        Sorry for the noise.

        July 10, 2019 at 12:19 pm
        • Carlos Tutte Reply

          Hi again Luca! Now I get it, I will change prompt to engineer@percona as it will make it more clear that OS root user is not needed to ineract with MySQL. Thanks for your suggestion

          July 10, 2019 at 12:55 pm
  • sedwards Reply

    The only example shell command above that requires sudo is ‘sudo passwd ap_user’

    July 12, 2019 at 2:31 pm
    • Carlos Tutte Reply

      Thanks for your feedback. I will ask for this to be changed

      July 12, 2019 at 3:33 pm

Leave a Reply