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

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.

---

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

---

from superuser accepted answer:

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

---

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

---

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.

---

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 :)

---

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?

---

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.

---