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

[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 MSQLUserName@Se rverName.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.

[Bill Karwin]

Re: How to configure MySQL ODBC driver to connect to a mysql serveron my own side of the router?

MLH wrote:[color=blue]
> 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.[/color]

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.

[Michael A. Capone]

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

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!

[MLH]

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

On 1 Apr 2005 01:46:39 -0800, "Michael A. Capone" <macapone@yahoo .com>
wrote:
[color=blue]
>telnet 192.168.1.106 3306[/color]
Mr Capone. You were right! Something is wrong. I ran the telnet.
Here's what I got...
C:\WINDOWS>teln et 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???

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxx
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!

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxx
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)

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxx
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.

[Bill Karwin]

Re: How to configure MySQL ODBC driver to connect to a mysql serveron my own side of the router?

MLH wrote:[color=blue]
> So I'm pinging but unable to telnet. Hmmm???[/color]

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.
[color=blue]
> 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.[/color]

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_networkin g';
+-----------------+-------+
| 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_defaul ts -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/privileges-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.

[Archie Ball]

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

<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?

[color=blue]
>xxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxx
>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@appserve r 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.[/color]

[MLH]

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

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.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx

On Mon, 04 Apr 2005 10:26:53 -0700, Bill Karwin <bill@karwin.co m>
wrote:
[color=blue]
>MLH wrote:[color=green]
>> So I'm pinging but unable to telnet. Hmmm???[/color]
>
>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.
>[color=green]
>> 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.[/color]
>
>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_networkin g';
> +-----------------+-------+
> | 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_defaul ts -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.[/color]

[Bill Karwin]

Re: How to configure MySQL ODBC driver to connect to a mysql serveron my own side of the router?

>>Try this on your Linux host, in the mysql monitor tool:[color=blue][color=green]
>>
>> mysql> show variables like 'skip_networkin g';
>> +-----------------+-------+
>> | Variable_name | Value |
>> +-----------------+-------+
>> | skip_networking | OFF |
>> +-----------------+-------+[/color][/color]

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

Regards,
Bill K.

[Bill Karwin]

Re: How to configure MySQL ODBC driver to connect to a mysql serveron my own side of the router?

Archie Ball wrote:[color=blue]
> <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?[/color]

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.

[Archie Ball]

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

My value is OFF.
xxxxxxxxxxxxxxx xxxxxxxxx

On Tue, 05 Apr 2005 10:08:13 -0700, Bill Karwin <bill@karwin.co m>
wrote:
[color=blue][color=green][color=darkred]
>>>Try this on your Linux host, in the mysql monitor tool:
>>>
>>> mysql> show variables like 'skip_networkin g';
>>> +-----------------+-------+
>>> | Variable_name | Value |
>>> +-----------------+-------+
>>> | skip_networking | OFF |
>>> +-----------------+-------+[/color][/color]
>
>First thing's first: does your MySQL say 'ON' or 'OFF' when you run the
>above command?
>
>Regards,
>Bill K.[/color]

[Archie Ball]

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

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

[MLH]

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

OFF
xxxxxxxxxxxxxxx xxxxxxxxxxxx[color=blue]
>
>First thing's first: does your MySQL say 'ON' or 'OFF' when you run the
>above command?
>
>Regards,
>Bill K.[/color]

[Bill Karwin]

Re: How to configure MySQL ODBC driver to connect to a mysql serveron my own side of the router?

Archie Ball wrote:[color=blue]
> And here is a snippet from my /etc/my.cnf file...
>
> # keep secure by default!
> bind-address = 127.0.0.1[/color]

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.
[color=blue]
> port = 3306
> # this can make it even more secure:
> #skip-networking[/color]

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

Regards,
Bill K.

[MLH]

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



On Tue, 05 Apr 2005 14:14:29 -0700, Bill Karwin <bill@karwin.co m>
wrote:
[color=blue]
>Archie Ball wrote:[color=green]
>> And here is a snippet from my /etc/my.cnf file...
>>
>> # keep secure by default!
>> bind-address = 127.0.0.1[/color]
>
>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.
>[/color]

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx
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?
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx
[color=blue][color=green]
>> port = 3306
>> # this can make it even more secure:
>> #skip-networking[/color]
>
>The # character is a comment character; commenting out a line like this
>prevents it from taking effect.
>
>Regards,
>Bill K.[/color]

[MLH]

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

!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!