Access MySQL from outside

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

Access MySQL from outside

Postby supun24 » 03. November 2009 04:12

Hi.. I have installed Xampp. it's working fine. I need to access my MySql DB from outside the network (Access from any where using Internet). I have setup my router to forward 3306 port to mySql server which is 192.168.1.200. Im using dyndns for dynamic ip. it is http://supun24.go.dyndns.org.

Please anyone help me to config my MYSQL....

Thank you,
Supun Silva
supun24
 
Posts: 4
Joined: 02. November 2009 17:53

Re: Access MySQL from outside

Postby Izzy » 03. November 2009 06:34

Log in to your XAMPP's phpMyAdmin.

In the top menu select Privileges.

Click on the little edit icon at the end of the line of the user of your database only do not click on root or pma or any other user but your own database user name.

At the next screen drill down to the Change Login Information / Copy User section.

You should see:
Login Information
User name: (first box) Use text field (second box) yourdbusername <-- leave this entry intact.
Host: (first box) Local (second box) localhost <--change localhost using the first box drop menu to Any Host.
Password: (first box) Do not change the password <-- leave this selection selected in the first box.

Create a new user with the same privileges and ...
...keep the old one <-- don't change this.

Next click on Go.

Close phpMyAdmin after checking your changes have been actioned by clicking on the Home (little house) icon in the left menu then clicking once again on the Privileges menu.
You should now see Any host under the heading Host column of your database user where as before your changes it would have had localhost.

That should allow your database to be accessed from any host using the correct database credentials.
Don't forget to select Any host in place of localhost in your script's database configuration settings or you may still only be able to access from localhost - these settings are usually:
MySQL Database Name
MySQL Database User
MySQL Database User Password
MySQL Database Hosts
<-- this will have to be changed from localhost to any host in your script's configuration settings, usually in the administrators pages, depending on what script you are using.
Izzy
 
Posts: 3344
Joined: 25. April 2006 17:06

Re: Access MySQL from outside

Postby supun24 » 03. November 2009 18:33

Hi.. Thank you for your reply. All are fine. But What is the host name should I use. I don't understand which script am I using. I have dynamic IP. Therefor I am using dns: Http://supun24.go.dyndns.org. I have already redirected ports 80,3306 to mysql server. Please help me to understand which hostname should I use.

thank you,
Supun Silva
supun24
 
Posts: 4
Joined: 02. November 2009 17:53

Re: Access MySQL from outside

Postby Izzy » 03. November 2009 22:11

Did you follow the instructions in phpMyAdmin especially this line?
Host: (first box) Local (second box) localhost <--change localhost using the first box drop menu to Any Host.
Your new host name to use in phpMyAdmin is Any host.

Perhaps you could try and be more clear about what exactly you are trying to do and how you are trying to do it.
Izzy
 
Posts: 3344
Joined: 25. April 2006 17:06

Re: Access MySQL from outside

Postby supun24 » 04. November 2009 05:08

I want to connect Mysql database to Vb.net 2008 application. All are working fine under localhost. But when I run this program outside from network.. it's unable to connect. Please let me know how can I do it.

Thanks,
Supun Silva
supun24
 
Posts: 4
Joined: 02. November 2009 17:53

Re: Access MySQL from outside

Postby Izzy » 04. November 2009 05:13

Izzy wrote:Did you follow the instructions in phpMyAdmin especially this line?
Host: (first box) Local (second box) localhost <--change localhost using the first box drop menu to Any Host.
:?: :?:

What version of XAMPP?
What Windows OS?
How exactly are you trying to connect to the MySQL database?
Izzy
 
Posts: 3344
Joined: 25. April 2006 17:06

Re: Access MySQL from outside

Postby supun24 » 04. November 2009 05:27

Hi... Iam using XAMPP for Windows Version 1.7.1 on Windows XP. I have installed MySql Connector.net.
Following is my vb2008 coding

Code: Select all
Imports MySql.Data.MySqlClient ' Before  the class

   Dim conn As New MySqlConnection
      Dim myCommand As New MySqlCommand

        Dim myConnString As String
      Dim UserID As Integer

        myConnString = "server=localhost" _
            & "user id=" & txtUsername.Text & ";" _
            & "password=" & txtPassword.Text & ";" _
            & "database=home"

        conn.ConnectionString = myConnString

      Try
         conn.Open()

         myCommand.Connection = conn
         myCommand.CommandText = "SELECT user_id FROM user WHERE BINARY username = ?Username"
            'myCommand.Parameters.Add("?Username", txtUsername.Text)

         UserID = myCommand.ExecuteScalar

         conn.Close()

          Dim mainForm As New frmMain
         mainForm.UserID = UserID
            mainForm.connectionString = myConnString
         mainForm.Show()

         Me.Hide()
         Me.Close()
      Catch myerror As MySqlException
         MessageBox.Show("Error Connecting to Database: " & myerror.Message)
         conn.Dispose()
      End Try
supun24
 
Posts: 4
Joined: 02. November 2009 17:53

Re: Access MySQL from outside

Postby Izzy » 04. November 2009 05:45

myConnString = "server=localhost" _
Can you see anything wrong here?

Quote from the MySQL manual:
Host, Server, Data Source, DataSource, Address, Addr, Network Address
The name or network address of the instance of MySQL to which to connect.

Default is localhost which you can't access from outside.

So according to your first post the network address (server) that MySQL is running on is 192.168.1.200 not localhost and if you have also changed localhost to Any host in phpMyAdmin for your home database then I see no reason after changing the above myConnString line that you should not be able to connect from outside now.

Read it all hear then adjust that myConnString line for your particular needs:
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
Izzy
 
Posts: 3344
Joined: 25. April 2006 17:06

Re: Access MySQL from outside

Postby mythrix » 03. January 2011 18:07

Hey... i know this is an old thread but instead of creating a new one with the same topic i'll post here... first of all i want to say thx to Izzy for his/her replies that helped me to finally create a connection from my pc to mysql server located on my laptop,but i got a problem; i have all privileges for my database but still i can't change it from my java program(update doesn't work)

the screen from phpmyadmin with users and privileges for my database:
Image

the connection code to database:
Code: Select all
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url ="jdbc:mysql://192.168.0.148:3306/chat";
instance=DriverManager.getConnection(url, "root", "");


the update code:
Code: Select all
Connection con=DbConnection.getInstance();
      try {
         Statement s= con.createStatement();
         s.executeQuery("UPDATE user SET Status="+sx+"WHERE Username='"+ux+"'");      //sx-is type INT and ux-STRING
         DbConnection.close();
        }catch (SQLException e) {}


and the table:
Image

I can use the operation SELECT but UPDATE doesn't work;
and i have another problem... how can i connect to my mysql server from another PC with different external IP?

PS: I use xampp for windows version 1.7.3
mythrix
 
Posts: 14
Joined: 03. January 2011 17:34

Re: Access MySQL from outside

Postby Altrea » 03. January 2011 19:59

Hi mythrix,

Have you tried to give the sql Statement out as plain text after your script constructs it?
Is there a whitespace between the status value and the WHERE?

And for your other problem: If you want to request internal IPs from external, you have to use portforwarding for MySQL-port 3306 on your router/firewall.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8299
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Access MySQL from outside

Postby mythrix » 03. January 2011 21:24

hey Altrea,
thx for fast reply... before using mysql from xampp i used sql server express 2005 and i was able to update my database(it's the same syntax.. nothing changed); i tried several sql statements even without using WHERE... i don't know what is the problem since i have all privileges in phpmyadmin

and about the other thing... can you give me an example with DriverManager.getConnection(...) typing all options to be able to connect to db from all around the internetz? :D
and yes i have port 3306 forwarded; so i guess the extra option in .getConnection() method is the external ip?
mythrix
 
Posts: 14
Joined: 03. January 2011 17:34

Re: Access MySQL from outside

Postby Altrea » 03. January 2011 22:26

mythrix wrote:before using mysql from xampp i used sql server express 2005 and i was able to update my database(it's the same syntax.. nothing changed);

SQL is not in every case equal SQL between different SQL Servers. Maybe SQL Server Express 2005 don't need a whitespace at this place, but i'm very sure MySQL needs one.
But without your plain SQL Statement i can't say anything more about whats going wrong. Maybe the WHERE simply don't match because SQL Server Express 2005 uses the Searchstring in a different way.

mythrix wrote:can you give me an example with DriverManager.getConnection(...) typing all options to be able to connect to db from all around the internetz? :D

No. I can't write Java very well, especially not with (external) database connections.

mythrix wrote:i guess the extra option in .getConnection() method is the external ip?

I don't know, sorry.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8299
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Access MySQL from outside

Postby mythrix » 03. January 2011 22:45

like i said it's not the syntax.. it's the fact that mysql doesn't allow me to modify the database and this is what i want to know why.. i've tested even like this "UPDATE user SET Status=1 WHERE Username='asd'" but still not working... thx anyway
mythrix
 
Posts: 14
Joined: 03. January 2011 17:34

Re: Access MySQL from outside

Postby Altrea » 03. January 2011 22:51

So you should get an error message!?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8299
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Access MySQL from outside

Postby mythrix » 04. January 2011 17:08

Altrea wrote:So you should get an error message!?

i don't get any errors..
mythrix
 
Posts: 14
Joined: 03. January 2011 17:34

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: DarthSidus and 55 guests