[gentoo-user] Network access to MySQL

[gentoo-user] Network access to MySQL

[Peter Humphrey]

Having just installed mysql on my server, I've found that I have to set 
bind-address = 0.0.0.0 in /etc/mysql/my.cnf to enable me to connect to 
mysqld over the local network: leaving it at the default 127.0.0.1 causes 
connection requests to be rejected.

Is there a more secure value for this parameter? I want to be able to 
connect over either of two network segments, 192.168.2.0/29 and 
192.168.3.0/29, as well as locally on the server box. I've tried a compound 
setting in bind-address, but mysqld then refuses to start. 0.0.0.0 is the 
only setting I've found so far that lets me in.

-- 
Rgds
Peter
-- 
gentoo-user@lists.gentoo.org mailing list

Re: [gentoo-user] Network access to MySQL

[Hamish]

On Tuesday 29 April 2008 10:51:30 Peter Humphrey wrote:
> Having just installed mysql on my server, I've found that I have to set
> bind-address = 0.0.0.0 in /etc/mysql/my.cnf to enable me to connect to
> mysqld over the local network: leaving it at the default 127.0.0.1 causes
> connection requests to be rejected.
>

Yes. because 127.0.0.1 is the address of the LOOPBACK interface. An INTERNAL 
only pseudo network interface. 0.0.0.0 means that the local socket will be 
bound to no specific interface, and thus will accept inbound connections TO 
any IP address configured on the system.

> Is there a more secure value for this parameter? I want to be able to
> connect over either of two network segments, 192.168.2.0/29 and
> 192.168.3.0/29, as well as locally on the server box. I've tried a compound
> setting in bind-address, but mysqld then refuses to start. 0.0.0.0 is the
> only setting I've found so far that lets me in.
>

Sorry, the question doesn't make sense... The security for connecting to the 
database is performed elsewhere. Either using IPTables (And specifying who 
can connect), and/or userid's with passwords specified for explicit hosts 
(Read up on the grant syntax in the mysql manual for details of granting 
access and how to limit. 

I'd recommend BOTH iptable and limited userid's myself.

Hamish.
-- 
gentoo-user@lists.gentoo.org mailing list

Re: [gentoo-user] Network access to MySQL

[kashani]

Peter Humphrey wrote:
> Having just installed mysql on my server, I've found that I have to set 
> bind-address = 0.0.0.0 in /etc/mysql/my.cnf to enable me to connect to 
> mysqld over the local network: leaving it at the default 127.0.0.1 causes 
> connection requests to be rejected.
> 
> Is there a more secure value for this parameter? I want to be able to 
> connect over either of two network segments, 192.168.2.0/29 and 
> 192.168.3.0/29, as well as locally on the server box. I've tried a compound 
> setting in bind-address, but mysqld then refuses to start. 0.0.0.0 is the 
> only setting I've found so far that lets me in.
> 

I generally remove the bind setting so that Mysql listens on all IPs on 
the box. You can then have firewall rules at your border or locally on 
the box to control access to 3306. You can also set access on a per user 
basis within mysql

GRANT CREATE,DELETE,INSERT,SELECT,UPDATE PRIVILEGES ON your_db.* TO 
'your_user'@'localhost';
GRANT CREATE,DELETE,INSERT,SELECT,UPDATE PRIVILEGES ON your_db.* TO 
'your_user'@'192.168.2.%';

and so on.

kashani
-- 
gentoo-user@lists.gentoo.org mailing list

Re: [gentoo-user] Network access to MySQL

[Mick]

[-- Attachment #1: Type: text/plain, Size: 1857 bytes --]

On Tuesday 29 April 2008, kashani wrote:
> Peter Humphrey wrote:
> > Having just installed mysql on my server, I've found that I have to set
> > bind-address = 0.0.0.0 in /etc/mysql/my.cnf to enable me to connect to
> > mysqld over the local network: leaving it at the default 127.0.0.1 causes
> > connection requests to be rejected.
> >
> > Is there a more secure value for this parameter? I want to be able to
> > connect over either of two network segments, 192.168.2.0/29 and
> > 192.168.3.0/29, as well as locally on the server box. I've tried a
> > compound setting in bind-address, but mysqld then refuses to start.
> > 0.0.0.0 is the only setting I've found so far that lets me in.
>
> I generally remove the bind setting so that Mysql listens on all IPs on
> the box. You can then have firewall rules at your border or locally on
> the box to control access to 3306. You can also set access on a per user
> basis within mysql
>
> GRANT CREATE,DELETE,INSERT,SELECT,UPDATE PRIVILEGES ON your_db.* TO
> 'your_user'@'localhost';
> GRANT CREATE,DELETE,INSERT,SELECT,UPDATE PRIVILEGES ON your_db.* TO
> 'your_user'@'192.168.2.%';
>
> and so on.
>
> kashani

The --bind-address option works for one IP address only.  If you set it to 
127.0.0.1 only connections from localhost will be listened to.  If you set it 
to 0.0.0.0 connections from all addresses are listened to.  I believe that 
you can have one IP address set by using --bind-address and also have defined 
a unix socket for mysqld to listen to.  The unix socket can be set up for 
local connections (you need to allow fs access for the unix socket to the 
mysql client user of course, otherwise the socket will not be accessible).

As others have mentioned the firewall adds security by restricting inbound 
source addresses.

HTH.
-- 
Regards,
Mick

[-- Attachment #2: This is a digitally signed message part. --]
[-- Type: application/pgp-signature, Size: 197 bytes --]

Re: [gentoo-user] Network access to MySQL

[Peter Humphrey]

On Tuesday 29 April 2008 22:40:09 Mick wrote:

> HTH.

Thanks to both of you. I'll leave the bind address unspecific.

-- 
Rgds
Peter
-- 
gentoo-user@lists.gentoo.org mailing list

[gentoo-user] Network access to mysql

[Peter Humphrey]

Afternoon all,

I have mysql running on my workstation and on my local server, and I want to 
connect as an ordinary user from the workstation to the server; I can't. 
This is what happens:

$ mysql -p -h serv.ethnet
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'serv.ethnet' (111)

The same thing happens if I try as root.

I can connect locally as myself or as root on either machine and manipulate 
tables in various ways. I haven't yet installed a firewall on either 
machine.

I've set DEBUG=4 in /etc/conf.d/mysql on both machines, but nothing shows up 
in /var/log/mysql/*; only some startup debug messages. I've run tcpdump on 
the server, which shows that one packet passes in each direction, followed 
immediately by a reverse lookup of the workstation being sent to the name 
server. I don't know why nothing happens after the name-service request is 
answered, but it seems to imply that the workstation is refusing the 
request itself rather than forwarding it to the server.

I can't see anything in /etc/conf.d/mysql or in /etc/mysql/* on either 
machine to restrict network access, so what have I missed?

-- 
Rgds
Peter

Re: [gentoo-user] Network access to mysql

[AllenJB]

Peter Humphrey wrote:
> Afternoon all,
> 
> I have mysql running on my workstation and on my local server, and I want to 
> connect as an ordinary user from the workstation to the server; I can't. 
> This is what happens:
> 
> $ mysql -p -h serv.ethnet
> Enter password:
> ERROR 2003 (HY000): Can't connect to MySQL server on 'serv.ethnet' (111)
> 
> The same thing happens if I try as root.
> 
> I can connect locally as myself or as root on either machine and manipulate 
> tables in various ways. I haven't yet installed a firewall on either 
> machine.
> 
> I've set DEBUG=4 in /etc/conf.d/mysql on both machines, but nothing shows up 
> in /var/log/mysql/*; only some startup debug messages. I've run tcpdump on 
> the server, which shows that one packet passes in each direction, followed 
> immediately by a reverse lookup of the workstation being sent to the name 
> server. I don't know why nothing happens after the name-service request is 
> answered, but it seems to imply that the workstation is refusing the 
> request itself rather than forwarding it to the server.
> 
> I can't see anything in /etc/conf.d/mysql or in /etc/mysql/* on either 
> machine to restrict network access, so what have I missed?
> 

Check the bind-address setting in /etc/my.cnf - if this is 127.0.0.1 
then no other machines will be able to connect to the mysql server. To 
listen on all available interfaces, this setting should be "0.0.0.0" or 
unset.

Also check that skip-networking is not enabled.

Re: [gentoo-user] Network access to mysql

[Alejandro]

[-- Attachment #1: Type: text/plain, Size: 1584 bytes --]

2009/1/28 AllenJB <gentoo-lists@allenjb.me.uk>

> Peter Humphrey wrote:
>
>> Afternoon all,
>>
>> I have mysql running on my workstation and on my local server, and I want
>> to connect as an ordinary user from the workstation to the server; I can't.
>> This is what happens:
>>
>> $ mysql -p -h serv.ethnet
>> Enter password:
>> ERROR 2003 (HY000): Can't connect to MySQL server on 'serv.ethnet' (111)
>>
>> The same thing happens if I try as root.
>>
>> I can connect locally as myself or as root on either machine and
>> manipulate tables in various ways. I haven't yet installed a firewall on
>> either machine.
>>
>> I've set DEBUG=4 in /etc/conf.d/mysql on both machines, but nothing shows
>> up in /var/log/mysql/*; only some startup debug messages. I've run tcpdump
>> on the server, which shows that one packet passes in each direction,
>> followed immediately by a reverse lookup of the workstation being sent to
>> the name server. I don't know why nothing happens after the name-service
>> request is answered, but it seems to imply that the workstation is refusing
>> the request itself rather than forwarding it to the server.
>>
>> I can't see anything in /etc/conf.d/mysql or in /etc/mysql/* on either
>> machine to restrict network access, so what have I missed?
>>
>>
> Check the bind-address setting in /etc/my.cnf - if this is 127.0.0.1 then
> no other machines will be able to connect to the mysql server. To listen on
> all available interfaces, this setting should be "0.0.0.0" or unset.
>
> Also check that skip-networking is not enabled.
>
> Too late for me.

[-- Attachment #2: Type: text/html, Size: 2114 bytes --]

Re: [gentoo-user] Network access to mysql

[Peter Humphrey]

On Wednesday 28 January 2009 16:25:16 AllenJB wrote:

> Check the bind-address setting in /etc/my.cnf - if this is 127.0.0.1
> then no other machines will be able to connect to the mysql server. To
> listen on all available interfaces, this setting should be "0.0.0.0" or
> unset.

That was it - thanks.

I'll raise a document bug to have this added to the MySQL startup guide, 
which talks gaily of connecting to various hosts, but not how to make that 
possible.

> Also check that skip-networking is not enabled.

It's disabled by default, and I knew I didn't want to enable it.

-- 
Rgds
Peter