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



Question:

I just installed Ubuntu 16.04 LTS along with the packages php, mariadb and nginx. I ran mysql_secure_installation and changed the root password.

Now when I try to login to mysql using the root account while logged in Ubuntu as normal user account I get access denied.

When I login using sudo mysql, mysql doesn't even ask me password. If I run mysql_secure_installtion I see that old settings never got set permanently.

What am I doing wrong?


Solution:1

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'@'%' 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'@'%';  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

I hope this will help someone :)


Solution:2

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;  


Solution:3

To expand on what @Zarco said, you can use sudo mysql to operate as root temporarily if you have sudo access on the machine. It looks like the standard is to use the plugin field now to do unix_socket authentication on MySQL.


Solution:4

Try this code first,

echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql  echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql   echo "FLUSH PRIVILEGES;" >> your_init_file.sql  

and then,

killall mysqld  mysqld_safe --init-file=$PWD/your_init_file.sql  

then press Ctrl+Z and type: bg to run the process from the foreground into the background, then verify your access by:

mysql -u root -proot  mysql> show grants;  


Solution:5

Try to create new mysql account, for me it has worked (mysql 5.7.12):

  1. Login as sudo:

    sudo mysql -uroot  
  2. Create new user and grant him privileges (no password):

    CREATE USER 'admin'@'localhost' IDENTIFIED BY '';  GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';  
  3. Login as new user:

    mysql -uadmin  


Solution:6

I had the same issue and running the following fixed it:

mysql_upgrade --force  


Solution:7

Did you set up a password for the root user? I got the same problem. A possible workaround could be to set up your configuration (rest of users you will use, create new DBs, etc...) using sudo to operate with the root user. For standard operations with non-root users I am not experiencing any problem.

Hope this help.


Solution:8

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';


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »