My login attempts fail for a new user account that I have created, and I have spent several hours learning how authentication is handled by MySQL, with no progress.
Everything is on the same server:
The end result doesn’t change, regardless of whether I:
Here’s what I have tried from phpMyAdmin (logged in as root):
flush privileges;
create user 'testuser'@'localhost' identified by 'password!';
grant * on inventory to 'testuser'@'localhost';
flush privileges;
While logged into Ubuntu via ssh: mysql -u testuser -p mysql -u testuser -h 127.0.0.1 -p mysql -u testuser -h localhost -p mysql -u testuser -h <the-actual-hostname> -p
When attempting to log in, depending on whether I used 'testuser'@'%'
or 'testuser'@'localhost'
I’ll get one of two errors:
Access denied for user ‘testuser’@‘%’ (using password: YES) Access denied for user ‘testuser’@‘localhost’ (using password: YES)
To rule out this being a permissions issue, I assigned the user all global permissions, but the error message didn’t change, so I reverted the change.
Running ‘’‘select host,user,plugin,authentication_string,account_locked from mysql.user where user=testuser;’‘’ shows:
host | user | plugin | authentication_string | account_locked |
---|---|---|---|---|
localhost | testuser | mysql_native_password | blahblahblah | N |
Just to show another way I’m trying to connect, I have a LAMP stack running and navigate to this php page in multiple web browsers:
<?php
$dbhost='localhost';
$dbuser='testuser';
$dbpass='password';
$dbname='inventory';
$conn = new mysqli($dbhost,$dbuser,$dbpass,$dbname);
if($conn->connect_error) {
die("Error: Couldn't connect: ".$conn->connect_error);
}
?>
I’ve tried deleting the user and re-creating again after having confirmed that there’s no blank usernames or duplicates of this user.
/var/log/mysql/error.log only shows the exact same error message.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Hello,
I think that you have a typo in your
grant
privileges query. I’ve tried your queries and it worked as normal, I just had to adjust the grant privileges query a little bit.Here’s what I did:
I could suggest just trying this from scratch, just drop the user and try to create a new one.
Hope that this helps! Regards, Bobby
lol wrong server, time for coffee, thanks Bobby