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 Access Denied triggers in the following cases:
1) Connecting to wrong host:
1 2 3 | [engineer@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:
1 | [engineer@percona]# mysql -u root -psekret -h <IP> -P 3306 |
2) User does not exist:
1 2 3 | [engineer@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:
1 2 | mysql> SELECT User FROM mysql.user WHERE User='nonexistant'; Empty set (0.00 sec) |
If the user does not exist, create a new user:
1 2 | mysql> CREATE USER 'nonexistant'@'localhost' IDENTIFIED BY 'sekret'; Query OK, 0 rows affected (0.00 sec) |
3) User exists but client host does not have permission to connect:
1 2 3 | [engineer@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:
1 2 3 4 5 6 7 | 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:
1 2 3 | [engineer@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:
1 2 | [engineer@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:
1 2 | 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:
1 2 3 | [engineer@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:
1 2 3 4 5 6 7 8 | 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. 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:
1 2 | mysql> set password for 'nonexistant'@'%' = 'hello$!world'; Empty set (0.00 sec) |
5) Special characters in the password being converted by Bash:
1 2 3 | [engineer@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:
1 2 3 4 | [engineer@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:
1 2 3 4 5 6 7 8 9 | 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) ... [engineer@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)
1 2 3 | [engineer@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:
1 2 3 4 5 6 | mysql> CREATE USER 'ap_user'@'%' IDENTIFIED WITH auth_pam; Query OK, 0 rows affected (0.00 sec) ... [engineer@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:
1 2 | [engineer@percona]# cat /etc/passwd | grep ap_user ap_user:x:1000:1000::/home/ap_user:/bin/bash |
To reset password:
1 2 3 | [engineer@percona]# sudo 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:
- Stop the instance
- 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]
- Start the instance
- Access with root user (mysql -uroot -hlocalhost);
- 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’;” - Stop the instance
- Edit my.cnf and remove skip-grant-tables and skip-networking
- Start MySQL again
- You should be able to login with root from the localhost and do any other necessary corrective operations with root user.
Pretty useful post.
Just an observation, in case 2 the “FLUSH PRIVILEGES” is not needed.
Hi Jonatas! Thanks for your comment
Staring with “skip-grant” won’t allow to run GRANT.
Keep that in mind!
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!
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.
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).
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.
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
The only example shell command above that requires sudo is ‘sudo passwd ap_user’
Thanks for your feedback. I will ask for this to be changed
Hi,
I just want to say that I’m not a Percona Server user but this post was super useful to me anyway – many thanks!
Hi Ian! Glad it was helpful!
Thank You So Much. I Was Trying To Fix it for My Collage Project.
Hi Krunal! Glad it helped you 🙂
While this saved my life, I think there is an error. In mariaDB v 10.1.26 I needed to write
set password for ‘user’@’%’ = PASSWORD(‘mypassword’)
to make it work, instead of
set password for ‘user’@’%’ = ‘mypassword’
Hi Pablo! Syntax for changing password would be different depending on version used. For upstream MySQL 5.6 you need to hash the password with ‘password’ function as shown here https://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html, whereas on 5.7 due to changes in authentication and plugins used, syntax changes to the one used in the blog as shown here https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html . For MariaDB, you can change password by using either “SET PASSWORD” or “ALTER USER” (as shown here https://mariadb.com/kb/en/alter-user/ ). There can (and possibly will) be small differences in syntax depending on which major version you use, and if used MariaDB since it’s slowly diverging from MySQL
Hi Carlos,
Or just add the port number to thee mySQL localhost
Thank You So Much. I Was Trying To Fix it for My Collage Project.