How to Fix Access Denied Errors in MySQL

/ Category: Tutorials / Comments: 5

How-to-Fix-Access-Denied-Errors-in-MySQL
Spread the love

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 login successfully after executing either of the two commands above.

Of course you don’t have to do any of this if you use one of our Linux VPS Hosting services, in which case you can simply ask our expert Linux admins to fix these MySQL errors for you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

Feedback

  Comments: 5


  1. But I don’t want to grant ALL PRIVILEGES to the user??


  2. it shows Access denied to root


  3. When I run the commmand in step 6 it says:
    The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement

Your feedback