Re: Remote MySQL connect
Joe said...
> marc wrote:
> > Joe said...
> >> marc wrote:
> >>> What is the 'correct' way to configure MySQL for remote connections?
> >>>
> >>> The db in question is running fine and can be accessed via phpmyadmin,
> >>> amongst other things.
> >>>
> >>> The default my.cnf has:
> >>>
> >>> bind-address - 127.0.0.1
> >>>
> >>> When I comment this out (and restart the db), I can connect remotely -
> >>> so user/password and privs are fine) but this leaves the db wide open.
> >>>
> >>> Say I want to provide remote access to 192.168.0.1. Can this be done via
> >>> MySQL's config or must it be done via the firewall and removing bind-
> >>> address?
> >>>
> >>> The machine is not on a fixed IP.
> >>>
> >> I'd do it with the firewall, but MySQL has built-in provision for
> >> client IP addresses. If you use phpmyadmin to look at privileges,
> >> you'll see the users are all user@address entries, where address
> >> is usually either localhost or %, the wildcard. It's possible to
> >> create users that only have privileges from particular IP addresses,
> >> where fred@192.168.0.1 has read privileges on all or just certain
> >> databases, or just certain fields of certain tables of certain
> >> databases, if you want to go that fine. fred@192.168.0.1 is a
> >> completely separate user from fred@localhost, and may have a
> >> different password, and certainly different privileges.
> >
> > I understand how users and privileges work, but to do what you suggest
> > must I remove bind-address from my.cnf?
> >
> > There is scant documentation on bind-address - the 1,400-page ref has
> > six words: "The IP address to bind to", which is neither English nor
> > very useful.
> >
>
> It's the network interface on the database machine, and unless that
> is a two-NIC firewall device, it's not likely to be of much use to
> you. If you do have a two-NIC machine, you can set MySQL to bind to
> localhost and the address on the LAN NIC, to allow local access only.
Thanks, Joe.
So, in the absence of the syntax of "bind-address", can wildcards be
used? e.g. 192.168.0.* or 192.168.0.%
> If you leave it out the binding is to every NIC, and with only one NIC
> you either do that or bind to the NIC explicitly, which achieves the
> same result. If you do the latter, leave localhost in as well.
I tried the latter, but MySQL fails to restart. The problem being, I
suspect, that I used the wrong syntax, because, well, you get the
message ;-)
> A NIC can have more than one IP address, so it's possible to be a
> bit creative with this, but what you can't do is filter by remote
> IP address, which I assume is what you want. iptables is what you
> need there.
Ah, okay, I'll probably go that route. Seems odd that the devs put in
minimal filtering and then stopped.
> I'd still be reluctant to open MySQL to the Internet. If it's an
> MS environment, can you use a VPN?
The environment is mixed and a VPN is certainly another idea worth
looking at - or configuring the existing one to deal with it.
> Also, depending on the user
> sophistication, the puTTY ssh client can open tunnels from Windows,
> using the same public/private keys as OpenSSH.
Pfft! A command line ;-)
I had a closer look, and the GUI Windows tool in question and it allows
tunneling over SSH. A few minutes of configuring and I was able to
connect. And the MySQL bind-address remains at 127.0.0.1.
Thanks for the help. That's definitely a result. And I learned a bit
more about MySQL's config to boot.
--
Best,
Marc
Reply to: