Re: Restrict MySQL server 4/5 to single IP
On 4/10/2006 8:50 PM, Yves Goergen wrote: On 10.04.2006 18:32 (+0100), Jorrit Kronjee wrote: I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? Yes, as I have explained earlier in this thread. Hostnames resolve into IP adresses, which are used to connect to the MySQL server. MySQL doesn't care if you connect via a hostname or via an IP address. It's not virtual hosting like Apache does. - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 11.04.2006 09:35 (+0100), Jorrit Kronjee wrote: Hostnames resolve into IP adresses, which are used to connect to the MySQL server. MySQL doesn't care if you connect via a hostname or via an IP address. It's not virtual hosting like Apache does. I know that. But to access different MySQL servers through different hostnames, you need different IP addresses for them to listen on. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 23:40 (+0100), Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. I'm missing the part to connect to MySQL 4.0 via mysql4.mydomain and to MySQL 5.0 via mysql5.mydomain... But I'll try to do it by restricting access to the primary hostname/IP for now. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 09.04.2006 23:40 (+0100), Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. I'm missing the part to connect to MySQL 4.0 via mysql4.mydomain and to MySQL 5.0 via mysql5.mydomain... But I'll try to do it by restricting access to the primary hostname/IP for now. Yves, I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 10.04.2006 18:32 (+0100), Jorrit Kronjee wrote: I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? Yes, as I have explained earlier in this thread. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 01:03 (+0100), Eric Braswell wrote: Does that make sense? Did I misunderstand? That's exactly what I'm doing right now. In my test network: MySQL 4.0 - 192.168.0.32 (mysql4.myhost) MySQL 5.0 - 192.168.0.33 (mysql5.myhost) But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 to a) keep both servers on different IPs with DNS names and b) let system applications connect via the localhost interface which lets me put additional security into it by only allowing access from localhost for these applications, while all other users may connect from everywhere. And of course, connecting to 127.0.0.1 is the obvious way in a small webhosting environment, but that's not so important, I can tell my users to change their database connection to the new name (mysql4) when I migrate to the new server. Or is there another way to only allow certain users to connect from localhost? As I think about it, a local TCP forwarder would accept connections on localhost, but MySQL won't see that, so this wouldn't work anyway. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 I'm going to assume you are using some kind of Unix-like platform. When you connect to localhost, you are actually connecting by default through a Unix socket file, not TCP/IP, because it is much faster. Thus it is perfectly possible to do what you outline without having to specify multiple IPs in the bind-address option. External connections will use TCP/IP, internal will use a unix socket file. If you wanted to use -only- the unix socket file to connect to a particular instance, thus completely disallowing external connections, you can use the skip-networking option. I think the clearest description of this is actually: http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html As you can see, the same concept works on Windows, except through named pipes. To verify what type of connection you are using, use the status command. You'll see a line like: Connection: Localhost via UNIX socket -or- Connection: somehostname via TCP/IP Or is there another way to only allow certain users to connect from localhost? Of course. Grant privileges only to connect to localhost. See http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html Or use skip-networking to turn off TCP/IP for that instance. As I think about it, a local TCP forwarder would accept connections on localhost, but MySQL won't see that, so this wouldn't work anyway. Completely unnecessary! You are far better off simply using the default behaviour of connecting through a socket, not TCP/IP, when connecting from the same machine. What you are wanting to do is not that unusual, and I'm confident you'll find there are ample options to get the setup that works best for you. I strongly suggest you read up in the manual on how MySQL handles TCP/IP and local connections. Eric -- Eric Braswell Web Manager MySQL AB Cupertino, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 20:02 (+0100), Eric Braswell wrote: I'm going to assume you are using some kind of Unix-like platform. Correct, it's a Debian Linux x86. When you connect to localhost, you are actually connecting by default through a Unix socket file, not TCP/IP, because it is much faster. Thus it is perfectly possible to do what you outline without having to specify multiple IPs in the bind-address option. External connections will use TCP/IP, internal will use a unix socket file. I know this strange behaviour, but it's not working for me. Because I'm running two servers, I needed to alter the socket names so that in the end, clients won't find the default socket location anymore. So I'm not connecting to localhost but to 127.0.0.1, which works again. If you wanted to use -only- the unix socket file to connect to a particular instance, thus completely disallowing external connections, you can use the skip-networking option. None of the MySQL servers should not be reachable by TCP/IP at all, so skip-networking is not what I'm looking for. Or is there another way to only allow certain users to connect from localhost? Of course. Grant privileges only to connect to localhost. You mean say localhost as hostname for the GRANT command? From my experience (which is actually not so clear in this) this doesn't work. Granting access only for connections from localhost when connecting to the server actually from the same host but through its external IP/hostname, it won't let me in. Need to test it further. I guess the correct way would be to allow access from the external IP of the server? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 09.04.2006 01:03 (+0100), Eric Braswell wrote: Does that make sense? Did I misunderstand? That's exactly what I'm doing right now. In my test network: MySQL 4.0 - 192.168.0.32 (mysql4.myhost) MySQL 5.0 - 192.168.0.33 (mysql5.myhost) But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 Yves, You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. Then your clients won't have to change anything and they can migrate to MySQL5 when they're ready. Or am I mistaken? - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. Then your clients won't have to change anything and they can migrate to MySQL5 when they're ready. Or am I mistaken? From Yves' last post, I concur with this. It's definitely not optimal -- you are substantially reducing performance to avoid a simple parameter that you could easily script (I assumed you would be prepared to simply specify the socket name as a parameter), but if your requirement is that clients must connect using default settings, then this is one way. Personally I'd find another. Eric -- Eric Braswell Web Manager MySQL AB Cupertino, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
I would be tempted to say bind-address = Comma seperated list of IP's or Just a list of such statements one per line bind-address = IP1 bind-address = IP2 . . But since you said this is not properly documented, I would encourage you to give this a shot and find it out for yourself on a Spare/Test Mysql Box. Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/8/06, Yves Goergen [EMAIL PROTECTED] wrote: On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 08.04.2006 18:31 (+0100), Kishore Jalleda wrote: bind-address = Comma seperated list of IP's Doesn't work. MySQL binds to address 255.255.255.255 instead. Also a colon-separated list does this. bind-address = IP1 bind-address = IP2 This always takes the last option, so if I add 127.0.0.1 after the other IP, it will only bind to this one. Any more suggestions? Maybe someone from the dev team? Should I install a local port forwarder? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. Why do you want to do this? Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 08.04.2006 23:14 (+0100), Eric Braswell wrote: Yves Goergen wrote: How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. Why do you want to do this? Currently, I have one IP address on my server, with one MySQL server. In the near future, I'll have a server with multiple IP addresses and I'm going to install MySQL 4.0 and 5.0 in parallel. My first design was to use different ports for both servers, but that's always a little complicated to configure for the clients. Now I want to use one IP for one MySQL server. Both are external addresses, which can be assigned with a DNS name for simple access. But the 4.0 server still has some system tasks and also for legacy reasons, I'd like to keep the 4.0 server listening on the localhost interface. This also allows me to assign more strict access rights for these system-related tasks. They can be limited to the local host instead of any host. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
You can't specify multiple IPs for bind-address. Let's back up. You want to run both 4.0 and 5.0, and you want to be able to set it up so it's 'easy' to connect to each separate instance, i.e. without having to specify different ports. This is pretty easy to do. In this case you use separate configurations for each instance (5.0 has an instance manager for this, but you can start mysql server with any arbitrary configuration file or configuration options). An example: start each mysqld4 --bind-address=192.168.1.1(more options for each basedir, datadir, etc) mysqld5 --bind-address=192.168.1.2(more options for each basedir, datadir, etc) Now if 192.168.1.1 resolves to mysql4.somedomain.com, you simply connect to that for version 4, and mysql5.somedomain.com for version 5. If you want one copy to -only- listen locally and not be accessible from an external IP, you can use --bind-address=127.0.0.1 (or some other internal-only IP). Alternatively you can use --skip-networking to not use TCP/IP, and connect to your 'local-only' copy via a socket/name pipes. See: http://dev.mysql.com/doc/refman/4.1/en/server-options.html and: http://dev.mysql.com/doc/refman/4.1/en/option-files.html If this were me, I would create two configuration files, my4.cnf and my5.cnf with these options, as well as unique settings for data directories and so on, and start the two instances by telling safe_mysqld to use the appropriate configuration file, and the appropriate mysqld binary, for each instance. Before the instance manager in 5.0, there is also mysqld_multi: http://dev.mysql.com/doc/refman/4.1/en/mysqld-multi.html, which might be of help here. Does that make sense? Did I misunderstand? -- Eric Braswell Web Manager MySQL AB Cupertino, USA Yves Goergen wrote: On 08.04.2006 23:14 (+0100), Eric Braswell wrote: Yves Goergen wrote: How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. Why do you want to do this? Currently, I have one IP address on my server, with one MySQL server. In the near future, I'll have a server with multiple IP addresses and I'm going to install MySQL 4.0 and 5.0 in parallel. My first design was to use different ports for both servers, but that's always a little complicated to configure for the clients. Now I want to use one IP for one MySQL server. Both are external addresses, which can be assigned with a DNS name for simple access. But the 4.0 server still has some system tasks and also for legacy reasons, I'd like to keep the 4.0 server listening on the localhost interface. This also allows me to assign more strict access rights for these system-related tasks. They can be limited to the local host instead of any host. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
my.cnf: bind-address = ip Will probably do the trick. -- Eric Braswell Web Manager MySQL AB Cupertino, USA Yves Goergen wrote: Hi, I have a machine with multiple IP addresses on my network interface and I have setup multiple MySQL servers on the machine, version 4.0 and 5.0. Currently, they're all listening on all IP addresses on different ports (3306 and 3307) but I'd like to make use of the second IP to make it easier to connect to each MySQL instance with different DNS names (mysql4.mydomain and mysql5.mydomain) on the default port. Only I couldn't find any hint on how to tell the MySQL server to listen only on a single IP address. I can change the port, the UNIX socket and disable IP networking entirely, but no idea how to specify a custom IP. Any hints? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. Thank you, that's what I was looking for. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]