can't login as mysql user root from normal user account in ubuntu 16.04

I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:

1. First, connect in sudo mysql

    sudo mysql -u root
   

2. Check your accounts present in your db

    SELECT User,Host FROM mysql.user;
    +------------------+-----------+
    | User             | Host      |
    +------------------+-----------+
    | admin            | localhost |
    | debian-sys-maint | localhost |
    | magento_user     | localhost |
    | mysql.sys        | localhost |
    | root             | localhost |
   

3. Delete current root@localhost account

    mysql> DROP USER 'root'@'localhost';
    Query OK, 0 rows affected (0,00 sec)
   

4. Recreate your user

    mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY '';
    Query OK, 0 rows affected (0,00 sec)
   

5. Give permissions to your user (don't forget to flush privileges)

    mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
    Query OK, 0 rows affected (0,00 sec)
   
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0,01 sec)
   

6. Exit MySQL and try to reconnect without sudo from your local machine.

_{Edit: a previous version of the answer post recommended creating a user 'root'@'%'. However, it is more secure to create 'root'@'localhost' so connections can only be made from localhost, and not remotely. Both solutions work equally as well for local development.}

I hope this will help someone :)

If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.

Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket"

So in order to to change the plugin back to mysql_native_password:

1. Login with sudo:

   sudo mysql -u root
   

2. Change the plugin and set a password with a single command:

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
   

Of course you can also use the command above to set an empty password.

Just for the record, (and MariaDB < 10.2 users) there is also another way to only change the plugin without providing a password (leaving it empty):

update mysql.user set plugin = 'mysql_native_password' where User='root';
// to change the password too (credits goes to Pothi Kalimuthu)
// UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;

In short, on MariaDB

UPDATE mysql.user SET plugin = 'mysql_native_password', 
      Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

where you replace NEWPASSWORD with the password you want, and everything else verbatim.

The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user. You can test this as follows by switching to system root, and then type:

mysql -uroot -p

Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql as the password is irrelevant and the user is defined).

So what's happening? Well, if you log in as root and do the following:

select User,host,plugin from mysql.user; 
+----------------+-----------+-----------------------+
| User           | host      | plugin                |
+----------------+-----------+-----------------------+
| root           | localhost | auth_socket           |
+----------------+-----------+-----------------------+

you'll note auth_socket (which may read unix_socket on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.

So the solution is to update the Users to not use the auth_socket/unix_socket and properly set a password.

On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password you type verbatim.

UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

(It's possible that setting the plugin to empty would work. YMMV. I didn't try this. So this is an alternative.)

UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

Otherwise:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';

Then

FLUSH PRIVILEGES;

For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant statement exactly right - easier to simply update the root you already have.

In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.

By default, root user is set to authenticate through an auth_socket rather than with a password.

In order to use the password authentication, do:

1. Login to MySQL root shell:

   sudo mysql
   

2. Check authentication methods enabled for different users (optional)

   SELECT * FROM mysql.user;
   

3. Make root to authenticate with a password:

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
   

4. Flush privileges for the changes to take effect;

   FLUSH PRIVILEGES;
   

5. Exit and authenticate with your password

    exit
    mysql -u root -p 
   

If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost. .

The only way to set password is to switch to native authentication like:

$ sudo mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';