By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

How to configure MySQL ODBC driver to connect to a mysql server on my own side of the router?

P: n/a
MLH
Using MS Access, I have attached to MySQL servers in other states
and other countries on the other side of my router. But when I use the
MySQL ODBC driver 3.51 to connect to a MySQL server on my own LAN,
the driver tells me it cannot make the connection. Here are the ODBC
driver connection parms:

Data Source Name: (free field - name my "my linux box" will do nicely)
Host/Server Name (or IP) - something like MS**********@ServerName.net
has worked fine for me in the past. Now I'm trying 192.168.1.106
Database Name - whatever, mysqldb works fine, any valid db
User - root
Password - myrootpass
Port - 3306 has worked fine 4 me N the past. Dunno whether 2 use now
SQL Command On Connect - haven't ever put anything here

Perhaps there is some configuration in mysql user privilege for the
mysqluser "root" that would prevent him from logging from a remote PC
to the mysql server??? I just don't know. Suggestions appreciated.
Jul 23 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
MLH wrote:
Perhaps there is some configuration in mysql user privilege for the
mysqluser "root" that would prevent him from logging from a remote PC
to the mysql server??? I just don't know. Suggestions appreciated.


I'd run the Windows application MySQL Administrator (a separate download
from MySQL.com) and try to connect to the database on your Linux box
from the Windows client machine, using the account & password you want
to use in your ODBC datasource.

There shouldn't be any difference vis. the privileges whether you
connect through ODBC or through the MySQL Administrator, so this is a
reasonable test. You can also administer the privileges on your
database through this application.

Regards,
Bill K.
Jul 23 '05 #2

P: n/a
What is the exact error you get?

Two things to check:

1. Go to Start -> Run and run this command:
telnet 192.168.1.106 3306

You should get a response like:
/
4.0.12-max-log!DM9rT,}R,C

If you wait a moment, mysql will close the connection automatically.
(the string above shows which version of mysql is running on the box).
If the telnet command gives an error message (i.e., "cannot connect to
host"), then that will tell you that either mysql is not running or
there's a firewall / router rule preventing you from accessing port
3306 on the box. Note that this test is mysql-permissions independent,
since it couldn't even get as far as a user authentication.

2. If the above test is successful, then there's nothing
network/router/firewall-wise that is stopping you. Good. Now it'll be
time to check mysql user permissions. You'll probably have to do these
checks either from the Mysql Control center (as the previous poster
noted) or through the mysql client on the 192.168.1.106 box. If using
the local client, run these two queries within the "mysql" database:

mysql> SELECT Host, User from user;
.... and also...
mysql> SELECT Host, Db, User from db;

You'll be looking for some pattern in the "Host" column that indicates
that the host you are connecting *from* has access. '%' is a wildcard
meaning "all hosts". Unless that is present, there will need to be
some entry that matches your client machine.

Good luck!

Jul 23 '05 #3

P: n/a
MLH
On 1 Apr 2005 01:46:39 -0800, "Michael A. Capone" <ma******@yahoo.com>
wrote:
telnet 192.168.1.106 3306

Mr Capone. You were right! Something is wrong. I ran the telnet.
Here's what I got...
C:\WINDOWS>telnet 192.168.1.106 3306
Connecting To 192.168.1.106...Could not open connection to the host,
on port 3306: Connect failed

And, just to make sure, I ran a ping...
C:\WINDOWS>ping 192.168.1.106

Pinging 192.168.1.106 with 32 bytes of data:

Reply from 192.168.1.106: bytes=32 time<1ms TTL=64
Reply from 192.168.1.106: bytes=32 time<1ms TTL=64
Reply from 192.168.1.106: bytes=32 time<1ms TTL=64
Reply from 192.168.1.106: bytes=32 time<1ms TTL=64

Ping statistics for 192.168.1.106:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\WINDOWS>

So I'm pinging but unable to telnet. Hmmm???

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Two things to check:

1. Go to Start -> Run and run this command:
telnet 192.168.1.106 3306

You should get a response like:
/
4.0.12-max-log!DM9rT,}R,C

If you wait a moment, mysql will close the connection automatically.
(the string above shows which version of mysql is running on the box).
If the telnet command gives an error message (i.e., "cannot connect to
host"), then that will tell you that either mysql is not running or
there's a firewall / router rule preventing you from accessing port
3306 on the box. Note that this test is mysql-permissions
independent,
since it couldn't even get as far as a user authentication.

2. If the above test is successful, then there's nothing
network/router/firewall-wise that is stopping you. Good. Now it'll
be
time to check mysql user permissions. You'll probably have to do
these
checks either from the Mysql Control center (as the previous poster
noted) or through the mysql client on the 192.168.1.106 box. If using
the local client, run these two queries within the "mysql" database:

mysql> SELECT Host, User from user;
.... and also...
mysql> SELECT Host, Db, User from db;

You'll be looking for some pattern in the "Host" column that indicates
that the host you are connecting *from* has access. '%' is a wildcard
meaning "all hosts". Unless that is present, there will need to be
some entry that matches your client machine.

Good luck!

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxx
Here's the screen output from the queries you asked me to run.
I'm afraid I cannot interpret the results.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host, user from user
-> ;
+-----------+------+
| host | user |
+-----------+------+
| appserver | |
| appserver | root |
| localhost | |
| localhost | root |
+-----------+------+
4 rows in set (0.00 sec)

mysql> select host, db, user from db;
+------+---------+------+
| host | db | user |
+------+---------+------+
| % | test | |
| % | test\_% | |
+------+---------+------+
2 rows in set (0.00 sec)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxx
Mr Capone:
Someone told me that my linux box might not even be listening
for MyODBC driver traffic on port 3306. So, I ran netstat -tl and
netstat -ntl. Here's what I got...

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address
State
tcp 0 0 appserver.crci.co:mysql *:*
LISTEN
tcp 0 0 *:www *:*
LISTEN
tcp 0 0 *:ssh *:*
LISTEN
tcp 0 0 *:smtp *:*
LISTEN
mlh@appserver credifree $ netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address
State
tcp 0 0 127.0.0.1:3306 0.0.0.0:*
LISTEN
tcp 0 0 0.0.0.0:80 0.0.0.0:*
LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:*
LISTEN
tcp 0 0 0.0.0.0:25 0.0.0.0:*
LISTEN

So maybe I'm not listening to port 3306 for MyODBC driver
traffic. I'll try to find out how to configure the linux server to
listen for such traffic on port 3306. Maybe that'll help.
Jul 23 '05 #4

P: n/a
MLH wrote:
So I'm pinging but unable to telnet. Hmmm???
This is not necessarily unusual. It means that the Linux host is not
listening on port 3306, or else something is blocking connections on
that port from reaching host 106. The former is probably more likely.
So maybe I'm not listening to port 3306 for MyODBC driver
traffic. I'll try to find out how to configure the linux server to
listen for such traffic on port 3306. Maybe that'll help.


MySQL might not be listening for network connections at all, but still
be usable by local clients, e.g. PHP applications running on the same
host. The mysqld command includes a startup option "--skip-networking",
to prevent it from listening for remote client connection requests.

Try this on your Linux host, in the mysql monitor tool:

mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+

This variable should be 'OFF' if MySQL is configured to permit
connections from remote machines. If it's 'ON', then your MyODBC
connections (or any client connection from another host) won't work. If
that's the case, it's likely that MySQL was started with the
--skip-networking command line option, or else that option is specified
in one of the my.cnf files.

It's not unusual to start the MySQL service without networking support,
if the only intended MySQL client is a web application running on the
same host. The idea is that MySQL is less likely to be a security
weakness if it _can't_ accept connections from intruders.

Run the following command to see the process listing of your mysqld
process. Look for the "--skip-networking" command-line option.

# ps -efww | grep mysqld

I've included sample output of this ps command from my Linux server.
The --skip-networking option does _not_ appear.

root 5356 1 0 08:58 ? 00:00:00 /bin/sh
/usr/bin/mysqld_safe --old-passwords --datadir=/var/lib/mysql
--pid-file=/var/lib/mysql/laylah.pid
mysql 5500 5356 0 08:58 ? 00:00:00 /usr/sbin/mysqld-max
--basedir=/ --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/lib/mysql/laylah.pid --skip-locking --old-passwords

If you do see that option, it's probably specified either in the init
script (/etc/init.d/mysql), the startup script (safe_mysqld), or else
one of the my.cnf files, which on Linux typically are located in /etc or
the MySQL datadir. Here are a few commands that might help locate this
option, if it is being used:

# grep skip-networking /etc/init.d/mysql
# grep skip-networking /usr/bin/safe_mysqld
# my_print_defaults -c /etc/my.cnf -e /var/lib/mysql/my.cnf mysqld
server mysql_server mysql.server

The skip-networking option is described in these web pages:
http://dev.mysql.com/doc/mysql/en/server-options.html
http://dev.mysql.com/doc/mysql/en/pr...s-options.html

(Note the command-line option for mysqld is spelled with a "-" while the
MySQL system variable is spelled with a "_".)

Regards,
Bill K.
Jul 23 '05 #5

P: n/a
<snip>
I'm curious. What does one do if the database server isn't listening
for to the ethernet port 3306 ODBC packets trying to reach a MySQL
database? Is it a linux configuration or a mysql configuration?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxx
Mr Capone:
Someone told me that my linux box might not even be listening
for MyODBC driver traffic on port 3306. So, I ran netstat -tl and
netstat -ntl. Here's what I got...

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address
State
tcp 0 0 appserver.crci.co:mysql *:*
LISTEN
tcp 0 0 *:www *:*
LISTEN
tcp 0 0 *:ssh *:*
LISTEN
tcp 0 0 *:smtp *:*
LISTEN
mlh@appserver credifree $ netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address
State
tcp 0 0 127.0.0.1:3306 0.0.0.0:*
LISTEN
tcp 0 0 0.0.0.0:80 0.0.0.0:*
LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:*
LISTEN
tcp 0 0 0.0.0.0:25 0.0.0.0:*
LISTEN

So maybe I'm not listening to port 3306 for MyODBC driver
traffic. I'll try to find out how to configure the linux server to
listen for such traffic on port 3306. Maybe that'll help.


Jul 23 '05 #6

P: n/a
MLH
So then, until my linux server will respond to a command like
this: telnet 192.168.1.106 3306
I haven't got a snowball's chance of attaching to my mysql
tables on that same linux server, right? I'm afraid I don't know
how to configure the linux box to allow that, but that's a subject
for another news group. Let me see what I can find out and I'll
revisit this topic with this group after I can telnet in over port
3306. Thx.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx

On Mon, 04 Apr 2005 10:26:53 -0700, Bill Karwin <bi**@karwin.com>
wrote:
MLH wrote:
So I'm pinging but unable to telnet. Hmmm???


This is not necessarily unusual. It means that the Linux host is not
listening on port 3306, or else something is blocking connections on
that port from reaching host 106. The former is probably more likely.
So maybe I'm not listening to port 3306 for MyODBC driver
traffic. I'll try to find out how to configure the linux server to
listen for such traffic on port 3306. Maybe that'll help.


MySQL might not be listening for network connections at all, but still
be usable by local clients, e.g. PHP applications running on the same
host. The mysqld command includes a startup option "--skip-networking",
to prevent it from listening for remote client connection requests.

Try this on your Linux host, in the mysql monitor tool:

mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+

This variable should be 'OFF' if MySQL is configured to permit
connections from remote machines. If it's 'ON', then your MyODBC
connections (or any client connection from another host) won't work. If
that's the case, it's likely that MySQL was started with the
--skip-networking command line option, or else that option is specified
in one of the my.cnf files.

It's not unusual to start the MySQL service without networking support,
if the only intended MySQL client is a web application running on the
same host. The idea is that MySQL is less likely to be a security
weakness if it _can't_ accept connections from intruders.

Run the following command to see the process listing of your mysqld
process. Look for the "--skip-networking" command-line option.

# ps -efww | grep mysqld

I've included sample output of this ps command from my Linux server.
The --skip-networking option does _not_ appear.

root 5356 1 0 08:58 ? 00:00:00 /bin/sh
/usr/bin/mysqld_safe --old-passwords --datadir=/var/lib/mysql
--pid-file=/var/lib/mysql/laylah.pid
mysql 5500 5356 0 08:58 ? 00:00:00 /usr/sbin/mysqld-max
--basedir=/ --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/lib/mysql/laylah.pid --skip-locking --old-passwords

If you do see that option, it's probably specified either in the init
script (/etc/init.d/mysql), the startup script (safe_mysqld), or else
one of the my.cnf files, which on Linux typically are located in /etc or
the MySQL datadir. Here are a few commands that might help locate this
option, if it is being used:

# grep skip-networking /etc/init.d/mysql
# grep skip-networking /usr/bin/safe_mysqld
# my_print_defaults -c /etc/my.cnf -e /var/lib/mysql/my.cnf mysqld
server mysql_server mysql.server

The skip-networking option is described in these web pages:
http://dev.mysql.com/doc/mysql/en/server-options.html
http://dev.mysql.com/doc/mysql/en/pr...s-options.html

(Note the command-line option for mysqld is spelled with a "-" while the
MySQL system variable is spelled with a "_".)

Regards,
Bill K.


Jul 23 '05 #7

P: n/a
>>Try this on your Linux host, in the mysql monitor tool:

mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+


First thing's first: does your MySQL say 'ON' or 'OFF' when you run the
above command?

Regards,
Bill K.
Jul 23 '05 #8

P: n/a
Archie Ball wrote:
<snip>
I'm curious. What does one do if the database server isn't listening
for to the ethernet port 3306 ODBC packets trying to reach a MySQL
database? Is it a linux configuration or a mysql configuration?


Might be either.
The MySQL daemon might not be configured to listen at all, or might be
configured to listen on another port. Or it might be that Linux is
preventing network traffic from reaching that port, through firewall
configuration.

Regards,
Bill K.
Jul 23 '05 #9

P: n/a
My value is OFF.
xxxxxxxxxxxxxxxxxxxxxxxx

On Tue, 05 Apr 2005 10:08:13 -0700, Bill Karwin <bi**@karwin.com>
wrote:
Try this on your Linux host, in the mysql monitor tool:

mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+


First thing's first: does your MySQL say 'ON' or 'OFF' when you run the
above command?

Regards,
Bill K.


Jul 23 '05 #10

P: n/a
And here is a snippet from my /etc/my.cnf file...

# keep secure by default!
bind-address = 127.0.0.1
port = 3306
# this can make it even more secure:
#skip-networking

Jul 23 '05 #11

P: n/a
MLH
OFF
xxxxxxxxxxxxxxxxxxxxxxxxxxx

First thing's first: does your MySQL say 'ON' or 'OFF' when you run the
above command?

Regards,
Bill K.


Jul 23 '05 #12

P: n/a
Archie Ball wrote:
And here is a snippet from my /etc/my.cnf file...

# keep secure by default!
bind-address = 127.0.0.1
That's a good point. The bind-address option is used when you want the
MySQL server to be network-accessible, but *only* to clients running on
one specific host. In this case, 127.0.0.1 is an alias for 'localhost'.
So only clients running on the same machine as MySQL can connect.

I'd be interested to see if MLH has that same setting. That would
prevent him from connecting to MySQL from his Windows computer.
port = 3306
# this can make it even more secure:
#skip-networking


The # character is a comment character; commenting out a line like this
prevents it from taking effect.

Regards,
Bill K.
Jul 23 '05 #13

P: n/a
MLH


On Tue, 05 Apr 2005 14:14:29 -0700, Bill Karwin <bi**@karwin.com>
wrote:
Archie Ball wrote:
And here is a snippet from my /etc/my.cnf file...

# keep secure by default!
bind-address = 127.0.0.1


That's a good point. The bind-address option is used when you want the
MySQL server to be network-accessible, but *only* to clients running on
one specific host. In this case, 127.0.0.1 is an alias for 'localhost'.
So only clients running on the same machine as MySQL can connect.

I'd be interested to see if MLH has that same setting. That would
prevent him from connecting to MySQL from his Windows computer.


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Matter of fact, I do. Should I simply REM out this line with the #
character? Or, will I need to add another line in its place?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
port = 3306
# this can make it even more secure:
#skip-networking


The # character is a comment character; commenting out a line like this
prevents it from taking effect.

Regards,
Bill K.


Jul 23 '05 #14

P: n/a
MLH
!SUCCESS!

All I did was rem out the line in my.cnf with the
loopback address in it. that's it! I'm ODBC'ing
away - happy as a clam. Never had to be able
to telnet to the linux box or anything like that at
all. Just rem out that one itsy bitsy line and the
user I set up in mysql can log in remotely with
his username & pass!
Jul 23 '05 #15

P: n/a
MLH wrote:
!SUCCESS!

All I did was rem out the line in my.cnf with the
loopback address in it. that's it! I'm ODBC'ing
away - happy as a clam.
Hooray!
Never had to be able to telnet to the linux box


In case you're interested, there's nothing magic about telnet 3306.
It's just a test that someone suggested. Kind of like telephoning your
home phone number to make sure your answering machine is on, even if you
don't leave a message.

Regards,
Bill K.
Jul 23 '05 #16

P: n/a
MLH
<big snip>
A personal thanks to you, Bill, for your patience and generosity.
And thanks to the others who shared their hard-earned knowledge.
I appreciate it and hope I can one day add helpful comments to
this group.

Warmest regards,
Mike Harvell
Jul 23 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.