Connect to remote database
Say I wanted to run a script on example.com. Example.com doesn't allow me to have a database and the script requires one. Is it possible to use a database hosted on example2.com if I know the username, password etc?
If so, how would I connect to it? Where I usually say localhost, would I type example2.com? The database is MySQL.
Thanks!
Abhishek Reddy posted this at 06:46 — 14th October 2006.
He has: 3,348 posts
Joined: Jul 2001
Yes, you would use example2.com instead of localhost.
It's pretty much the same as connecting to localhost, as long as the MySQL server is listening for incoming TCP connections on port 3306 (or whatever) not only localhost. In your my.cnf file, make sure skip-networking and bind-address are commented out.
You'll also want to make sure [email protected] is granted sufficient privileges on the database, not just youruser@localhost.
It's a good idea to specifically grant access and permit connections only from known remote hosts. Grant privileges to [email protected] or youruser@IP and not youruser@%. Use /etc/hosts.deny and /etc/hosts.allow, or a firewall, to refuse mysql service connections from unknown hosts.
Comments in the documentation are helpful if you have problems.
teammatt3 posted this at 17:09 — 15th October 2006.
He has: 2,102 posts
Joined: Sep 2003
Would I have to talk to my host to make sure MySQL is listening for incoming TCP connections? Or is that something I can do (I'm on shared hosting)? I looked in my account under /etc and saw no file called my.cnf, am I supposed to make it? Or is it located on the server somewhere where I can't get to it (once again, I'm on a shared hosting account.)
I did create a host.allow and deny file without a problem, but I can't test it if it works :S
Abhishek Reddy posted this at 01:30 — 16th October 2006.
He has: 3,348 posts
Joined: Jul 2001
Looks like you will have to ask them. There's a good chance they will refuse as it's shared, though.
Normally you would get a virtual dedicated server or similar with root access (which I assumed) to run your own services like MySQL and Apache, if you want to modify main configuration options.
Check with your host anyway.
FrankR posted this at 16:58 — 25th October 2006.
He has: 45 posts
Joined: Oct 2006
Many hosting providers do not let their MySQL servers accept traffic from outside the firewall. There have been authentication bypass vulnerabilities in the past and it is just good practice to protect the database server from the outside world. However, if your hosting provider allows SSH access then you can use it to connect from your computer directly to the MySQL server through SSH port forwarding. That is, you use SSH to securely connect behind the firewall (think VPN).
If your provider is concerned about password security and SSH then they may permit you to use public key authentication. Do a little homework as to the options and then ask your hosting provider. Many will make reasonable accommodations to customers with particular needs.
I hope this helps.
Author of SQL Converter for Excel, which is an Excel add-in for converting data to MySQL.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.