remote database connection problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

seaten
Forum Newbie
Posts: 22
Joined: Thu Mar 16, 2006 1:30 pm

remote database connection problem

Post by seaten »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi guys Im having the following problem.

I have MySQL server installed on computer A, no problem I can do everything there I want.

Now from Computer B I want to access this server and use its database, but I have no luck in doing this.


After doing some research, I discovered you had to this to get it running remotely.

[syntax="sql"]grant select,insert,update, delete on db_name.* to user_name@computerB IDENTIFIED BY "user_password";
or

Code: Select all

grant select,insert,update, delete on db_name.* to user_name@ipnumberofB IDENTIFIED BY "user_password";

+ "FLUSH PRIVILEGES;"
or I could also do this by right-clicking on my users in my administration tool.


Then I thought it might be a problem with my ini configuration file. Nothing there either.

All I'm doing so far is to see if I can connect to the database.

On computer A : I do this

Code: Select all

# connect
db = MySQLdb.connect(host="localhost", user="root", passwd="****",
db="test")
Works Fine !

Computer I have tried the following:

Code: Select all

# connect
db = MySQLdb.connect(host="ipaddressofcomputerA", user="root", passwd="****",
db="test")

# connect
db = MySQLdb.connect(host="nameofcomputerA", user="root", passwd="****",
db="test")

No luck, does anyone have any ideas !


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Whats the exact error you get when trying to connect?
seaten
Forum Newbie
Posts: 22
Joined: Thu Mar 16, 2006 1:30 pm

Post by seaten »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


ok this is the error message I get.

Code: Select all

File "database.py", line 6, in ?
    db="test",port=3306)
  File "C:\Python23\Lib\site-packages\MySQLdb\__init__.py", line 64, in Connect
    return apply(Connection, args, kwargs)
  File "C:\Python23\Lib\site-packages\MySQLdb\connections.py", line 116, in __in
it__
    self._make_connection(args, kwargs2)
  File "C:\Python23\Lib\site-packages\MySQLdb\connections.py", line 41, in _make
_connection
    apply(super(ConnectionBase, self).__init__, args, kwargs)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on '19
2.168.213.108' (10060)")

#######################

most of that is python stuff, althought the actual language im using here is not the problem here. It's me not actualluy being able to connect to the database on a different computer

this is my connect statement on remote machine:

Code: Select all

#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="ipnumber", user="uname", passwd="passwd",
db="test",port=3306)
# create a cursor
print 'connected'

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Last edited by seaten on Tue Jul 25, 2006 10:36 am, edited 1 time in total.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Go Python ... do this from command line on computerB.

Code: Select all

mysql -h computerA -u username -p
Replacing the parameters with your parameters. Then enter your password when prompted.

Lets get a proper error message.
seaten
Forum Newbie
Posts: 22
Joined: Thu Mar 16, 2006 1:30 pm

Post by seaten »

this is the error message :

ERROR 2003 (HY000): Can't connect to MySQL server on my ip address10060)
Last edited by seaten on Tue Jul 25, 2006 10:35 am, edited 1 time in total.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

I dont think this is a username and password error. Is MySQL running on a non-default port? Also check iptables or any other firewall that it accepts connections on the mysql port from other machines.

To make sure its not user details. Try this:

Code: Select all

GRANT USAGE ON * TO username@ipaddressB IDENTIFIED BY 'password';
GRANT USAGE ON * TO username@hostnameB IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE ON db_name.* TO username@ipaddressB;
GRANT SELECT,INSERT,UPDATE,DELETE ON db_name.* TO username@hostnameB;
FLUSH PRIVILEGES;
But I doubt its that. More likely a connectivity issue.
seaten
Forum Newbie
Posts: 22
Joined: Thu Mar 16, 2006 1:30 pm

Post by seaten »

I was able to run your sql statements no problem.

Also Im running on the default port 3306.
Also check iptables or any other firewall that it accepts connections on the mysql port from other machines.
How do I this ?

Thanks for your help by the way
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Hope you substituted your details into those SQL statements before running them :)

Code: Select all

netstat -al | grep mysql
Should output *:mysql not localhost:mysql .

Also

Code: Select all

iptables --list
To see if you have any iptable rules.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

To save you time, if the output is localhost:mysql , open up your my.cnf (/etc/my.cnf) and remove the following line:

Code: Select all

bind-address                = 127.0.0.1
... and restart MySQL.

Bear in mind mysql now accepts external connections so never ever add user username@* .. always specify an IP address.
seaten
Forum Newbie
Posts: 22
Joined: Thu Mar 16, 2006 1:30 pm

Post by seaten »

Yes I did subsitute in all the values :D

I ran these commands on computerA:


netstat -a

returns some information for me

netstat -a | grep mysql

returns nothing

iptables is not a command that i can use !

[/b]
seaten
Forum Newbie
Posts: 22
Joined: Thu Mar 16, 2006 1:30 pm

Post by seaten »

jamiel wrote:To save you time, if the output is localhost:mysql , open up your my.cnf (/etc/my.cnf) and remove the following line:

Code: Select all

bind-address                = 127.0.0.1
... and restart MySQL.

Bear in mind mysql now accepts external connections so never ever add user username@* .. always specify an IP address.
well im doing this all on windows.

so my config file is C:\Program Files\MySQL\MySQL Server 5.0\my.ini

and now 127.0.0.1 can be found there at all
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Ahh right. Assumed from #!/usr/bin/python that you were Linux.

So is a Linux box connecting to the windows box or is it windows -> windows? Does this windows box have a firewall? What did the lines for :3306 or :mysql in netstat -a look like?
seaten
Forum Newbie
Posts: 22
Joined: Thu Mar 16, 2006 1:30 pm

Post by seaten »

sorry just back from meeting now,

this what it looks like

TCP name:3306 localhost:1111 ESTABLISHED 1111
[mysqld-nt.exe]


it's windows -> windows

and yes this box has a firewall
Last edited by seaten on Tue Jul 25, 2006 10:35 am, edited 1 time in total.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Make sure you restarted MySQL if you made that change to my.cnf earlier. Make sure port 3306 is open on the firewall.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

If your still having issues post the contents of your my.cnf please.
Post Reply