Monday, December 3, 2012

Remote Access To MySql Database



After shifting to MySql from MSSQL server and very basic but useful since I was using vb6.0 & classic asp and best for stand alone ie. Access, I realized that my apps are working like faster than anything. The data fetching became faster just as same as we fetch data from XML.

They said that it could be hacked or it is not good for heavy data storage, but personally I experienced it's performance and shifted all my apps to it. And for clients it was like boosted and updated version.

I came across facebook groups and so many forums as well I googled things to know what people do face when they are shifted or actually started using Mysql. Basically its really cool in all manners, engineering books said about ACID properties to benchmark it , still its beyond all and fair enough for desktop apps, professional LAN connected workstations and web systems.

So very first solution I'd like to put for those who might be facing similar prob is to access MySql remotely from client machine.

At first inbound and outbound rules for TCP Port 3306 at firewall should be allowed. 
(Goto Run - type wf.msc - click inbound rules - click new rule - create a rule to allow Port 3306, similarly create outbound rule)


But the problem is they could access databases through phpmyadmin just by putting url with IP of host, but their app could not connect to it.

Only Logical thing we must know about it , unless we provide access to users from each location your database could not be accessed, as users are identified by the host from which database is supposed to be accessed.

To achieve it
on your localhost go to

PRIVILEGES

Then create new user or edit existing one, with Required Privileges. And don't forget to put HOST as IP of machine from your system is supposed to accessed, you can restrict users privileges from each machine from here.

But in case it is supposed to be access from each of machine.Then choose any host or put % sign. It stands for wildcard. 



And people asked me what if they want similar thing in case of the database comes in their Web Site's hosting plan, even mostly developers try to access database from their apps in automation purpose or making some customized FTP tools. But most probably their algorithms fails just while creating database connection. This is only because their hosting providers are not providing any remote hosting or VPN kind of thing. They used to waste their productive time in playing with query like GRANT ALL PRIVILEGES

Most probably hosting panels provide their own interface to create database users,
still before all make sure that your hosting plan does include remote database 
identified by unique IP. 



Thanks
Keep Developing
Keep evolving...