If you have experienced Access denied while using Navicat or SQLYOG or any other mysql client, this post is for you.
I am assuming that, the username and password are working fine when you SSH into the machine.
Usually this happens because by default the user only has the privileges to access mysql-server on the localhost and not via remote. Yes! the same user in mysql has to be give access to localhost as well as remote. Here are the steps for ubuntu:
Run a command like below to access from all machines.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
Run a command like below to give access from specific IP.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1111.2222.3333.4444' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
You can replace
1111.2222.3333.4444 with your IP. You can run above command many times to GRANT access from multiple IPs.
You can also specify a separate USERNAME & PASSWORD for remote access.
You can check final outcome by:
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";
Finally, you may also need to run:
mysql> FLUSH PRIVILEGES;
Check connection now, it should work!