Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.
During support work, one of the most common issues we see is: “I try to connect to MySQL and get a 1045 error,” often followed by “but I’m sure my user and password are correct.” This post outlines other common causes.
|
1 2 |
[engineer@percona]# mysql -u root -psekret ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) |
If no host is specified, MySQL connects to localhost by default.
Fix:
|
1 |
mysql -u root -psekret -h <IP> -P 3306 |
|
1 2 |
mysql -u nonexistant -psekret -h localhost ERROR 1045 (28000) |
Fix: Check and create user if needed:
|
1 |
SELECT User FROM mysql.user WHERE User='nonexistant'; |
|
1 |
CREATE USER 'nonexistant'@'localhost' IDENTIFIED BY 'sekret'; |
|
1 |
SELECT Host, User FROM mysql.user WHERE User='nonexistant'; |
Check client IP:
|
1 |
ip address | grep inet | grep -v inet6 |
|
1 |
dig +short myip.opendns.com @resolver1.opendns.com |
Fix:
|
1 |
CREATE USER 'nonexistant'@'%' IDENTIFIED BY 'sekret'; |
|
1 |
SELECT Host, User, authentication_string FROM mysql.user WHERE User='nonexistant'; |
Fix: Reset password:
|
1 |
SET PASSWORD FOR 'nonexistant'@'%' = 'hello$!world'; |
|
1 |
mysql -u nonexistant -phello$!world |
Fix:
|
1 |
mysql -u nonexistant -p'hello$!world' |
|
1 |
ALTER USER 'ssluser'@'%' REQUIRE SSL; |
Fix:
|
1 |
mysql -u ssluser -psekret --ssl-mode=REQUIRED |
More info:
|
1 |
CREATE USER 'ap_user'@'%' IDENTIFIED WITH auth_pam; |
Fix: Verify system user:
|
1 |
cat /etc/passwd | grep ap_user |
Reset password:
|
1 |
sudo passwd ap_user |
skip-grant-tables to my.cnfFLUSH PRIVILEGES;skip-grant-tablesYou should now be able to log in normally.
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.