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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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. [EMAIL PROTECTED] is a > >> completely separate user from [EMAIL PROTECTED], 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 -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
