Ubuntu – Problem in accessing to Mysql

MySQLpermissions

I have installed and reinstalled more times Mysql, but the program deny me always the access saying:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Event if I use the real password, there's no way to get into Mysql. Any suggestion? I'm thinking there's a problem with Grants, by I have no method see them, because they are in Mysql.

Best Answer

If you have installed mySQL from Ubuntu repository, the authentication of root user is not possible as usual with mysql -u root -p.

Instead, you have to type sudo mysql and the terminal will prompt you with your sudo password.

That should solve your problem and allow you to connect mySQL.

I had the same issue recently and I preferred getting back usual mysql -u root -p method (also easier to configure some tools e.g. mySQL Workbench, DBeaver...). Here are the steps I followed to get this done:

Connect to mySQL

sudo mysql

Create an admin account

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

You can stop here and use the admin user instead of root. They basically have the same access rights.

If you prefer to come back to root user, you can follow the steps below: Quit root connection and reconnect with new admin account:

quit; /* mySQL will tell you "Bye" */
mysql -u admin -p

Remove root user

drop user root@localhost;

Re-create root user with usual authentication method

CREATE USER 'root'@'localhost' IDENTIFIED BY 'the_password_you_wish_here';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

Then disconnect, re-connect with root and delete the admin account

quit; /* mySQL will tell you "Bye" */
mysql -u root -p
drop user admin@localhost;

Now you'll be able to connect with mysql -u root -p

Related Question