Page 1 of 2

remote database connection problem

Posted: Tue Jul 25, 2006 7:46 am
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]

Posted: Tue Jul 25, 2006 7:50 am
by jamiel
Whats the exact error you get when trying to connect?

Posted: Tue Jul 25, 2006 8:03 am
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]

Posted: Tue Jul 25, 2006 8:07 am
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.

Posted: Tue Jul 25, 2006 8:13 am
by seaten
this is the error message :

ERROR 2003 (HY000): Can't connect to MySQL server on my ip address10060)

Posted: Tue Jul 25, 2006 8:28 am
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.

Posted: Tue Jul 25, 2006 8:36 am
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

Posted: Tue Jul 25, 2006 8:39 am
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.

Posted: Tue Jul 25, 2006 8:49 am
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.

Posted: Tue Jul 25, 2006 8:53 am
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]

Posted: Tue Jul 25, 2006 8:54 am
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

Posted: Tue Jul 25, 2006 9:11 am
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?

Posted: Tue Jul 25, 2006 9:56 am
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

Posted: Tue Jul 25, 2006 10:09 am
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.

Posted: Tue Jul 25, 2006 10:12 am
by Benjamin
If your still having issues post the contents of your my.cnf please.