To Enable Mysql user to connect remotely via any programme (tested on Heidisql)
OS: Ubuntu 12.04 LTS
MYSQL: mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (i686) using readline 6.2
To enable user to access the DB remotely from any IP first of all edit my.cnf
i.e. choosing any editor
nano /etc/mysql/my.cnf
change the port bind address to the public address if you are using it from outside the network if being used locally put in the private address such as “ 192.168.1.xxx”
bind-address = 107.170.x.xxx (public address)
then connect mysql user with
mysql –u ‘user’ -p (where user can be whichever user you are using)
after that simply give the following commands
CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypass’;
CREATE USER ‘myuser’@’107.170.xxx.xxx’ IDENTIFIED BY ‘mypass’; ( For access from a specified IP i.e. 107.170.xxx.xxx)
CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypass’; ( For access from a all IP )
After doing this you have created a new user with a new password and that user is identified locally and can connect remotely from any IP after privileges have been awarded.
Now, run the following commands.
GRANT ALL ON *.* TO ‘myuser’@’localhost’;
GRANT ALL ON *.* TO ‘myuser’@’107.170.xxx.xxx’; ( For access from a specified IP i.e. 107.170.xxx.xxx)
GRANT ALL ON *.* TO ‘myuser’@’%’; ( For access from any IP)
Finally,
FLUSH PRIVILEGES;
And voila you are done.