Setting up MySQL and remote ODBC - more of an answer than a question!!

[NZMarke]

Hi there,

Thought I'd post this, as I was having issues connecting to a MySQL server via a remote ODBC connection, and none of the other posts that I read appeared to have the answer to the problem I was having connecting. I posted a trouble ticket, and in double-quick time had an answer. So in thanks and payment I thought I'd list the answer here in case someone else has the same problem in the future.

I'd set up the connection to use:

Server : www.mydomainnameonJag.com
User : MySQLusername
Password : yeahlikeimgoingtopostTHAThere! ;-) (but it was the MySQL p/w)

In addition, I set the port number to 3306 (which is actually the default I understand).

However I was still getting bounced. Turns out that you need to configure to allow your remote IP address access to the server's MySQL port/database. You do that through CPanel by going into the "MySQL Database" settings, and
down the bottom, adding your remote IP address to the list of "Access Hosts". Let's hope your remote IP address is static rather than dynamic (although I think you can enter a range - if you know what your dynamic range is).

I know it's simple and "newbie" ... but it caught me out, and there might be other simple newbies like me who come along later with the same problem!

- and out,

Mark

[NZMarke]

Further to the previous message ... I found another couple of problems (and solutions) so thought I'd add them to this thread.

I initially installed the 3.51.12 version of the MySQL ODBC Connector. Turns out there's a bug with this version that causes MS-Access not to connect (gives a "User disconnected" message on trying to connect). The solution is to download the updated version of the driver from:
ftp://ftp.mysql.com/pub/mysql/hidden/connectors/odbc

The version I used was 3.51.13.

It's a good idea to deinstall the previous version - otherwise things seem to get confused. Then reinstall the new one.

I then got another connection problem trying to connect:
Error 1044 access denied.

Turned out I had the tablename wrong. It was:
_myJaguser_dbname
.. and it needed the leading underscore removed.

After that, sweet as a nut!

Cheers and hth someone,

Mark

[Ron]

Great documentation. Thanks!

[kandukury]

This is a great documentation. It really helped me to configure MySQL - ODBC. Please make this thread as sticky.

Satya

[kandukury]

One more question, doest MySQL -ODBC drivers works through a Proxy Server? I am behind a company firewall and Proxy Server.
I could connect through the home, but not from the office.

Satya

[Gwaihir]

Usually not. It depends on the configuration of the firewall+proxyserver. What they tend to do is block all traffic + reroute some traffic they allow via the proxy. The MySQL traffic to port 3306 usually isn't in that allowed mix. You need to get your administrator to open a route to port 3306 for you (either via the proxy or directly).

[olokiop]

We're all n00bies! Except MAYBE Greg, he does own the place...

[kandukury]

thanks to all for excellent replies.

Satya