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.1When 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. 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. 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. I'd still be reluctant to open MySQL to the Internet. If it's an MS environment, can you use a VPN? Also, depending on the user sophistication, the puTTY ssh client can open tunnels from Windows, using the same public/private keys as OpenSSH. I use it to connect to my home IMAP server from clients, with my private key on a USB drive.