Access denied for user 'root'@'localhost' (using password: YES) after new installation on Ubuntu

mysql access denied for user ''@'localhost'
mysql2::error: access denied for user 'root'@'localhost' (using password: yes)
access denied for user to database 'mysql
access denied for user mysql remote
error 1045 (28000): access denied for user
how to solve access denied for user 'root'@'localhost
failed to connect to mysql: access denied for user
access denied for user andrew localhost' (using password: no)

Today I did a login as root into Ubuntu 14.04.1 LTS ll

and then apt-get install mariadb-server (without sudo but as root).

With mySQL -h localhost -u root --password=<PW> I got

Access denied for user 'root'@'localhost' (using password: YES)

With mySQL -u root -p I logged into the DB and did

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<PW>';
FLUSH ALL PRIVILEGES;

But this did not help. Have you got any idea? I did not find the answer for the similar questions.

TL;DR: To access newer versions of mysql/mariadb after as the root user, after a new install, you need to be in a root shell (ie sudo mysql -u root, or mysql -u root inside a shell started by su - or sudo -i first)


Having just done the same upgrade, on Ubuntu, I had the same issue.

What was odd was that

sudo /usr/bin/mysql_secure_installation

Would accept my password, and allow me to set it, but I couldn't log in as root via the mysql client

I had to start mariadb with

sudo mysqld_safe --skip-grant-tables

to get access as root, whilst all the other users could still access fine.

Looking at the mysql.user table I noticed for root the plugin column is set to unix_socket whereas all other users it is set to 'mysql_native_password'. A quick look at this page: https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/ explains that the Unix Socket enables logging in by matching uid of the process running the client with that of the user in the mysql.user table. In other words to access mariadb as root you have to be logged in as root.

Sure enough restarting my mariadb daemon with authentication required I can login as root with

sudo mysql -u root -p

or

sudo su -
mysql -u root -p

Having done this I thought about how to access without having to do the sudo, which is just a matter of running these mysql queries

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;

(replacing <password> with your desired mysql root password). This enabled password logins for the root user.

Alternatively running the mysql query:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
FLUSH PRIVILEGES;

Will change the root account to use password login without changing the password, but this may leave you with a mysql/mariadb install with no root password on it.

After either of these you need to restarting mysql/mariadb:

sudo service mysql restart

And voila I had access from my personal account via mysql -u root -p

PLEASE NOTE THAT DOING THIS IS REDUCING SECURITY Presumably the MariaDB developers have opted to have root access work like this for a good reason.

Thinking about it I'm quite happy to have to sudo mysql -u root -p so I'm switching back to that, but I thought I'd post my solution as I couldn't find one elsewhere.

mysql how to fix Access denied for user 'root'@'localhost', Follow the steps below. Start the MySQL server instance or daemon with the --​skip-grant-tables option (security setting). $ mysqld --skip-grant-tables. Execute  Access denied for user 'root'@'localhost' (using password yes) in MySQL occurs mainly due to insufficient privileges to the user.

In clean Ubuntu 16.04 LTS, MariaDB root login for localhost changed from password style to sudo login style...

so, just do

sudo mysql -u root

since we want to login with password, create another user 'user'

in MariaDB console... (you get in MariaDB console with 'sudo mysql -u root')

use mysql
CREATE USER 'user'@'localhost' IDENTIFIED BY 'yourpassword';
\q

then in bash shell prompt,

mysql-workbench

and you can login with 'user' with 'yourpassword' on localhost

MySQL remote connection, The error message indicates that you're not connecting to the remote MySQL at all, but to localhost. Make sure you connect using option -h to the remote server. Is there a user-friendly way for a novice to get around this error: `DB connection error: Access denied for user 'root'@'localhost' (using password: YES) (localhost)'? I have searched around both on here and elsewhere and the answers seem really complicated (I don't know how to shell command line type various routines; I am novice).

Try the command

sudo mysql_secure_installation

press enter and assign a new password for root in mysql/mariadb.

If you get an error like

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

enable the service with

service mysql start

now if you re-enter with

mysql -u root -p

if you follow the problem enter with sudo su and mysql -u root -p now apply permissions to root

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<password>';

this fixed my problem in MariaDB.

Good luck

MySQL Error: : 'Access denied for user 'root'@'localhost', To fix MySQL Error: : 'Access denied for user 'root'@'localhost', you should follow these steps: Step 1: Open and Edit /etc/my.cnf or  Try accesing the server with 'root'@'127.0.0.1' which is diferent from 'root'@'localhost'. Then issue a command to create the user 'root'@'localhost' and grant all privileges to it. – Gimmy Jun 3 '13 at 8:06. Also see How to have MySQL entitle the root user? on Super User. It attempts to avoid resetting passwords. – jww Apr 2 '16 at 11:28.

from superuser accepted answer:

sudo mysql -u root
use mysql;
update user set plugin='' where User='root';
flush privileges;
exit;

In MySQL, how do I resolve the error "Access denied for user , mysql> GRANT ALL ON *.* to user_name@localhost IDENTIFIED BY 'password';. Replace user_name with the user's username and password  ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db' Ask Question Asked 8 years, 4 months ago. Active 6 days ago. Viewed 679k times

I had to be logged into Ubuntu as root in order to access Mariadb as root. It may have something to do with that "Harden ..." that it prompts you to do when you first install. So:

$ sudo su
[sudo] password for user: yourubunturootpassword
# mysql -r root -p
Enter password: yourmariadbrootpassword

and you're in.

How to Fix MySQL Error: Access denied for user root@localhost, 'Access denied for user root' error appears for new MySQL installations when you try to connect to MySQL with the root user. Learn to easily fix  Access denied for user 'root'@'localhost' with mariadb 10.4.8 docker container using docker compose and issue while attaching external volume Ask Question Asked 8 months ago

ACCESS DENIED: Reset MySQL root user password - DEV, Access denied for user 'root'@'localhost'. error, even though you're sure it's the right login. You've also already tried the official documentation  I finally found a good solution here: Step 1: Identify the Database Version. Step 2: Stopping the Database Server. Step 3: Restarting the Database Server Without Permission Checking. Step 4: Changing the Root Password. Step 5: Restart the Database Server Normally.

Fixing MySQL 1045 Error: Access Denied, So let's take a look at why the MySQL 1045 error Access Denied triggers. 3) User exists but client host does not have permission to connect:. Check users info in mysql.user. mysql> select User,Password,Host from mysql.user; Normally, create user DDL pattern is like this below. CREATE USER 'peter'@'localhost' IDENTFIED BY 'password';

1044/1045 - Access denied for user 'username , 1044/1045 - Access denied for user 'username'@'yourhost' updated at September 09, 2019 20:14. Apply OS: Windows, macOS, Linux, iOS. Apply Navicat  access is denied.. Watch TV Shows and Movies Without Downloading. access is denied.. Watch TV Online, HD Streaming. Reviews & News.

Comments
  • Have you tried reseting the password for root?
  • "to access mariadb as root you have to be logged in as root." - This sentence should be at the top of the answer.
  • why do you run the GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>'; command twice before flushing privvies?
  • This should be somewhere more visible, like big red letters during instalation or something... Just wasted few hours of life trying to setup server that I need ASAP....
  • btw, if you're lazy like me, you can save like two seconds with sudo service mysql restart
  • Thanks for the tip, great for dev but how do you reverse this back to original ?