Problem in accessing to Mysql

Question

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.

Answer 1

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

Answer 2

Try this step by step first remove mysql from your machine then just install it by sudo apt-get install mysql-server you'll have ask to set a password.

sudo apt-get remove --purge mysql && sudo apt-get purge mysql && sudo apt-get autoremove && sudo apt-get autoclean && sudo apt-get remove dbconfig-mysql && sudo apt-get dist-upgrade && sudo apt-get install mysql-server