Ubuntu – can’t login as thesql user root from normal user account in ubuntu 16.04

MySQLpermissions

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?

Best Answer

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

First, connect in sudo mysql
```
sudo mysql -u root
```

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 |

Delete current root@localhost account

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

Recreate your user

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

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

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

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)

Exit MySQL and try to reconnect without sudo.

I hope this will help someone :)

Related Solutions

Ubuntu – Unable to set password for the thesql “root” user

Same root password setting problem here, mine possibly caused by an unsuccessful manual installation of mysql server 5.6 previously. A thorough mysql uninstallation is no easy task. I purged/reinstalled for hours then finally solved it with

sudo dpkg --purge mysql-client-core-5.5 # or alternative version
sudo dpkg --purge mysql-client
sudo dpkg --purge mysql-server-core-5.5 # or alternative version
sudo dpkg --purge mysql-common

Basically I just type

sudo dpkg --purge mysql # followed by two tabs

Then --purge any packages the terminal auto-completes. Purge mysql-common at last because of some dependency problems.

Use above dpkg commands in addition to

sudo apt-get --purge remove mysql-server
sudo apt-get --purge remove mysql-client
sudo apt-get --purge remove mysql-common
sudo apt-get autoremove
sudo apt-get autoclean

Also I tried Greq's method

sudo rm -rf /etc/mysql

Remove the mysql folder from /var/lib

sudo rm -rf /var/lib/mysql/

At this point, to make sure mysql is fully removed, check with

which mysql
mysql --version

The first one should return no output instead of a folder. The second should return mysql is not installed instead of a version number. Otherwise the removal is still incomplete.

The significance of dpkg --purge is, when using apt-get alone, which mysql and mysql --version behave like mysql is still there.

Before reinstallation, reconfigure dpkg and update

sudo dpkg --configure -a
sudo apt-get update

Problem resolved finally. Hope it will be helpful for other people.

Ubuntu – ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Check the file: /etc/mysql/mariadb.conf.d/mysqld.cnf

verify if this line exists:

plugin-load-add = auth_socket.so

then go to MariaDB:

sudo mysql -u root

and make these modifications:

MariaDB [(none)]>use mysql;
MariaDB [(none)]>update user set plugin=' ' where User='root';
MariaDB [(none)]>flush privileges;
MariaDB [(none)]>exit

I didn't have access to mysqlworkbench, but after I applied this solution, everything ruan well...

Best Answer

Related Solutions

Ubuntu – Unable to set password for the thesql “root” user

Ubuntu – ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Related Question