How to Fix Access Denied Errors in MySQL

MySQL is one of the most popular relational database management systems (RDBMS). It is open-source and it is used by many individuals and organizations. If you are using our Linux Cloud VPS hosting services, you are most likely using MySQL as a database system. Therefore, it is important for you to know how to operate with your database system when you need to make some changes in your databases. Today, we will have a very simple task – to teach you how to fix access denied errors in your MySQL database system using these simple commands from your Linux VPS.

Fixing access denied for ‘root’@’localhost’

So you’ve locked yourself out of your root MySQL account and MySQL won’t let you in?
No problem by following these simple instructions you’re guaranteed to restore access to your root MySQL account:

1. Edit the /etc/my.cnf file
2. Under [mysqld] add skip-grant-tables
3. Restart your MySQL server
4. You should be able to login to mysql now using the below command mysql -u root -p
5. Run flush privileges; inside the MySQL shell
6. Set new password with ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘your_new_password’;
7. Edit /etc/my.cnf and remove the skip-grant-tables line
8. Restart MySQL again
9. Now you can login with your new MySQL password using mysql -u root -p

Fixing access denied for ‘user’@’ip_address’

Sometimes you may want to add a remote user to MySQL, you try to connect remotely but your MySQL server returns something like access denied for ‘user’@’192.168.0.10’.
The most probable cause for this is that you didn’t grant the host or ip address of the user permission to use the server.
You can do that by typing in the following commands in the MySQL shell:

grant all privileges on *.test_db to 'user'@'192.168.0.10';
flush privileges;

In the example above we gave “all privileges” to the user with ip address 192.168.0.10 on the database test_db.
If you do not want to restrict the user to one ip address in MySQL you can use the percentage symbol to specify to the MySQL server that the user can log in from any ip address or host:

grant all privileges on *.test_db to 'user'@'%';
flush privileges;

Now your user has rights to access the test_db database using any ip address or host.

Fixing access denied for ‘user’@’%’ with correct password and privileges

This time you have the right permissions and the right password but MySQL still won’t let you in.
If you’re trying to login from localhost then this problem arises because you have an anonymous user that matches ‘user’@’%’.
The remedy to this problem is to drop the anonymous user and we can do that by issuing either of the following two commands:

drop user ''@'localhost';
drop user ''@'127.0.0.1';

You should be able to log in successfully after executing either of the two commands above.

By now, we hope you’ve learned how to fix access denied errors in MySQL from our post. Now, it’s your opportunity to share:

Did you find any of the steps confusing, or do you think we left something out?

Could you share any unique topics or tutorial requests you’d like us to consider for our upcoming posts?

We look forward to hearing your thoughts, so please leave a comment below.