mysql configuration

[Mike Wright]

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