MySql Database Access Remotely

Home / Articles / MySql Database Access Remotely

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please Answer to Proceed? *