The above given is a simple error which shows that the user don’t have access to login from the console but this can turn into a nightmare if you were able to login a couple of moments ago and now you are getting this error. After a couple of moment when you enter a command show databases and you only get test database and nothing else, you will certainly knock your head in the wall. but keep calm take a deep breath, think twice and then shutdown the MySQL Server.
Go to /etc/my.cnf
and enter the under given lines:
[mysqld]skip-grant-tablesskip-networking
Now restart the MySQL server, use your operating system’s commands.
Connect to my MySQL Server:
mysql
In most of the cases users needs to reset the root password for this error but in some cases where users are not very much “tech” they might need to check the host given in the mysql.user table. In my case the host of inappropriate and I had to delete the record from the table.
For those users who need to change the password of root, please follow the instructions given below:
UPDATE mysql.user SET password=password('password')
WHERE user='root' AND host='localhost';
exit
Remember you won’t be able to add a new user at this point because of the skip command in my.cnf. if you try to add a user you will MySQL: ERROR 1227 (42000): Access denied – Cannot CREATE USER.
For Users who need to remove the record with a bad host please use the instructions given below:
Connect to my MySQL Server:
mysqluse mysql;
select * from mysql.users;
select from mysql.user where Host='some_host_name';
After performing all these tasks, don’t forget to remove the top most commands from my.cnf otherwise will always boot in skip_grant mode.
