Re-enable root access in mysql

Question

I recently installed mysql on my (cloud) server and then I ran sudo /usr/bin/mysql_secure_installation and disallowed remote root login. Then I created another user to run all my queries and gave it appropriate permissions. I can't remember the password now (or password got changed somehow) and am unable to connect to mysql. Whenever I try to connect using mysql -u user -p I get the following error

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

I tried following what answers to other question here on SO suggested but most of them require access to mysql shell.

I also tried to reconfigure using sudo dpkg-reconfigure mysql-server-5.5 (my version number is 5.5.41).

Using this I am able to set the root password but still can't connect it. I also tried running mysql in safe mode, as suggested by some user, with no luck.

Last thing I could think of was somehow renable the remote access for root, since I can reset it's password, and then reset the user password through mysql shell. So, I tried looking the /etc/mysql/my.cnf file but didn't see anything relevant.

Is there anything I am missing? How can I reset the password and get access again?

Answer 1

Stop MySQL service

#/etc/init.d/mysql stop

Exec MySQL server with --skip-grant-tables

#mysqld -u mysql --skip-grant-tables &

Exec mysql client as root

#mysql -u root

Update your password

mysql> update mysql.user set password=password('newpassword') where user='anotheruser';

Reload privileges

mysql> flush privileges;

Kill mysqld

#killall mysql

Start mysql daemon

#/etc/init.d/mysql start