mysql configuration

Sat May 2 08:32:02 UTC 2015

On 05/01/2015 12:40 PM, Mike Wright wrote:
>
>
> On 05/01/2015 08:37 AM, Chris Kottaridis wrote:
>> I am running on a fedora 21 machine. I installed mysqld from RPM. I
>> believe I have the 3306 port available thru the firewall and I used
>> systemctl to get the daemon started and it seems to be up and running. I
>> made no other configuration changes other then what the RPM installation
>> did.
>>
>> I am running in a vmware virtual host and it has 2 virtual ethernet
>> cards attached, ens33 and ens37. I have assigned IP addresses 
>> accordingly:
>>
>> ens33: 192.168.200.131
>> ens37: 192.168.223.63
>>
>> The hostname is fed21.cjk.net.
>>
>> In DNS fed21.cjk.net is assigned to 192.168.223.63.
>>
>> I have no hostname assoicated with 192.168.200.131.
>>
>> I noticed in the mysqld.log file:
>> -------------------------------------------------------
>> 2015-05-01 07:07:22 1393 [Note] Server hostname (bind-address): '*';
>> port: 3306
>> 2015-05-01 07:07:22 1393 [Note] IPv6 is available.
>> 2015-05-01 07:07:22 1393 [Note]   - '::' resolves to '::';
>> 2015-05-01 07:07:22 1393 [Note] Server socket created on IP: '::'.
>> 2015-05-01 07:07:22 1393 [Note] Event Scheduler: Loaded 0 events
>> 2015-05-01 07:07:22 1393 [Note] /usr/sbin/mysqld: ready for connections.
>> Version: '5.6.24'  socket: '/var/lib/mysql/mysql.sock'  port: 3306 MySQL
>> Community Server (GPL)
>> 2015-05-01 07:09:05 1393 [Warning] Hostname 'fed21.cjk.net' does not
>> resolve to '192.168.200.131'.
>> 2015-05-01 07:09:05 1393 [Note] Hostname 'fed21.cjk.net' has the
>> following IP addresses:
>> 2015-05-01 07:09:05 1393 [Note]  - 192.168.223.63
>> -----------------------------------------------------
>> Which as mentioned above the hostname is indeed mapped to 192.168.223.63
>> and not 192.168.200.131.
>>
>> When I try to run this script:
>> ==============================================
>> #!/usr/bin/perl
>>
>> use strict;
>> use warnings;
>> use DBI;
>>
>> # Connect to the database.
>> my $dbh = DBI->connect("DBI:mysql:database=test;host=127.0.0.1",
>>                         "joe", "joe's password",
>>                         {'RaiseError' => 1});
>>
>> # Disconnect from the database.
>> $dbh->disconnect();
>>
>> exit;
>> ===============================================
>> $ ./nosrcapp.pl
>> DBI connect('database=test;host=127.0.0.1','joe',...) failed: Host
>> '192.168.200.131' is not allowed to connect to this MySQL server at
>> ./nosrcapp.pl line 8.
>>
>> You can see I get an error immediately from the mysqld server that it
>> doesn't like 192.168.200.131, which is a little weird because I am
>> accessing through 127.0.0.1. The script didn't even get a chance to send
>> the login request. You can see if I telnet in the server immediately
>> sends the message on connection:
>>
>> $ telnet 127.0.0.1 3306
>> Trying 127.0.0.1...
>> Connected to 127.0.0.1.
>> Escape character is '^]'.
>> HHost '192.168.200.131' is not allowed to connect to this MySQL
>> serverConnection closed by foreign host.
>> $
>>
>> I set wireshark to only look at the loopback interface and it shows:
>>
>>
>>
>> While it seems weird that when sending to 127.0.0.1 the system uses a
>> source address of 192.168.200.131 instead of 127.0.0.1, but seems to
>> know to respond to 127.0.0.1, the only question I am interested in is:
>>
>> What mysqld configuration am I missing that is not allowing
>> 192.168.200.131 access ?
>>
>> Of secondary importance is if there is a way to tell mysqld to expect
>> the hostname to be mapped to 192.168.223.63 instead of 192.168.200.31,
>> but if that doesn't affect functionality then I am much less concerned
>> about that.
>>
>> This is a local environment so I have no cncerns about access and I
>> essentially want any address to be able to access from either the
>> 192.168.200 network or 192.168.223 network as well as through the 
>> loopback.
>>
>> What mysqld configuration am I missing ?
>
> Hello Chris,
>
> No expert here but I've been down this road a couple times ;)
>
> Mysql maintains its own database (called mysql) that controls access 
> to its databases, tables, columns, etc through a permission system.  
> I'm guessing that Joe hasn't been granted any of those permissions.
>
> The command you need is "GRANT".  In its simplest form it looks 
> something like this:
>
> GRANT permissions ON database at host TO user IDENTIFIED BY 'password';
>
> e.g.
>
> GRANT select,insert,update,delete ON test at localhost TO joe IDENTIFIED 
> BY "joe's password";
>
> The GRANT command holds the keys to the kingdom.  You definitely want 
> to google on "mysql and grant" before you start using it. After you've 
> experimented with the GRANT command remember to remove any test 
> privileges from the mysql database.  Whatever you do, don't lose those 
> GRANT settings or you won't be able to get back in unless you use root 
> access.  (You did remember to set a root password, didn't you?)
>
> Good luck,
> Mike Wright

Thanks Mike. From the wireshark output the error comes before the 
request to login Joe gets sent. So, it's not an issue of Joe not having 
access. What is really strange is when I tried it at my home computer, a 
real computer with one ethernet card not a VM, when I try to telnet to 
localhost I don't get the error:

$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
J
5.6.23    JfC[!/f�)^")HnroYN]hmysql_native_password

I have to hit ^C to get out of it. So, it seems the server is OK and 
allows the connection and is waiting for a request. Wireshark shows the 
source IP to be 127.0.0.1, whereas my host at work showed the source IP 
being the first virtual ethernet card IP. When I run the script on my 
home machine it does seem to send the request to log Joe in which fails, 
which is expected:

./nosrcapp.pl
DBI connect('database=test;host=127.0.0.1','joe',...) failed: Access 
denied for user 'joe'@'localhost' (using password: YES) at ./nosrcapp.pl 
line 8.

Interesting when I try to telnet to the IP of the ethernet on my home 
machine I get the error I get at work:

$ telnet 192.168.1.98 3306
Trying 192.168.1.98...
Connected to 192.168.1.98.
Escape character is '^]'.
EHost '192.168.1.98' is not allowed to connect to this MySQL 
serverConnection closed by foreign host.

And immediately kicks me out like at work.

I think that mySQL by default accepts connections from 127.0.0.1, and as 
you say I then have to tell MySQL to accept Joe from 127.0.0.1 as well. 
So, I think I need to tell MySQL it's also OK to accept connections from 
other IP's not just 127.0.0.1. I'll check out the GRANT command you 
mention to see if that is specifically for users or if I can also grant 
generic access from non-127.0.0.1 hosts and get the same response using 
192.168.1.98 as localhost via telnet.

But, I find it odd that my VM at work uses the IP of the ens33 ethernet 
card as the source IP when I send requests to 127.0.0.1, while my home 
machine uses a source IP of 127.0.0.1 when sending requests to 
127.0.0.1. My home machine seems correct and MySQL by default is OK with 
requests coming from 127.0.0.1, but not requests coming from some other 
source IPs.

Maybe the issue is that my work machine is being used as a router for 
the local VM network and it has Masquerade On set. My home machine does 
not have Masquerade on set. So, even when sending to 127.0.0.1 it is 
masquerading as coming from the IP on ens33.

I can turn masquerade off for a little while when I get back to work and 
see what impact that has.

In the mean time I'll try to figure out if GRANT on  MySQL can enable 
connections from IP's other than 127.0.0.1 here at home and that should 
translate to work.

Thanks
Chris Kottaridis