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?
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