Fixing a MySQL 1045 Error

During 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 triggers in the following cases:

1) Connecting to wrong host:

[root@percona]# mysql -u root -psekret
mysql: [Warning] Using a password on the command line interface can be insecure. 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

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:

[root@percona]# mysql -u root -psekret -h <IP> -P 3306

2) User does not exist:

[root@percona]# mysql -u nonexistant -psekret -h localhost
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'nonexistant'@'localhost' (using password: YES)

Fix:Double check if the user exists:

mysql> SELECT User FROM mysql.user WHERE User='nonexistant';
Empty set (0.00 sec)

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

mysql> CREATE USER 'nonexistant'@'localhost' IDENTIFIED BY 'sekret';
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

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

[root@percona]# mysql -u nonexistant -psekret
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'nonexistant'@'localhost' (using password: YES)

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

mysql> SELECT Host, User FROM mysql.user WHERE User='nonexistant';
+-------------+-------------+
| Host        | User        |
+-------------+-------------+
| 192.168.0.1 | nonexistant |
+-------------+-------------+
1 row in set (0.00 sec)

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

[root@percona]# ip address | grep inet | grep -v inet6
    inet 127.0.0.1/8 scope host lo
    inet 192.168.0.20/24 brd 192.168.0.255 scope global dynamic wlp58s0

or for public IP:

[root@percona]# dig +short myip.opendns.com @resolver1.opendns.com
177.128.214.181

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

mysql> CREATE USER 'nonexistant'@'%' IDENTIFIED BY 'sekret';
Query OK, 0 rows affected (0.00 sec)

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

[root@percona]# mysql -u nonexistant -pforgotten
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'nonexistant'@'localhost' (using password: YES)

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:

mysql> SELECT Host, User, authentication_string, PASSWORD('forgotten') FROM mysql.user WHERE User='nonexistant';  
+-------------+-------------+-------------------------------------------+-------------------------------------------+
| Host        | User        | authentication_string                     | PASSWORD('forgotten')                     |
+-------------+-------------+-------------------------------------------+-------------------------------------------+
| 192.168.0.1 | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 |
| %           | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 |
+-------------+-------------+-------------------------------------------+-------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

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.

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

mysql> set password for 'nonexistant'@'%' = 'hello$!world';
Empty set (0.00 sec)

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

[root@percona]# mysql -u nonexistant -phello$!world 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'nonexistant'@'localhost' (using password: YES)

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

[root@percona]# mysql -u nonexistant -p'hello$!world'
mysql: [Warning] Using a password on the command line interface can be insecure
...
mysql>

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

mysql> create user 'ssluser'@'%' identified by 'sekret';
Query OK, 0 rows affected (0.00 sec)
 
mysql> alter user 'ssluser'@'%' require ssl;
Query OK, 0 rows affected (0.00 sec)
...
[root@percona]# mysql -u ssluser -psekret
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)

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

[root@percona]# mysql -u ssluser -psekret --ssl-mode=REQUIRED
...
mysql>

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:

mysql> CREATE USER 'ap_user'@'%' IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.00 sec)
...
[root@percona]# mysql -u ap_user -pap_user_pass
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'ap_user'@'localhost' (using password: YES)

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:

[root@percona]# cat /etc/passwd | grep ap_user
ap_user:x:1000:1000::/home/ap_user:/bin/bash

To reset password:

[root@percona]# passwd ap_user
Changing password for user ap_user.
New 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’
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.