MySQL – Help! I’m locked out

Source: https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04?

I just installed MySQL and/or mariadb on Ubuntu and 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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *