Cannot log in with created user in mysql
MysqlMysql Error-1045Mysql Problem Overview
Using this command
GRANT ALL PRIVILEGES ON *.* to 'brian'@'%' identified by 'password';
I try to login with:
mysql -u brian -ppassword
The error is:
ERROR 1045 (28000): Access denied for user 'brian'@'localhost' (using password: YES)
I am doing this as root and I did try to flush privileges.
I tried this with countless users but it does not seem to work. I can create a user with no password and login works. Command line and from phpmyadmin
Also check to see if the user was in mysql.user which it is.
Show grants for brian shows:
| GRANT ALL PRIVILEGES ON *.* TO 'brian'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
Mysql Solutions
Solution 1 - Mysql
You probably have this perpetual MySQL problem where one of the default users in the user table is '' @ localhost, which winds up denying all localhost users later in the table. What I would do is mysqldump the mysql database and look for this entry in the User table; if found, delete it and flush privileges.
For more details see https://dev.mysql.com/doc/refman/5.5/en/connection-access.html.
> It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from h1.example.net by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.
Solution 2 - Mysql
This is a problem caused by the anonymous users. Once I install MySQL I always run
shell> mysql_secure_installation
and select to set/change the root password, remove anonymous users, disallow remote root login, remove the test database. This will remove the anonymous user and secure your installation. It should also solve the problem you have.
Solution 3 - Mysql
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Solution 4 - Mysql
None of the solutions provided here worked. After loads of error and trial I realised that I had special characters in the password. Changing password without special characters solved the issue
Solution 5 - Mysql
The mysql docs have this to say: (from http://dev.mysql.com/doc/refman/5.1/en/adding-users.html):
> Two of the accounts have a user name of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.
> It is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the
local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order.
With this in mind I would recommend you create a 'brian'@'localhost' user with the same privileges.
Solution 6 - Mysql
Finally this worked for me:
MariaDB [(none)]> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Solution 7 - Mysql
I think 'Russell Silva' is right...
I created an user by
CREATE USER 'username'@'%' PASSWORD='userpassword';
But I cannot login in this account.The console told me that
ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)
So I created an user with the same username except that changing '%' to 'localhost',and I could finally login in as 'username'. It's quite weird for me though.
Solution 8 - Mysql
You forgot the quotes around brian in your grant statement. Try it like this:
GRANT ALL PRIVILEGES ON *.* to 'brian'@'%' identified by 'password';
Solution 9 - Mysql
In my case it was due to me clicking "SSL: REQUIRE SSL" (in phpmyadmin). When I changed it to "REQUIRE NONE" I could log in.
Solution 10 - Mysql
Change to native password using this command:
ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';
Solution 11 - Mysql
You can also connect from another host and then the localhost anonymous user is bypassed and you can remove it and flush privileges:
mysql -u brian -ppassword -h 'other_host_than_localhost'
Solution 12 - Mysql
I had a similar problem attempting to connect to a Maria DB running on Ubuntu after upgrading to 17.04.
The default was to listen only on localhost, 127.0.0.1.
To make MySQL/Maria listen on all available ports and interfaces I needed to explicitly specify bind-address=0.0.0.0. I added this line to the end of the file /etc/mysql/my.cnf, i.e.
...
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
bind-address=0.0.0.0
Then...
sudo /etc/init.d/mysql restart
Solution 13 - Mysql
Similar problem occurred for me even after verifying i haven't entered a wrong password, i couldn't login. Below two steps solved my problem.
- Dropping test database
- Deleting anonymous user