Remote Database Connection

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Remote Database Connection

Postby dimitri » 29. June 2011 01:26

Alright, so I have been using XAMPP for some very basic personal hosting for a long time, everything works perfectly. However, what I would like to do is be able to access my database from a remote computer (not locally). I have MySQL Workbench on my laptop and I'll be going away for a couple of weeks on a business trip. I would like to be able to connect to my database remotely. I have already have set up a static host name and port 3306 is already being forwarded to the main computer in the office.

Here is my approach of how I think it should work.

Server: http://www.hostname.com:3306
User: root
Password: password

When I try to connect to this using MySQL WorkBench, I get an error:

FAILED to connect to MySQL at http://www.hostname.com with user root. Host is not allowed to connect to this MySQL Server.


Is there a security feature in XAMPP that doensn't allow inbound connections or something? Or is it something I'm missing completely to my approach.

I need help! :)
dimitri
 
Posts: 3
Joined: 07. November 2010 07:04

Re: Remote Database Connection

Postby JonB » 29. June 2011 02:23

You probably have to change your socket type.

by default MySQL is configured to work with a local socket, if you want to make a remote connection you have to switch to a TCP/IP socket to make a connection

Did you change that????

If you don't know what I mean or that does not sound familiar, read this

viewtopic.php?f=16&t=42567&p=167604&hilit=Mysql+socket#p167604

http://serverfault.com/questions/52794/ ... sing-xampp


BTW, I use mySQL Workbench myself - very cool (so is Navicat)
8)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Remote Database Connection

Postby Sharley » 29. June 2011 02:39

I am not sure which version of XAMPP you are using so I will use a general setting change that should work in most XAMPP versions.

Open phpMyAdmin http://localhost/phpmyadmin and click on the Privileges tab and select the user's edit button, at the end of the line of root in your case, and in the Edit Privileges window locate the Change Login Information / Copy User box.
You may see a root for 127.0.0.1 and Localhost so you may wish to repeat the following for both instances.

Locate the Host: line and from the drop box change from Local to Any Host which should put a % character in the box next to the one you just changed.

If required then enter your password twice and select the Go button associated with the Change Login Information / Copy User box.
The password can be skipped if you are not changing the password.

Restart MySQL and then test your remote access again.

Remember to return this setting change back to Local after you have completed your trip so as to again give the root user a modicum of security.


BTW it is not advisable to use the super user root to access a specific database but rather you should create a user name and password specifically for accessing specific databases and then grant specific privileges for that user - root user has absolute access to all databases and could pose a security threat if it were to get into the wrong hands, alternatively make sure your root user password is kept safe and is of sufficient strength as to be as hard to discover as possible - the root user password change is best implemented using the http://localhost/security pages.

Good luck and have a nice trip. :)

Edit - JonB posted while I was compiling this, no conflict or second opinion intended. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: Remote Database Connection

Postby dimitri » 29. June 2011 03:28

WOW!

That worked perfectly! I just created a new user called admin and set a password for it. I'll be using the admin user for this purpose and root user for local purposes :)

Thanks a lot for the help guys! :):)
dimitri
 
Posts: 3
Joined: 07. November 2010 07:04


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 120 guests