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

Question

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.

Veriee

Have you tried reseting the password for root? – Hermaphroditus

Answer 1

TL;DR: To access newer versions of mysql/mariadb 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.

Leucas

btw, if you're lazy like me, you can save like two seconds with sudo service mysql restart – Barbados

Does <password> mean "type in an arbitrary password", "type in the password for the root user on your machine", or "literally type in the string <password>"? – Thanhthank

<password> in the grant statement means 'provide a password that you want the mysql root user to use to login'. It should be different from the host system's root user password for security reasons - though there is no technical reason you can't use the same password. – Leucas

Thanks for the tip, great for dev but how do you reverse this back to original ? – Sacker

@sam : To revert changing security just change the plugin back to 'unix_socket': UPDATE mysql.user SET plugin = 'unix_socket' WHERE user = 'root' AND plugin = 'mysql_native_password'; FLUSH PRIVILEGES; – Leucas

For anyone who is wondering, MariaDB made this change in 10.4.3, where unix_socket is now the default for root@localhost: mariadb.com/kb/en/authentication-plugin-unix-socket . – Electrotechnics

I used sudo mysqld --skip-grant-tables instead of sudo mysqld_safe --skip-grant-tables, and it works. – Garrettgarrick

At some mariadb versions it's need to be first FLUSH PRIVILEGES; before any action to GRANTetc. could be taken. At create new user please follow this instructions: #5555828 – Tannenwald

Answer 2

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

Lariat

Nope. sudo mysql -u root gives me the same error. – Demoss

Answer 3

from superuser accepted answer:

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

Defunct

Nope. sudo mysql -u root gives me the same error. – Demoss

this works for me on mariadb 10.3.34 with ubuntu 20.04. – Egad

Answer 4

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

Equalitarian

Answer 5

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.

Discolor

Answer 6

The new command to flush the privileges is:

FLUSH PRIVILEGES

The old command FLUSH ALL PRIVILEGES does not work any more.

You will get an error that looks like that:

MariaDB [(none)]> FLUSH ALL PRIVILEGES; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALL PRIVILEGES' at line 1

Hope this helps :)

Millican

Answer 7

Run mysql_upgrade.

Check that

SHOW GRANTS FOR 'root'@'localhost';

says

GRANT ALL PRIVILEGES ON ... WITH GRANT OPTION 

Check that the table exists _mysql.proxies_priv_.

Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?

Marteena

Answer 8

System like Ubuntu prefers to use auth_socket plugin. It will try to authenticate by comparing your username in DB and process which makes mysql request; it is described in here

The socket plugin checks whether the socket user name (the operating system user name) matches the MySQL user name specified by the client program to the server, and permits the connection only if the names match.

Instead you may want to back with the mysql_native_password, which will require user/password to authenticate.

About the method to achieve that, I recommend this instead.

Tartarous

Answer 9

First of all close terminal to exit this cmd;

Then run: sudo mysql (Do not forgot sudo otherwise MySQL requires password which you don't know - use sudo to skip mysql password authentication)

Select mysql database by following cmd

mysql> use mysql

then set new password for your root user

mysql> alter user root@localhost identified with mysql_native_password by 'MyNewPassword@123';

then

mysql> flush privileges;

and quit to close mysql connection

mysql> quit

Now you can run sudo mysql_secure_installation without any error Here you have to enter your new set password (MyNewPassword@123)

Frons

When I run "use mysql" then I get this error Access denied for user ''@'localhost' to database 'mysql' – Transonic